MERGE and UPSERT Patterns

Concepts: paIdempotency

What They Want to Hear 'MERGE matches source rows against target rows on a key. When matched, it updates. When not matched, it inserts. For idempotency, the key must be the business key (e.g., order_id), not a surrogate. To optimize a slow MERGE: (1) partition the target table and scope the MERGE to affected partitions only, (2) stage the delta into a temp table with dedup applied before merging, (3) if the delta exceeds 30% of the partition, switch to partition REPLACE instead.' This is the answer that shows you have hit the MERGE performance wall and solved it.