What is dynamic partition pruning?

Summary

• Dynamic partition pruning (DPP) lets Spark skip partitions of a partitioned table at runtime, using a filter from the other side of a join. It shipped in Spark 3.0.0 (SPARK-11150).
• Static pruning works from predicates known at planning time. DPP covers the case where the partition column is a join key and the matching values come from a filtered table.
• DPP is its own optimizer feature, controlled by spark.sql.optimizer.dynamicPartitionPruning.enabled (default true). Adaptive query execution is a separate feature.
• Confirm it with EXPLAIN by looking for dynamicpruning in the scan's PartitionFilters.

What is dynamic partition pruning?

Dynamic partition pruning is a Spark SQL optimization that skips partitions of a partitioned table when the values that identify the needed partitions are only known at runtime. Spark's source describes the mechanism: during query optimization, Spark inserts a predicate on the filterable table using the filter from the other side of the join, wrapped in a custom DynamicPruning expression. The Spark 3.0.0 release notes list it as its own performance enhancement, separate from adaptive query execution.

How does dynamic partition pruning work?

Take a sales table partitioned by sale_date and a small dim_date table:

CREATE TABLE sales (amount DOUBLE, sale_date DATE)
USING parquet
PARTITIONED BY (sale_date);

SELECT s.amount
FROM sales s
JOIN dim_date d ON s.sale_date = d.sale_date
WHERE d.fiscal_quarter = 'Q1';

The only filter sits on dim_date, so no predicate on sales.sale_date is known at planning time. Spark can still add a pruning predicate on the sales scan, built from the dim_date rows that pass the filter. The rule's documentation sets two conditions: the table to prune is filterable by the join key, and the join is INNER, LEFT SEMI, LEFT OUTER (partitioned on right) or RIGHT OUTER (partitioned on left).
Planning then picks a form. If the join is a broadcast hash join, Spark reuses the broadcast results. Otherwise, if the estimated benefit of pruning outweighs the overhead of running the subquery twice, it keeps the duplicated subquery. If neither holds, it drops the subquery, so the setting alone does not guarantee pruning.

How does it differ from static pruning and adaptive query execution?

Technique	When values are known	What it does	Setting
Static partition pruning	Planning time	Eliminates partitions using predicates available during planning	spark.sql.hive.metastorePartitionPruning for metastore pushdown
Dynamic partition pruning	Runtime	Eliminates partitions using values from the other side of a join	spark.sql.optimizer.dynamicPartitionPruning.enabled (true)
Adaptive query execution	Runtime statistics	Re-optimizes the plan mid-execution, for example by coalescing shuffle partitions	spark.sql.adaptive.enabled (true since Spark 3.2.0)
Dynamic file pruning (Databricks, Delta Lake)	Runtime	Skips files rather than partitions	spark.databricks.optimizer.dynamicFilePruning (true)

How do I check that it is applied?

EXPLAIN takes [ EXTENDED | CODEGEN | COST | FORMATTED ] and shows the physical plan by default. To compare plans, switch the setting off for your session:

SET spark.sql.optimizer.dynamicPartitionPruning.enabled=false;
EXPLAIN FORMATTED
SELECT s.amount
FROM sales s
JOIN dim_date d ON s.sale_date = d.sale_date
WHERE d.fiscal_quarter = 'Q1';

Run it again with the setting true. In the sales scan, PartitionFilters should then contain a dynamicpruningexpression(... IN dynamicpruning#<id>) entry, and the plan's Subqueries section lists a subquery hosted by that expression, with the filtered dim_date plan beneath it. A dynamicpruningexpression(true) entry prunes nothing: in the planner source, that is what Spark substitutes when the filter is allowed only with a reusable broadcast and none exists.
After the query runs, each node in the SQL plan visualization carries its own metrics. In Spark's source, scans report "number of partitions read", and scans that use DPP also report "dynamic partition pruning time".

What does Databricks document?

Databricks documents a related Delta Lake feature, dynamic file pruning. The optimizer triggers it for queries that contain filter statements or WHERE clauses. It is especially efficient for non-partitioned tables, or for joins on non-partitioned columns, and its effect often correlates with how the data is clustered. In MERGE, UPDATE and DELETE statements it requires Photon-enabled compute. spark.databricks.optimizer.dynamicFilePruning defaults to true, and two thresholds on the probe-side Delta table decide whether it triggers: spark.databricks.optimizer.deltaTableSizeThreshold (default 10 GB) and spark.databricks.optimizer.deltaTableFilesThreshold (default 10 files).

FAQs

Does DPP work with any partitioned table?

The table to prune must be filterable by the join key. In the source, that means a file-source or Hive table whose partition columns include the key, or a DataSource V2 scan that supports runtime filtering.

Is DPP the same as adaptive query execution?

No. They have separate release-note entries (SPARK-11150 and SPARK-31412) and separate settings. The Spark docs describe adaptive query execution as using runtime statistics to choose the most efficient plan, enabled by default since Spark 3.2.0.

Can DPP make a query slower?

Spark applies a cost check. Outside a reused broadcast, it keeps the pruning subquery only when the estimated benefit outweighs the overhead of running the subquery twice. You can also turn the optimization off with the setting above.

Is dynamic file pruning the same thing?

No. Dynamic file pruning is a Databricks feature for Delta Lake tables that skips files, and it helps non-partitioned tables. DPP is an Apache Spark feature that skips partitions.