Table compaction

What is table compaction?

Table compaction rewrites the many small data files that a table has accumulated into a smaller set of larger ones, without changing a single row. The values, the schema, and the query results stay identical. Only the physical layout on storage changes.

It matters most in a lakehouse. Every append, streaming micro-batch, or MERGE tends to drop its own Parquet files, so an actively written table can end up spread across thousands of tiny ones. Each file an engine reads carries fixed overhead: it has to be listed, opened, and have its footer parsed before any real data comes back. Pay that per-file cost thousands of times over and scans slow down badly. That is the small files problem, and compaction is the standard fix for it.

How compaction runs: OPTIMIZE and rewrite_data_files

Two table formats own this job with a named command. In Delta Lake you run OPTIMIZE, which bin-packs small files into larger ones and by default aims for files of roughly 1 GB. In Apache Iceberg the equivalent is the rewrite_data_files procedure, whose binpack strategy defaults to a target of about 512 MB.

OPTIMIZE sales;

CALL catalog.system.rewrite_data_files('db.sales');

There is no single correct size. In Microsoft Fabric, the guidance for Delta tables is to target somewhere between 128 MB and 1 GB per file, leaning smaller for small tables and larger as the table grows. You want files big enough to amortise the per-file overhead, but not so big that the engine loses the parallelism of reading several files at once. A sorting layout such as Z-ordering can be applied in the same pass, but that reorganises data for skipping and is a separate concern from compaction itself.

Compaction, versions, and time travel

Compaction does not edit files in place. It writes brand new large files and commits a new table version that points at them. The original small files are left on storage, because older versions of the table still reference them. This is what keeps time travel working: you can still query the table as it looked before the compaction ran.

Those superseded files only disappear when you run a separate cleanup command, VACUUM in Delta Lake, which deletes files that no version inside the retention window still references. Compaction and VACUUM pull in opposite directions: compaction leaves more historical files behind, VACUUM clears them out. In Microsoft Fabric, table maintenance refuses a retention period under seven days by default, because vacuuming too aggressively right after a rewrite can cut off time travel and, if a reader is still holding an old file, break a running query.

What to watch out for with table compaction

• It costs compute. Compaction reads and rewrites real data, so schedule it for a quiet window rather than firing it after every write.

• It is not a data-quality fix. The layout changes, the content does not. Duplicate rows, nulls, and wrong values survive a compaction untouched.

• Bigger is not always better. Oversized files starve the engine of parallelism, which is why the target sits in a band and not at a maximum.