Static partition pruning vs dynamic partition pruning (DPP) | Data Engineering

Q: How do static and dynamic pruning compare?

Static: literal filter txn.filter(F.col("branch") == "Mumbai").explain() Shows: PartitionFilters: [branch = Mumbai] Dynamic: joinderived filter txn.join( branches.filter(F.col("region") == "West"), "branch_id" ).explain() Shows: PartitionFilters: [dynamicpruning123]

Definition

Static pruning uses a literal known at plan time; dynamic pruning derives the pruning values at runtime from the other side of a join.

Static partition pruning – the filter value on a partition column is a literal known at plan time (e.g., WHERE branch = 'Mumbai'), so Catalyst decides which directories to read before the job runs.
Dynamic partition pruning (DPP) – introduced in Spark 3.0, handles the case where filter values are derived at runtime from another table in a join; Spark evaluates the small dimension side first, extracts distinct join-key values, and injects them as a runtime filter on the large partitioned fact table.
Controlled by spark.sql.optimizer.dynamicPartitionPruning.enabled (on by default in Spark 3.0+).

Static pruning is when the auditor at the National Bank of India walks in and says 'give me the Mumbai drawer' – the clerk knows exactly which drawer immediately. Dynamic pruning is when the auditor says 'give me the branches on this list,' but the list is in a sealed envelope that gets opened during the audit. Spark opens the small envelope first (evaluates the dimension), reads which branches it names, and only then knows which large drawers to pull.

Understanding the Concepts

What is static partition pruning?

Static partition pruning is the simpler and older behaviour. When your query contains a literal filter on a partition column, Catalyst sees the value at plan time and restricts the file listing to only that directory. The decision is made while building the physical plan, before any data is read.

PYTHON

from pyspark.sql import SparkSession
import pyspark.sql.functions as F

spark = SparkSession.builder.appName("NBI-Pruning").getOrCreate()

# Fact table partitioned by branch
txn = spark.read.parquet("hdfs:///bank/transactions/")

# Static pruning: literal value known at plan time
mumbai_txn = txn.filter(F.col("branch") == "Mumbai")
mumbai_txn.explain()

TEXT

== Physical Plan ==
*(1) ColumnarToRow
+- FileScan parquet
     PartitionFilters: [branch = Mumbai]
     PushedFilters: []
     ReadSchema: struct<account_id:string,amount:double,txn_date:string>

What is dynamic partition pruning (DPP)?

Dynamic partition pruning solves a case static pruning cannot. Consider a star-schema join: a large fact table partitioned by branch_id, joined to a small dimension table with a filter. The set of matching branch_ids is not a literal: it is computed from the dimension table at runtime.

PYTHON

# Fact table: transactions partitioned by branch_id
txn = spark.read.parquet("hdfs:///bank/transactions/")  # partitioned by branch_id

# Dimension table: branches
branches = spark.read.parquet("hdfs:///bank/dim_branches/")

# Star-schema query: filter on dimension, join to fact
west_txn = txn.join(
    branches.filter(F.col("region") == "West"),
    txn.branch_id == branches.branch_id
)

# Without DPP: reads ALL partitions of txn, drops after join
# With DPP: evaluates branches filter first, gets West branch_ids,
#           injects them as runtime filter on txn partitions
west_txn.explain()

TEXT

