A partition filter that looks like pruning but is not

What it is

Pruning happens at planning time, before any data is read, so the planner must be able to resolve the filter value statically. Constants and functions of constants qualify. Anything requiring data to evaluate — a subquery, a join key, a correlated reference — does not.

Why it costs money

• The query looks correct, returns correct results, and costs full price. There is no error and no warning.
• It is a common refactoring casualty: replacing a hard-coded date with "(SELECT MAX(load_date) FROM control_table)" is tidier code and 100× the cost.
• Everyone on the team believes the table is being filtered, so nobody investigates when the bill rises.

Worked example

SELECT *
FROM `analytics.events`
WHERE event_date = (SELECT MAX(load_date) FROM `meta.control`)

-- Resolve the date first, then use it as a literal:
DECLARE target_date DATE DEFAULT (SELECT MAX(load_date) FROM `meta.control`);

SELECT *
FROM `analytics.events`
WHERE event_date = target_date

How to fix it

1. 1Use a scripting variable (DECLARE … DEFAULT (SELECT …)) so the value is a literal by the time the main query is planned.
2. 2Or resolve the value in your orchestrator and pass it as a query parameter.
3. 3Verify with a dry run before and after. If bytes did not fall, it did not prune.