Cloud Data Warehouses¶

Time: 70 minutes reading + 30 minutes decision exercise
Prerequisites: columnar storage, Trino, data modelling
Outcomes: explain what BigQuery, Snowflake, and Redshift actually do with a query and a byte on disk; predict which one queues, which one scans everything, and which one skews; compare managed warehouses with lakehouse/query-engine stacks; choose from workload evidence, not the vendor slide.

Monday, 9 AM. A finance analyst kicks off the same GROUP BY region query as every week — same SQL, same data volume as last Monday.

Guess which failure belongs to which vendor before you read on:

A. Same query, bigger bill this time. B. Same query, but it queues for ten minutes behind Friday's batch load. C. Was instant for years; now one table has outgrown a node's memory, and every query touching it starts spilling to disk together.

Match them up, then check: on BigQuery it costs money per byte scanned whether the warehouse is busy or idle (A). On Snowflake it queues behind Friday's batch load if the warehouse is undersized (B). On Redshift it is fast for years, then one day a table grows past the machine's memory and every query holding a hash table for that table starts spilling to disk together (C).

None of that is a vendor defect. It is the architecture showing through the SQL. BigQuery, Snowflake, and Redshift are not interchangeable implementations of Trino wearing different logos — they package storage, execution, governance, workload management, and operations behind three different physical models and three different commercial contracts.

Start with the situation¶

Same workload as the rest of this module: SaaS analytics, hundreds of millions of events per day, an analyst who wants p95 latency by plan tier this afternoon — except now the company decided it does not want to operate Iceberg compaction, a catalog, and a Trino cluster. It wants to COPY INTO or stream the events into a managed warehouse and let the vendor own storage layout, scaling, and the 2 a.m. pages.

SELECT plan, APPROX_PERCENTILE(latency_ms, 0.95) AS p95
FROM analytics.events
WHERE event_date BETWEEN '2024-06-01' AND '2024-06-07'
GROUP BY plan;

The SQL is nearly identical across BigQuery, Snowflake, and Redshift. What happens underneath it — what gets scanned, what gets billed, what queues, what skews — is not.

Why the obvious approach breaks¶

Three vendors sell "just write SQL," and each one hides a different failure mode behind that promise.

• BigQuery bills (on-demand) by bytes scanned, not by time. A SELECT * on a wide table with no partition filter is a cost incident, not a performance incident — the query may finish in seconds and still cost more than a day of Snowflake compute.
• Snowflake separates storage from compute into virtual warehouses, but a warehouse is a fixed-size cluster you provision. Undersize it and queries queue behind each other; oversize it and you pay for idle credits.
• Redshift is the most "you own it" of the three: distribution keys, sort keys, and vacuum/analyze are your responsibility even though AWS runs the boxes. Get the distribution key wrong and one node does the work of the whole cluster while the others idle.

The trap is treating "managed" as "the physics went away." It didn't. It moved into billing dimensions and configuration knobs that look optional and are not.

Build the mental picture¶

Think of each warehouse as the same three ingredients — columnar storage, a cost-based optimizer, distributed execution — assembled around a different scarce resource:

Every gotcha in this page is a symptom of forgetting which resource you are actually spending.

Under the hood¶

BigQuery: Dremel over Colossus¶

Storage (Colossus, Google's distributed file system) and compute (Dremel, the query execution engine) are fully separate services, elastic independently, with no persistent cluster you provision.

• Tables are stored in Capacitor, a columnar format, sharded across Colossus. You never see files or manage compaction directly.
• A query runs as a tree of mixers and leaf nodes: leaf nodes read columnar shards in parallel, mixers merge partial results up the tree — the same shuffle-tree shape as Trino's stages, but Google-operated and typically backed by an in-memory shuffle tier rather than local disk.
• Compute is measured in slots (a bundle of CPU, RAM, and IO). On-demand pricing bills bytes scanned and Google allocates slots dynamically; capacity pricing (editions/reservations) buys a pool of slots you share across queries and bills slot-time regardless of bytes.
• Pruning comes from partitioning (by ingestion time or a date/timestamp column — at most one partition column) and clustering (up to four columns that sort storage blocks so BigQuery can skip blocks by min/max, similar to a ClickHouse granule skip).

Snowflake: micro-partitions and virtual warehouses¶

• On load, Snowflake automatically slices data into micro-partitions: immutable, compressed, columnar blocks of roughly 50–500 MB uncompressed. Every micro-partition carries per-column min/max metadata.
• A separate cloud services layer holds that metadata and does pruning before any compute cluster touches storage — this is why SHOW / metadata-only queries can return instantly with zero warehouse running.
• Compute is a virtual warehouse: an independent MPP cluster, T-shirt sized (XS doubles to S doubles to M …), that reads shared, decoupled cloud storage. Multiple warehouses can read the same tables with no contention; multi-cluster warehouses auto-scale out (not up) to absorb concurrent queries instead of queueing them.
• Clustering keys are optional and reorder micro-partitions over time via an automatic background service (automatic clustering) that burns credits — unlike BigQuery's clustering, which is free storage layout, Snowflake reclustering is metered compute.
• A 24-hour result cache and per-warehouse local SSD cache mean the fastest query is often the one you already ran.

Redshift: MPP, slices, distribution, and sort keys¶

• Classic Redshift is shared-nothing MPP: a leader node plans and distributes work; each compute node splits into slices (one slice per vCPU core), and each slice owns a physical subset of every table's rows.
• Distribution style decides which slice a row lives on: KEY (hash a column — co-locates matching join keys, the highest-leverage and highest-risk choice), ALL (replicate small dimension tables to every node, avoiding a shuffle), EVEN (round-robin, safe default, no join locality).
• Sort key (compound or interleaved) decides on-disk row order per slice, enabling zone-map pruning — the Redshift analogue of ClickHouse's ORDER BY or Iceberg's file-level min/max stats.
• RA3 nodes decouple storage from compute (managed storage backed by S3-like infrastructure with a local SSD cache), closing much of the gap with BigQuery/Snowflake's separation — but distribution keys and sort keys are still yours to choose and still physically place data.
• Redshift Spectrum lets Redshift compute query external tables directly in S3 — architecturally the same federation idea as Trino, scoped to Redshift's optimizer.
• Concurrency scaling spins up transient additional clusters for read queries when the main cluster queues; it does not fix a bad distribution key.

One table, three physical fates¶

Put it to work¶

BigQuery: dry-run before you run¶

-- Always dry-run an unfamiliar query first — it's free and shows bytes billed
-- bq query --dry_run --use_legacy_sql=false 'SELECT ...'

SELECT plan, APPROX_QUANTILES(latency_ms, 100)[OFFSET(95)] AS p95
FROM `project.analytics.events`
WHERE event_date BETWEEN '2024-06-01' AND '2024-06-07'  -- partition filter: required
GROUP BY plan;

-- What did this actually cost, and did it use a partition filter?
SELECT job_id, total_bytes_billed, query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
ORDER BY total_bytes_billed DESC
LIMIT 20;

Snowflake: size the warehouse to the query, not the other way around¶

USE WAREHOUSE analytics_wh;

SELECT plan, APPROX_PERCENTILE(latency_ms, 0.95) AS p95
FROM analytics.events
WHERE event_date BETWEEN '2024-06-01' AND '2024-06-07'
GROUP BY plan;

-- Is this table worth a clustering key, or is metadata pruning already enough?
SELECT SYSTEM$CLUSTERING_INFORMATION('analytics.events', '(event_date)');

-- Is a warehouse queueing instead of scaling?
SELECT * FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
  DATE_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())));