== Physical Plan ==
*(2) BroadcastHashJoin [branch_id], [branch_id]
:- *(2) FileScan parquet
:    PartitionFilters: [dynamicpruning#123]
:    PushedFilters: []
:    SubqueryBroadcast dynamicpruning#123
:      +- BroadcastExchange
:         +- *(1) Filter (region = West)
:            +- *(1) FileScan parquet [dim_branches]
+- BroadcastExchange
   +- *(1) Filter (region = West)
      +- *(1) FileScan parquet [dim_branches]

How does DPP work internally?

The mechanism has three steps. First, Spark evaluates the small filtered dimension side. Second, it collects the distinct join-key values from that result. Third, it injects those values as a runtime filter (a subquery broadcast) on the partition column of the fact table. The fact scan then reads only the matching partitions.

PYTHON

# Step by step:
# 1. Evaluate: branches.filter(region == "West") → {"MUM001", "PUN002", "GOA003"}
# 2. Collect distinct branch_ids: {"MUM001", "PUN002", "GOA003"}
# 3. Inject: txn scan reads only branch_id=MUM001, branch_id=PUN002, branch_id=GOA003

# Verify DPP is enabled
print(spark.conf.get("spark.sql.optimizer.dynamicPartitionPruning.enabled"))

TEXT

true

How do static and dynamic pruning compare?

| Feature | Static Pruning | Dynamic Pruning (DPP) |
|---|---|---|
| Filter value | Literal, known at plan time | Derived at runtime from join |
| When decided | During plan optimization | During execution |
| Applies to | Direct filters on partition cols | Star-schema join filters |
| Spark version | All versions | Spark 3.0+ |
| Configuration | Always on | dynamicPartitionPruning.enabled |
| Plan indicator | PartitionFilters: [col = literal] | dynamicpruning#N subquery |

PYTHON

# Static: literal filter
txn.filter(F.col("branch") == "Mumbai").explain()
# Shows: PartitionFilters: [branch = Mumbai]

# Dynamic: join-derived filter
txn.join(
    branches.filter(F.col("region") == "West"),
    "branch_id"
).explain()
# Shows: PartitionFilters: [dynamicpruning#123]

What are the requirements for DPP?

DPP has specific requirements. The fact table must be partitioned on the join key. The dimension filter must be selective so that only a subset of partition values are needed. The join must involve a broadcast-eligible dimension side so Spark can evaluate it early.

PYTHON

# Requirement 1: fact table partitioned on join key
# txn partitioned by branch_id → DPP can apply on branch_id join

# Requirement 2: dimension filter is selective
# branches.filter(region == "West") → returns ~10 branch_ids out of 200
# DPP prunes 190 partitions

# Requirement 3: dimension side must be broadcastable
# branches is small enough to broadcast

# Check broadcast threshold
print(spark.conf.get("spark.sql.autoBroadcastJoinThreshold"))

TEXT

10485760

When does DPP not help?

DPP does not help when the dimension filter is not selective (returns most key values), when the fact table is not partitioned on the join key, or when the dimension side is too large to broadcast.

PYTHON

# DPP does NOT help here:

# Case 1: non-selective filter (returns 195 out of 200 branches)
non_selective = txn.join(
    branches.filter(F.col("status") == "ACTIVE"),  # 195 of 200 branches active
    "branch_id"
)
# DPP fires but prunes only 5 partitions: minimal benefit

# Case 2: fact not partitioned on join key
# txn partitioned by txn_date, joined on branch_id
# DPP cannot prune txn_date partitions using branch_id filter

# Case 3: dimension too large to broadcast
# If branches had 10M rows, broadcast fails, DPP cannot evaluate early

What are common mistakes with partition pruning?

The most common mistake is assuming pruning happens when it does not. Always verify by checking the physical plan.

PYTHON

# WRONG: filter on non-partition column does NOT prune
# txn partitioned by branch, but filtering on amount
txn.filter(F.col("amount") > 100000).explain()
# Shows PushedFilters (predicate pushdown), NOT PartitionFilters

# WRONG: using a function on the partition column prevents pruning
txn.filter(F.upper(F.col("branch")) == "MUMBAI").explain()
# Function wrapping prevents Catalyst from recognizing the partition filter

# CORRECT: filter directly on partition column with literal
txn.filter(F.col("branch") == "Mumbai").explain()
# Shows PartitionFilters: [branch = Mumbai]

What should a senior engineer know?

A senior engineer verifies that DPP actually fired by looking for the dynamicpruning subquery in the physical plan. They also know that DPP is one of the biggest real-world speedups for data-warehouse-style joins, often turning a full fact-table scan into reading a handful of partitions.

PYTHON

# Senior verification: confirm DPP fired
plan = west_txn._jdf.queryExecution().executedPlan().toString()
assert "dynamicpruning" in plan, "DPP did not fire!"
print("DPP confirmed in physical plan")

# Senior tip: combine static and dynamic pruning
# Static prunes on date, DPP prunes on branch
result = txn.filter(F.col("txn_date") == "2026-01-15").join(
    branches.filter(F.col("region") == "West"),
    "branch_id"
)
# Both pruning mechanisms apply: date statically, branch dynamically

TEXT

DPP confirmed in physical plan

Visual Overview

DIAGRAM

Static Partition Pruning:
  Query: WHERE branch = 'Mumbai'
  Plan time: Catalyst knows the value immediately
  ┌──────────────────┐
  │ branch=Mumbai    │ ← READ
  │ branch=Delhi     │ ← SKIP (known at plan time)
  │ branch=Chennai   │ ← SKIP
  └──────────────────┘

Dynamic Partition Pruning (DPP):
  Query: txn JOIN branches ON branch_id WHERE region = 'West'

  Step 1: Evaluate dimension          Step 2: Inject filter
  ┌──────────────┐                   ┌──────────────────────────┐
  │ branches     │                   │ txn (fact)               │
  │ filter:      │                   │ branch_id=MUM001 ← READ │
  │ region=West  │──→ {MUM001,      │ branch_id=PUN002 ← READ │
  │              │     PUN002,      │ branch_id=GOA003 ← READ │
  │ Result:      │     GOA003}      │ branch_id=DEL004 ← SKIP │
  │ 3 branch_ids │    (runtime      │ branch_id=CHN005 ← SKIP │
  └──────────────┘     filter)       │ ... (190 more skipped)  │
                                     └──────────────────────────┘
  DPP turns a full fact scan into reading 3 partitions.

Key Takeaway

Both forms of pruning skip partition directories on the large table, but they differ in when the pruning value is known. Static partition pruning applies when the filter value is a literal that the optimizer can see while building the plan, such as filtering on branch equals Mumbai. Catalyst decides which directory to read before the job even starts, which is the everyday benefit of partitioning on a column you filter with constants.

Dynamic partition pruning, added in Spark 3.0, handles the star-schema case where the filter value is not a literal but is derived from another table in a join. Imagine a huge transactions fact table partitioned by branch, joined to a small branches dimension filtered on region equals West. The set of West branch_ids is unknown until the dimension is evaluated. Dynamic pruning evaluates the small dimension side first, collects the distinct join-key values, and injects them as a runtime filter on the fact table's partition column, so the fact scan reads only the matching partitions instead of everything.

In my reporting work at the National Bank of India this was one of the biggest speedups we got. A regional report joined a terabyte-scale transaction table to a tiny branch dimension, and before Spark 3.0 it scanned the whole fact table and dropped rows after the join. With dynamic partition pruning enabled, Spark derived the handful of branch_ids for the requested region and pruned the fact table down to just those partitions, cutting the scan dramatically.

The requirements I always mention are that the fact table must be partitioned on the join key and the dimension filter must be selective, and I verify it fired by checking the physical plan for a dynamicpruning subquery on the fact scan.

Remember one sentence: static pruning uses a constant known at plan time, and dynamic pruning learns the values at runtime from the other side of the join to skip fact-table partitions.

---