# `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:
```sql
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
```text
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:
| | copy-on-write (`MERGE INTO`) | merge-on-read (the "note") |
|---|---|---|
| time to write | 34 ms | 2 ms |
| bytes written | 1.9 MB | 0.04 MB |
| time to read the table afterwards | 4 ms, and it stays 4 ms | 38 ms after one, **173 ms after eight** |
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.