nordisk/skade

MERGE INTO, for idiots

The problem, in one sentence

A skade table could only ever have rows added to it. If the same customer sent you their address twice, you ended up with two addresses and no way to say which one is current.

Why that was hard to fix

Iceberg tables are made of immutable files. You cannot open a file and edit row 417. To change a row you must either

1. write a new file with the corrected rows and tell the table "use this one instead of that one" — copy-on-write, or
2. leave the file alone and write a little note saying "ignore the row with id 417" — merge-on-read.

skade could already do (2). It could not do (1), because the Rust Iceberg library it uses had exactly one way to change a table — "add these files" — and no way at all to say "and stop using those". Not a skade oversight: the upstream library still does not have it, in September 2026.

What landed

Both halves of (1):

• OverwriteFilesAction — a new operation in the Iceberg library, "swap this set of files for that set, atomically".
• MERGE INTO — the SQL statement, wired to drive it.

So now you can write:

MERGE INTO customers USING today ON customers.id = today.id
WHEN MATCHED THEN UPDATE SET address = today.address
WHEN NOT MATCHED THEN INSERT (id, address) VALUES (today.id, today.address)

and the table ends up with one row per customer, holding today's address.

Read that as: for every row in today, find the row in customers with the same id. If there is one, update its address. If there is not, add a new row.

What actually happens on disk

Say the table is four files and your update touches rows in file 2 only.

before:   [file1] [file2] [file3] [file4]
                     ^ holds the rows you are changing

after:    [file1] [file2'] [file3] [file4]
                     ^ a BRAND NEW file with the corrected rows.
                       file2 is still on disk; the table just stops
                       pointing at it.

Files 1, 3 and 4 are not touched, not copied, not read. And because file2 is still there, yesterday's version of the table still works — that is how time travel keeps working after a merge.

If your update matches nothing at all, nothing happens: no new files, no new version of the table. The statement returns 0 and goes home.

The number it gives you back

count
-----
3

That is how many rows in the table changed. Not how many rows you fed it. If you hand it 5 000 rows and only 3 of them match anything, you get 3. This matters more than it sounds: the easy wrong implementation echoes back your input size, always looks plausible, and tells you nothing.

The catch

Rewriting a file means writing the WHOLE file, even to change one row. Measured on a 200 000-row table:

The notes pile up. Every reader has to read every note, every time, forever — until somebody compacts the table. So merge-on-read is cheap once and gets expensive slowly, and copy-on-write is expensive once and then free.

MERGE INTO is copy-on-write because a warehouse table is read far more often than it is written. If you have the opposite problem, the note-writing path is still there under its own name: Table::delete_equality followed by Table::append.

Things it will refuse to do

It refuses loudly rather than doing something almost-right:

• joining on anything other than = between a target column and a source column
• a table that is split by partition
• giving it two source rows that both claim the same target row ("which one did you mean?")

Each refusal says which of these it was.