There is a pattern that appears in a lot of BigQuery pipelines and looks completely reasonable. You have a control table that stores the latest processed date. Rather than hardcoding a date into your query, you pull it dynamically:
SELECT *
FROM `project.dataset.events`
WHERE event_date = (
SELECT latest FROM `project.dataset.control_table`
);
The query returns the right results. The logic is clean. And if your events table is large and date-partitioned, you might be scanning the entire thing every time it runs.
Why BigQuery struggles with this
Date-partitioned tables in BigQuery only save you money if BigQuery knows which partitions to read before it starts scanning. That decision happens at query planning time — before any data is touched.
When your partition filter contains a subquery, BigQuery cannot resolve the filter value at planning time. The subquery has to execute first, and until it does, BigQuery does not know which date to filter on. In practice this means BigQuery cannot prune partitions effectively, and may scan the full table before applying the filter.
The query works. The cost is wrong.
The fix: move the subquery to DECLARE
BigQuery's scripting syntax includes a DECLARE statement that evaluates an expression once and stores the result as a variable. Move the subquery there instead:
DECLARE latest_date DATE DEFAULT (
SELECT latest FROM `project.dataset.control_table`
);
SELECT *
FROM `project.dataset.events`
WHERE event_date = latest_date;
The difference in execution order matters. DECLARE runs first. By the time the main query starts planning, latest_date holds a concrete date value — not an expression, not a subquery, just a date. BigQuery can use that value to prune partitions before scanning begins.
We hit this on a project where the events table had years of data. The subquery version scanned the full table on every run. After moving to DECLARE, it scanned a single day's partition. Same output, a fraction of the cost.
The secondary benefit: consistency
If the same date is referenced more than once in your query — in multiple WHERE clauses, in a JOIN condition, in a subquery — the inline version runs the control table lookup each time. Usually those results will be identical, but in a pipeline that is updating while your query runs, there is a window where they could differ.
With DECLARE, the value is resolved once at the start of the script and reused everywhere. Every reference to latest_date returns the same value for the lifetime of that query execution.
How to check whether it is affecting you
Open the query in the BigQuery console without running it. The estimated bytes processed shown in the bottom right reflects whether BigQuery can prune partitions or not. If that number looks like the full table size regardless of the date filter, partition pruning is not working.
Run the query once with the subquery inline and once with DECLARE, and compare the actual bytes processed in the job details. On a large partitioned table the difference is usually significant enough to make the fix an easy decision.
