MERGE & Upsert Patterns cheat sheet

MERGE syntax across warehouses, dedup before merge, SCD2 upserts, and the idempotency rules that make reruns safe.

SQL & DatabasesAdvanced5 sections

The basic MERGE

merge into analytics.silver.customers t using staging.customers s on t.customer_id = s.customer_id when matched then update set t.email = s.email, t.updated_at = s.updated_at when not matched then insert (customer_id, email, updated_at) values (s.customer_id, s.email, s.updated_at): The canonical upsert. Works as written in Snowflake, BigQuery, Databricks SQL, and Postgres 15+.
when matched and s.updated_at > t.updated_at then update set ...: Guard the update so an out-of-order late batch cannot overwrite newer data with older values.
when matched and s.is_deleted then delete: Handles soft-delete CDC feeds in the same statement instead of a second pass.

with ranked as ( select *, row_number() over ( partition by customer_id order by updated_at desc ) as rn from staging.customers ) select * from ranked where rn = 1: MERGE errors (Snowflake, BigQuery) or picks nondeterministically when the source has duplicate keys. Always dedup upstream.
ERROR_ON_NONDETERMINISTIC_MERGE = TRUE: Snowflake session parameter — leave it on. Silent nondeterministic merges are far worse than a failed load.
qualify row_number() over (partition by id order by ts desc) = 1: Snowflake and DuckDB shorthand that removes the CTE wrapper entirely.

insert into t ... on conflict (id) do update set email = excluded.email: Postgres upsert. Requires a unique constraint on the conflict target; excluded refers to the proposed row.
insert or replace into t values (...): DuckDB and SQLite shorthand. Replaces the whole row, so unspecified columns revert to defaults.
create or replace table t as select ...: The full-refresh alternative. On small dimensions this is simpler, atomic, and cheaper than maintaining MERGE logic.

merge into analytics.silver.dim_customer t using changes s on t.customer_id = s.customer_id and t.is_current when matched and s.row_hash <> t.row_hash then update set t.valid_to = s.effective_at, t.is_current = false: Step one closes the outgoing version. Match on is_current so history rows are never touched.
insert into dim_customer select ..., s.effective_at as valid_from, null as valid_to, true as is_current from changes s: Step two inserts the new version. Two statements in one transaction beats a clever single MERGE.
md5(concat_ws('|', coalesce(email,''), coalesce(segment,''))): Row hash for change detection. coalesce every column or a single null makes the hash null and every row looks changed.

delete from t where load_date = '2026-08-01'; insert into t select ...: Delete-insert by partition. Simpler than MERGE and trivially idempotent when the batch boundary is a clean partition.
begin; merge ...; merge ...; commit;: Wrap multi-statement upserts in an explicit transaction so a mid-flight failure cannot leave two current rows.
merge on a natural key with no unique index: The classic production bug. Verify key uniqueness with a test before the first run, not after the duplicates appear.

From DataLane — tutorials at/blog, practice SQL live in theplayground.