I am currently working on a streaming project in Databricks and noticing significant latency because of the "small file problem" in our Delta tables. Does anyone have a guide on how to implement Auto-Optimize or manual OPTIMIZE commands effectively without killing the cluster performance during peak hours?
3 answers
To handle small files in Databricks, you should primarily look at the "Auto-Optimize" feature which includes optimized writes and auto-compaction. Optimized writes ensure that Databricks writes larger files during the initial ingestion phase, while auto-compaction runs a light-weight process to merge small files into larger ones after a write. For existing tables, running the OPTIMIZE command on a schedule during off-peak hours is best practice. You can also use Z-Ordering on frequently filtered columns to collocate related information, which significantly improves query skip performance.
Have you considered if your partition strategy is contributing to the file fragmentation? Sometimes over-partitioning by a high-cardinality column like a timestamp or a unique ID creates thousands of tiny directories that the Spark engine struggles to scan efficiently.
The simplest fix is enabling spark.databricks.delta.optimizeWrite.enabled in your cluster config. This ensures the engine re-partitions data before writing it to the storage layer.
I agree with Jessica; using the cluster-level configuration is a "set it and forget it" solution that works wonders for teams that don't want to manually trigger maintenance tasks.
You are spot on, Michael. I checked my schema and I was partitioning by 'transaction_hour' which created way too many small files per day. I’ve switched to partitioning by 'transaction_date' and let Databricks handle the sub-filtering. This change, combined with the OPTIMIZE command, reduced our metadata overhead by nearly 40% and improved our overall job stability.