Gerador de upsert

Upsert no Snowflake: MERGE INTO … QUALIFY-deduped USING

Nomeie tabela, chave e colunas e receba a instrução que o Snowflake realmente aceita, com uma guarda para que uma linha antiga nunca sobrescreva uma nova. Abaixo: como a mecânica funciona, o que quebra e como a Datrise carrega o Snowflake de forma incremental.

As notas técnicas desta página estão em inglês.

Gere a instrução

Instrução · 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");

Como o upsert funciona no 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.

Antes de executar

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.

Perguntas frequentes

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.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no Snowflake com exatamente esta mecânica, uma marca d'água sobre updated-at e colunas tipadas, então a instrução acima é a que roda por você. Entre na lista de espera para acesso antecipado.

Explorar o catálogo de integrações