Partition pruning

01 · What it is

Reading only the directories you need

Partition pruning is Spark reading only the on-disk directories a query needs and skipping the rest, instead of scanning every file in a table. When a table is written with partitionBy, each distinct value of the partition column becomes its own directory — and a filter that maps to a subset of those directories lets Spark ignore the others entirely. It comes in two flavours, and the deep dive explains each once:

• Static pruning — a literal filter on a partition column (WHERE country = 'US') picks the directories at plan time.
• Dynamic partition pruning (DPP) — a partitioned fact joined to a filtered dimension prunes the fact at runtime, even though no literal was ever written against it.

analogy A partitioned table is a filing cabinet with one labelled drawer per value. Pruning is opening only the drawers your query names and leaving the rest shut — you don't read a folder to decide you didn't want it.

02 · Why it matters

I/O is usually the first bottleneck

• The biggest cheap win is to read less. Before you tune a join or a shuffle, cut the bytes at the source. A 5-year, daily-partitioned table queried for a single day should touch ~1/1825 of its files — if pruning fires.
• The classic enterprise query depends on it. A huge partitioned fact ⋈ a small filtered dimension (sales by order_date ⋈ date_dim WHERE quarter='Q4-2025') scans the whole fact without DPP, but only the Q4 partitions with it — often a 10×+ reduction in bytes read.
• It's a classic interview probe: "Your query reads the whole fact table even though you only want one region — why, and how do you fix it?" The answer lives in PartitionFilters / dynamicpruningexpression in the plan. The how is the next section.

03 · How it works — deep dive

Two meanings of "partition", then static, then dynamic

Almost every partition-pruning bug starts with the word "partition" meaning two different things. So the useful order is: (1) pin down which partition we mean, (2) static pruning at plan time, (3) dynamic pruning at runtime. Each idea is explained here, once — later sections show it, verify it, and help you decide, but don't re-explain it.

3.1 · Two meanings of "partition" — never confuse them

• (a) On-disk Hive-style partitions — PARTITIONED BY (col) / partitionBy(col). Each distinct value becomes its own directory (/sales/order_date=2025-01-01/…). Pruning skips these.
• (b) In-memory Spark partitions — the parallelism chunks, governed by spark.sql.shuffle.partitions (200) and repartition()/coalesce(). About parallelism, not file-skipping.

use case Create the directories with a low-cardinality column: events.write.partitionBy("event_date"). Dashboards filter by date, so every query prunes to a handful of day-directories. Partitioning a high-cardinality column (user_id, order_id) explodes the table into millions of tiny directories — the small-files problem plus heavy driver-side metadata (Lesson 03). Aim for partitions in the hundreds-of-MB-to-GB range.

3.2 · Static partition pruning — decided at plan time

• Mechanism. A literal filter on a partition column resolves at plan time. Because partition values are encoded in the directory paths, Spark lists only the matching directories from the catalog and never opens the rest — before a single task runs. In the plan this appears as a PartitionFilters: [(country = US)] line on the Scan node.
• Why it's free. No extra computation — the prune happens during file listing, so the saved directories cost nothing to skip.
• The trap (Rung 3). It only fires when the column is filtered directly with a literal. Wrap it in a function (year(order_date)=2025) or a UDF and Catalyst can't match it to directory names — the predicate falls to Filter/PushedFilters and the whole table scans. Filter the raw column with a literal or range instead.
• The gap it can't cover. When the filter sits on another table's output (a join), there's no literal to prune with at plan time. That gap is exactly what DPP fills.

3.3 · Dynamic partition pruning (DPP) — decided at runtime

analogy DPP is a bouncer who first checks the small VIP list (the filtered dimension), then opens only the cloakroom drawers (fact partitions) for names actually on it — instead of opening every drawer and checking each coat afterwards.

• The problem. fact JOIN dim WHERE dim.x='Q4'. The filter is on dim, so static pruning can't help — at plan time Spark has no literal for the fact, so it would scan the entire fact and filter after the join.
• The mechanism. With spark.sql.optimizer.dynamicPartitionPruning.enabled = true (default; since Spark 3.0.0), Spark runs the small filtered dimension first, collects its surviving join-key values (a subquery / broadcast result), and pushes them onto the fact's partition column at runtime. The fact scan then reads only matching partitions before the join executes. In the plan this is PartitionFilters: [dynamicpruningexpression(...)] fed by a SubqueryBroadcast.
• Requirements — all must hold (Rung 3). The fact is partitioned on the join key; the filtered dimension is broadcastable (small enough to ride the broadcast machinery); it's an equi-join on the partition column; and it is not a streaming query.
• The trade-off. When it fires, a massive I/O win for free. When the dimension is too big to broadcast or the fact isn't partitioned on the join key, DPP simply doesn't engage — silently — and you fall back to a full fact scan. A UDF anywhere in the filter can defeat it, just as it defeats static pruning.

use case sales (by order_date, 2 TB) ⋈ date_dim WHERE quarter='Q4-2025' — DPP prunes sales to ~90 day-partitions at runtime instead of scanning all 2 TB.

Figure A · which directories open

Watch the directories light up

The grid is a sales table with one on-disk directory per region value. Pick a filter and watch matching directories light up (read) while the rest grey out (skipped) — static pruning. Then flip on DPP and choose which quarter the dimension keeps; the surviving key prunes the fact at runtime. A function-wrapped filter collapses pruning back to a full scan. This figure isolates which drawers open; Figure B shows the same prune running across the nodes.

