+1 (726) 227-3497
RougeWarehouse

MERGE, Upserts and SCD Type 2 in Amazon Redshift

Every ingestion path into Redshift — auto-copy from S3, streaming ingestion, Zero-ETL from Aurora — lands raw rows. The next question is always the same: how do you fold those rows into a curated table without duplicating them when a batch replays, and how do you keep history when a dimension attribute changes?

Redshift has supported the SQL standard MERGE statement since 2023, and it replaces the old delete-then-insert idiom most Redshift codebases still carry. This tutorial covers the modern pattern end to end: staging, deduplication, a Type 1 upsert with MERGE, a Type 2 slowly changing dimension, idempotency, and what to check when performance disappoints.

The setup

A target dimension and a staging table loaded by whatever ingestion job you use:

CREATE TABLE dim_customer (
  customer_id   BIGINT NOT NULL,
  email         VARCHAR(320),
  plan          VARCHAR(32),
  country       VARCHAR(2),
  updated_at    TIMESTAMP NOT NULL
)
DISTKEY (customer_id)
COMPOUND SORTKEY (customer_id);

CREATE TABLE stg_customer (LIKE dim_customer);

Staging tables should match the target's distribution key. A MERGE joins staging to target on the key; if both are distributed on customer_id, the join is collocated and no data moves between nodes. This one decision is usually the difference between a merge that takes seconds and one that takes minutes.

Step 1: deduplicate the staging batch

MERGE raises an error if more than one source row matches the same target row. Change-data-capture batches almost always contain several versions of the same key, so collapse them to the latest first:

CREATE TEMP TABLE stg_customer_latest AS
SELECT customer_id, email, plan, country, updated_at
FROM (
  SELECT s.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY updated_at DESC
         ) AS rn
  FROM stg_customer s
)
WHERE rn = 1;

If your source emits deletes, carry the operation flag through (op = 'D') rather than dropping those rows — you need them in the merge.

Step 2: the Type 1 upsert

Type 1 means "overwrite; no history". That is one statement:

MERGE INTO dim_customer t
USING stg_customer_latest s
  ON t.customer_id = s.customer_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET email = s.email,
             plan = s.plan,
             country = s.country,
             updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, plan, country, updated_at)
  VALUES (s.customer_id, s.email, s.plan, s.country, s.updated_at);

Two details matter:

  • The AND s.updated_at > t.updated_at guard makes the statement idempotent. Re-running the same batch after a failed pipeline run changes nothing, because no staging row is newer than what is already in the target. Without the guard you get a no-op update that still rewrites blocks and bloats the table.
  • Redshift also supports the shorthand MERGE INTO t USING s ON <cond> REMOVE DUPLICATES, which deletes matching target rows and inserts all source rows. It is concise, but it discards columns the source does not carry and it cannot express a guard clause. Prefer the explicit form for anything you have to maintain.

Handling deletes is one more clause:

WHEN MATCHED AND s.op = 'D' THEN DELETE

Order matters: Redshift evaluates WHEN MATCHED clauses top to bottom and applies the first that qualifies, so put the delete clause before the update clause.

Step 3: Type 2 history

A Type 2 dimension keeps every version of a row with validity dates. Add the bookkeeping columns:

CREATE TABLE dim_customer_scd2 (
  customer_sk   BIGINT IDENTITY(1,1),
  customer_id   BIGINT NOT NULL,
  email         VARCHAR(320),
  plan          VARCHAR(32),
  country       VARCHAR(2),
  valid_from    TIMESTAMP NOT NULL,
  valid_to      TIMESTAMP NOT NULL DEFAULT '9999-12-31',
  is_current    BOOLEAN NOT NULL DEFAULT TRUE
)
DISTKEY (customer_id)
COMPOUND SORTKEY (customer_id, valid_from);

Type 2 needs two operations against the same rows — close the old version, insert the new one — so it is naturally two statements inside one transaction. Detect real changes first, so that a batch where nothing changed produces no new versions:

BEGIN;

CREATE TEMP TABLE changed AS
SELECT s.*
FROM stg_customer_latest s
JOIN dim_customer_scd2 d
  ON d.customer_id = s.customer_id
 AND d.is_current
WHERE s.updated_at > d.valid_from
  AND (d.email    IS DISTINCT FROM s.email
    OR d.plan     IS DISTINCT FROM s.plan
    OR d.country  IS DISTINCT FROM s.country);

-- 1. close the superseded versions
MERGE INTO dim_customer_scd2 d
USING changed c
  ON d.customer_id = c.customer_id AND d.is_current
