Upsert generator

Upsert into Snowflake: MERGE INTO … QUALIFY-deduped USING

Name your table, key and columns and get the statement Snowflake actually accepts, with a guard so an older row never overwrites a newer one. Below it: how the mechanic works, what breaks, and how Datrise loads Snowflake incrementally.

Generate the statement

Statement · MERGE INTO … QUALIFY-deduped USING

MERGE INTO "deals" AS t
USING (
  SELECT "id", "name", "stage", "amount", "owner_id", "updated_at"
  FROM "deals_staging"
  QUALIFY ROW_NUMBER() OVER (PARTITION BY "id" ORDER BY "updated_at" DESC) = 1
) AS s
  ON t."id" = s."id"
WHEN MATCHED AND s."updated_at" > t."updated_at" THEN UPDATE SET
  t."name" = s."name",
  t."stage" = s."stage",
  t."amount" = s."amount",
  t."owner_id" = s."owner_id",
  t."updated_at" = s."updated_at"
WHEN NOT MATCHED THEN
  INSERT ("id", "name", "stage", "amount", "owner_id", "updated_at")
  VALUES (s."id", s."name", s."stage", s."amount", s."owner_id", s."updated_at");

How the upsert works in Snowflake

Snowflake's MERGE reads like the SQL standard, and the cost model is what you need to understand. Tables are immutable micro-partitions; a MERGE rewrites every partition that holds a changed row. So the bill tracks how scattered your changed keys are across partitions, not how many rows changed. The generated statement dedupes the source with QUALIFY, because Snowflake aborts a MERGE when two source rows target one row, and puts the watermark test on the MATCHED branch.

The efficient pipeline is stage, COPY INTO a transient staging table, MERGE, truncate. Cluster the target on the key when batches are wide, or carry a date column in the ON clause so the merge prunes to recent partitions. Snowflake upper-cases unquoted identifiers, which is why the statement quotes every name; keep the quoting consistent with how the table was created.

Before you run it

Snowflake fails a MERGE with "Duplicate row detected during DML action" when two source rows hit one target row. The QUALIFY subquery keeps only the latest version per key so the merge is deterministic.
Credits scale with the micro-partitions Snowflake rewrites, not with the rows you change. Cluster the target on the key (or on a date the ON clause can filter) so a small batch touches few partitions.
Load the staging table with COPY INTO from a stage, then MERGE; row-by-row INSERTs from a client are the slowest and most expensive path.

Questions people ask

Why does my MERGE fail with Duplicate row detected during DML action?

Two rows in the source matched one target row. The QUALIFY subquery keeps the latest row per key. ERROR_ON_NONDETERMINISTIC_MERGE = FALSE silences the error but picks a row arbitrarily; avoid it.

Why is a small MERGE so slow on a large table?

Because Snowflake rewrites whole micro-partitions. If the changed keys are spread across the table, most of it is rewritten. Cluster on the key or filter the target by a recent date range in ON.

MERGE or a Snowpipe / Dynamic Table?

Snowpipe appends. Dynamic Tables can materialise a latest-version view over an append log. MERGE is still the right tool when you need the base table itself to hold one row per key.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into Snowflake with this exact mechanic, a watermark on updated-at, and typed columns, so the statement above is what runs on your behalf. Join the waitlist to get early access.

Browse the integration catalog