Iceberg copy-on-write vs merge-on-read cheat sheet: write modes, delete files and the compaction that keeps reads fast

Row-level changes in Iceberg are written one of two ways. Copy-on-write (CoW) rewrites every data file that contains a changed row. Merge-on-read (MoR) writes small delete files and leaves the data files alone, so readers have to apply the deletes at scan time. The wrong mode for a workload shows up as either slow, expensive MERGE jobs (CoW on a table with frequent small updates) or queries that get slower every day (MoR without compaction).

This cheat sheet covers the properties, the checks and the maintenance for both modes.

#The write mode properties

Each operation has its own property. Setting one does not change the others.

Merge-on-read needs format version 2 or later. Version 1 tables support only copy-on-write.

-- Check what a table is actually using
SHOW TBLPROPERTIES prod.sales.orders;

-- Switch all three row-level operations to merge-on-read
ALTER TABLE prod.sales.orders SET TBLPROPERTIES (
  'format-version'    = '2',
  'write.delete.mode' = 'merge-on-read',
  'write.update.mode' = 'merge-on-read',
  'write.merge.mode'  = 'merge-on-read'
);

Warning

Upgrading format-version is one-way. Every engine that reads the table (older Spark, Trino, Snowflake, Athena, Flink jobs) must support the new version before you change it. Test with each reader on a copy of the table first.

A common mistake is setting only write.delete.mode. MERGE still defaults to copy-on-write, so the nightly MERGE keeps rewriting whole files and nobody can explain why the change did nothing.

#Which mode for which workload

#Delete file types

Equality deletes are the ones that hurt most. A table receiving Flink upserts for a week without compaction can scan slower by an order of magnitude.

#Measure delete-file build-up

The files metadata table includes a content column: 0 = data, 1 = position deletes, 2 = equality deletes.

SELECT content,
       count(*)                          AS files,
       sum(record_count)                 AS records,
       round(sum(file_size_in_bytes)/1e9, 2) AS gb
FROM prod.sales.orders.files
GROUP BY content
ORDER BY content;

Per partition, to find where compaction is most needed:

SELECT partition,
       sum(CASE WHEN content = 0 THEN 1 ELSE 0 END) AS data_files,
       sum(CASE WHEN content > 0 THEN 1 ELSE 0 END) AS delete_files
FROM prod.sales.orders.files
GROUP BY partition
ORDER BY delete_files DESC
LIMIT 20;

The snapshot summary shows what each commit added:

SELECT committed_at, operation,
       summary['added-delete-files']      AS added_delete_files,
       summary['total-delete-files']      AS total_delete_files,
       summary['total-position-deletes']  AS total_pos_deletes,
       summary['total-equality-deletes']  AS total_eq_deletes
FROM prod.sales.orders.snapshots
ORDER BY committed_at DESC
LIMIT 20;

Summary keys vary slightly by writer and version; if a key is null, list the whole summary map for one snapshot and use the names you see.

#Compaction for merge-on-read tables

By default rewrite_data_files does not rewrite a data file just because deletes point at it. Lower delete-file-threshold so files with deletes get rewritten and the deletes applied.

-- Rewrite data files that have 2+ delete files referencing them, last 7 days only
CALL prod.system.rewrite_data_files(
  table   => 'sales.orders',
  where   => 'order_date >= current_date() - 7',
  options => map(
    'delete-file-threshold', '2',
    'target-file-size-bytes', '536870912',
    'partial-progress.enabled', 'true',
    'max-concurrent-file-group-rewrites', '10'
  )
);

Compact the position delete files themselves when you cannot afford a full data rewrite yet. This also drops deletes that point at files already removed:

CALL prod.system.rewrite_position_delete_files(table => 'sales.orders');

Then expire old snapshots so the replaced files can be cleaned up:

CALL prod.system.expire_snapshots(
  table      => 'sales.orders',
  older_than => TIMESTAMP '2026-11-06 00:00:00',
  retain_last => 50
);

Caution

expire_snapshots removes the ability to time-travel to the expired snapshots and permanently deletes files only they referenced. Check older_than against your recovery requirements and any readers pinned to an old snapshot before running it.

Tip

partial-progress.enabled commits file groups as they finish. On a large table a failure halfway no longer throws away hours of work, and the commit conflicts with concurrent writers are smaller.

#Concurrency trade-offs

• CoW MERGE under serializable isolation conflicts with any concurrent commit that added files matching its filter. Long CoW MERGE jobs on hot partitions retry and fail more often.
• MoR writes are smaller and commit faster, which reduces the conflict window, but compaction itself becomes a writer that can conflict with them. Schedule compaction on partitions that are no longer receiving writes, using where.
• snapshot isolation allows more concurrency but can let two writers both apply changes based on the same old state. Use it only when you understand what the writers do.

#Quick symptom map

#Prevent it next time

• Pick the write mode per table when you create it, and set all three properties explicitly so nobody relies on defaults.
• Put the delete-file query above on a dashboard and alert when the delete-to-data file ratio for any partition crosses a threshold you choose.
• Schedule rewrite_data_files with delete-file-threshold for every MoR table, followed by expire_snapshots and remove_orphan_files on their own cadence.
• Record each reader engine and its Iceberg support level before any format-version change.