04 · The code — and how to verify it

Three moves, each with its proof

Every technique below is paired with how to prove it worked — the .explain() plan line or the Spark-UI signal. Apply, then verify; never assume.

Create on-disk partitions with partitionBy

python# partitionBy writes one directory per distinct order_date value.
# Pick a LOW-cardinality column (date/region) — never user_id/order_id.
(sales
   .write
   .partitionBy("order_date")                 # ← creates /order_date=.../ directories
   .mode("overwrite")
   .saveAsTable("main.pyspark_perf.sales"))    # UC 3-level name; Delta is the default

# VERIFY the table really is partitioned:
spark.sql("DESCRIBE EXTENDED main.pyspark_perf.sales").show(truncate=False)
#   look for "Partition Columns: [order_date]"

Trigger static partition pruning — a literal filter

python# A LITERAL filter on the partition column → plan-time pruning.
one_day = spark.table("main.pyspark_perf.sales").where("order_date = '2025-01-01'")

# VERIFY: the scan node must carry a PartitionFilters line with your literal.
one_day.explain(mode="formatted")
#   (1) Scan ... main.pyspark_perf.sales
#       PartitionFilters: [isnotnull(order_date#..), (order_date#.. = 2025-01-01)]   ✅
#   Spark UI SQL tab: "number of partitions read" = 1 (not the full table)

# ❌ Anti-pattern: a function on the partition column defeats static pruning.
slow = spark.table("main.pyspark_perf.sales").where("year(order_date) = 2025")
# explain() shows it under Filter / PushedFilters, NOT PartitionFilters → full scan.
# Fix — filter the raw column with a literal/range:
#   order_date >= '2025-01-01' AND order_date < '2026-01-01'

Trigger DPP — a partitioned fact ⋈ a filtered dimension

pythonfrom pyspark.sql.functions import col

sales    = spark.table("main.pyspark_perf.sales")      # partitioned by order_date (the join key)
date_dim = spark.table("main.pyspark_perf.date_dim")   # small dimension, broadcastable

# Filter lives on the DIMENSION, not the fact — only DPP can prune the fact here.
q = (sales.join(date_dim, sales.order_date == date_dim.d_date)
          .where(col("d_quarter") == "Q4-2025"))         # ← dim filter

# VERIFY: the fact scan's PartitionFilters carries a dynamicpruningexpression.
q.explain(mode="formatted")
#   (n) Scan ... sales
#       PartitionFilters: [dynamicpruningexpression(order_date#.. IN subquery#..)]   ✅
#   ...a SubqueryBroadcast / ReusedExchange feeds it the surviving dates.
#   Spark UI SQL tab: the `sales` Scan reads only the Q4 partitions.

python# DPP is on by default; A/B it by turning it OFF to see the before-state, then reset.
print(spark.conf.get("spark.sql.optimizer.dynamicPartitionPruning.enabled"))  # 'true'
spark.conf.set("spark.sql.optimizer.dynamicPartitionPruning.enabled", "false")  # demo only
# ...re-run q.explain() → dynamicpruningexpression gone, fact reads ALL partitions ❌
spark.conf.set("spark.sql.optimizer.dynamicPartitionPruning.enabled", "true")   # reset

Figure B · where the prune happens

See DPP run across the cluster

This figure isolates a different thing from Figure A: not which directories, but where and when the prune is decided. Step through a full scan, then static pruning, then DPP across 1 driver + 2 executors. Watch the driver broadcast the filtered dimension's surviving keys at runtime and prune the fact partitions before the executors scan them. Use Prev / Play / Next.

05 · Decision table

Which pruning, and what fires it

Read this by the situation you're in — it answers "will this prune, and how?" at a glance. (The why is §3; this is the which. The last column is the in-memory contrast, not a fourth kind of pruning.)

06 · Edge cases & limitations

Where it gets subtle

New nuance an interviewer probes — not a re-run of §3:

• The dimension that outgrew broadcast. DPP rides the broadcast machinery, so a dimension that was 8 MB last quarter and 80 MB now can silently stop pruning — fact scans quietly go full again. Pre-filter or shrink the dimension; confirm DPP still fires.
• A UDF in the filter is a black box. Catalyst can't push a UDF into PartitionFilters, so it defeats both static pruning and DPP. Rewrite as native column expressions.
• Non-partition-column filters get no partition pruning. A filter only on a non-partition column skips no directories — though Delta file-stats data-skipping can still help, a different mechanism.

Hard limits worth stating plainly:

• DPP is not applied to streaming queries. It's a batch-plan optimization; a Structured Streaming query won't get it.
• Pruning skips directories, nothing inside a surviving file. Trimming rows/columns within a file is column pruning + predicate pushdown's job — reach for all three together.
• Fact not partitioned on the join key → DPP can't prune. It prunes partition directories, so the join key must be the partition column. For high-cardinality join keys use bucketing (Lesson 11) or Z-ORDER / liquid clustering instead.

07 · Common mistakes

Gotchas — the mistake and its one-line fix

08 · References

Sources

• Apache Spark — SQL Performance Tuning (Dynamic Partition Pruning, AQE, joins)
• Apache Spark — Configuration (dynamicPartitionPruning.enabled, shuffle.partitions)
• Apache Spark — CREATE TABLE ... PARTITIONED BY
• Azure Databricks — Dynamic partition pruning
• Azure Databricks — When to partition tables (cardinality guidance)