Dynamic Partition Pruning override

[pantelis_mare]

Hello everybody,

Another strange issue I have and I would like to confirm me if this is a bug or expected behaviour:

I'm joining a large dataset with a dimension table and as expected DPP is activated.

I was trying to deactivate the feature as it changes the read partitions so I disabled through

spark.sql.optimizer.dynamicPartitionPruning.enabled and spark.databricks.optimizer.dynamicPartitionPruning but I STILL had the dynamic partition prunning.

Finally I discovered that by pushing spark.databricks.optimizer.deltaTableFilesThreshold to a big number I managed to see my sql query not to use DPP.

Is this behavior expected? I would say no given the DPP documentation here

Tested on both DBR 9.1 and 10

[Anonymous]

Hello there! Thanks for your question. I'd like to give this a bit longer to see what the community comes up with. Otherwise, I'll bump this to the SMEs.

[Kaniz_Fatma]

Hi @Pantelis Maroudis, spark.databricks.optimizer.deltaTableFilesThreshold(default is 10 in Databricks Runtime 8.4 and above, 1000 in Databricks Runtime 8.3 and below): Represents the number of files of the Delta table on the probe side of the join required to trigger dynamic file pruning. When the probe side table contains fewer files than the threshold value, dynamic file pruning is not triggered. If a table has only a few files, it is probably not worthwhile to enable dynamic file pruning.

Source:-https://docs.databricks.com/delta/optimizations/dynamic-file-pruning.html?_ga=2.75262864.326194262.1...

[pantelis_mare · Accepted solution]

Hello @Kaniz Fatma​ 

Thank you for taking the time to answer.

The issue in this case was that spark.databricks.optimizer.deltaTableFilesThreshold was activating DPP even if it was formally deactivated by setting all available "enabled" properties to false (see my initial post)