What are the common challenges when implementing Dynamic Partition Pruning (DPP) in Spark?

The usual challenge is that DPP silently doesn't happen: the table isn't partitioned by the join key, the join isn't eligible, or the small side isn't broadcast. The other challenges are designing partitions that make it useful and verifying it in the plan.

1. Table layout

• DPP only prunes partition columns. If sales is partitioned by region but joined on date_id, nothing is pruned.
• Partitioning by a high-cardinality key (such as customer_id) creates huge numbers of small files, which costs more than DPP saves. Pick partition columns that are both commonly joined or filtered on and low in cardinality, such as a date.

2. The small side isn't broadcast With default settings, Spark only uses DPP when it can reuse the broadcast of the filtering side. If that side is too big for the broadcast threshold (10 MB by default), or broadcast is disabled, the plan shows dynamicpruningexpression(true), meaning pruning was switched off. Filtering the dimension more, raising the threshold modestly, or a broadcast hint can bring it back.

3. Join type and conditions

• Only equi-joins on the partition column qualify; a join on an expression of the column (CAST(s.date_id AS STRING) = d.key) or a non-equality condition doesn't.
• Outer joins only allow pruning the side whose unmatched rows are discarded. In sales RIGHT JOIN dates, the broadcastable side would have to be sales, so dates can't be reused and DPP doesn't happen.

4. Filter selectivity If the dimension filter keeps most rows, pruning saves little. Spark estimates whether the filter is selective enough when it would need a separate subquery.

5. Source support DPP works with file-based tables (Parquet, ORC, Delta) and data sources that support runtime filtering, such as Apache Iceberg. Other connectors, such as JDBC sources, don't benefit.

6. Verifying it

• explain("formatted") shows dynamicpruningexpression(... IN dynamicpruning#...) on the scan when it applies.
• The SQL tab of the Spark UI shows the number of partitions and files read by the scan; compare it with the total.

7. Old statistics Wrong size estimates can stop Spark broadcasting the small side. ANALYZE TABLE ... COMPUTE STATISTICS keeps estimates accurate, and adaptive query execution can switch to a broadcast at runtime when the filtered side turns out small.