skip to content

Why does joining a partitioned fact table to a filtered date dimension often still scan every partition?

level: middleimportance: should knowfreq 50%

answer

  1. the planner has not read the dimension yet
  2. a literal in the text versus a value produced at run time
  3. the cheapest filter is one that never opens the file
  4. the derived keys must match the partition column exactly
  5. you can just write the date range twice

basics

~20 s

The partition column is filtered only indirectly, through the join, so at planning time no literal date range is known. Unless the engine defers pruning to runtime using the dimension's surviving keys, it must assume every partition might contain matches.

solid answer

~50 s

Static partition pruning happens during planning and needs literals in the query text: `WHERE date_key BETWEEN 20240101 AND 20240331` eliminates partitions before execution starts. A star query instead writes `JOIN dim_date d ON f.date_key = d.date_key WHERE d.fiscal_quarter = 'Q1-2024'`, and the planner has no idea which `date_key` values that predicate yields — the dimension has not been read yet. So the naive plan scans all partitions and filters after the join. **Dynamic partition pruning** fixes this by executing the dimension side first, collecting the surviving key values (or their min/max range), and using them to choose which fact partitions to open. When an engine does not do this, or the surviving keys span the whole range, you scan everything. The practical workaround is to also express the date range as a literal predicate on the fact table's own partition column so static pruning applies.

code

sql · 14 lines
sql
-- relies on runtime pruning: no literal bounds date_key
SELECT d.fiscal_quarter, SUM(f.revenue)
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.fiscal_quarter = 'Q1-2024'
GROUP BY d.fiscal_quarter;

-- prunes at plan time: redundant literal on the partition column
SELECT d.fiscal_quarter, SUM(f.revenue)
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
WHERE d.fiscal_quarter = 'Q1-2024'
  AND f.date_key BETWEEN 20240101 AND 20240331
GROUP BY d.fiscal_quarter;

go deeper

for a junior

Recall that skipping partitions requires the engine to know which values to look for, and that a filter written against a joined lookup table does not give it that until the query is already running.

for a middle

Explain the plan-time versus run-time split, name dynamic partition pruning as the bridge, and know the workaround of adding the equivalent literal range against the fact table's own partition column.

for a senior

Show you verify it from the plan: compare partitions read against the table's total, know that a cast on the join key silently kills it, and recognize when the derived key set is too wide to eliminate anything.

for a principal

Own the pattern at platform level — decide whether BI templates emit the redundant range predicate, whether fact tables are partitioned on the column dimensions actually filter, and what monitoring catches a query that quietly starts reading every partition.

