# Keep the history of changes

Join / merge upsert

A variant of [Merge into a table](https://meridian.online/operations/merge-into-a-table).

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

Page: <https://meridian.online/operations/merge-into-a-table/keep-the-history-of-changes>

## Status

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.

## What other tools call it

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

- Bruin: [`scd2_by_column`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27)
- Bruin: [`scd2_by_time`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27)
- dbt-utils: [`check`](https://docs.getdbt.com/docs/build/snapshots) (read 2026-09-27)
- dbt-utils: [`snapshot`](https://docs.getdbt.com/docs/build/snapshots) (read 2026-09-27)
- dbt-utils: [`timestamp`](https://docs.getdbt.com/docs/build/snapshots) (read 2026-09-27)
- Duckle: [`SCD Type 2`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25)
- Duckle: [`SCD Type 3`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25)
- Apache Hop: [`Dimension lookup/update`](https://hop.apache.org/manual/latest/pipeline/transforms/dimensionlookup.html) (read 2026-09-27)
- Pentaho Data Integration: [`Dimension lookup/update`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27)
- SQLMesh: [`SCD_TYPE_2_BY_COLUMN`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/models/model_kinds.md) (read 2026-09-27)
- SQLMesh: [`SCD_TYPE_2_BY_TIME`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/models/model_kinds.md) (read 2026-09-27)
- Talend: [`Database slowly changing dimension ELT components (family)`](https://help.qlik.com/talend/en-US/components/8.0/About) (read 2026-09-27)
- Talend: [`Database slowly changing dimension components (family)`](https://help.qlik.com/talend/en-US/components/8.0/About) (read 2026-09-27)