MERGE and source coverage

Opening your lesson

MERGE compares a source feed with a target table. The ON condition defines identity. WHEN MATCHED can update the existing row; WHEN NOT MATCHED can insert a source row with no target match. The clauses are independent: an insert clause does not update existing orders.

Aster Supply's changes feed contains updated orders 2 and 4 and a new order 20. Match by order id, then decide which columns the feed owns. A full source snapshot may also justify WHEN NOT MATCHED BY SOURCE THEN DELETE. An incremental feed usually does not: an order absent from today's changes may still be valid. A source with multiple rows for one target key can make a merge ambiguous. Deduplicate it using a stated ordering rule before writing, rather than relying on whichever record the engine happens to see first.

MERGE INTO main.silver.work_orders t USING main.bronze.changes s ON t.id=s.id WHEN MATCHED THEN UPDATE SET amount=s.amount,status=s.status;

The matched clause updates existing ids; an insert-only merge leaves their old values.

Choose your character

Your Work Gym account