WHEN MATCHED THEN
  UPDATE SET valid_to = c.updated_at,
             is_current = FALSE;

-- 2. insert new versions + brand-new customers
INSERT INTO dim_customer_scd2
  (customer_id, email, plan, country, valid_from, valid_to, is_current)
SELECT c.customer_id, c.email, c.plan, c.country,
       c.updated_at, '9999-12-31'::TIMESTAMP, TRUE
FROM changed c
UNION ALL
SELECT s.customer_id, s.email, s.plan, s.country,
       s.updated_at, '9999-12-31'::TIMESTAMP, TRUE
FROM stg_customer_latest s
LEFT JOIN dim_customer_scd2 d
  ON d.customer_id = s.customer_id
WHERE d.customer_id IS NULL;

COMMIT;

Use IS DISTINCT FROM rather than <> so that a column going from NULL to a value counts as a change. That single operator is the most common source of "why did history stop tracking that field" tickets.

Because the change detection compares attribute values, this block is idempotent too: replay the same batch and changed comes back empty.

Step 4: query the history

Point-in-time lookups become a range predicate, which the sort key on (customer_id, valid_from) serves well:

-- the plan each customer was on when the order was placed
SELECT o.order_id, o.placed_at, d.plan
FROM fact_orders o
JOIN dim_customer_scd2 d
  ON d.customer_id = o.customer_id
 AND o.placed_at >= d.valid_from
 AND o.placed_at <  d.valid_to;

-- current state only
SELECT * FROM dim_customer_scd2 WHERE is_current;

If most consumers want current state, give them a view (CREATE VIEW dim_customer AS SELECT ... WHERE is_current) rather than making every analyst remember the filter.

Performance: what to check when a merge is slow

Collocation. MERGE compiles into a join, a delete and an insert. If the staging table is DISTSTYLE EVEN and the target is DISTKEY(customer_id), every merge redistributes the whole staging batch. Match the distribution key, or use DISTSTYLE ALL on staging when the batch is small.

SELECT "table", diststyle, sortkey1, unsorted, tbl_rows
FROM SVV_TABLE_INFO
WHERE "table" IN ('dim_customer_scd2','stg_customer');

Unsorted rows and bloat. Merges produce deleted rows behind the scenes. Auto-vacuum normally keeps up, but a table merged every few minutes can drift. Watch unsorted in SVV_TABLE_INFO; if it stays above ~20% on a large table, look at batching the merge less often rather than reaching for manual VACUUM.

Statistics. Run ANALYZE on the staging table after loading it if the batch size varies wildly; the planner picks join strategies for the merge from those statistics.

Isolation. Wrap multi-statement Type 2 logic in an explicit transaction so readers never see a moment with two current rows for one key. Redshift's snapshot isolation makes the whole block atomic for concurrent queries.

Concurrency. Two merges into the same target serialize. If you have many small merges from parallel tasks, land them into one staging table and merge once — the fixed cost of a merge dominates for small batches.

Where materialized views fit instead

Not every derived table needs a merge. If the target is a straightforward aggregation over an append-only fact table, an incrementally refreshed materialized view does the work for you:

CREATE MATERIALIZED VIEW mv_daily_revenue
AUTO REFRESH YES
AS
SELECT DATE_TRUNC('day', placed_at) AS day,
       country,
       SUM(amount) AS revenue,
       COUNT(*)    AS orders
FROM fact_orders
GROUP BY 1, 2;

Redshift refreshes incrementally when the definition qualifies (most single-table aggregations and simple joins do) and falls back to a full recompute when it does not. Check which you are getting:

SELECT mv_name, is_stale, state, last_refresh_status
FROM SVV_MV_INFO;

Use merges for dimensional state and history; use materialized views for derived aggregates. Reaching for a merge where an auto-refreshing view would do is the most common source of unnecessary ELT code in Redshift warehouses.

A checklist for a production merge job

  1. Staging table distributed on the same key as the target.
  2. Deduplicate the batch to one row per key before merging.
  3. A guard clause (s.updated_at > t.updated_at, or attribute comparison) so replays are no-ops.
  4. Deletes handled explicitly if the source emits them.
  5. IS DISTINCT FROM for null-safe change detection in Type 2.
  6. Explicit transaction around multi-statement logic.
  7. A weekly look at SVV_TABLE_INFO for skew, unsorted and stats_off on merged tables.

If your warehouse still runs 2017-era delete-and-insert transformation scripts, or your dimensions lost history somewhere along the way, our Data Modeling & Architecture and Redshift Performance Optimization teams do exactly this kind of rebuild. Get in touch with the tables and load frequency and we will tell you what the pattern should look like.