Skip to content

Merge

The write modes of chapter 16 are create, append and replace, and each is about the table as a whole. An analytical table that is kept up to date needs a fourth, about rows: a row whose key the table does not hold is inserted, and a row whose key it does hold is updated. Warehouses call it upsert or merge, and Delta Lake calls the statement MERGE INTO.

Merge as a pipeline

Merge is deduplication by key over the union of what the table holds and what arrives. The current rows and the incoming rows are read, concatenated, and deduplicated with the same rule as the previous page, and the result replaces the table:

type Event {
    id       : Int
    kind     : String
    customer : String
    amount   : Decimal
    at       : String
}
current  : Seq<Event> = source("ledger")
incoming : Seq<Event> = source("silver_replayed")

current
| concat(incoming)
| group(by = r => r.id)
| map(g => g.items | max_by(r => r.at))
| somes
| sort(by = r => r.id)
| map(r => {id: r.id, amount: r.amount})
[{amount: 120.50, id: 1}, {amount: 42.00, id: 2}, {amount: 42.00, id: 3}, {amount: 7.99, id: 4}, {amount: 120.50, id: 5}, {amount: 18.00, id: 6}]

The ledger held six orders; the replay brought nine rows; the result is six rows, with order 6 updated to its corrected amount and the other five unchanged. An incoming row with a new id would be inserted by the same steps, since a group of one keeps its one row. Written back under mode = "replace", the table is the merge, in one transaction:

current  : Seq<Event> = source("orders_silver")
incoming : Seq<Event> = source("silver_replayed")

current
| concat(incoming)
| group(by = r => r.id)
| map(g => g.items | max_by(r => r.at))
| somes
| sort(by = r => r.id)
| target("orders_silver")
Step

The merge rule, which copy wins, is the max_by and nothing else. A rule that prefers the row already held is min_by over the same key; a rule that prefers the incoming row whatever its timestamp is last over each group, since concat put the current rows first.

This reads and rewrites the whole table, which is right for a table of thousands of rows and wrong for one of billions. The cost is the point of having a write mode for it. A row changed in place, one at a time as a transaction does it, is update of the transfer page; a merge is the whole table kept up to date by key.

Merge as a write mode

Design only

This section describes functionality that is not in the current release. It is written as if it existed: names and keys may change before it ships.

A table's key is a fact about the table, so it is configured beside it, and mode = "merge" makes the key the rule of every write:

[endpoint.orders_silver]
kind = "spark"
url_env = "LAKEHOUSE_URL"
table = "default.orders_silver"
order = ["id"]
mode = "merge"
key = ["id"]
rows : Seq<Event> = source("silver_replayed")
rows
| group(by = r => r.id)
| map(g => g.items | max_by(r => r.at))
| somes
| sort(by = r => r.id)
| target("orders_silver")

target sends only the deduplicated rows, and the system does the rest: a row whose key the table holds replaces that row, and a row whose key it does not hold is inserted, in one commit. A lakehouse runs it as MERGE INTO, a PostgreSQL table as INSERT … ON CONFLICT DO UPDATE, a SQLite table as INSERT OR REPLACE. The program is the one above, and which statement the kind runs is the kind's business, as a transaction is today.

Two rules follow. A write under mode = "merge" to an endpoint with no key is a check error naming the key. And the rows a target sends must themselves be unique by the key, so the deduplication stays in the program: a merge is told which row to keep among the incoming ones by the pipeline, and which to keep between an incoming row and a held one by the mode.

sink under mode = "merge" writes each row as it arrives, inserting or updating one at a time, which is the streaming load of the previous page without its blind spot: a copy that lands in a later batch updates the row the earlier one inserted.