Querying a partitioned table without a partition filter

What it is

Partitioning splits a table into segments that can be skipped. Skipping only happens when the query filters on the partition column. Without that filter you pay for every partition ever written.

Why it costs money

• Partitioned tables are usually the big ones — that is why someone partitioned them — so the mistake is expensive precisely where it hurts most.
• The cost grows silently as the table accumulates history. The query that cost $2 last year costs $30 today with no change to the SQL.
• It is invisible in testing: on a small dev table with 5 partitions, the missing filter costs nothing.

Worked example

SELECT user_id, SUM(revenue)
FROM `analytics.orders`
GROUP BY user_id

SELECT user_id, SUM(revenue)
FROM `analytics.orders`
WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY user_id

How to fix it

1. 1Add a filter on the partition column with a constant, or an expression of constants such as DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY).
2. 2Set require_partition_filter = true on the table. Then the next person gets an error instead of a bill, which is the only fix that survives staff turnover.
3. 3Check the pruning worked: a dry run should report far fewer bytes than the table's total logical size.