MERGE, UPDATE & DELETE

UPDATE, DELETE, and MERGE all change existing rows. To do that efficiently, Iceberg needs format-version 2 (or later), which adds delete files — small side files that mark rows as removed without rewriting every data file. Version 1 tables are append-only.

-- opt a table into row-level operations
ALTER TABLE db.orders SET TBLPROPERTIES ('format-version' = '2');

-- (new tables: set it at CREATE time, as in Module 1)
-- CREATE TABLE db.orders (...) USING iceberg TBLPROPERTIES ('format-version'='2');

UPDATE … SET … WHERE changes matching rows and commits a new snapshot. Below we re-label every cancelled order as refunded. The runnable cell does it on a scratch copy so it's safe to re-run:

UPDATE db.table SET col = expr [, ...] WHERE <condition>;

UPDATE db.orders
SET status = 'refunded'
WHERE status = 'cancelled';

SELECT order_id, product, status FROM db.orders
WHERE order_id IN (4, 5, 6) ORDER BY order_id;

DELETE FROM … WHERE removes matching rows. GDPR erasure, dropping test data, purging a bad batch — all one statement, committed atomically as a new snapshot (and recoverable by time travel until you expire it).

DELETE FROM db.table WHERE <condition>;

DELETE FROM db.orders WHERE status = 'cancelled';

SELECT count(*) AS remaining FROM db.orders;

MERGE INTO is the feature people come to Iceberg for. In one atomic statement it compares a target table to a source, then updates rows that match, inserts rows that don't, and optionally deletes matched rows. It's the backbone of CDC pipelines, slowly-changing dimensions, and idempotent upserts.

MERGE INTO db.target t
USING source s  ON t.key = s.key
WHEN MATCHED [AND cond] THEN UPDATE SET col = s.col, ...
WHEN MATCHED [AND cond] THEN DELETE
WHEN NOT MATCHED [AND cond] THEN INSERT (cols) VALUES (s.cols);

Say a CDC batch order_updates arrives: order 4 shipped, order 6 delivered, and a brand-new order 7. One MERGE applies all three changes — updating the two existing rows and inserting the new one:

MERGE INTO db.orders t
USING order_updates s
  ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.status = s.status
WHEN NOT MATCHED THEN INSERT *;

This is the single most important tuning decision for row-level changes. When a row changes, Iceberg can either rewrite whole data files immediately, or write small delete files and reconcile at read time. You choose per operation via table properties.

-- copy-on-write (default for v2 writes): fast reads, heavier writes
ALTER TABLE db.orders SET TBLPROPERTIES (
  'write.update.mode'  = 'copy-on-write',
  'write.delete.mode'  = 'copy-on-write',
  'write.merge.mode'   = 'copy-on-write');

-- merge-on-read: fast writes, extra work at read time
ALTER TABLE db.orders SET TBLPROPERTIES (
  'write.update.mode'  = 'merge-on-read',
  'write.delete.mode'  = 'merge-on-read',
  'write.merge.mode'   = 'merge-on-read');

-- how each mode behaves