Warehouses at scale

How the warehouse runs a query

Modern warehouses separate storage (columnar files in object storage, with metadata about each block: min/max per column, row counts) from compute (clusters of workers that scan, filter, join and aggregate in parallel). A query is planned into stages; each stage runs across many workers on slices of the data; between stages, data is shuffled (redistributed by key) so that rows that must meet — the two sides of a join, the members of a group — end up on the same worker. Cost and time follow from three quantities: bytes scanned from storage, bytes shuffled between workers, and the size of the largest single-worker operation.

Because storage is columnar and block metadata exists, the planner can skip whole blocks whose min/max exclude the filter (pruning) and read only the referenced columns (projection). This is why SELECT * over a wide table and a filter on an unpartitioned column are the two most expensive habits: the first reads every column, the second reads every block. Everything in this module is about making the planner able to skip.

Partitioning and clustering

Partitioning splits a table into physical segments by a column, almost always a date or timestamp truncated to day. A query filtering on that column reads only the matching partitions; everything else is not touched or billed. Clustering (sort keys in Redshift, clustering keys in Snowflake and BigQuery) orders data within partitions by one or more columns so block min/max ranges are tight and pruning works on those columns too. Partition on the column every query filters by time; cluster on the two or three columns most often used in filters and joins, highest selectivity first.

-- BigQuery: partition by day, cluster by up to four columns; require the partition filter
CREATE TABLE analytics.fct_events
(
  event_id STRING, user_id INT64, event_name STRING, event_at TIMESTAMP, country STRING, properties JSON
)
PARTITION BY DATE(event_at)
CLUSTER BY event_name, user_id
OPTIONS (require_partition_filter = TRUE, partition_expiration_days = 730);

-- Snowflake: micro-partitions are automatic; declare a clustering key for large tables
CREATE TABLE analytics.fct_events (
  event_id STRING, user_id NUMBER, event_name STRING, event_at TIMESTAMP_NTZ, country STRING, properties VARIANT
)
CLUSTER BY (TO_DATE(event_at), event_name);

Pruning only works when the query's filter is on the partition or cluster column in a form the planner recognises: WHERE event_at >= '2026-03-01', not WHERE DATE(event_at) = ... wrapped in a function the engine cannot push down (BigQuery handles DATE(event_at) on a day-partitioned table; many engines do not handle arbitrary functions). Check the query's estimated bytes or the profile's "partitions scanned" before and after; pruning is measurable. Setting require_partition_filter turns the unbounded scan into an error, which is the right default for large event tables.

Reading a query profile

Every warehouse shows a profile: stages, rows in and out, bytes scanned, shuffle volume, time per stage and skew between workers. The recurring findings: a full scan where a partition filter was expected; a join explosion where output rows far exceed both inputs (a fan-out, from the SQL module); skew, where one worker gets most rows for a hot key (a NULL customer id, a default value) and everyone waits for it; spilling, where a sort or aggregation exceeds memory and writes to disk; and late filtering, where a WHERE that could have run on the scan runs after a join instead.

-- BEFORE: full scan of events, fan-out on a non-unique dimension, skew on NULL user_id
SELECT e.event_name, u.plan, COUNT(*)
FROM analytics.fct_events e
LEFT JOIN analytics.user_plans u ON u.user_id = e.user_id      -- user_plans has one row per plan change
WHERE u.plan = 'pro' AND DATE(e.event_at) BETWEEN '2026-03-01' AND '2026-03-07'
GROUP BY 1, 2;

-- AFTER
WITH current_plan AS (
  SELECT user_id, plan
  FROM analytics.user_plans
  QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY changed_at DESC) = 1   -- one row per user
),
events AS (
  SELECT user_id, event_name
  FROM analytics.fct_events
  WHERE event_at >= TIMESTAMP '2026-03-01' AND event_at < TIMESTAMP '2026-03-08'   -- prunes partitions
    AND user_id IS NOT NULL                                                        -- drop the hot key early
)
SELECT e.event_name, p.plan, COUNT(*)
FROM events e
JOIN current_plan p USING (user_id)
WHERE p.plan = 'pro'
GROUP BY 1, 2;

Read profiles in the same order every time: bytes scanned per table (pruning), rows out of each join versus rows in (fan-out), max versus average time per worker (skew), and spill indicators. Most slow queries are fixed by moving a filter earlier, deduplicating a join side, or adding the partition column to the WHERE clause.

Precompute what is asked repeatedly

A dashboard that runs the same aggregation over a year of events every time someone opens it should read a small table instead. Views save no work; they are query text. Materialised views store the result and refresh incrementally or on schedule, and some engines rewrite queries to use them automatically. Aggregate tables built by the transformation layer (daily revenue by region, weekly active users) are the explicit version, with the advantage that the definition is one dbt model that everyone uses. The result cache returns identical queries for free within a window; BI tools that add a timestamp to every query defeat it.

Compute, isolation and the bill

Compute is where the money goes. Size clusters to the workload rather than the peak: a warehouse that scales from zero and suspends after a minute idle costs a fraction of one left running. Isolate workloads — a cluster for scheduled transformations, another for dashboards, another for ad hoc analysis — so a heavy analyst query cannot slow the executive dashboard, and so each bill has an owner. Set quotas and limits: per-query byte caps, per-user daily budgets, statement timeouts. Then monitor: the most expensive queries by user and by dashboard, bytes scanned per day per table, and clusters that never suspend.

SELECT
  user_name,
  warehouse_name,
  COUNT(*)                                       AS queries,
  ROUND(SUM(total_elapsed_time) / 1000 / 3600, 1) AS hours,
  ROUND(SUM(bytes_scanned) / POWER(1024, 4), 2)   AS tb_scanned,
  ROUND(SUM(credits_used_cloud_services), 2)      AS cloud_credits
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY tb_scanned DESC
LIMIT 20;

• Auto-suspend at 60 seconds and auto-resume; a cluster running overnight for nothing is the most common waste.
• Right-size before scaling out; a larger cluster finishes a single big query faster, more clusters handle more concurrent small ones.
• Byte caps on ad hoc access (maximum_bytes_billed or statement timeouts) so a missing filter cannot cost a month's budget.
• Tag queries and clusters with team and purpose; attribute cost weekly.
• Expire old partitions and archive cold data to cheaper storage; retention is a cost control as well as a privacy one.

Warehouse cost incidents look like security incidents: a small mistake, amplified by scale, discovered late. The controls are the same shape — limits, isolation, monitoring, alerts on anomalies — and the FinOps module in the DevOps track applies directly.