How do you estimate what a BigQuery query will scan before running it?
answer
- ask the planner, do not run the query
- the console validator already shows it
- a flag on the CLI, a field in the API
- free, creates no job
- clustered tables estimate high
basics
~10 sRun it as a BigQuery dry run: the console query validator, bq query --dry_run, or dryRun: true in the API. It returns estimated bytes processed without executing the query and without charge.
solid answer
~50 sA **dry run** asks BigQuery to plan the query but not execute it. In the console the validator shows "this query will process N bytes when run"; from the CLI it is `bq query --dry_run --use_legacy_sql=false '<sql>'`; through the API you set `dryRun: true` on the job configuration and read `totalBytesProcessed` from the returned statistics. It creates no job and costs nothing, which makes it safe to wire into CI or a pre-commit check on generated SQL. Multiply the byte estimate by the on-demand rate for the region to get money. Two caveats matter: for a clustered table the estimate is an **upper bound**, because block pruning happens at run time; and pruning is only reflected when the partition filter is a constant the planner can evaluate, so a filter built from a subquery estimates as a full scan. To make the estimate binding rather than advisory, set `maximum_bytes_billed` on the real job.
code
bash · 4 linesbq query --dry_run --use_legacy_sql=false \
'SELECT user_id, event_name
FROM `proj.ds.events`
WHERE event_date BETWEEN "2026-08-01" AND "2026-08-07"'go deeper
Know that BigQuery shows an estimate before you run anything — the console validator line, or bq query --dry_run — and that reading it before pressing Run is the expected habit.
Explain that a dry run plans without executing, returns totalBytesProcessed, costs nothing, and over-estimates for clustered tables and for filters the planner cannot evaluate.
Show where you put it in practice: a CI check on generated SQL with a byte threshold, paired with maximum_bytes_billed at run time so an escaped mistake fails instead of billing.
Frame estimation as part of a cost-governance loop — pre-merge estimates, run-time ceilings, per-team quotas and after-the-fact attribution — rather than a habit you hope analysts adopt.
## What a dry run is A dry run is a request to BigQuery to parse, resolve and plan a query without executing it. BigQuery returns job statistics — most importantly `totalBytesProcessed` — and then throws the plan away. No job is created, no data is read, and nothing is billed. It is the standard answer to "how much will this cost?", and it is the only answer that does not involve spending money to find out. ## The three ways to run one **Console.** The query validator in the BigQuery UI dry-runs continuously as you type and displays the estimate above the editor. Analysts should be taught to read that line before pressing Run. **CLI.** `bq query --dry_run --use_legacy_sql=false 'SELECT ...'` prints the validation message with the byte count. This is the form that goes into scripts. **API/client libraries.** Set `dryRun: true` in the query job configuration (`QueryJobConfig(dry_run=True)` in Python, `DryRun` in other clients) and read `totalBytesProcessed` off the returned statistics. Because it is free and fast, this is what belongs in a CI check that fails a pull request when a generated model would scan more than an agreed threshold. ## Turning bytes into money On-demand cost is bytes processed times the published per-TiB rate for the region, so the conversion is arithmetic. Do not hard-code a rate in tooling that outlives the quarter; rates and regional differences change. Under capacity (slot) pricing the byte estimate is not a price at all — you pay for slot time — but it is still a useful proxy for how much work the query will do and how long it will hold slots. ## Where the estimate is wrong, and in which direction **Clustered tables: upper bound.** Clustering prunes blocks using values the planner does not evaluate until the query runs, so a dry run reports what would be read without that pruning. Real cost comes in at or below the estimate. Teams that cluster aggressively often see estimates several times the actual bill, which is confusing unless you know the rule. **Partition pruning depends on the predicate shape.** A constant filter such as `WHERE event_date BETWEEN '2026-08-01' AND '2026-08-07'` is evaluated at planning time and shrinks the estimate. A filter whose value comes from a subquery, a join, or a non-deterministic function cannot be resolved at planning time, so the dry run assumes the whole table — even though the executed query may prune dynamically. That is the second source of over-estimates. **The result cache is not modelled.** If the identical query has run recently against unchanged tables, the real run is served from cache and bills zero, while the dry run still reports the full byte count. The estimate is a worst case, not a forecast of your invoice. **Tables move.** The estimate reflects the table as of now. A daily-partitioned event table that grows steadily makes the same SQL more expensive every week, so a threshold that passed in CI last month may not reflect production today. ## Making the estimate binding A dry run informs; it does not prevent. The enforcement mechanism is `maximum_bytes_billed`, set per job: ```bash bq query --use_legacy_sql=false --maximum_bytes_billed=100000000000 \ 'SELECT user_id FROM `proj.ds.events` WHERE event_date = "2026-08-01"' ``` If BigQuery determines the query would exceed the limit, the job fails immediately and nothing is billed. The pairing is the standard pattern: dry-run in review and CI to catch the problem early, `maximum_bytes_billed` at run time so a mistake that slips through fails loudly instead of quietly costing money. ## What a dry run does not tell you It gives no slot estimate, no runtime estimate and no plan shape. A query that reads 50 GB but shuffles badly can be slow and slot-hungry while looking cheap; a query that reads 5 TB of a narrow column may finish quickly. For runtime and slot behaviour you need the executed job's statistics — elapsed time and `total_slot_ms` in `INFORMATION_SCHEMA.JOBS`. Dry run answers the cost question under on-demand billing, and only that question. ## Interview framing The reason this comes up so often is that on-demand BigQuery is one of the few systems where a careless statement typed into a console has an immediate, unbounded dollar consequence. A candidate who reaches for the dry run first, knows it is free, knows it over-estimates for clustered and dynamically-filtered queries, and knows the enforcement knob that goes with it, is demonstrating exactly the habit the question is testing.
- For a clustered table, is the dry-run estimate exact?No — it is an upper bound. Cluster-level block pruning happens at execution time using values the planner has not evaluated, so the dry run reports the bytes that would be read without that pruning. Actual billed bytes come in at or below the estimate, sometimes far below on a well-clustered table.
- A query filters on the partitioning column yet the dry run still shows a full-table scan. Why?The planner only prunes when the predicate is a constant expression it can evaluate before running. If the date comes from a subquery, a join, or a non-deterministic function, the dry run cannot resolve it and assumes every partition. Rewrite the filter with a literal or parameter where you need a trustworthy estimate.
- How do you stop a query from running at all if it would be too expensive?Set `maximum_bytes_billed` on the job — the `--maximum_bytes_billed` flag in `bq`, the `maximumBytesBilled` field in the API, or the query setting in the console. If BigQuery determines the query exceeds it, the job fails before reading data and you are not billed.
saying these in an interview costs you the question
- Thinks a dry run executes a sample of the query
- Believes the dry run is billed
- Treats the estimate as exact for clustered tables
- Expects the estimate to account for a cache hit
- Assumes a dry run also predicts runtime or slots