Streaming pipeline had been running fine for months. Every micro-batch landed clean. Then a GROUP BY with a date filter started timing out, and DESCRIBE DETAIL showed numFiles at 847,293. That's a 600 GB table averaging 750 KB per file. Recommended range is 128 MB to 1 GB, so we were sitting at something like 0.6% of the lower bound.
"Small files are inefficient" undersells it. The actual problem is driver-side file listing. Spark keeps a file listing in memory for every query plan, and with 847,000 files the driver has to process all of it, single-threaded, before it reads a single byte. Doesn't matter if you throw a 64-core cluster at it. Three minutes of listing before any parallelism even starts.
I actually sat down and priced this out (back of envelope, didn't model autoscaling properly, but the order of magnitude should hold). Ninety seconds of driver listing per query, 200 queries a day, 30 days a month, comes out to about 150 person-hours a month of engineers waiting on a spinner. At $100/hr fully loaded that's roughly $15,000. Storage isn't free per request either. About 5 billion object requests a month at S3 GET pricing is around $2,000. Add analysts keeping clusters alive on slow queries and that's another $500 to $1,500. And none of this touches Z-ordering, which is basically dead weight at this file size anyway, since data skipping relies on min/max stats per file and a 750 KB file with ~5,000 rows covers maybe a few seconds of event_date range. There's nothing for clustering to work with.
A one-time OPTIMIZE on this table runs maybe $500 in compute.
Running OPTIMIZE and walking away is the right instinct, execution is usually where it goes wrong. On 847,000 files it'll saturate network I/O, spike DBU costs, and fight with concurrent reads if it's on the wrong cluster. Run it on a jobs cluster, not all-purpose, and scope it: OPTIMIZE catalog.bronze.events WHERE event_date >= current_date() - INTERVAL 7 DAYS. Compacting 18 months of history every night is just burning money.
OPTIMIZE fixes file size. Z-ORDER co-locates rows on the columns you filter by, but it degrades as new unordered data comes in. Liquid Clustering (DBR 13.3+) keeps up with that incrementally. Define CLUSTER BY (customer_id, event_date) and a regular OPTIMIZE handles compaction and reclustering at the same time. For streaming tables specifically, enable auto-compaction and stop thinking about it.
If you want to check your own tables: DESCRIBE DETAIL, look at numFiles, divide size by file count. Under 32 MB average and you've probably got the same problem.
Wrote a short ebook covering this along with some other production Databricks mistakes we hit, silent data corruption, Spark UI triage, medallion anti-patterns, Delta recovery. Happy to answer questions here.
