Hello Databricks Community!
I recently published a detailed breakdown on Medium about a real-world optimization nightmare we faced, and I wanted to share the core lessons learned with this group.
We had a highly efficient Delta table pipeline handling 1.2 billion records that completed its hourly incremental updates in just 12 minutes. In a bid to speed up specific queries, we made a seemingly logical choice: partitioning the table by a high-cardinality column (TransactionID).
Instead of speeding things up, this single layout choice turned our 12-minute job into a 2-hour nightmare.
The root cause? A catastrophic small file explosion (creating 2.7 million partitions and 3.2 million tiny files) that completely drowned Spark in metadata overhead. Upgrading cluster sizes, running standard OPTIMIZE, and trying ZORDER barely made a dent because Spark was spending all its time just navigating physical directories.
We ultimately solved this by migrating completely to Delta Lake's Liquid Clustering, which slashed our file count down to 18,000, removed directory overhead entirely, and dropped our total pipeline runtime down to just 8 minutes.
I've shared the full, step-by-step optimization journey, including our exact benchmarking numbers for each failed attempt, over on Medium.
👉 Read the Full Story on Medium
The Results
| Metric | Traditional Partitioning | Liquid Clustering |
| Total Files | 3.2 Million | 18,000 |
| Partition Directories | 2.7 Million | 0 |
| Pipeline Runtime | ~120 minutes | 8 minutes |
Key Takeaway
The old rule of "partition by the column you filter on" fails spectacularly on high-cardinality keys like IDs. If you are facing massive metadata overhead or slow merges, skip the cluster upgrades and switch to Liquid Clustering.
Have you run into similar small-file bottlenecks in your production environment? Let's discuss below!
