BigQuery Query Cost and Performance Optimization

Table of Contents

1. Challenge

   Run the baseline query and record its processing evidence

   Establish what the existing finance report query costs before you change anything.

   • Review the business requirement and the baseline query.
   • Note the validator's bytes estimate before running.
   • Run the baseline query with cached results turned off.
   • Record bytes processed, bytes billed, and slot milliseconds.
   • Save the query result as the baseline_result table.
2. Challenge

   Inspect the query plan and identify the main inefficiency

   Use BigQuery's execution evidence to find out where the baseline query's work goes.

   • Find the most expensive stages in the execution graph.
   • Relate the input and aggregation stages back to the SQL.
   • Check the table's clustering configuration and key uniqueness.
   • Identify why the query reads every column and every block of the table.
3. Challenge

   Change the query to remove that inefficiency

   Rewrite the query so it reads only what the report needs.

   • Replace the whole-row dedupe with only the required columns.
   • Rewrite the month filter as a direct range on the clustered column.
   • Compare the validator's new estimate with the baseline estimate.
4. Challenge

   Run the optimized query against the same requirement

   Measure what the optimized query actually costs.

   • Run the optimized query with cached results turned off.
   • Record bytes processed, bytes billed, and slot milliseconds.
   • Compare the actual bytes processed with the validator's estimate.
   • Save the query result as the optimized_result table.
5. Challenge

   Verify the result matches and the processing cost is lower

   Prove that the optimized query returns the same report for less.

   • Compare the two result tables in both directions.
   • Retrieve both jobs' processing statistics from job history metadata.
   • Save the side-by-side evidence as the run_evidence table.