MERGE & Upserts: Handling Replays

# MERGE & Upserts: Handling Replays Sources resend data. Late events arrive. Your load must converge, not duplicate. ```sql -- Postgres: replay-safe upsert INSERT INTO dim_customers (customer_id, name, tier) VALUES (1, 'Acme', 'enterprise') ON CONFLICT (customer_id) DO UPDATE SET name = EXCLUDED.name, tier = EXCLUDED.tier; -- Warehouse (BigQuery/Snowflake): MERGE MERGE INTO mart_orders AS t USING stg_orders AS s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET status = s.status, amount_cents = s.amount_cents WHEN NOT MATCHED THEN INSERT (order_id, customer_id, ordered_at, amount_cents, status) VALUES (s.order_id, s.customer_id, s.ordered_at, s.amount_cents, s.status); ``` ## Rules 1. **Every incremental load needs a merge key** (usually the source PK). No key, no safe replay — fix the key before building the pipeline. 2. **`ON CONFLICT DO NOTHING`** for insert-only streams (events, logs); `DO UPDATE` for slowly-changing dimensions. 3. **MERGE is SCD-1 in one statement** (overwrite latest). SCD-2 history still needs explicit effective-date logic — MERGE alone won't version rows. 4. **Test the replay:** run the load twice in staging and diff. Zero new rows on the second run is the acceptance test.