Gerador de upsert

Upsert no Amazon Redshift: DELETE … USING + INSERT (staging pattern)

Nomeie tabela, chave e colunas e receba a instrução que o Amazon Redshift 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 Amazon Redshift de forma incremental.

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

Gere a instrução

Instrução · DELETE … USING + INSERT (staging pattern)

BEGIN;

DELETE FROM "deals"
USING "deals_staging" AS s
WHERE "deals"."id" = s."id";

INSERT INTO "deals" ("id", "name", "stage", "amount", "owner_id", "updated_at")
SELECT "id", "name", "stage", "amount", "owner_id", "updated_at"
FROM (
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY "id" ORDER BY "updated_at" DESC) AS rn
    FROM "deals_staging"
  ) WHERE rn = 1
);

COMMIT;

Como o upsert funciona no Amazon Redshift

Redshift gained a native MERGE in 2023, but the pattern AWS documents for loads, and the one the generator emits, is still delete-and-insert from a staging table inside one transaction: delete every target row whose key appears in staging, then insert the deduped staging rows. It is simple, restartable, and fast when staging shares the target's DISTKEY so the delete join runs on-node.

Load staging with COPY from S3 in multiple files so every slice works in parallel. A SORTKEY on the load timestamp keeps recent rows together, and the DISTKEY on the join id keeps merges local. Deleted rows are only marked until VACUUM reclaims them, so a table merged hourly needs VACUUM DELETE ONLY and ANALYZE on a schedule or query plans drift.

Antes de executar

This is the merge pattern AWS documents for Redshift: replace matched rows wholesale inside one transaction. Give the staging table the same DISTKEY as the target so the DELETE join stays on-node.
Redshift's native MERGE (2023) works too, but it requires both a WHEN MATCHED and a WHEN NOT MATCHED branch and a source with exactly one row per key; the delete-and-insert form is easier to reason about at scale.
Deletes leave ghost rows until VACUUM runs; on a busy table schedule VACUUM DELETE ONLY and ANALYZE after the batch.

Perguntas frequentes

Should I use Redshift's MERGE instead?

You can. It needs both a WHEN MATCHED and a WHEN NOT MATCHED branch and a source with one row per key. There is also a MERGE … REMOVE DUPLICATES form that replaces matched rows wholesale, which is the same semantics as this pattern.

Why is the DELETE step slow?

Staging and target have different distribution, so Redshift redistributes one side for the join. CREATE the staging table with the same DISTKEY as the target.

Do I need VACUUM after every batch?

Not every batch, but regularly. Deleted rows stay on disk as ghost rows until VACUUM DELETE ONLY runs; auto-vacuum handles light churn, hourly merges usually need a scheduled one.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no Amazon Redshift 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