MERGE (upsert)

Incremental load: a batch arrived with new orders and orders whose status changed. Deleting and reinserting everything is expensive; inserting only the new rows leaves the old status in place. MERGE matches each row of the batch with the table by key and decides what to do.

MERGE INTO silver.orders AS target
USING daily_batch AS source
ON target.id = source.id
WHEN MATCHED AND source.updated_at > target.updated_at THEN
  UPDATE SET *
WHEN NOT MATCHED THEN
  INSERT *
WHEN NOT MATCHED BY SOURCE AND target.order_date >= current_date() - INTERVAL 7 DAYS THEN
  DELETE;

• WHEN MATCHED: the key exists on both sides (here, it only updates if the data is newer).
• WHEN NOT MATCHED: it exists only in the batch, so it is inserted.
• WHEN NOT MATCHED BY SOURCE: it exists only in the table (it disappeared from the source). Use it with a filter, otherwise it deletes the whole table except what came in the batch.

The rule that breaks MERGE the most

If two rows of the batch match the same row of the table, Delta fails (the update would be ambiguous). Deduplicate the batch by key first (see Deduplication).

The same idea, running here

SQLite has no MERGE, but it has upsert with INSERT … ON CONFLICT. The effect is the same for the first two cases: it updates 106 and inserts 111.

INSERT INTO orders (id, customer_id, order_date, status, amount) VALUES
  (106, 3, '2024-06-03', 'delivered', 250.0),
  (111, 6, '2024-06-06', 'delivered', 80.0)
ON CONFLICT(id) DO UPDATE SET status = excluded.status, amount = excluded.amount;

SELECT id, customer_id, status, amount FROM orders WHERE id IN (106, 111) ORDER BY id;

id  | customer_id | status    | amount
--- | ----------- | --------- | ------
106 | 3           | delivered | 250
111 | 6           | delivered | 80

• A merge on a large table reads the files that may contain the batch keys. Filtering the target by partition or date in the ON (and Z-ORDER/clustering by the key) reduces what is read.
• MERGE is a Delta transaction: either the whole batch goes in, or nothing changes.