Pattern
Apply a change feed
Reduce a batch of changes to the last one per key, deletes included, then apply it with one MERGE.
QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY seq DESC) = 1 first: a MERGE with two source rows for a key
fails or keeps an arbitrary one. Then WHEN MATCHED AND op = 'D' THEN DELETE, WHEN MATCHED THEN UPDATE,
WHEN NOT MATCHED AND op <> 'D' THEN INSERT. Never drop the deletes first. With a full extract instead of a feed, a
missing key is a delete: close its current version.
WITH changes(seq, op, customer_id, city) AS (VALUES (1, 'I', 7, 'Bern'), (2, 'U', 7, 'Zurich'), (3, 'I', 8, 'Paris'), (4, 'D', 8, NULL), (5, 'U', 2, 'Lille')) SELECT customer_id, op, city FROM changes QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY seq DESC) = 1 ORDER BY customer_id;
| customer_id | op | city |
|---|---|---|
| 2 | U | Lille |
| 7 | U | Zurich |
| 8 | D | NULL |