Generador de upsert

Upsert en Snowflake: MERGE INTO … QUALIFY-deduped USING

Nombra tu tabla, clave y columnas y obtén la sentencia que Snowflake acepta de verdad, con una guarda para que una fila antigua nunca sobrescriba una nueva. Debajo: cómo funciona la mecánica, qué se rompe y cómo Datrise carga Snowflake de forma incremental.

Las notas técnicas de esta página están en inglés.

Genera la sentencia

Sentencia · 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");

Cómo funciona el upsert en 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 ejecutarla

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.

Preguntas frecuentes

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.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en Snowflake con esta misma mecánica, una marca de agua sobre updated-at y columnas tipadas, así que la sentencia de arriba es la que corre por ti. Únete a la lista de espera para acceso anticipado.

Explorar el catálogo de integraciones