The listing turns mart.dim_customer from Dimension Tables into a Type 2 dimension and applies three sample address changes. A data-modifying CTE closes the current versions and inserts the new ones in one atomic statement:
-- 1. Make dim_customer versioned: one row per customer per period of validity
ALTER TABLE mart.dim_customer
ADD COLUMN valid_from date NOT NULL DEFAULT '1900-01-01',
ADD COLUMN valid_to date NOT NULL DEFAULT '9999-12-31',
ADD COLUMN is_current boolean NOT NULL DEFAULT true,
DROP CONSTRAINT dim_customer_customer_id_key,
ADD UNIQUE (customer_id, valid_from);
-- 2. Sample changes from the app: three customers move country
CREATE TABLE mart.customer_changes (customer_id int, country char(2), changed_on date);
INSERT INTO mart.customer_changes VALUES
(1, 'GB', '2026-03-01'), (2, 'DE', '2026-04-15'), (3, 'CA', '2026-05-20');
-- 3. Close the current version and insert the new one, in one statement
WITH closed AS (
UPDATE mart.dim_customer d SET valid_to = ch.changed_on, is_current = false
FROM mart.customer_changes ch
WHERE d.customer_id = ch.customer_id AND d.is_current AND d.country <> ch.country
RETURNING d.customer_id, d.name, d.signup_date, ch.country, ch.changed_on)
INSERT INTO mart.dim_customer (customer_id, name, country, region, signup_date, valid_from)
SELECT customer_id, name, country, CASE WHEN country IN ('US', 'CA') THEN 'Americas'
WHEN country IN ('GB', 'DE') THEN 'Europe' ELSE 'Asia-Pacific' END,
signup_date, changed_on
FROM closed;
-- 4. Point each fact at the version valid on its order date
UPDATE mart.fact_sales f SET customer_key = v.customer_key
FROM mart.dim_customer old, mart.dim_customer v, mart.dim_date d
WHERE f.customer_key = old.customer_key AND f.date_key = d.date_key
AND v.customer_id = old.customer_id AND v.customer_key <> old.customer_key
AND d.full_date >= v.valid_from AND d.full_date < v.valid_to;
SELECT customer_key AS key, customer_id AS id, country, valid_from, valid_to, is_current,
(SELECT count(*) FROM mart.fact_sales f WHERE f.customer_key = c.customer_key) AS lines
FROM mart.dim_customer c WHERE customer_id <= 3 ORDER BY customer_id, valid_from; key | id | country | valid_from | valid_to | is_current | lines
------+----+---------+------------+------------+------------+-------
1 | 1 | AU | 1900-01-01 | 2026-03-01 | f | 14
5001 | 1 | GB | 2026-03-01 | 9999-12-31 | t | 2
2 | 2 | GB | 1900-01-01 | 2026-04-15 | f | 10
5002 | 2 | DE | 2026-04-15 | 9999-12-31 | t | 3
3 | 3 | US | 1900-01-01 | 2026-05-20 | f | 17
5003 | 3 | CA | 2026-05-20 | 9999-12-31 | t | 4Customer 1's 14 earlier lines stay in Australia and her two lines after the move count for the UK. Step 4 exists only because BookNest's sample history was loaded before the changes; a daily load performs the same range join as each new fact arrives (order_date >= valid_from AND order_date < valid_to). The d.country <> ch.country guard makes the change batch safe to re-apply: an unchanged value opens no new version. Real feeds track several columns, so compare a hash of them instead.
With both keys available, one query can report either view of history: join the fact's own version for "as was", and the current row of the same natural key for "as is":
SELECT c.customer_id AS id, c.country AS as_was, cur.country AS as_is,
min(d.full_date) AS first_line, max(d.full_date) AS last_line,
sum(f.gross_amount) AS gross
FROM mart.fact_sales f JOIN mart.dim_date d USING (date_key)
JOIN mart.dim_customer c USING (customer_key) -- version at order time
JOIN mart.dim_customer cur ON cur.customer_id = c.customer_id AND cur.is_current -- today
WHERE c.customer_id <= 3 GROUP BY 1, 2, 3 ORDER BY 1, 4;id | as_was | as_is | first_line | last_line | gross ----+--------+-------+------------+------------+-------- 1 | AU | GB | 2025-03-23 | 2026-02-25 | 480.37 1 | GB | GB | 2026-03-04 | 2026-04-30 | 47.39 2 | GB | DE | 2025-02-22 | 2026-03-24 | 228.19 2 | DE | DE | 2026-05-18 | 2026-06-12 | 78.58 ...
Snapshots gets the same versioned rows from a dbt 37,942 snapshot without writing the statements by hand.