Slowly changing dimension type 2 in SQL: valid_from, valid_to and the join

Part 2 built a customer dimension that keeps only the newest row, and its report put Ana's Portuguese orders in Spain. That table is a slowly changing dimensionSlowly Changing DimensionsThe ways a warehouse handles a change to a descriptive record, such as a customer's address: type 1 overwrites it, type 2 adds a new dated version, and type 3 keeps the previous value in an extra column. of type 1. This part builds type 2, which keeps the past, and goes through the 3 ways it goes wrong. Add the queries to scd.sql and run python run_sql.py setup.sql star.sql scd.sql.

Type 1 or type 2

Customers change slowly: a new address, a new name, a new tier. Kimball names the ways a dimension can deal with that.

• Type 1 overwrites. The row always holds the current value and history is gone. Right for corrections, such as a misspelt name, and for columns no report looks at over time.
• Type 2 adds a row. Each change becomes a new version with the time it started and ended, and each fact joins to the version true when it happened. Right for anything a report groups by over time, such as the country revenue is booked to.

Type 1 by country, from part 2's table:

SELECT c.country, sum(f.amount) AS revenue
FROM fact_orders f JOIN dim_customer_now c ON f.customer_id = c.customer_id
GROUP BY c.country ORDER BY c.country;

+-------+-------+
|country|revenue|
+-------+-------+
|DE     |75.00  |
|ES     |95.50  |
|IE     |53.00  |
+-------+-------+

Portugal is missing. Ana's orders of 1 and 2 October, 65.50 in all, were placed while she lived there.

Give each version its dates

Each change starts a version, and the customer's next change ends it. lead() reads the next row in a window, so it finds the end:

CREATE OR REPLACE TEMP VIEW dim_customer_v1 AS
SELECT customer_id, name, country,
       changed_at AS valid_from,
       lead(changed_at) OVER (PARTITION BY customer_id ORDER BY changed_at) AS valid_to
FROM customer_changes;

SELECT * FROM dim_customer_v1 ORDER BY customer_id, valid_from;

+-----------+----+-------+-------------------+-------------------+
|customer_id|name|country|valid_from         |valid_to           |
+-----------+----+-------+-------------------+-------------------+
|C01        |Ana |PT     |2026-09-01 08:00:00|2026-10-03 09:30:00|
|C01        |Ana |ES     |2026-10-03 09:30:00|NULL               |
|C02        |Ben |IE     |2026-09-15 10:00:00|NULL               |
|C03        |Chen|DE     |2026-10-02 12:00:00|NULL               |
+-----------+----+-------+-------------------+-------------------+

Now join each order to the version true when it was placed: the same customer, and an order time from the version's start up to, but not including, its end.

SELECT c.country, sum(f.amount) AS revenue
FROM fact_orders f
JOIN dim_customer_v1 c
  ON f.customer_id = c.customer_id
 AND f.ordered_at >= c.valid_from
 AND (f.ordered_at < c.valid_to OR c.valid_to IS NULL)
GROUP BY c.country ORDER BY c.country;

+-------+-------+
|country|revenue|
+-------+-------+
|DE     |15.00  |
|ES     |30.00  |
|IE     |53.00  |
|PT     |65.50  |
+-------+-------+

Portugal is back, and Spain has only the order Ana placed after she moved. But Germany has 15.00, not 75.00.

The first version starts too late

Chen's order 1003 was placed at 11:30 on 2 October, and the shop's system saved Chen's record at 12:00. It happens all the time: a record is saved after the checkout, loaded late, or the history starts on the day the system was switched on. No version is true at 11:30, so the inner join drops the order without a word. A left join that counts the matches finds it:

SELECT f.order_id, f.customer_id, f.ordered_at, count(c.customer_id) AS versions
FROM fact_orders f
LEFT JOIN dim_customer_v1 c
  ON f.customer_id = c.customer_id
 AND f.ordered_at >= c.valid_from
 AND (f.ordered_at < c.valid_to OR c.valid_to IS NULL)
GROUP BY f.order_id, f.customer_id, f.ordered_at
HAVING count(c.customer_id) <> 1;

+--------+-----------+-------------------+--------+
|order_id|customer_id|ordered_at         |versions|
+--------+-----------+-------------------+--------+
|1003    |C03        |2026-10-02 11:30:00|0       |
+--------+-----------+-------------------+--------+

The usual fix is to start each customer's first version long ago, at 1900-01-01. It assumes the first values you know were also true before you knew them, which is nearly always a better guess than losing the order. The same table also ends the current version at 9999-12-31 rather than NULL, so every version has an end and the join is 1 plain range, and it marks the current version, so a query for today's state needs no dates:

CREATE OR REPLACE TEMP VIEW dim_customer AS
SELECT xxhash64(customer_id, changed_at) AS customer_key,
       customer_id, name, country,
       CASE WHEN row_number() OVER w = 1 THEN TIMESTAMP'1900-01-01 00:00:00'
            ELSE changed_at END AS valid_from,
       coalesce(lead(changed_at) OVER w, TIMESTAMP'9999-12-31 00:00:00') AS valid_to,
       lead(changed_at) OVER w IS NULL AS is_current
