Maintaining Apache Iceberg Tables: Compaction, Expiry, and Cleanup

This article, part 10 of a 15-part Apache Iceberg Masterclass, explains the four key maintenance operations required to keep Iceberg tables healthy: compaction, snapshot expiry, orphan file cleanup, and manifest rewriting. It details how compaction merges small files into optimally-sized ones (128-512 MB) and can optionally re-sort data to improve query performance, while snapshot expiry removes old metadata to enable time travel retention policies. The article also covers three approaches to running these operations—manual scheduling, threshold-based automation, and autonomous platform handling—and provides code examples for Spark and Dremio.

This is Part 10 of a 15-part Apache Iceberg Masterclass. Part 9 covered how tables degrade. This article covers the four maintenance operations that keep Iceberg tables healthy and the three approaches to running them. Compaction reads small files, merges them into optimally-sized files 128-512 MB , and optionally re-sorts the data. It is the most impactful maintenance operation because it directly addresses the small file problem and restores sort order effectiveness. In Spark: CALL system.rewrite data files 'analytics.orders' In Dremio: OPTIMIZE TABLE analytics.orders REWRITE DATA USING BIN PACK Compaction with sorting rewrites files so that column values are ordered, tightening the min/max statistics and making file skipping far more effective: OPTIMIZE TABLE analytics.orders REWRITE DATA USING SORT order date, customer id Snapshot expiry removes old snapshots from the metadata. After expiry, the snapshot and its exclusive data files are eligible for cleanup. You typically retain snapshots for a window e.g., 7 days to support time travel, then expire everything older. -- Spark CALL system.expire snapshots 'analytics.orders', TIMESTAMP '2024-04-22 00:00:00' -- Dremio ALTER TABLE analytics.orders EXPIRE SNAPSHOTS OLDER THAN = '2024-04-22 00:00:00' After snapshots are expired, the data files they exclusively referenced become orphans. Orphan cleanup scans the storage directory, compares files against the current metadata, and deletes files that are not referenced by any snapshot. -- Spark CALL system.remove orphan files 'analytics.orders' This operation should run after snapshot expiry and with a safety delay e.g., files older than 3 days to avoid deleting files from in-progress writes. Running orphan cleanup too aggressively can delete files from long-running write operations. A 3-day safety window ensures that any write operation has had time to complete before its files are considered orphans. Over many commits, manifests accumulate. A single snapshot's manifest list might reference hundreds of small manifests from individual commits. Manifest rewriting consolidates them into fewer, larger manifests. -- Spark CALL system.rewrite manifests 'analytics.orders' This speeds up scan planning because the engine reads fewer manifest files. Each manifest file requires a separate I/O operation to read, so reducing the count from 500 to 20 eliminates 480 I/O round trips during query planning. Standard compaction BIN PACK merges small files without changing the data order. Sort-order compaction rewrites files with data sorted by specified columns, which tightens the min/max statistics and makes file skipping more effective: -- Dremio sort-order compaction OPTIMIZE TABLE analytics.orders REWRITE DATA USING SORT order date, customer id -- Spark sort-order compaction CALL system.rewrite data files table = 'analytics.orders', strategy = 'sort', sort order = 'order date ASC NULLS LAST, customer id ASC NULLS LAST' Sort-order compaction is more expensive than BIN PACK because it reads, sorts, and rewrites all data. However, the performance improvement for queries that filter on the sorted columns is substantial: file skipping can eliminate 90%+ of data files when the sort columns match common query filters. Decide how long to keep historical data accessible through time travel: Longer retention means more snapshots, more metadata, and more storage consumed by old data files. Shorter retention reduces costs but limits time travel capabilities. Run maintenance operations on a schedule using Spark, Trino, or Dremio. A typical pattern: Pros: Full control over timing and configuration. Cons: Requires operational effort; forgotten or broken jobs lead to degradation. Build a monitoring layer that checks table health metrics Part 9 diagnostics and triggers maintenance only when thresholds are exceeded e.g., average file size drops below 64 MB . Use a platform that handles maintenance autonomously. Dremio's automatic table optimization runs compaction, expiry, and cleanup for tables managed by Open Catalog without any user configuration. AWS S3 Tables provides built-in compaction. For most teams, starting with Dremio's autonomous optimization and only adding manual jobs for tables with unusual requirements is the most practical approach. Running compaction during peak query hours: Compaction reads and rewrites data files, which competes with analytical queries for I/O bandwidth. Schedule compaction during off-peak hours, or use a separate compute cluster Spark on EMR that does not share resources with your query engine. Expiring snapshots too aggressively: If you expire snapshots while a long-running query is using one of them, the query can fail because the data files it needs might be cleaned up. Always keep snapshots for at least as long as your longest-running query. Forgetting orphan cleanup: Many teams run compaction and snapshot expiry but forget orphan cleanup. Without it, compacted and expired data files accumulate indefinitely. Set up orphan cleanup as a weekly job with a 3-day safety window. Not monitoring after migration: Tables migrated from Hive or other formats Part 15 often inherit poor file layouts. Run an immediate compaction pass after any in-place migration. Part 11 covers how to query the metadata tables that power diagnostics.