What is partition pruning? Why your query scans the whole table to read one day

Your query scans 2 TB to read one day. The table is partitioned, the filter is there, and the engine still reads everything, because the filter is written in a way it cannot use. One page on what pruning is, what silently turns it off, and the guardrails that protect the bill. The examples are BigQuery; the idea carries to other warehouses, the exact rules do not. The print-ready A4 PDF is at the bottom.

What it is

• A partition is one chunk of the table, usually one per day.
• Pruning means the engine reads only the days you need.
• No date filter: you pay for every day.

What kills pruning

• Wrapping the column: CAST, FORMAT_DATE and similar hide it from the planner.
• A filter that comes from a subquery is not a constant, so nothing is pruned.
• Filtering on the wrong column: created_at only, when the table is partitioned on event_date.

Write it right

• Use the raw column: event_date, untouched.
• Compare it to a constant: a literal or CURRENT_DATE().
• For ranges: >= start AND < end.
• Isolate it: the column alone on the left side of the comparison.

Cluster: lever 2

• Partition is the coarse lever: the date.
• Cluster is the fine lever: user_id, country.
• Up to 4 clustering columns, and their order matters.

Guardrails

• require_partition_filter rejects queries with no usable partition filter.
• Dry run: read the bytes before you run.
• Maximum bytes billed: the query fails, not the budget.

Coarse partition, fine cluster

-- BigQuery: one partition per day
CREATE TABLE shop.events (
  event_date DATE, user_id STRING, amount NUMERIC
) PARTITION BY event_date
CLUSTER BY user_id
OPTIONS (require_partition_filter = TRUE);

SELECT SUM(amount) FROM shop.events
 WHERE event_date = '2026-10-08' -- 1 partition
   AND user_id = 'u_42';         -- few blocks

With require_partition_filter, a query that has no usable filter on event_date is rejected instead of billed. Before any big query, read the dry run estimate: it is free.

Gotchas

• LIMIT 10 is not a partition filter.
• SELECT *: every column is billed.
• Other engines: same idea, other rules.

The trap: the filter is there, the scan is full

The quiz

Which query scans less?

-- events: partitioned by day
A) SELECT * FROM events
   WHERE day = (SELECT MAX(day)
                FROM events)

B) SELECT * FROM events
   WHERE day >= '2026-10-08'
     AND day <  '2026-10-09'

B. In BigQuery, B compares the raw partition column to constants, so only the Oct 8 partition is read. In A the filter depends on a subquery, which is not a constant expression, so every partition is scanned. Check with a dry run.

Interview phrasing worth memorizing: pruning needs the raw partition column compared to a constant. Some wrappers still prune, like DATE(ts) on a TIMESTAMP partition, so I never guess: I dry run and read the bytes.