Reduce BigQuery scans without changing the answer

Why this is worth a look

Skipping a qualifying partition filter can make BigQuery scan data the answer does not need. Partition pruning skips those partitions and excludes them from scanned bytes. Clustering can also skip storage blocks, but the blocks scanned and final cost are not known before execution.

Work through these steps with an authorized BigQuery colleague using BigQuery dry runs, not normal query execution. Scope: one SELECT and its referenced tables. Record logical (uncompressed) bytes for the query's required data range, not a monthly total.

1. Record the exact SELECT, project, datasets, referenced tables and required data range. Confirm that the query uses on-demand pricing, charged per TiB processed, rather than capacity pricing for compute resources called slots.
2. Read each table's partitioning type and column. Record clustered columns in their defined order, which affects which filters benefit from clustering. Do not change table definitions.
3. Review only filters that preserve the required answer. To enable partition pruning, use a constant filter expression and isolate the partitioning column on one side of a comparison. For time ranges, use >= and < with the required boundaries. Do not rely on functions over an integer-range partitioning column to prune partitions.
4. Check ingestion-time boundaries separately. _PARTITIONTIME is UTC ingestion time truncated to the partition boundary; daily partitions also expose _PARTITIONDATE. Ask the query owner whether filtering ingestion time would exclude any required rows.
5. Request dry runs of the original and proposed SELECTs. Record each SQL statement, its estimated bytes and its required range. Compare bytes to assess pruning. Mark clustered-table estimates as uncertain. Return any unresolved answer-equivalence question to the query owner before normal execution.

How to confirm it

1. 01

   Confirm this is a scan-cost decision

   Ask the workload owner whether this SELECT uses on-demand pricing. Continue for charges per TiB (tebibyte) processed, not charges for compute capacity called slots.

2. 02

   Agree on the required answer

   Ask the query owner to state which rows and date range must remain. Do not add a narrower filter just to reduce estimated bytes.

3. 03

   Compare read-only estimates

   Use the checklist with your BigQuery colleague to review table layout and compare dry-run bytes. Keep the exact SQL beside each estimate so the decision applies to the statement that will run.

4. 04

   Resolve uncertainty before execution

   Wait for the query owner to confirm that any rewrite preserves the answer. If approval needs an exact pre-execution cost for a clustered-table query, do not approve it on the dry-run estimate alone.

Primary sources