Warehouse Performance & Cost

A correct query can still be a bad query. On a cloud warehouse, the same answer can cost a few cents or many dollars depending on how much data it scans — and the difference is almost entirely under your control.

This chapter is about that control: how columnar storage, partitioning, and clustering decide what gets read, how to read a query plan, and how to reason about scan cost the way a warehouse bills it.

The mental shift is from “does it run?” to “how much does it read?”. On engines like BigQuery, Snowflake, and Redshift, bytes scanned — not rows returned — is the unit of both time and money.

To keep everything runnable, the Practice Lab works on two small metadata tables you would really query to tune a warehouse: table_partitions (per-partition row and byte counts) and query_runs (a query-history log with bytes scanned and partitions touched).

columnar storage

Data stored column-by-column, so a query only reads the columns it names.

partition

A physical slice of a table (often one per day) that can be skipped if a filter excludes it.

pruning

Skipping partitions or columns the query cannot possibly need, so they are never read.

bytes scanned

The amount of data a query reads — the basis of on-demand cost and most of its runtime.

-- both touch all 8 partitions, but one reads far more data
select
  query_label,
  partitions_scanned,
  gb_scanned
from query_runs
where query_id in (1, 2)
order by query_id;

Analytical warehouses store data column-by-column, not row-by-row. Each column lives in its own compressed blocks, so a query only reads the columns it actually names.

That is why SELECT * is expensive: it forces the engine to read every column, including wide ones like JSON payloads, even if you only display two. Naming the columns you need is the cheapest optimization there is.

Column pruning is automatic once you stop asking for everything. The skill is simply the discipline to select the narrow set of columns the question requires.

-- gb per partition is high when a query reads many (wide) columns
select
  query_id,
  query_label,
  printf('%.2f', gb_scanned * 1.0 / partitions_scanned) as gb_per_partition
from query_runs
order by gb_scanned * 1.0 / partitions_scanned desc, query_id;

Partitioning splits a table into physical slices, usually one per day on an event or fact table. Each partition can be read or skipped independently, which is what makes pruning possible.

You choose a partition key — most often a date column like event_day — when the table is created. A filter on that key then lets the engine touch only the matching partitions.

Partitioning is a storage-layout decision, not a query you write each time. Its payoff shows up in every later query that filters on the partition key.

-- BigQuery: partition an events table by the event day
create table events (
  event_id   int64,
  user_id    int64,
  event_time timestamp,
  payload    json
)
partition by date(event_time);

-- Snowflake uses clustering keys instead; Postgres uses
-- declarative partitions (partition by range on a date column).

select
  partition_day,
  size_mb
from table_partitions
where partition_day between '2026-01-03' and '2026-01-05'
order by partition_day;

Pruning is the engine skipping partitions a filter cannot match. A clean predicate on the partition key — partition_day BETWEEN x AND y — lets it read only the needed slices.

Pruning is fragile: wrapping the partition key in a function, or comparing it to a non-constant, can force a full scan because the engine can no longer reason about which partitions match.

The habit is to filter the raw partition key with constant bounds. date(partition_col) = … or substr(partition_col, …) often defeats pruning even though the query still returns the right answer — just much more slowly and expensively.

One clarification that trips people up: only the WHERE clause decides pruning. The same date() call in SELECT or GROUP BY is free, and a table declared as partition by date(event_time) still prunes perfectly from a plain range filter on event_time itself — the function in the DDL is not the problem, a function around the key in the predicate is.

-- how much is read vs how much exists
select
  count(*)            as partitions_scanned,
  sum(size_mb)        as mb_scanned,
  (select count(*)    from table_partitions) as partitions_total,
  (select sum(size_mb) from table_partitions) as mb_total
from table_partitions
where partition_day between '2026-01-03' and '2026-01-05';

-- BAD: wrapping the partition key in a function reads every partition
select count(*)
from events
where substr(cast(event_day as string), 1, 7) = '2026-01';

-- GOOD: a direct range keeps the key prunable
select count(*)
from events
where event_day >= '2026-01-01'
  and event_day <  '2026-02-01';

Clustering (or sort keys) physically orders rows within a partition by a column you filter on, like country or customer_id. Every storage block records the smallest and largest value it holds, so once matching values sit together the engine can read that range and skip whole blocks without ever opening them — a second layer of pruning below the partition level.

Clustering helps most for high-cardinality columns you filter or join on frequently — the ones partitioning cannot handle. A filter on a clustered column reads only the blocks where those values live. Order the key by how you actually query: a key of (country, user_id) serves a filter on country, or on both, far better than one on user_id alone.

