Compaction, OPTIMIZE and Z-order

Compaction

from delta.tables import DeltaTable

DeltaTable.forName(spark, "events").optimize() \
    .where("event_date >= '2025-03-01'").executeCompaction()

-- Delta Lake
OPTIMIZE events WHERE event_date >= '2025-03-01';

-- Apache Iceberg
CALL catalog.system.rewrite_data_files(
  table => 'db.events',
  where => 'event_date >= "2025-03-01"');

• Delta OPTIMIZE bin-packs files toward a target size (1 GB by default, configurable).
• Iceberg rewrite_data_files targets write.target-file-size-bytes (512 MB by default) and supports bin-pack and sort strategies.
• Both commit atomically: readers see either the old files or the new ones. Old files stay until VACUUM or snapshot expiration.
• Compaction is idempotent: running it on already compacted data does little.

Why clustering matters

File skipping uses min/max statistics per file. If rows are in arbitrary order, every file contains every customer, every file's range for customer_id spans almost everything, and a filter on one customer reads all files. Clustering puts similar values together so ranges are narrow.

Z-ordering

Sorting by one column clusters only that column perfectly; the second sort column is clustered only within ties of the first. A Z-order curve interleaves the bits of several columns, so rows close in any of them tend to land in the same file. It gives good, not perfect, skipping on each column.

-- Delta Lake
OPTIMIZE events ZORDER BY (customer_id, product_id);

-- Apache Iceberg
CALL catalog.system.rewrite_data_files(
  table => 'db.events', strategy => 'sort',
  sort_order => 'zorder(customer_id, product_id)');

• Choose columns used in filters and joins with many distinct values, such as ids. Low-cardinality columns are better as partitions or not needed.
• Effectiveness drops with each added column; two to four is typical.
• Z-ordering a column with no statistics (beyond the first 32 columns in Delta) is useless.
• Delta Z-order is not incremental: re-running it rewrites the affected data. Liquid clustering is the incremental successor.

How often

Compaction costs compute: it reads and rewrites data. Common practice is a daily or weekly job per table, limited to recent partitions with a WHERE clause, since old partitions rarely change. Streaming tables with many small commits need it more often, or auto compaction. Measure the average file size (DESCRIBE DETAIL, or Iceberg's files table) and the bytes scanned by common queries before and after to decide.

Common mistakes