Iceberg Small-File Compaction Versus Audit Retention
A lakehouse stores 3.2 TB of clickstream in an Iceberg v2 table partitioned by days(event_ts) and bucket(16, user_id). Streaming ingestion commits every 30 seconds and creates roughly 2,000 small Parquet files per day. After six months the table has 180,000 data files, 12,000 manifests, and a 38 MB metadata JSON. Dashboard p95 for a 24-hour scan rose from 9 seconds to 75 seconds. The team runs rewrite_data_files weekly with default settings and expire_snapshots with 7-day retention. A recent compaction job failed after four hours with executor OOM; it used 20 executors with 4 cores and 16 GB each and a target file size of 128 MB. Some dashboards time-travel to yesterday; compliance requires 30-day snapshot retention for audit. Disk is 70% full. The team wants lower latency without breaking audit or time travel.
1. Why is weekly default compaction insufficient, and how would you redesign compaction to avoid OOM and reduce query latency?
Read the worked answer
Thirty-second commits produce about 2,000 files per day, so a weekly rewrite leaves roughly 14,000 new small files between runs. Default rewrite settings can also group too much data into one file group, which explains the four-hour OOM. A 128 MB target is reasonable for query efficiency, but it increases file count compared with 256-512 MB targets.
Run compaction more frequently and scope it to hot recent partitions, for example rewrite the last two days every few hours and older partitions daily. Set a smaller max-file-group-size-bytes so each rewrite unit fits executor memory, enable partial progress, and control max-concurrent-file-group-rewrites. Use bin-pack rewriting to consolidate small files, then rewrite manifests to reduce the 12,000 manifests and the 38 MB metadata file. Keep the target file size aligned with dashboard scan patterns. Before changing production, measure file-group sizes, rewrite duration, and executor spill; increasing executor count alone will not fix oversized groups.
2. How do you reconcile 7-day snapshot expiration with 30-day audit and yesterday time travel? What order of operations is safe?
Read the worked answer
Seven-day expiration cannot satisfy a 30-day audit requirement. Extending history retention will increase metadata and disk pressure, so do it deliberately. Set snapshot retention to at least 30 days for the main branch, and use tags or branches for audit points so you can retain specific monthly or investigation snapshots without keeping every intermediate commit forever. Yesterday time travel then remains available.
Order matters. First create and verify the audit tags. Then raise the retention window and rewrite manifests to keep metadata manageable. Run expire_snapshots against the new policy. Only after that run remove_orphan_files with an older-than threshold that exceeds the longest running write and compaction, so you do not delete files still referenced by an in-progress commit. Remember that snapshot expiration does not immediately free disk; orphan removal does. With disk at 70%, archive or tier older partitions before extending retention broadly.
3. What table-design changes would you consider for 30-second commits, and what are the risks?
Read the worked answer
The bucket(16, user_id) partition transform means each commit can write up to 16 files, and frequent commits magnify that into many small files. Consider increasing the commit interval to 1-5 minutes for non-urgent data, using write distribution and sort order to produce larger files, and setting a target file size that matches the sink's write volume per commit. For hot recent data, hour-level partitioning may improve pruning, but it also increases partition count and metadata overhead.
The risks are real: larger commit intervals increase latency and replay volume; more partitions increase planning cost; and changing partition layout through partition evolution can leave old and new layouts coexisting. Test query plans against both layouts before adopting a change. Do not bucket more finely without evidence that joins or scans need it. Keep the day partition for long-range dashboards and use compaction, not more partitions, as the first fix for small files.