Partition Pruning on Date Columns

Concepts covered: Storage Optimization, Filtering

The pruning question rarely says partition. It shows up as "this dashboard costs $40 a refresh," "this job gets slower every month," or a pull request whose filter returns the right rows. The tell is a query whose cost grows with the table's history while its answer needs a week of it. The interviewer wants you to explain the mechanism before you propose the rewrite, then show the evidence.

• $6.25

What the engine decides before reading

Pruning is decided at planning time, before a byte of data is read. The planner compares the WHERE clause with what it already knows about each partition. That's either the value in a directory name such as dt=2024-03-14 or the minimum and maximum a file's metadata records for the column. A partition whose values cannot satisfy the predicate is skipped. The form every planner can compare is the bare partition column against a constant.

Wrap the column in a function and the outcome becomes engine-specific. Spark evaluates deterministic expressions against directory partition values, so YEAR(dt) = 2024 still prunes there. On Postgres declarative partitioning and BigQuery a transformed column can cost the pruning, and so it can under min/max pruning over Snowflake micro-partitions or Parquet and Iceberg file statistics, because the planner would need to know the function preserves order. The answer that holds on every engine keeps the column bare and moves the arithmetic to the constant side.

The last row is the one people miss: a table partitioned by booking_date gets nothing from a filter on created_at_utc, unless the table format derives its partitions from that column, as Iceberg hidden partitioning does with days(created_at_utc).

The Rewrite

-- Reads every partition on most engines: the column is transformed
SELECT market, SUM(amount) AS revenue
FROM bookings
WHERE DATE_TRUNC('month', booking_date) = DATE '2024-03-01'
GROUP BY market;

-- Reads 31 daily partitions: the arithmetic moved to the constants
SELECT market, SUM(amount) AS revenue
FROM bookings
WHERE booking_date >= DATE '2024-03-01'
  AND booking_date <  DATE '2024-04-01'
GROUP BY market;

Both return identical rows. That is why this bug survives code review: the result is right, and only the bill shows the difference.

The incremental model that scans everything

The most expensive case is in incremental pipelines. A dbt model filtered with WHERE booking_date >= (SELECT MAX(booking_date) FROM target) looks incremental, but its bound is a subquery, and an engine that can't resolve it before planning reads the entire source on every run. Resolve the bound first and pass it in as a literal. The orchestrator's data interval works, and so does a script variable or any value rendered into the SQL. Spark 3.0 added dynamic partition pruning for the related join case, where the wanted dates come from a filtered dimension table, but a portable pipeline does not depend on it.

Prove it from the plan

"How would you know?" is the follow-up, and "the query got faster" is the answer that loses the point. Every engine shows pruning directly. Spark's physical plan lists PartitionFilters on the scan node, and an empty list means a full read. A BigQuery dry run reports bytes processed before anything runs. Snowflake's query profile shows partitions scanned against partitions total. Postgres EXPLAIN lists only the partitions that survived.

Guardrails beyond one query

Fixing a query helps a dashboard. The organizational answer is a guardrail: BigQuery's require_partition_filter table option rejects any query without a filter on the partition column, which turns an accidental full scan into an error message. Pair it with an alert on bytes scanned per query, since a filter that satisfies the rule can still ask for years of partitions. Then choose the partition column by what readers filter on: a table partitioned by ingestion date serves the pipeline, while analysts filtering on booking date get no pruning at all.

Granularity is the other half of the design. Daily partitions over 3 years make 1,095; hourly makes 26,280, and at modest volume most of those hold files far below the 128MB to 1GB that columnar readers scan efficiently. Partition by day and sort or cluster by the timestamp inside each partition: the day prunes, and file statistics on the timestamp narrow further within it.

About this lesson

This section is part of the Date Arithmetic: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.