Why does a BigQuery dry run overestimate bytes for a query on a clustered table?

seniorshould knowfreq 50%

answer

one saving is knowable before running, one is not
the estimate promises a ceiling, not the bill
clustered tables bill less than quoted
byte caps reject cheap queries after clustering
job statistics carry the real figure

basics

~20 s

A dry run reports what BigQuery can determine before execution. Partition elimination is decided then and is reflected, but clustering skips storage blocks during execution, so the estimate assumes no block skipping and is an upper bound on the bytes a clustered table actually bills.

solid answer

~50 s

BigQuery's dry run costs a query from metadata alone: it applies partition pruning, which is a planning-time decision, and reports the resulting bytes. Clustering works differently — rows are sorted within each partition, and BigQuery discards storage blocks whose value ranges cannot match while the query runs. That saving cannot be known in advance, so the dry run quotes the un-skipped total. On a well-clustered table the real bill is often a small fraction of the estimate. Practical consequences: maximum_bytes_billed and pre-flight cost gates built on the estimate will be pessimistic and can reject queries that would have been cheap; and cost dashboards must read the actual figures from job statistics — total_bytes_processed and total_bytes_billed in INFORMATION_SCHEMA.JOBS — not from dry runs. To evaluate a clustering design, run the query and compare billed bytes before and after, never the estimate.

BigQuery's dry run costs a query from metadata alone: it applies partition pruning, which is a planning-time decision, and reports the resulting bytes. Clustering works differently — rows are sorted within each partition, and BigQuery discards storage blocks whose value ranges cannot match **while the query runs**. That saving cannot be known in advance, so the dry run quotes the un-skipped total. On a well-clustered table the real bill is often a small fraction of the estimate. Practical consequences: `maximum_bytes_billed` and pre-flight cost gates built on the estimate will be pessimistic and can reject queries that would have been cheap; and cost dashboards must read the actual figures from job statistics — `total_bytes_processed` and `total_bytes_billed` in `INFORMATION_SCHEMA.JOBS` — not from dry runs. To evaluate a clustering design, run the query and compare billed bytes before and after, never the estimate.

code

bash · 4 lines

bash

bq query --dry_run --use_legacy_sql=false \
  'SELECT SUM(amount) FROM ds.events
   WHERE DATE(event_ts) BETWEEN "2026-08-01" AND "2026-08-07"
     AND tenant_id = "t_9001"'

go deeper

for a junior

Know that a dry run tells you bytes before you spend anything, and that on a clustered table the real cost can come out lower than the quote.

for a middle

Explain the timing difference: partition elimination happens when the plan is built, block skipping happens while the query runs, so only the first is visible to an estimate.

for a senior

Use the right instrument for the job — estimates to catch missing partition filters, job statistics to measure clustering — and recognise byte-cap rejections on clustered tables as this asymmetry rather than a query problem.

for a principal

Decide how cost control is enforced organisation-wide given that pre-flight estimates are pessimistic for clustered tables, and how chargeback reporting sources true billed bytes.

The two data-skipping mechanisms have different timing

BigQuery reduces the bytes a query reads in two ways, and the difference in when each decision is made explains the whole phenomenon.

Partition elimination is static. The set of partitions is table metadata; the planner intersects it with the predicate on the partitioning column and produces the surviving set before any scan begins. Because it happens at planning time, a dry run — which does planning without execution — already accounts for it.

Block skipping from clustering is dynamic. CLUSTER BY sorts rows within a partition, giving each storage block a narrow range of clustering-column values. During execution, BigQuery checks those ranges against the predicate and skips blocks that cannot contain a match. How many blocks that eliminates depends on how well sorted the data currently is and on the specific values being filtered — nothing the planner can compute cheaply up front. So the dry run reports as if none were skipped.

What you actually observe

sql

SELECT SUM(amount)
FROM ds.events                       -- PARTITION BY DATE(event_ts) CLUSTER BY tenant_id
WHERE DATE(event_ts) BETWEEN '2026-08-01' AND '2026-08-07'
  AND tenant_id = 't_9001';