## Two different times at which pruning can happen Partition elimination means never opening the files for a partition that cannot contain matching rows. There are two moments at which the engine can decide that. **At plan time (static).** The query text contains a constant that constrains the partition column directly: ```sql SELECT SUM(revenue) FROM fact_sales WHERE date_key BETWEEN 20240101 AND 20240331; ``` The optimizer evaluates the predicate against partition metadata and produces a plan that touches only the qualifying partitions. This is cheap, reliable, and visible in the plan as a reduced partition count. **At run time (dynamic).** The constraint on the partition column is not a literal but the *output of another operator*: ```sql SELECT d.fiscal_quarter, SUM(f.revenue) FROM fact_sales f JOIN dim_date d ON f.date_key = d.date_key WHERE d.fiscal_quarter = 'Q1-2024' GROUP BY d.fiscal_quarter; ``` Nothing in the text bounds `f.date_key`. The bound exists — it is exactly the set of `date_key` values in Q1-2024 — but it only becomes concrete once the dimension side executes. An engine that supports dynamic pruning schedules the dimension scan first, derives the key set or its min/max, and then decides which fact partitions to open. An engine that does not simply opens all of them. ## Why this is the single most valuable form of runtime filtering Runtime filters can be applied at three granularities, and the savings differ by orders of magnitude: - Per row after decoding: saves join work only. - Per block using zone maps: saves reading some blocks. - Per partition: never touches the files at all, saves metadata listing, I/O and decode for entire slices of the table. Date is by far the most common partition column and date dimensions are the most commonly filtered dimension, so this pairing is where the technique earns most of its keep. A query that should read one quarter reading ten years of partitions is a 40× cost difference on a usage-billed warehouse. ## Why it fails or is skipped 1. **The engine does not implement it.** Not every analytical engine defers pruning to runtime for join-derived predicates. Check the plan for a reduced partition count; if the number of partitions scanned equals the table's total, it did not happen. 2. **The join key is not the partition key.** Pruning by `date_key` requires the fact table to be partitioned on `date_key`. If it is partitioned on ingestion date and joined on business date, the derived key set constrains the wrong column and nothing is eliminated. 3. **The key is transformed.** `ON f.date_key = CAST(d.d AS INT)` or `ON DATE(f.ts) = d.d` puts an expression between the dimension's values and the partition column's stored values, and the engine can no longer match derived values to partition metadata. 4. **The surviving key set is wide.** Filtering to a quarter helps; filtering to "all weekdays" yields keys spread across every partition and eliminates none of them. 5. **The dimension side is not cheap enough to run first.** Dynamic pruning imposes an ordering: the build must complete before the scan can start. Engines sometimes decline when that serialization would cost more than the pruning saves. ## The reliable workaround When you cannot rely on dynamic pruning, give the optimizer the literal it needs: ```sql SELECT d.fiscal_quarter, SUM(f.revenue) FROM fact_sales f JOIN dim_date d ON f.date_key = d.date_key WHERE d.fiscal_quarter = 'Q1-2024' AND f.date_key BETWEEN 20240101 AND 20240331 GROUP BY d.fiscal_quarter; ``` The redundant predicate is logically implied by the dimension filter, but it is written in terms the planner can act on before execution. This is the same trick as transitive predicate derivation, done by hand because the engine could not derive it across a join to a dimension it has not read. Report generators and BI layers that template the date range into both places get this for free; hand-written ad-hoc SQL usually does not. ## How to spot it in a plan Look for the number of partitions or files the fact scan reports. Compare it with the table's total. If the plan is estimated-only, the count may reflect the pessimistic assumption; the actual count after execution is what tells you whether runtime pruning kicked in. A large gap between estimated partitions and actually-read partitions is the positive signal that dynamic pruning worked. ## Interview framing State the core asymmetry first: static pruning needs a literal, and a star query's date filter is a literal about the *dimension*, not the fact table. Then name dynamic partition pruning as the mechanism that bridges the gap, explain the ordering it requires, and finish with the redundant-predicate workaround, which shows you can ship a fix today rather than wait for the optimizer.

  • Why does casting or wrapping the join key defeat partition pruning?
    Partition metadata records the stored values of the partition column. Pruning works by comparing derived key values against that metadata directly. Once an expression sits between them — a cast, a date truncation, a concatenation — the engine cannot map a derived value onto a stored partition bound, so it conservatively assumes every partition might match and opens them all.
  • How is dynamic partition pruning different from a bloom filter pushed into the scan?
    They share the mechanism of deriving a filter from the dimension at runtime, but differ in what they eliminate. A bloom filter is evaluated while scanning, so it skips rows and possibly blocks but the scan is already underway. Partition pruning acts before any file is opened, eliminating whole slices of the table from the scan's file list. The savings are correspondingly larger.
  • What ordering constraint does dynamic pruning impose on the plan, and what does it cost?
    The dimension side must be fully evaluated before the fact scan can decide which partitions to read, so the two cannot start in parallel. That serialization is trivial when the dimension is small, but it delays the start of the large scan. Engines sometimes skip dynamic pruning when the expected elimination does not justify the added latency.

saying these in an interview costs you the question

  • Assumes any WHERE clause on a joined table prunes partitions
  • Thinks partitioning the fact table alone guarantees pruning
  • Believes pruning works when the join key is wrapped in a cast
  • Confuses partition elimination with filtering rows after the join
  • Says the optimizer knows the date range because the dimension has it

context

resolving links…