In Iceberg, what changes when write.delete.mode is set to merge-on-read?

## The problem both modes solve Iceberg data files are immutable. Deleting or updating a row therefore cannot edit a file in place; the table format has to encode the change some other way. Iceberg offers two encodings, selected per operation by table property: ```sql ALTER TABLE prod.db.events SET TBLPROPERTIES ( 'write.delete.mode' = 'merge-on-read', 'write.update.mode' = 'merge-on-read', 'write.merge.mode' = 'copy-on-write' ); ``` The accepted values are `copy-on-write` and `merge-on-read`, and the documented default for all three is `copy-on-write`. Note that these are Iceberg *write modes*; they are a per-operation setting on one table, not two different table types. ## Copy-on-write The engine finds every data file containing at least one matching row, reads it, and writes a replacement file without those rows. The commit removes the old files and adds the new ones. Deleting one row from a 512 MB file rewrites the whole 512 MB — classic write amplification. In exchange, the table after the commit consists only of plain data files: readers scan them directly with no merge step, no extra file opens, and no accumulating debt. Copy-on-write suits tables that are written in large batches and read constantly: a nightly restatement, a GDPR erasure run, a slowly changing dimension rebuilt daily. ## Merge-on-read The engine leaves the data files untouched and writes **delete files** that mark rows as removed, committing them alongside the unchanged data files. Iceberg format v2 has two kinds: *position deletes*, which name a data file path and the row positions inside it, and *equality deletes*, which name column values whose rows are deleted. Format v3 replaces position delete files with **deletion vectors** stored in Puffin files — one compact bitmap of deleted positions per data file — which readers apply far more cheaply than a scattered set of small position delete files. The write is now proportional to the number of rows changed, not to the size of the files containing them, so a streaming CDC pipeline applying thousands of small upserts a minute becomes viable. The cost moves to the reader: for each data file the scan must locate the applicable delete files, load them, and filter out the marked rows. Iceberg decides applicability by sequence number within a partition — a position delete applies to data files whose sequence number is less than or equal to its own, an equality delete to data files with a strictly smaller sequence number. ## Where the cost accumulates Merge-on-read is a deferral, not a discount. Every un-compacted delete file is a file every relevant scan must open and merge. A table under continuous MoR writes degrades steadily: query latency climbs, and eventually a read fans out over thousands of tiny delete files. Two maintenance jobs pay the debt down. `rewrite_data_files` reads data files together with the delete files that apply to them and writes clean replacements, dropping the deletes; its `delete-file-threshold` option targets exactly the files carrying many deletes. `rewrite_position_delete_files` is a cheaper intermediate step that compacts the position delete files themselves without rewriting the data. Choosing merge-on-read therefore commits you to running compaction on a cadence matched to the write rate — that is the tradeoff an interviewer is probing. ## Choosing per operation Because the three properties are independent you can mix modes. A common shape: `write.delete.mode = merge-on-read` for frequent narrow deletes arriving from CDC, `write.merge.mode = copy-on-write` for a nightly `MERGE INTO` that touches a large fraction of the table anyway — when a rewrite would happen regardless, copy-on-write avoids the delete-file debt for free. Two more inputs matter. First, engine support: not every reader implements every delete type, so verify that all your engines can read the mode you enable. Second, format version: merge-on-read needs `format-version` 2 or later, so a v1 table must be upgraded first. ## Diagnosing which mode is in play The metadata tables answer it directly. `SELECT * FROM db.events.files` lists both data and delete files with their content type; a growing count of delete-content rows means merge-on-read writes are outrunning compaction. `SHOW TBLPROPERTIES prod.db.events` shows the configured modes, and the absence of the properties means the copy-on-write default is in force.