A dry run on this reports the full size of the referenced columns across those seven partitions. The completed job may report a small fraction of that in total_bytes_billed, because most blocks in each partition belong to other tenants and were skipped. The estimate was not wrong — it was an upper bound, which is what it promises to be.

Note that only the columns the query references count in either number. Columnar storage means SELECT SUM(amount) never reads payload, which is a separate and larger effect than either pruning or clustering, and it is reflected in the estimate.

Where it bites in practice

Cost guardrails. Setting maximum_bytes_billed on a job causes BigQuery to reject the query if the estimate exceeds the cap. On a heavily clustered table this rejects queries whose real cost would have been trivial. Teams that adopt clustering and then see previously-fine dashboards start failing their byte caps are hitting exactly this. Either raise the caps for clustered tables or move enforcement to a post-hoc budget review.

Chargeback and forecasting. Any cost dashboard that sums dry-run estimates systematically overstates spend on clustered tables. Read the real numbers instead:

sql

SELECT job_id, user_email,
       total_bytes_processed, total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE job_type = 'QUERY'
  AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
ORDER BY total_bytes_billed DESC;

Evaluating a clustering change. Because the estimate ignores clustering entirely, you cannot A/B a clustering design with dry runs — the before and after estimates are identical. You must execute the query against both layouts and compare billed bytes. Do this on a representative predicate, not a synthetic one: a filter on the leading clustering column will look excellent, and a filter on a non-leading column will look like nothing happened, and both are true.

Diagnosing a disappointing result. If billed bytes barely fall below the estimate on a clustered table, the usual causes are: the predicate does not touch the leading clustering column, so the sort prefix does not help; the filter is a wide range or a low-selectivity value that genuinely appears in most blocks; or a large recent write has left blocks overlapping in value range, which automatic background reclustering will improve over time without any action from you.

Under capacity pricing

If you buy slots rather than paying per byte, the billed-bytes figure stops being the bill — you pay for slot time. Clustering still matters, because reading fewer blocks means less slot time and less contention, but the dry-run byte number becomes a rough proxy for work rather than a price. The asymmetry described here is still worth knowing: it is why an on-demand cost model built on estimates and a capacity cost model built on slot-hours disagree about which query is expensive.

Summary of the rule

Dry run answers "at most how much will this read, given what I can know without running it". Partition pruning is knowable then; clustering is not. Trust the estimate as a ceiling and as a detector of missing partition filters — that is where it is genuinely diagnostic — and use job statistics for anything that needs the true number.

