MERGE combines INSERT, UPDATE, and DELETE into one statement matched on a join key. The risks are subtle.
Bug 1: Non-unique join key
If the source has more than one row per join key, MERGE behavior is undefined. Snowflake errors by default; SQL Server may silently overwrite. Always assert uniqueness on the source.
Bug 2: Forgotten WHERE on the change predicate
WHEN MATCHED THEN UPDATE without 'AND s.x <> t.x' rewrites every row every run, making MERGE non-idempotent for downstream CDC.
Bug 3: Mixing INSERT/UPDATE conditions in one branch
MERGE evaluates branches top-to-bottom. Re-order them and behavior changes. Keep one decision per branch.
Bug 4: Forgetting OUTPUT for audits
If you need to know what changed, MERGE without OUTPUT throws away the answer. Always emit to an audit table during dimension loads.
Run the MERGE example in the playground to see column-level lineage from staging into the target dim and the audit log at the same time.