After rewrite_data_files, an Iceberg table still has thousands of small files. Why?

## What the procedure actually plans ```sql CALL prod.system.rewrite_data_files( table => 'db.events', strategy => 'binpack', options => map( 'min-input-files', '2', 'target-file-size-bytes', '268435456', 'partial-progress.enabled', 'true' ) ); ``` The action scans the table's current snapshot, groups candidate data files **per partition**, and bin-packs each group toward the target size. Two thresholds decide whether a group is rewritten at all: `min-input-files`, default 5, and the size window around `target-file-size-bytes`, which defaults to the table property `write.target-file-size-bytes` (512 MB) with `min-file-size-bytes` and `max-file-size-bytes` derived from it. Files already inside that window are not rewritten, because rewriting them would burn IO for nothing. Setting `rewrite-all` to true overrides the thresholds and rewrites every selected file — useful when you are changing sort order or materialising deletes rather than fixing size. ## The partition boundary is the usual culprit Compaction never merges data files from different partitions, because a data file belongs to exactly one partition tuple. A table partitioned at hourly granularity that receives a few megabytes an hour has, by construction, several small files per hour and nothing to combine them with. The procedure will report almost nothing rewritten, and it is behaving correctly. The real fix is upstream: coarsen the partition transform (hours to days), evolve the partition spec, or change the writer's `write.distribution-mode` so each partition receives fewer, larger files in the first place. Compaction cannot repair a partition layout that is too fine for the data volume. ## Other reasons the run looks like a no-op - **Scope was narrowed.** A `where` argument restricts rewriting to matching partitions. Passing yesterday's predicate leaves every older partition untouched, which is often intentional but easy to forget. - **The target size never changed.** If the table sets nothing, groups are packed toward 512 MB. Complaining about "small" 200 MB files while the target is 512 MB and `min-input-files` is 5 means the files were simply not eligible. - **Partial progress.** With `partial-progress.enabled` the job commits in batches capped by `partial-progress.max-commits` (default 10) instead of one big commit at the end. If the job hit the cap or was killed, only some groups landed. Without partial progress, a failure at the end discards everything — on a huge table that is the difference between slow progress and none. - **Concurrency limits and group size.** `max-concurrent-file-group-rewrites` (default 5) and `max-file-group-size-bytes` bound how much runs at once; a long job may still be working through the backlog. - **New files arrived.** A streaming or micro-batch writer keeps appending small files; compaction fixes the past, and its effect is invisible if ingest granularity is the underlying problem. - **Commit conflict.** The rewrite commits by replacing the files it read. A concurrent write that deleted or replaced those same files can fail validation, so the run does work but does not commit. ## Storage did not drop either Even a fully successful rewrite frees no space immediately. The pre-compaction files are still referenced by earlier snapshots, so the table holds both sets until `expire_snapshots` removes those snapshots. Maintenance order is therefore rewrite, then expire. ## Beyond size: sort and deletes `strategy => 'sort'` with a `sort_order` argument — including `'zorder(col_a, col_b)'` — rewrites while clustering rows, which improves data skipping rather than just file count. On a merge-on-read table, `rewrite_data_files` also applies the delete files that cover the files it rewrites, so deleted rows are materialised away; the `delete-file-threshold` option makes files carrying many deletes eligible even when their size is fine. Where only the delete files themselves are the problem, `rewrite_position_delete_files` compacts those without touching the data. ## How to check rather than guess The procedure returns the number of rewritten data files and file groups. Compare file counts and sizes before and after with `SELECT partition, count(*), avg(file_size_in_bytes) FROM db.events.files GROUP BY partition`, and look at `db.events.snapshots` to confirm a `replace` snapshot was actually committed.