.nornir/merge-into-guide.md

`MERGE INTO` — guide

Change rows in a skade table with one SQL statement.

The statement

use skade_sql::WarehouseSql;

let wh = skade::open("/data/lake").await?;
let ctx = wh.session().await?;

// the source can be anything the session can see: another skade table, a
// registered Arrow batch, a CSV, a subquery
ctx.register_batch("incoming", batch)?;

ctx.sql("
    MERGE INTO events USING incoming ON events.id = incoming.id
    WHEN MATCHED THEN UPDATE SET status = incoming.status, seen_at = incoming.seen_at
    WHEN NOT MATCHED THEN INSERT (id, status, seen_at)
                        VALUES (incoming.id, incoming.status, incoming.seen_at)
").await?.collect().await?;

The statement returns one row, one column:

+-------+
| count |
+-------+
| 1417  |
+-------+

count is rows updated + rows deleted + rows inserted — what the table changed by, not how many rows the source had. A merge with 5 000 source rows of which 3 match returns 3.

Bare names (events) and catalog paths (skade.main.events) are the same writable object. Table::sql on a skade::Table handle is still a snapshot-pinned read-only view; use the warehouse session for anything that writes.

Every clause shape that executes

WHEN MATCHED THEN UPDATE SET col = expr [, col = expr ...]
WHEN MATCHED THEN DELETE
WHEN MATCHED AND <predicate> THEN UPDATE SET ...
WHEN MATCHED AND <predicate> THEN DELETE
WHEN NOT MATCHED [BY TARGET] THEN INSERT (cols) VALUES (exprs)
WHEN NOT MATCHED [BY TARGET] THEN INSERT VALUES (exprs)      -- all columns
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ...
WHEN NOT MATCHED BY SOURCE THEN DELETE

Clauses are tried in written order; the first one of the applicable category whose predicate holds wins. A predicate that evaluates to NULL does not fire — same rule as WHERE.

WHEN NOT MATCHED and WHEN NOT MATCHED BY TARGET mean the same thing.

Every shape that is refused, and what it says

you wrote	what comes back
`ON t.a > s.a`	"needs an ON condition that is a conjunction of `target.col = source.col` equalities"
`ON t.id = s.id` with `id` `Int64` vs `Int32`	"the join key types must match exactly. Cast in the USING subquery."
`MERGE INTO` a partitioned table	"a rewritten row needs its partition values recomputed, which this path does not do"
`WHEN NOT MATCHED BY SOURCE THEN INSERT`	"it names no row to act on. Refusing rather than silently doing nothing."
`WHEN NOT MATCHED THEN DELETE`	sqlparser refuses it before DataFusion: "DELETE is not allowed in a NOT MATCHED merge clause"
a source with two rows for one target key	"matched the same target row … with more than one source row … Narrow the ON condition or de-duplicate the source."

None of these silently does nothing. That is the point of every one of them.

What it does to the table

Copy-on-write. Each data file that holds a changed row is rewritten with the new content; files that hold none are carried into the new snapshot untouched. One overwrite snapshot commits the swap.

Consequences worth knowing before you run it on a big table:

A merge that matches nothing commits nothing. No snapshot, no new files, the head does not move, count is 0.
Cost tracks the size of the files you hit, not the number of rows you change. Updating 100 rows spread over ten 20 MB files rewrites ~200 MB. Updating 100 rows inside one file rewrites one file. Cluster your writes if you can.
History is intact. The pre-merge snapshot still reads back through RedbCatalog::load_table_at; the old data files stay on disk until the snapshot is expired. That is also why disk grows.
Concurrency is optimistic. Two merges racing on one table: the second fails its snapshot-ref requirement (or its not-live-file check) and nothing is half-applied. Retry it.

When NOT to use it

Use skade::Table::delete_equality + Table::append — merge-on-read — instead when the write rate is high and the reader is a delta consumer rather than a full scan. It writes ~48× less and commits ~17× faster, and it makes every subsequent full scan slower, linearly in the number of delete files it has accumulated (measured 38 ms → 173 ms over 8 rounds; see merge-into-design.md). If you take that path, compact.

Running the numbers yourself

cargo test --release --test merge_cow_vs_mor -- --ignored --nocapture

Prints the copy-on-write and merge-on-read columns side by side for keys spread across every data file and for keys concentrated in one.

MERGE INTO — guide