Dynamic file pruning

When a query joins a large Delta Lake table to a smaller, filtered table, the filter values aren't known until the query runs. Without runtime pruning, the query scans every file in the large table, even files that contain no matching rows. Dynamic file pruning solves this problem. At runtime, the optimizer pushes the filter results from one side of the join down to the scan of the Delta Lake table and skips files that can't contain matching rows.

Dynamic file pruning is enabled by default. You don't need to change your queries to benefit from it.

When dynamic file pruning applies

Dynamic file pruning applies only to joins on Delta Lake tables. In a MERGE statement, it uses the join key values from the source to skip target files when the merge condition compares a source column with a target column for equality. In UPDATE and DELETE statements, it applies when the condition contains a subquery that makes the same kind of comparison.

Dynamic file pruning in MERGE, UPDATE, and DELETE statements requires Photon-enabled compute. For SELECT statements, Photon has broader and more reliable dynamic file pruning. Without Photon, dynamic file pruning might still apply to SELECT statements, depending on query shape and execution plan. See What is Photon?.

Dynamic file pruning improves performance most for non-partitioned tables and for joins on non-partitioned columns. By default, it only triggers when the large Delta Lake table in the join is at least 10 GB and has at least 10 files. See Configuration.

Dynamic file pruning also reduces write conflicts. When Delta Lake checks for conflicts during a commit, it considers files that dynamic file pruning skipped. This reduces the number of failed transactions. See Isolation levels and write conflicts.

Best practices

The performance effect of dynamic file pruning correlates with how data is clustered. If the values you join on are spread across every file, dynamic file pruning skips few files. To help dynamic file pruning skip more files, follow these practices:

• Use liquid clustering on the columns you frequently join on, so that matching rows are colocated in fewer files.
• Use Photon-enabled compute, which MERGE, UPDATE, and DELETE statements require for dynamic file pruning.

Configuration

Most workloads don't need to change these settings. Consider tuning the thresholds if a join against a smaller Delta Lake table would benefit from pruning but falls below the default size or file-count threshold.

These Apache Spark configuration properties control dynamic file pruning:

Property	Type	Default	Description
spark.databricks.optimizer.dynamicFilePruning	Boolean	true	Turns dynamic file pruning on or off.
spark.databricks.optimizer.deltaTableSizeThreshold	Long	10000000000 (10 GB)	The minimum size, in bytes, of the large Delta Lake table in the join that triggers dynamic file pruning. For a smaller table, scanning the whole table is usually cheaper than pushing down the filters. To find the size of a Delta Lake table, run DESCRIBE DETAIL table_name and check the sizeInBytes column.
spark.databricks.optimizer.deltaTableFilesThreshold	Integer	10	The minimum number of files in the large Delta Lake table in the join that triggers dynamic file pruning. If the table has fewer files than this threshold, dynamic file pruning doesn't run, because it might not be worthwhile for a table with only a few files. To find the number of files in a Delta Lake table, run DESCRIBE DETAIL table_name and check the numFiles column.