Upsert generator

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

Name your table, key and columns and get the statement Amazon Redshift 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 Amazon Redshift incrementally.

Generate the statement

Statement · 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;

How the upsert works in 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.

Before you run it

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.

Questions people ask

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.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into Amazon Redshift 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