MERGE: The Upsert Pattern
Snowflake · Data Types & SQL Fundamentals
Theory
MERGE applies a source of changes to a target table in one statement. You give a join condition (ON target.emp_id = source.emp_id) and clauses for each case: WHEN MATCHED THEN UPDATE or DELETE for rows found in both, and WHEN NOT MATCHED THEN INSERT for source rows with no match. Each clause can have an extra AND condition, for example WHEN MATCHED AND source.is_deleted THEN DELETE.
If one target row matches several source rows, the result would be ambiguous, so Snowflake raises an error by default (ERROR_ON_NONDETERMINISTIC_MERGE). For example, merging staging rows for Ravi (new salary) and Karan (new hire) updates Ravi and inserts Karan in one pass.
Why we use it
Without MERGE you need separate UPDATE and INSERT statements, with the risk of missing or double-processing rows between them. MERGE does both in one atomic statement, which is why incremental loads rely on it.
When to use it
Use MERGE to load changed data (often from a stream) into a target, for upserts and for applying deletes. For pure append data, a plain INSERT is simpler and cheaper. Deduplicate the source first so each key appears once.
Matching Source to Target
MERGE INTO target USING source ON <join condition> compares every source row to the target by a key column; what happens next depends on whether a matching target row was found.
WHEN MATCHED vs WHEN NOT MATCHED
WHEN MATCHED THEN UPDATE changes an existing row's columns; WHEN NOT MATCHED THEN INSERT adds it as new. A MATCHED branch can also DELETE, useful for propagating source deletions (common when the source is a Stream).
Why This Beats Separate INSERT/UPDATE
Without MERGE you'd need to first check row existence, then branch into an INSERT or an UPDATE yourself — MERGE does that check-and-branch in one atomic statement, which is both simpler and avoids race conditions between the check and the write.
How it works
- Read a source row
- Join it to the target with the ON condition
- Does a target row match? Yes: Apply WHEN MATCHED: UPDATE or DELETE. No: Apply WHEN NOT MATCHED: INSERT.
- Do several source rows match one target row? Yes: Error by default: deduplicate the source. No: Continue with the next row.
- Commit all changes together
Example
MERGE INTO employees AS target
USING staging_employees AS source
ON target.emp_id = source.emp_id
WHEN MATCHED THEN
UPDATE SET target.salary = source.salary, target.department = source.department
WHEN NOT MATCHED THEN
INSERT (emp_id, name, department, salary)
VALUES (source.emp_id, source.name, source.department, source.salary);Key points
- One atomic statement instead of separate INSERT and UPDATE logic
- WHEN MATCHED can also DELETE, not just UPDATE
- Extra AND conditions on clauses handle cases like soft deletes
- Duplicate source keys cause a nondeterministic-merge error by default
- The standard pattern for loading stream changes into a target