## The two data-skipping mechanisms have different timing BigQuery reduces the bytes a query reads in two ways, and the difference in *when* each decision is made explains the whole phenomenon. **Partition elimination** is static. The set of partitions is table metadata; the planner intersects it with the predicate on the partitioning column and produces the surviving set before any scan begins. Because it happens at planning time, a dry run — which does planning without execution — already accounts for it. **Block skipping from clustering** is dynamic. `CLUSTER BY` sorts rows within a partition, giving each storage block a narrow range of clustering-column values. During execution, BigQuery checks those ranges against the predicate and skips blocks that cannot contain a match. How many blocks that eliminates depends on how well sorted the data currently is and on the specific values being filtered — nothing the planner can compute cheaply up front. So the dry run reports as if none were skipped. ## What you actually observe ```sql SELECT SUM(amount) FROM ds.events -- PARTITION BY DATE(event_ts) CLUSTER BY tenant_id WHERE DATE(event_ts) BETWEEN '2026-08-01' AND '2026-08-07' AND tenant_id = 't_9001'; ``` A dry run on this reports the full size of the referenced columns across those seven partitions. The completed job may report a small fraction of that in `total_bytes_billed`, because most blocks in each partition belong to other tenants and were skipped. The estimate was not wrong — it was an upper bound, which is what it promises to be. Note that only the columns the query references count in either number. Columnar storage means `SELECT SUM(amount)` never reads `payload`, which is a separate and larger effect than either pruning or clustering, and it *is* reflected in the estimate. ## Where it bites in practice **Cost guardrails.** Setting `maximum_bytes_billed` on a job causes BigQuery to reject the query if the *estimate* exceeds the cap. On a heavily clustered table this rejects queries whose real cost would have been trivial. Teams that adopt clustering and then see previously-fine dashboards start failing their byte caps are hitting exactly this. Either raise the caps for clustered tables or move enforcement to a post-hoc budget review. **Chargeback and forecasting.** Any cost dashboard that sums dry-run estimates systematically overstates spend on clustered tables. Read the real numbers instead: ```sql SELECT job_id, user_email, total_bytes_processed, total_bytes_billed FROM `region-us`.INFORMATION_SCHEMA.JOBS WHERE job_type = 'QUERY' AND creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) ORDER BY total_bytes_billed DESC; ``` **Evaluating a clustering change.** Because the estimate ignores clustering entirely, you cannot A/B a clustering design with dry runs — the before and after estimates are identical. You must execute the query against both layouts and compare billed bytes. Do this on a representative predicate, not a synthetic one: a filter on the leading clustering column will look excellent, and a filter on a non-leading column will look like nothing happened, and both are true. **Diagnosing a disappointing result.** If billed bytes barely fall below the estimate on a clustered table, the usual causes are: the predicate does not touch the leading clustering column, so the sort prefix does not help; the filter is a wide range or a low-selectivity value that genuinely appears in most blocks; or a large recent write has left blocks overlapping in value range, which automatic background reclustering will improve over time without any action from you. ## Under capacity pricing If you buy slots rather than paying per byte, the billed-bytes figure stops being the bill — you pay for slot time. Clustering still matters, because reading fewer blocks means less slot time and less contention, but the dry-run byte number becomes a rough proxy for work rather than a price. The asymmetry described here is still worth knowing: it is why an on-demand cost model built on estimates and a capacity cost model built on slot-hours disagree about which query is expensive. ## Summary of the rule Dry run answers "at most how much will this read, given what I can know without running it". Partition pruning is knowable then; clustering is not. Trust the estimate as a ceiling and as a detector of missing partition filters — that is where it is genuinely diagnostic — and use job statistics for anything that needs the true number.

Does the dry run reflect partition pruning?
Yes, fully. Partition elimination is resolved when the plan is built from table metadata and the predicate, so the estimate already excludes pruned partitions. That is why a dry run is the right instrument for catching a missing or unprunable partition filter — an unexpectedly huge estimate on a partitioned table means the filter is not doing its job.
How do you measure whether a new clustering design actually helped?
Run the real query against both layouts and compare `total_bytes_billed` from job statistics or `INFORMATION_SCHEMA.JOBS`. Dry runs are useless here because they ignore clustering, so before and after estimates are identical. Use production-representative predicates, since a filter on the leading clustering column and one on a trailing column give very different results.
Why might a query set maximum_bytes_billed and fail on a clustered table that is cheap in practice?
That control is enforced against the pre-execution estimate, which does not credit block skipping. A query whose real cost is a fraction of the ceiling can still be rejected because its estimate exceeds it. Raise the limit for clustered tables, or shift cost enforcement to monitoring billed bytes after the fact rather than gating on estimates.

saying these in an interview costs you the question

Believing the dry run is the exact price
Assuming pruning is also invisible to the estimate
Comparing dry-run estimates to evaluate a clustering change
Concluding clustering is broken because the estimate did not fall
Building chargeback dashboards from estimated bytes

Why does a BigQuery dry run overestimate bytes for a query on a clustered table?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The two data-skipping mechanisms have different timing

What you actually observe

Where it bites in practice

Under capacity pricing

Summary of the rule

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions4 questions

▾related topics4 topics

The two data-skipping mechanisms have different timing

What you actually observe

Where it bites in practice

Under capacity pricing

Summary of the rule

answer

go deeper

context