Why does a filter on a BigQuery date-partitioned table sometimes scan every partition?

## What pruning actually is BigQuery stores a partitioned table as a set of physical partitions plus metadata recording which partitioning value each one holds. When you submit a query, the planner examines the `WHERE` clause, tries to derive the set of partition values that could possibly satisfy it, and reads only those partitions. Everything else is never opened, and under on-demand pricing never billed. This is *static* elimination — it must be decided from the query text and table metadata alone, before a single byte is scanned. That requirement is the source of every failure mode below: if BigQuery cannot compute the surviving partition set at planning time, it must assume every partition survives. ## Shape 1 — the value comes from a subquery The classic: ```sql SELECT SUM(amount) FROM ds.events WHERE event_date = (SELECT MAX(event_date) FROM ds.events); ``` This looks like it touches one day. It does not. The scalar subquery is evaluated during execution, so at planning time the comparison value is unknown and no partition can be excluded — you scan the whole table and pay for it. The same applies to a partition column compared against a column from a joined table. The rewrite is to resolve the value out of band and inline it: run the `MAX` first and substitute a literal, use a query parameter, or use scripting to `SET` a variable and then reference it in the filter. Where the maximum is simply "the latest data", a bounded range against `CURRENT_DATE()` is usually better still, because it is a constant the planner can fold. ## Shape 2 — OR with a non-partition predicate ```sql WHERE event_date = '2026-08-01' OR user_id = 'u_42' ``` A row satisfying the second disjunct can live in any partition, so the surviving set is "all". Disjunction only prunes when *every* branch constrains the partitioning column. This is a frequent accident in generated SQL and in "optional filter" patterns like `WHERE event_date = @d OR @d IS NULL`. Split the query into a `UNION ALL` of two pruned branches, or push the optional-filter logic into the application so the emitted SQL always carries a real range. ## Shape 3 — the column is buried in an expression If the table is `PARTITION BY DATE(event_ts)`, then `WHERE DATE(event_ts) = '2026-08-01'` and `WHERE event_ts >= TIMESTAMP('2026-08-01') AND event_ts < TIMESTAMP('2026-08-02')` both prune, because BigQuery can map them onto partition boundaries. But: ```sql WHERE FORMAT_TIMESTAMP('%Y-%m', event_ts) = '2026-08' WHERE CAST(event_ts AS STRING) LIKE '2026-08%' ``` turn the partitioning value into a string first. The planner cannot invert an arbitrary function, so the boundary information is lost and everything is read. Always express the filter as a comparison or range on the partitioning column (or its declared partitioning expression) against constants. ## Shape 4 — the wrong column entirely A table partitioned by `DATE(event_ts)` and filtered on `created_at`, or on `order_id`, prunes nothing even if the two columns are perfectly correlated in practice. Correlation is not metadata. This is the most common report of "partitioning doesn't work" — the dashboard's date filter is on a different date column than the one the table is partitioned on. ## Diagnosing it A dry run is the fastest instrument: `bq query --dry_run --use_legacy_sql=false '<sql>'`, or the estimated-bytes readout in the console. Partition pruning **is** reflected in that estimate, because it is decided at planning time. So the loop is: run a dry run, see billions of bytes where you expected millions, and go find which of the four shapes above your `WHERE` clause has. (Clustering is different — block skipping happens at runtime, so a dry run does not credit it.) After the fact, `INFORMATION_SCHEMA.JOBS` gives `total_bytes_processed` and `total_bytes_billed` per job, which is how you find the expensive recurring queries rather than the one you happen to be looking at. ## Preventing it Set `require_partition_filter = TRUE` on large partitioned tables, either at creation or later with `ALTER TABLE ... SET OPTIONS`. Queries that do not carry a filter on the partitioning column then fail outright instead of quietly scanning years of data. It is a blunt instrument — it demands a filter, and cannot force that filter to be a prunable *shape* — but it converts the most expensive class of mistake into an immediate, obvious error. Pair it with `partition_expiration_days` so history that nobody queries stops existing, and with a scheduled review of the top jobs by `total_bytes_billed`.