Snowflake SQL Patterns

MERGE upserts — and when MERGE is the wrong tool

Upsert and flagged deletes with MERGE, why duplicate source keys error, and the fact that Snowflake has no WHEN NOT MATCHED BY SOURCE.

The problem

A change table (or a staging load) contains new rows, changed rows, and rows marked for deletion. You want the target table to catch up in one statement. That is what MERGE is for. Official Snowflake documentation describes it as inserting, updating, and deleting in the target based on a join to a source, including a change-log use case.

A working example

MERGE INTO target AS t
USING changes AS s
  ON t.id = s.id
WHEN MATCHED AND s.is_deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

Starting data:

target changes
(1, 10), (2, 20) (1, 11, false), (3, 30, false), (2, 20, true)

Executed in DuckDB 2.0.0, the target afterwards was (1, 11), (3, 30). Row 1 updated, row 2 deleted, row 3 inserted.

The same clause shape is supported by official Snowflake MERGE syntax: WHEN MATCHED [ AND predicate ] THEN UPDATE … | DELETE, and WHEN NOT MATCHED [ AND predicate ] THEN INSERT …. Official docs also require that a matching or not-matching clause without AND comes last in its group, otherwise later clauses are unreachable and the statement errors.

Deduplicate the source before the merge when a target key can match more than one source row. Official Snowflake documentation has a whole section on this (“Duplicate join behavior”). With the default ERROR_ON_NONDETERMINISTIC_MERGE = TRUE, a target row that matches multiple source rows in an UPDATE (or in some DELETE cases) returns an error. Official guidance is to GROUP BY the source key so each target row joins to one source row.

DuckDB 2.0.0 failed a two-source-row update against one target row with: “MERGE INTO command cannot affect the same target row more than once.” Different product, same class of bug.

MERGE INTO target AS t
USING (
  SELECT id, MAX(value) AS value, BOOL_OR(is_deleted) AS is_deleted
  FROM changes
  GROUP BY id
) AS s
  ON t.id = s.id
WHEN MATCHED AND s.is_deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

(BOOL_OR / MAX are a choice you must justify for your change log, not a universal rule.)

The gotcha: no WHEN NOT MATCHED BY SOURCE in Snowflake

Several dialects (Spark, BigQuery, SQL Server, and also DuckDB 2.0.0) accept a clause that deletes or updates target rows that have no match in the source:

-- Not Snowflake syntax
WHEN NOT MATCHED BY SOURCE THEN DELETE

Official Snowflake migration documentation is explicit: Snowflake has no equivalent clause. SnowConvert even emits an error-warning telling you to rewrite it as a separate post-MERGE DELETE/UPDATE, and the Snowpark Migration Accelerator shows a workaround: take the unmatched target keys with a LEFT JOIN and MERGE … WHEN MATCHED THEN DELETE.

Illustrative Snowflake-safe form (docs-verified pattern, not executed on Snowflake):

MERGE INTO target AS t
USING (
  SELECT t.id
  FROM target AS t
  LEFT JOIN source AS s
    ON t.id = s.id
  WHERE s.id IS NULL
) AS orphans
  ON t.id = orphans.id
WHEN MATCHED THEN DELETE;

A plain DELETE FROM target WHERE id NOT IN (SELECT id FROM source) is often clearer if you do not need to combine it with the upsert in one statement.

Do not paste WHEN NOT MATCHED BY SOURCE into a Snowflake worksheet because autocomplete or another warehouse taught you the phrase. Official MERGE syntax only documents WHEN NOT MATCHED as an insert path (optionally AND a predicate).

A second official gotcha: if the source has duplicate keys and those keys are new (no target match), Snowflake can insert one target row per source copy. Deduping protects both the update path and the insert path.

When not to use this

Verification

The upsert-plus-flagged-delete example was executed in DuckDB 2.0.0 and left target rows (1, 11) and (3, 30). A two-row source against one target key raised an error in DuckDB. Snowflake clause syntax, the unreachable-clause rule, duplicate-join / ERROR_ON_NONDETERMINISTIC_MERGE behavior, and the absence of WHEN NOT MATCHED BY SOURCE are from official Snowflake documentation (MERGE reference and migration guides). Snowflake MERGE was not executed.

Official docs