Slowly Changing Dimension Type 2 With MERGE: Effective Dating, Idempotent Reruns, Two Changes in One Batch and Late-Arriving Records

History as rows

A slowly changing dimension of type 2 keeps history by adding a row whenever a tracked attribute changes. The Kimball Group's description asks for a surrogate key, because one natural key now has several rows, and at least three extra columns: a row effective date, a row expiration date and a current-row indicator. Facts recorded on a given day join to the version that was current on that day, which is what makes last quarter's revenue by customer tier stay the same after the customer moves tier.

Implementations usually run once per batch: compare the incoming snapshot or change set with the current rows, close the versions that changed, and insert new ones. MERGE can do both in one statement. The Delta Lake documentation shows the pattern for SCD type 2, PostgreSQL has supported MERGE since version 15, and Apache Iceberg supports MERGE INTO through Spark SQL. The statement is short; the guarantees around it are what this article is about.

The staged-union MERGE

One source row can either update the target row it matches or insert a row, not both. To close the old version and insert the new one, feed each changed record twice: once with its natural key as the merge key, so it matches and closes the current row, and once with a NULL merge key, so it never matches and is inserted. Records for new customers appear once and are inserted. A hash of the tracked attributes decides whether anything changed.

Executed with DuckDB 1.5.3 against the fixture below; the syntax is close to PostgreSQL 15+ and Spark SQL with Delta or Iceberg.

MERGE INTO dim_customer AS d
USING (
  SELECT s.customer_id AS merge_key, s.*, md5(concat_ws('|', s.city, s.tier)) AS row_hash
  FROM stg s
  UNION ALL
  SELECT NULL AS merge_key, s.*, md5(concat_ws('|', s.city, s.tier)) AS row_hash
  FROM stg s
  JOIN dim_customer c ON c.customer_id = s.customer_id AND c.is_current
  WHERE c.row_hash <> md5(concat_ws('|', s.city, s.tier))
    AND s.changed_at > c.valid_from          -- late records go to quarantine instead
) AS u
ON d.customer_id = u.merge_key AND d.is_current
WHEN MATCHED AND d.row_hash <> u.row_hash AND u.changed_at > d.valid_from THEN
  UPDATE SET valid_to = u.changed_at, is_current = false
WHEN NOT MATCHED THEN
  INSERT VALUES (nextval('dim_sk'), u.customer_id, u.city, u.tier, u.row_hash,
                 u.changed_at, DATE '9999-12-31', true);

One detail appears when you run it: surrogate keys have gaps, because the sequence is evaluated for candidate rows that end up not being inserted. Gaps are harmless; code that assumes consecutive keys is not.

Idempotent reruns

Batch jobs are re-run after partial failures, by schedulers that retry and by operators who are not sure whether the last run finished. A rerun with the same staging data must change nothing. The statement above has that property because both branches compare against the current row: after the first run the current row carries the new hash, so the matched branch finds no difference and the NULL-key branch selects nothing. In the worked example, running the second day's batch twice leaves exactly the same four rows.

Two common variants lose the property. Inserting new versions unconditionally from the staging table, without the hash comparison, adds a duplicate current row on every rerun. Closing rows with valid_to = current_date instead of the record's own change time makes the history depend on when the job ran rather than on when the change happened.

Two changes for one key in one batch

Change-data feeds deliver every change, so one batch can contain a customer who moved tier on 20 October and city on 21 October. Both records match the same current row. The PostgreSQL MERGE documentation says the join should produce at most one candidate change row per target row; if it produces more, a repeated UPDATE raises a cardinality violation, as the SQL standard requires. Delta Lake documents that a merge can fail when several source rows match the same target row. Failing is the good outcome.

Running the same statement in DuckDB 1.5.3 did not fail. It closed the old version once and inserted both new versions, leaving two rows with is_current = true for one customer. Every query that joins on the current row now double-counts that customer. Do not depend on any engine's behaviour here. Either reduce the batch to the latest change per key, if intermediate versions do not matter, or rank changes per key and run one MERGE per rank in order, if they do. Then assert the invariant after every run.

Both checks must return no rows; fail the job if they do.

-- Run once per rank: stg_rank_n holds the n-th change of each key in this batch
CREATE TABLE stg_ranked AS
SELECT *, row_number() OVER (PARTITION BY customer_id ORDER BY changed_at) AS rk FROM stg_raw;

-- After the last rank: one current row per natural key, and no inverted intervals
SELECT customer_id FROM dim_customer WHERE is_current GROUP BY customer_id HAVING count(*) > 1;
SELECT customer_sk FROM dim_customer WHERE valid_to < valid_from;

Late-arriving records

A record dated 10 October can arrive after the version effective from 15 October is already current, because an upstream system replayed it or a partition was reprocessed. Without the changed_at > valid_from guards, the MERGE closes the current version with an expiration date earlier than its effective date and makes the older record current. History is now wrong in two places, and nothing errors. The guard sends such records nowhere; route them explicitly to a quarantine table, where a person or a separate repair job inserts them between the existing versions, splitting the interval they fall into. That repair is rare enough to be a manual step and dangerous enough to deserve one.

Worked example

Every row of this table was produced by running the statements against an in-memory DuckDB database; the checks in the previous section catch both unguarded failures.

Operating it

• Hash only the tracked attributes, in a fixed order with a separator, and store the hash on the row.
• Use the source change timestamp for effective dating, never the job run time.
• Rank or deduplicate per key before the MERGE; assert one current row per key after it.
• Quarantine records older than the current version and alert on the quarantine count.
• Keep the open-ended expiration as a fixed far-future date or NULL, consistently, so range joins work.

Failure modes

• Unconditional inserts that duplicate current rows on every rerun.
• Effective dates taken from the job clock.
• Several changes per key in one batch, which errors in some engines and silently corrupts in others.
• Late records closing a version before it began.
• Tracking volatile attributes such as last login in a type 2 dimension, which turns it into an event log.

Practice and related reading

Further reading:

• Idempotent backfills with partition overwrite and run manifests
• Change data capture
• Iceberg with Trino

Sources and scope

Checked against these sources on 21 October 2026. Examples and figures are illustrative, not measurements.

• Kimball Group: Slowly Changing Dimension techniques (Type 2: add new row)
• Delta Lake documentation: Table deletes, updates, and merges (SCD Type 2 with merge)
• PostgreSQL documentation: MERGE
• Apache Iceberg documentation: Spark writes (MERGE INTO)