Data engineering in the AI era · intermediate · About 10 minutes
The table that gets slower every week
A store sales table fed by a stream every 5 minutes took just over a minute to query in its first week and 7 minutes in its sixth. Last year's table holds as much data and answers the same query in 38 seconds.
You run the Databricks jobs behind the store sales dashboard.
What happened
Six weeks ago the store sales table moved from a nightly load to a stream, so the dashboard shows
sales within minutes. A streaming job appends to the Delta table sales_2026 every 5 minutes from
400 stores. The table is partitioned by sale_date and store_id.
The year-to-date query behind the dashboard has grown slower every week. The analysts ask for a bigger cluster.
The incident is made up. How things work in it is real, with sources at the end.
The evidence
Everything you need is here. Read it before you decide.
| Week | Size | Files | Average file | Year-to-date query |
|---|---|---|---|---|
| 1 | 87 GB | 806,400 | 108 KB | 1 min 10 s |
| 2 | 174 GB | 1,612,800 | 108 KB | 2 min 20 s |
| 4 | 348 GB | 3,225,600 | 108 KB | 4 min 40 s |
| 6 | 522 GB | 4,838,400 | 108 KB | 7 min |
| Table | Size | Files | Average file | Same query |
|---|---|---|---|---|
| sales_2025 | 520 GB | 4,160 | 128 MB | 38 s |
(events.writeStream
.format("delta")
.partitionBy("sale_date", "store_id")
.trigger(processingTime="5 minutes")
.option("checkpointLocation", "/checkpoints/sales_2026")
.toTable("sales_2026"))A 5-minute trigger is 288 micro-batches a day. Each batch writes at least 1 file into each of the 400 store partitions it touches: 288 × 400 = 115,200 files a day, or 806,400 a week. The stores sell about 12.4 GB a day, so the average file is about 108 KB.
Your decisions
Decision 1 of 3
Decision 1
Why is the query slower every week?
Decision 2
What do you do today?
Decision 3
How do you stop it from coming back?
What went wrong
A Delta table is a set of Parquet files and a log that lists them. A query reads the log, then finds and opens every file it needs. With large files, that overhead is small next to the reading. With millions of files of 108 KB, the overhead is the query.
The stream made the files. Every 5 minutes, each micro-batch wrote at least 1 file into each store's
partition, so a day became 115,200 files. Partitioning by store_id multiplied the count by 400 and
left each partition with about 30 MB a day. Databricks advises not partitioning tables under 1 TB at
all, and keeping every partition at 1 GB or more, because many small partitions perform worse than
few large ones.
OPTIMIZE repairs the table today: it compacts small files into large ones, changes no data, and is safe to run while the stream writes. To keep it that way, drop the store partitions in favour of liquid clustering, and let auto compaction or predictive optimization run the CompactionCompactionReplacing the older part of a long conversation with a summary, so an agent can keep working when its context fills up. for you. VACUUM is a separate job. It frees storage from files no version uses and does nothing for speed.
Keep these
- When a table slows while its size grows, look at the file count and the average file size.
- A frequent stream into a partition per key writes files faster than you think. Multiply batches by partitions.
- OPTIMIZE compacts the files; VACUUM only deletes unused ones. They solve different problems.
The ideas behind it
Sources