Control BigQuery scan costs with pruning and limits

Why this is worth a look

BigQuery documents separate on-demand and capacity-based pricing models for query processing. For on-demand work, its guidance describes estimating bytes with the query validator or a dry run and provides a maximum bytes billed field in advanced query settings. A qualifying partition filter can skip unmatched partitions, while clustered-column filters can prune storage blocks. These controls help review scan scope, but they do not by themselves prove that a changed predicate returns the same rows.

Use this console-based checklist to inspect pricing scope, partition and clustering predicates, dry-run estimates, and a proposed maximum-bytes-billed value without running the SELECT.

1. Confirm that this review is for a BigQuery on-demand query workload.
2. Read the table definition. Identify the partitioning column. For ingestion-time partitioned tables, use _PARTITIONTIME; if the ingestion-time partition granularity is daily, the table also exposes _PARTITIONDATE. If clustered, record the clustered column order.
3. Review the final read-only SELECT without changing its business meaning. Where applicable, filter the partitioning column with a constant expression and keep that column isolated on one side of the comparison. Review clustered-column filters against the business question.
4. In the Google Cloud console query editor, validate or dry-run the exact SELECT. Match the location to the referenced dataset or datasets and record the displayed bytes estimate. Do not submit the SELECT for execution during this review.
5. In Edit > Query settings > Advanced options, enter a reviewed integer in Maximum bytes billed and save the setting. Record the proposed value and approval context separately from the dry-run estimate.

How to confirm it

1. 01

   Confirm the pricing scope

   Use this workflow for BigQuery query processing under the on-demand pricing model. BigQuery documents on-demand and capacity-based models separately, so keep this review focused on bytes processed rather than presenting it as a capacity reservation review.

2. 02

   Read the table definition

   Identify the partitioning column and, if present, the clustered column order before reviewing SQL. For ingestion-time partitioned tables, use _PARTITIONTIME; if the ingestion-time partition granularity is daily, the table also exposes _PARTITIONDATE.

3. 03

   Check predicate meaning and form

   Keep the business predicates semantically intact. Where partition pruning applies, use a constant expression and isolate the partitioning column on one side of the comparison. Predicate placement in the WHERE clause does not need to follow a specific order, but dynamic expressions can prevent pruning.

4. 04

   Dry-run the final SELECT

   Use the Google Cloud console query validator or a dry-run configuration for the exact SELECT you plan to submit. Match the query location to the referenced dataset location and record the estimated bytes processed. A dry run is a review step, not a normal query execution.

5. 05

   Review the execution setting

   In the console query editor, open Edit > Query settings > Advanced options and enter a reviewed integer in Maximum bytes billed. Treat the value as an approval setting for the proposed query, and do not infer an execution outcome from the dry-run estimate alone.

Primary sources