SCD2 Dimension

Track dimension history with Slowly Changing Dimension Type 2

This example shows how to track the complete history of a dimension table using SCD2.

1. Source Data

-- models/raw/customers.sql
-- @kind: table

SELECT * FROM (VALUES
    (1, 'Alice', 'alice@example.com', 'gold',   'Sweden',  '2026-03-28 10:00:00'),
    (2, 'Bob',   'bob@example.com',   'silver', 'Norway',  '2026-03-28 10:00:00'),
    (3, 'Carol', 'carol@example.com', 'bronze', 'Denmark', '2026-03-29 12:00:00')
) AS t(customer_id, name, email, tier, country, updated_at)

2. SCD2 Model

-- models/staging/stg_customers.sql
-- @kind: scd2
-- @unique_key: customer_id
-- @constraint: customer_id NOT NULL
-- @audit: row_count > 0

SELECT
    customer_id,
    name,
    email,
    tier,
    country,
    updated_at
FROM raw.customers

3. First Run

ondatrasql run

Running 2 models...
[OK] raw.customers (table, backfill, 3 rows, 174ms — first run)
[OK] staging.stg_customers (scd2, backfill, 3 rows, 269ms — first run)

All rows are inserted with is_current = true:

customer_id	name	tier	is_current
1	Alice	gold	true
2	Bob	silver	true
3	Carol	bronze	true

4. When Data Changes

Change Alice’s tier from “gold” to “platinum” in raw/customers.sql, then run again:

ondatrasql run

Running 2 models...
[OK] raw.customers (table, backfill, 3 rows, 158ms — sql changed)
[OK] staging.stg_customers (scd2, incremental, 1 rows, 360ms — unchanged)

The old version is closed and a new version is inserted:

customer_id	name	tier	is_current
1	Alice	gold	false
1	Alice	platinum	true
2	Bob	silver	true
3	Carol	bronze	true

Auto-Generated Columns

OndatraSQL automatically adds three columns to the output:

Column	Type	Description
`valid_from_snapshot`	BIGINT	DuckLake snapshot ID when this version became active
`valid_to_snapshot`	BIGINT	Snapshot ID when superseded (NULL if current)
`is_current`	BOOLEAN	Whether this is the active version

Querying SCD2 Data

-- Current state
SELECT * FROM staging.stg_customers WHERE is_current = true;

-- Full history for a customer
SELECT * FROM staging.stg_customers
WHERE customer_id = 1
ORDER BY valid_from_snapshot;