FROM customer_changes
WINDOW w AS (PARTITION BY customer_id ORDER BY changed_at);

SELECT * FROM dim_customer ORDER BY customer_id, valid_from;

+--------------------+-----------+----+-------+-------------------+-------------------+----------+
|customer_key        |customer_id|name|country|valid_from         |valid_to           |is_current|
+--------------------+-----------+----+-------+-------------------+-------------------+----------+
|-5876299405855916264|C01        |Ana |PT     |1900-01-01 00:00:00|2026-10-03 09:30:00|false     |
|-6129443583856869281|C01        |Ana |ES     |2026-10-03 09:30:00|9999-12-31 00:00:00|true      |
|1907290492647614057 |C02        |Ben |IE     |1900-01-01 00:00:00|9999-12-31 00:00:00|true      |
|-921504631688345634 |C03        |Chen|DE     |1900-01-01 00:00:00|9999-12-31 00:00:00|true      |
+--------------------+-----------+----+-------+-------------------+-------------------+----------+

WINDOW w AS (...) names the window once for the 3 functions that use it. The same join, now without the NULL case:

SELECT c.country, sum(f.amount) AS revenue
FROM fact_orders f
JOIN dim_customer c
  ON f.customer_id = c.customer_id
 AND f.ordered_at >= c.valid_from
 AND f.ordered_at < c.valid_to
GROUP BY c.country ORDER BY c.country;

+-------+-------+
|country|revenue|
+-------+-------+
|DE     |75.00  |
|ES     |30.00  |
|IE     |53.00  |
|PT     |65.50  |
+-------+-------+

Every order is counted once, in the country its customer lived in at the time. Joining with c.is_current instead gives back the type 1 answer from the same table, so 1 table serves both kinds of report. Try both tables and both starts in the demo.

Try itJoin orders to the customer as they were

Customer table

Type 1: current rowType 2: every version

First version starts

When it was savedLong ago

dim_customer

customer	country	valid_from	valid_to
C01	PT	2026-09-01 08:00	2026-10-03 09:30
C01	ES	2026-10-03 09:30	9999-12-31
C02	IE	2026-09-15 10:00	9999-12-31
C03	DE	2026-10-02 12:00	9999-12-31

orders, joined

order_id	ordered_at	amount	country
1001	2026-10-01 09:15	25.50	PT
1002	2026-10-01 10:02	12.00	IE
1003	2026-10-02 11:30	60.00	Dropped
1004	2026-10-02 12:45	40.00	PT
1005	2026-10-02 16:20	18.25	IE
1006	2026-10-04 08:05	30.00	ES
1007	2026-10-04 13:40	15.00	DE
1008	2026-10-05 19:10	22.75	IE

Revenue by the country at the time of the order

• DE15.00Should be 75.00
• ES30.00Right
• IE53.00Right
• PT65.50Right

Half-open ranges, so nothing matches twice

The join uses >= at the start and < at the end. Ana's second version starts at 09:30 on 3 October, the instant her first one ends. With <= at the end as well, an order placed at exactly 09:30 would match both versions and be counted twice. With the end left open, it matches only the new one. Write every range of times this way.

A key that survives a rebuild

A fact table could store the customer's version key when the order is loaded, so reports join on 1 key, as Kimball's type 2 does. The key needs a property that is easy to miss: the same version must get the same key every time the table is built. Numbering the rows with row_number() fails that. A new change for C01 shifts the numbers of every row after it, and facts that stored the old numbers now point at other customers.

xxhash64(customer_id, changed_at) gives a 64-bit number computed from the version's natural key and start, so a rebuild gives each version the same key. 2 different versions could in principle hash to the same number, which is why the build in part 4 checks that the keys are unique. Delta tables can also hand out lasting numbers with an identity column; module 4 covers it.

Stop the build when a join is wrong

A missing or doubled match is silent, so make it loud. assert_true returns NULL when its condition holds and stops the query with your message when it does not:

SELECT assert_true(count(*) = 0, 'an order matches no customer version, or more than 1') AS ok
FROM (
  SELECT f.order_id
  FROM fact_orders f
  LEFT JOIN dim_customer c
    ON f.customer_id = c.customer_id
   AND f.ordered_at >= c.valid_from
   AND f.ordered_at < c.valid_to
  GROUP BY f.order_id
  HAVING count(c.customer_key) <> 1
);

+----+
|ok  |
+----+
|NULL|
+----+

Point it at dim_customer_v1, with that table's OR c.valid_to IS NULL and count(c.customer_id), and Spark stops with [USER_RAISED_EXCEPTION] an order matches no customer version, or more than 1 SQLSTATE: P0001, which in a scheduled job fails the run before a wrong report reaches anyone.

You will not always write this table by hand. Databricks pipelines can build type 2 from a stream of changes with AUTO CDC, which module 7 uses. It names the dates __START_AT and __END_AT and leaves the current version's end NULL, so the join needs the OR ... IS NULL case again. Knowing what such a table holds, and the 3 traps above, is what lets you trust it. Part 4 puts the model together as 1 script.