Join / merge upsert · All operations

Keep the history of changes

Close the current version of a changed row and add a new one, so the target keeps every past version with its validity period.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Close the current version of a changed row and add a new one

SQL

CREATE TEMP TABLE dim AS SELECT * FROM (VALUES
  (1, 'Leeds', DATE '2025-01-01', NULL::DATE), (2, 'York', DATE '2025-01-01', NULL), (3, NULL, DATE '2025-01-01', NULL),
  (5, 'Hull', DATE '2025-01-01', NULL), (2, 'Ely', DATE '2024-01-01', DATE '2025-01-01')) t(id, city, valid_from, valid_to);
-- the incoming snapshot, one row for each key: 1 and 3 changed (3 from NULL), 2 unchanged, 4 new, 5 absent, and a NULL key
CREATE TEMP TABLE incoming AS SELECT * FROM (VALUES (1, 'Bath'), (2, 'York'), (3, 'Wells'), (4, 'Rye'), (NULL, 'x')) t(id, city);
BEGIN;
-- close the open version of every changed row
UPDATE dim SET valid_to = DATE '2026-03-01'
FROM incoming WHERE dim.valid_to IS NULL AND dim.id = incoming.id AND dim.city IS DISTINCT FROM incoming.city;
-- open a version for every incoming row that has no open version with the same values (changed rows, new keys)
INSERT INTO dim
SELECT id, city, DATE '2026-03-01', NULL FROM incoming
WHERE id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim d WHERE d.id = incoming.id AND d.valid_to IS NULL AND d.city IS NOT DISTINCT FROM incoming.city);
COMMIT;
SELECT * FROM dim ORDER BY id, valid_from;
SELECT CASE WHEN ((SELECT list((id, city, valid_from, valid_to) ORDER BY id, valid_from) IS NOT DISTINCT FROM [
      (1, 'Leeds', DATE '2025-01-01', DATE '2026-03-01'), (1, 'Bath', DATE '2026-03-01', NULL),
      (2, 'Ely', DATE '2024-01-01', DATE '2025-01-01'), (2, 'York', DATE '2025-01-01', NULL),
      (3, NULL, DATE '2025-01-01', DATE '2026-03-01'), (3, 'Wells', DATE '2026-03-01', NULL),
      (4, 'Rye', DATE '2026-03-01', NULL), (5, 'Hull', DATE '2025-01-01', NULL)] FROM dim)
  AND (SELECT count(*) = count(DISTINCT id) FROM dim WHERE valid_to IS NULL)) THEN 'as expected' ELSE error('the dimension should have 8 rows: keys 1 and 3 closed on 2026-03-01 and reopened, key 2 untouched, key 4 added, key 5 left open, no row for the NULL key, and one open row for each key') END AS verdict;

The statement builds its own data.
It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the dimension should have 8 rows: keys 1 and 3 closed on 2026-03-01 and reopened, key 2 untouched, key 4 added, key 5 left open, no row for the NULL key, and one open row for each key”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache Hop (1)

Dimension lookup/updateread 2026-09-27
Maintain or look up a Kimball slowly changing dimension, type 1 and type 2

Bruin (2)

scd2_by_columnread 2026-09-27
Keep type-2 history, opening a new version when any non-key column value changes
scd2_by_timeread 2026-09-27
Keep type-2 history, opening a new version when the time-based incremental_key advances

dbt-utils (3)

checkread 2026-09-27
Detect changed rows by comparing a list of columns with their stored values
snapshotread 2026-09-27
Record changes to a mutable table over time as type-2 slowly changing dimension rows
timestampread 2026-09-27
Detect changed rows by comparing an updated_at column with the last snapshot

Duckle (2)

SCD Type 2read 2026-09-25
SCD type 2: close changed versions, keep history, insert new versions
SCD Type 3read 2026-09-25
SCD type 3: keep the previous value of tracked columns in sibling columns

Pentaho Data Integration (1)

Dimension lookup/updateread 2026-09-27
Maintain or look up a slowly changing dimension in a data warehouse

SQLMesh (2)

SCD_TYPE_2_BY_COLUMNread 2026-09-27
Track row history with valid_from and valid_to, detecting change by comparing listed columns
SCD_TYPE_2_BY_TIMEread 2026-09-27
Track row history with valid_from and valid_to, detecting change by an updated-at column

Talend (2)

Database slowly changing dimension ELT components (family)read 2026-09-27
9 components tracking changes in an SCD table through SQL queries: tDBSCDELT, tMysqlSCDELT, tOracleSCDELT
Database slowly changing dimension components (family)read 2026-09-27
14 components tracking changes in an SCD table: tDBSCD, tMysqlSCD, tOracleSCD, tPostgresqlSCD

Background

A Type 2 slowly changing dimension (SCD) tracks history by creating a new record for each change, so one natural key can have many versions.^[1] A Type 2 SCD can mark its versions with effective date columns, where a null end date indicates the current version.^[1]

When to use it

With a Type 2 SCD, an aggregate of facts by an attribute keeps reflecting the attribute's value at the time of each transaction, with no update needed.^[1] Type 2 SCDs are not a good choice if the dimensional model is subject to frequent change, since retroactive changes can force existing transactions to be updated, an expensive operation.^[1] In SQLMesh, maintaining SCD Type 2 history costs extra storage and compute and may not suit sources that change frequently, since the history could get very large.^[2] Treat a history table as data in its own right, to be backed up and protected, rather than as something a rerun can rebuild. (Meridian's view)

dbt snapshots detect a changed row either by an updated_at column more recent than the last snapshot run, or by comparing a list of columns with their historical values.^[3] In dbt, the snapshot's unique key is used to match rows up, so it is extremely important that the key is actually unique.^[3] By default, dbt snapshots take no action for rows deleted from the source, and the hard_deletes setting can instead close them or record them as new rows.^[3] Choose how a change is detected by what the source can be trusted to say: a reliable updated-at column, or a comparison of the columns that matter when it has none. (Meridian's view)

The method

On each dbt snapshot run after the first, dbt updates the dbt_valid_to column of existing records that have changed and inserts the updated and new records with dbt_valid_to null or a configured value.^[3] SQLMesh's SCD Type 2 models add a valid_from column, inclusive, and a valid_to column, exclusive, and set valid_to to NULL for the latest record.^[2] A standardised surrogate high date such as 9999-12-31 may be used as a Type 2 end date instead of null, though in some database software an artificial high date can cause performance issues.^[1]

Apache Hop's Dimension lookup/update transform implements Ralph Kimball's slowly changing dimension for both Type I (update) and Type II (insert).^[4] In Apache Hop's Dimension lookup/update, each field is set either to be updated in all versions, a Type I operation, or to be inserted into the dimension as a new version.^[4] Different SCD types can be applied to different columns of one table, such as Type 1 to a supplier's name and Type 2 to its state.^[1]

Compared with the alternatives

A Type 1 SCD overwrites old data with new and so keeps no history, which has the advantage of being easy to maintain.^[1] A Type 3 SCD tracks changes in separate columns and preserves limited history, as many earlier values as there are columns designated to hold them.^[1] A Type 4 SCD keeps the current data in one table and a record of some or all changes in an additional history table.^[1]

Sources

[1]Slowly changing dimension - Wikipediasecondary source
[2]sqlmesh/docs/concepts/models/model_kinds.md at 23b237cea7218cc8f338b89c14f0957394a0003b · SQLMesh/sqlmesh · GitHubdocumentation
[3]Add snapshots to your DAG | dbt Developer Hubdocumentation
[4]Dimension lookup/update | Apache Hopdocumentation