Redshift: read the plan for a broadcast or a skew, not just a row count¶

EXPLAIN
SELECT plan, PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95
FROM analytics.events
WHERE event_date BETWEEN '2024-06-01' AND '2024-06-07'
GROUP BY plan;

-- Skew and unsorted percentage, table by table
SELECT "table", skew_rows, unsorted, size
FROM svv_table_info
ORDER BY skew_rows DESC;

-- Did any step spill or broadcast unexpectedly?
SELECT query, step, rows, workmem, is_diskbased
FROM svl_query_summary
WHERE query = pg_last_query_id();

Where teams get caught¶

How it fails¶

How to investigate¶

The three vendors expose the same three questions through different system views — learn the question, not just the syntax.

1. What did this query actually touch? BigQuery: INFORMATION_SCHEMA.JOBS_BY_PROJECT.total_bytes_billed. Snowflake: QUERY_HISTORY.bytes_scanned / EXPLAIN USING TEXT. Redshift: EXPLAIN plan + svl_query_summary.rows.
2. Was it parallel or did it bottleneck on one unit? BigQuery: per-stage timing in the query execution details UI. Snowflake: QUERY_HISTORY execution profile, look for one operator dominating wall time. Redshift: svv_table_info.skew_rows and per-slice timing in svl_query_summary.
3. Was it queued behind something else? BigQuery: reservation slot contention in INFORMATION_SCHEMA.JOBS_TIMELINE. Snowflake: WAREHOUSE_LOAD_HISTORY queued-vs-running load. Redshift: WLM queue wait time in stl_wlm_query.

If you cannot answer all three for your slowest query this week, you are guessing at a capacity decision, not making one.

Scale 10× / 100× / 1000׶

Assume today's baseline: ~10 TB in the warehouse, a dozen analysts, a nightly load plus intraday dashboards.

None of the three warehouses' elasticity substitutes for physical design. Elastic compute makes a bad layout expensive faster, not correct.

Trade-offs¶

A managed warehouse is a good default for a team that wants SQL analytics without operating a lake stack. It is a bad default once multiple engines (ML training, streaming, ad-hoc Spark) need the same bytes — that is when the "just copy it out" tax starts compounding.

Alternatives¶

Apply¶

You are looking at a managed-warehouse decision when:

1. The team operating the platform is small and does not want to run compaction, a catalog, or a query-engine cluster.
2. The workload is governed BI/SQL analytics, not multi-engine ML or sub-second serving.
3. Vendor SQL, procedures, and semi-structured types are an acceptable one-way door for this workload.

Then: pick the warehouse whose scarce resource matches your actual pain — bytes-scanned unpredictability (avoid pure on-demand BigQuery at large unfiltered-scan risk), concurrency queueing (Snowflake multi-cluster), or physical layout ownership (Redshift, if you're prepared to own distkey/sortkey). Put a cost guard in from day one — maximum_bytes_billed, a resource monitor, or a WLM queue limit — before the first accidental full scan, not after.

Related: Trino, columnar storage, cost engineering, SaaS analytics architecture.

Check your understanding¶

A 12-person company has two data engineers, 20 TB in the warehouse, spiky weekday BI traffic (idle nights/weekends), and no ML engine requirement. Compare a managed warehouse with Trino + Iceberg. Then decide which managed warehouse fits best and name the first metric you'd watch to know you chose wrong.