Skew is the enemy of both: one giant partition (a hot day) or one giant cluster value processes far more data than the rest and becomes the slow straggler that dominates runtime.

-- BigQuery: partition by day, then order rows inside each day
create table events (
  event_id   int64,
  user_id    int64,
  country    string,
  event_time timestamp,
  payload    json
)
partition by date(event_time)
cluster by country, user_id;   -- up to 4 columns; the order matters

-- Snowflake: micro-partitions already exist, you only declare the key
alter table events cluster by (country, user_id);

-- Redshift: a compound sort key plays the same role
create table events (...) compound sortkey (country, user_id);

-- block_stats is a zone map: one row per storage block, with the
-- smallest and largest country value that block holds.
select
  layout,
  count(*) as blocks_total,
  sum(case when 'US' between min_country and max_country then 1 else 0 end)
    as blocks_read
from block_stats
group by layout
order by blocks_read;

-- a partition is "hot" when its row_count is above the average
select
  partition_day,
  row_count,
  case
    when row_count > (select avg(row_count) from table_partitions)
    then 'hot' else 'normal'
  end as skew_flag
from table_partitions
order by row_count desc, partition_day;

A query plan is the engine’s step-by-step recipe: scan these partitions, apply this filter, join in this order, then aggregate. Reading it tells you where the time and bytes actually go.

Read it bottom-up. The leaves are table scans, and work flows upward through filters, joins, and aggregations to the final output. The scan is where cost concentrates when pruning failed — but once a scan is well pruned, the joins and shuffles above it are usually what dominate.

You are looking for three things: how much each scan reads, whether a join explodes the row count, and where the optimizer’s estimate diverged from what actually happened. A large gap between estimated and actual rows is the usual root cause of a plan that went wrong.

-- plan_steps is one row per step from the engine's profile:
-- what the optimizer estimated, and what actually happened.
select
  step,
  operator,
  round(rows_actual / nullif(rows_est, 0), 1) as est_error_x,
  bytes_gb,
  spill_gb
from plan_steps
where bytes_gb + spill_gb > 0
order by bytes_gb + spill_gb desc;

On on-demand pricing, a query’s cost is a direct function of the bytes it scans — independent of how many rows it returns. Read 1 TB, pay for 1 TB, whether the result is one row or a million.

That makes cost estimable before you run: bytes scanned × the per-byte rate. Most warehouses even give you a dry-run estimate so you can see the bill before committing.

Not every warehouse bills this way. BigQuery on-demand charges for bytes read; Snowflake and Databricks charge for how long the compute runs (warehouse size × seconds). Under bytes-billed pricing, reading less is directly cheaper; under compute-billed pricing, reading less finishes sooner, and the clock is the bill. Either way the lever is identical — scan less — which is why every technique in this chapter is also a cost technique.

-- assume $5 per TB for round numbers: cost = gb_scanned / 1024 * 5
select
  query_id,
  query_label,
  gb_scanned,
  printf('%.2f', gb_scanned / 1024.0 * 5) as cost_usd
from query_runs
order by gb_scanned desc, query_id;

A full scan reads every partition of a table. It is not itself a bug — it is a symptom, and the same symptom has two completely different causes. Either a predicate failed to prune, or the query genuinely needs the whole history. Telling those apart is the skill this topic is about.

The detection is arithmetic. Warehouses log how many partitions each query touched, and you know how many the table has. If a query touched all of them, it skipped nothing. Dividing the two gives a pruning ratio: 0.13 means the query read one drawer of eight, and 1.00 means it opened every one.

Once a query is flagged, the fork is what matters. If the filter is broken, the repair belongs to Partition Pruning (And What Defeats It) — rewrite the predicate so the key stays comparable to constants. But if the query really does need every partition, no predicate will save it, and the only remaining lever is to stop reading the raw table at all: pre-aggregate it once, and let consumers read the summary.

-- pruning ratio: 1.00 means the query skipped nothing at all
select
  query_label,
  partitions_scanned,
  (select count(*) from table_partitions) as partitions_total,
  printf('%.2f', partitions_scanned * 1.0
                 / (select count(*) from table_partitions)) as pruning_ratio
from query_runs
order by pruning_ratio desc, query_id;

-- roll the raw events up to one row per day per country,
-- then point dashboards at the summary instead of the raw table
create table events_daily as
select
  event_day,
  country,
  count(*) as events,
  count(distinct user_id) as users
