Sources resend data. Late events arrive. Your load must converge, not duplicate.
-- 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
- Every incremental load needs a merge key (usually the source PK). No key, no safe replay — fix the key before building the pipeline.
ON CONFLICT DO NOTHINGfor insert-only streams (events, logs);DO UPDATEfor slowly-changing dimensions.- MERGE is SCD-1 in one statement (overwrite latest). SCD-2 history still needs explicit effective-date logic — MERGE alone won't version rows.
- Test the replay: run the load twice in staging and diff. Zero new rows on the second run is the acceptance test.