Compact the small files choking your Delta tables and keep them compacted

Overview

A Delta table fed by a streaming job with a short trigger interval and a high-cardinality partition column will accumulate small files faster than anyone notices. Queries that used to return in seconds start spending most of their time in file listing and metadata resolution. AceMQ remediates the tables and fixes the write path that created the problem.

Challenge

Symptoms usually appear as read latency that grows steadily rather than suddenly: a dashboard query that took eight seconds now takes four minutes, OPTIMIZE jobs run past their window and get killed, and VACUUM cannot complete because the file list itself is enormous. Naively running OPTIMIZE on the whole table triggers a rewrite that costs more compute than the team budgeted and conflicts with concurrent writers.

Environment

Databricks with Delta tables written by Structured Streaming, Auto Loader, or frequent micro-batch jobs on cloud object storage.

Approach

AceMQ profiles file counts and size distribution per partition to find where the damage is concentrated, then compacts incrementally by partition range so each OPTIMIZE run fits inside a maintenance window and does not collide with active writers. Once the table is healthy, the write path is corrected — trigger intervals, partition column choice, and auto-compaction settings — so it does not regress.

Solution

• Per-partition file count and size profiling to locate where small files actually accumulate
• Incremental OPTIMIZE with ZORDER on real query predicates, scheduled by partition range to fit the maintenance window
• Partition strategy review, usually removing a high-cardinality column that should be a ZORDER key instead
• Auto-optimize and auto-compaction configuration on the streaming write path with tuned target file size
• VACUUM retention review and staged execution once the file count is back in range
• Deletion vector and checkpoint settings reviewed for merge-heavy tables

Outcome

Technologies