Iceberg Small Files, Snapshot Bloat, and Compaction Conflicts

Planning time and scan volume have different causes and should not be fixed with one lever.

Planning time is metadata-bound. With 2.8 million files and 1,900 snapshots, the query engine must resolve the current snapshot, read manifests, and prune files. Hourly partitioning since 2024 multiplies manifests and small files, and default snapshot retention keeps old manifests reachable. Fourteen seconds is plausible from manifest count alone.

The 40 TB scan suggests partition pruning is not effective for that predicate. A one-day slice of a 1.2 PB table should not be 40 TB unless the day is unusually large or the filter is not on the partition column. Inspect EXPLAIN output for partition filters, confirm the query filters on the identity partition column (event_date) rather than a derived or differently typed timestamp, and check whether the table's partition spec changed without the query being rewritten. Also check column statistics; stale or missing stats can cause the engine to choose a broad scan even when pruning is available.

Inspect in this order: query plan and partition predicates, manifest and file counts for the target partitions, snapshot count, then file-size distribution. Fixing compaction alone will not repair a predicate that never prunes.

Treat the window as a sequence of cheap metadata work first, then partition-scoped rewrites.

1. Expire snapshots older than the required retention, keeping a small number for rollback. This reduces manifest overhead and is fast relative to data rewriting. Also run orphan-file cleanup after expiry, with a safety lag so in-flight commits are not deleted.
2. Rewrite manifests to consolidate small manifest files. This directly targets planning time and does not touch data files.
3. Run rewrite_data_files in binpack mode only on partitions older than 48 hours, targeting 512 MB files. Never compact the current day's partitions during ingestion; that is where commit conflicts originate. Scope each run to a partition range and stop before the four-hour limit rather than letting one job run for six hours.
4. For ingestion, set write.target-file-size-bytes to about 512 MB and add a small, frequent commit interval so the writer produces fewer, larger files instead of 90,000 nightly.
5. Change the partition spec forward-only: keep day partitioning, and avoid adding hour granularity for new data unless query patterns prove it is needed.

The principle is separation: metadata maintenance, historical compaction, and live ingestion should not contend for the same partitions or commit timeline.

The conflict is structural: a six-hour job touching partitions that are also being written will collide on the table's optimistic commit protocol. Shortening retries is not a fix; reducing overlap is.

Constrain compaction by partition age and scope. Build the job to discover partitions with last_updated older than 48 hours and rewrite only those, one commit per partition or small partition group. This keeps each commit short and ensures the streaming writer is never competing for the same partition metadata. Set a per-run time budget below the maintenance window and checkpoint progress so an interrupted run resumes rather than restarting.

Use conflict-aware retry with backoff for the remaining overlap, and separate the compaction workload's commit cadence from ingestion's. If the engine supports it, run compaction under a dedicated session with limited concurrency so it cannot open hundreds of simultaneous commits.

Measure success by commit-conflict rate, average commit duration, files per partition, and planning time, not by terabytes rewritten. A job that rewrites less data but commits cleanly is better than one that rewrites more and retries for six hours.