from events
group by event_day, country;

Warehouses log every query with how much it scanned and how long it took. That history is itself a table you can query — and it is where cost monitoring lives.

Two reports cover most of the value: the most expensive queries (rank by bytes scanned) and cost by owner (sum bytes per analyst or service account). Together they tell you what to fix and who to talk to.

The point is to make cost visible and attributable. A recurring expensive query you can name is a query you can optimize or schedule down.

-- sum bytes per owner, then convert to dollars at $5 per TB
select
  run_by,
  sum(gb_scanned) as gb_scanned,
  printf('%.2f', sum(gb_scanned) / 1024.0 * 5) as estimated_cost_usd
from query_runs
group by run_by
order by sum(gb_scanned) desc, run_by;

A view is a saved query. It stores no data and costs nothing until somebody selects from it, at which point the engine substitutes the definition and plans the whole thing as one query.

A materialized view stores the result. Reads are fast because the work is already done, and the price is staleness plus a refresh you have to own.

The decision between them is not about speed. It is about whether you can tolerate a stale answer, and who is on the hook when the refresh fails.

-- A view: no storage, no staleness, full cost on every read
create view paid_orders as
select order_id, buyer_id, total_amount, created_at
from orders
where status = 'paid';

-- A materialized view: storage, staleness, cheap reads
create materialized view daily_paid_revenue as
select date(created_at) as day,
       count(*)         as orders,
       sum(total_amount) as revenue
from orders
where status = 'paid'
group by date(created_at);

Most warehouse savings come from a handful of habits applied consistently, not from exotic tricks. Select the columns you need, filter the partition key, and avoid reading raw history when a summary exists.

When the same heavy aggregation runs repeatedly, materialize it — a scheduled table or materialized view turns a daily full scan into a one-time cost that every consumer then reads cheaply.

These habits compound. A query that prunes columns and partitions and reads a pre-aggregate can be a hundred times cheaper than the naive version that returns the identical numbers.

-- expensive: raw scan, all columns, no filter
-- select * from events;

-- cheaper: only needed columns, partition filter, grouped
select
  date(event_time) as event_day,
  count(*)         as events
from events
where event_time >= '2026-01-03'
  and event_time <  '2026-01-06'
group by date(event_time)
order by event_day;

Every fix costs something. It costs your time to write, and it costs everyone else’s time to read for as long as the query lives. So the real question is not “can this be faster?” — almost anything can. It is “will the saving be bigger than the fix?”

To answer that you need one number: how much a query reads in a day. Not how much it reads in one run. A 240 GB export that runs once a day reads 240 GB. An 85 GB dashboard that refreshes every hour reads 2,040 GB — over eight times more — even though each run looks three times smaller. Judge by the daily total and you will pick a different query than if you judge by size alone.

This is why a big, wasteful query can be safe to leave alone. If it runs once a quarter, cleaning it up saves a few cents a year, and leaves behind a more complicated query that someone has to maintain forever. That is a bad trade. Spend the effort where the daily total is large.

One warning before you tune anything. A clever rewrite that returns the wrong number is worse than a slow query that returns the right one. Make it correct first, then make the expensive ones cheap.

-- what a query really costs in a day = one run x how many runs
select
  query_label,
  gb_scanned,
  runs_per_day,
  gb_scanned * runs_per_day as gb_per_day
from query_runs
order by gb_per_day desc, query_id;

Warehouse cost bugs are predictable: SELECT * on wide tables, a missing or broken partition filter, a function hiding the partition key, and re-scanning raw history that should have been pre-aggregated.

The cure is the same discipline each time — name your columns, filter the raw partition key, confirm the scan size dropped, and materialize what many queries reuse.

-- counts the same events, but reads two columns and three partitions
select
  date(event_time) as event_day,
  count(*)         as events
from events
where event_time >= '2026-01-03'
  and event_time <  '2026-01-06'
group by date(event_time)
order by event_day;

These six problems make warehouse tuning concrete on two metadata tables you would really query: table_partitions (per-partition rows and bytes) and query_runs (a query-history log of bytes scanned and partitions touched).

Treat each as a cost review: state what you are measuring, write the query, run it to read the numbers, then submit for scored feedback. Predict which queries or partitions will stand out before you run.

-- most cost queries rank or summarise the history / partition metadata
select
  query_id,
  query_label,
  gb_scanned
from query_runs
order by gb_scanned desc, query_id
limit 3;