# Slowly changing dimension type 2 in SQL: valid_from, valid_to and the join A slowly changing dimension type 2 keeps 1 row per version of a member, with the time it became true (valid_from), the time it stopped (valid_to) and a flag on the current one. Build it from a change log with lead() over each member's changes, and join facts with valid_from <= the event's time < valid_to, so each event meets exactly 1 version. Start each member's first version early enough for its oldest event, or that event matches nothing and an inner join drops it. - Page: https://tecyfy.com/data-engineering/course/sql-and-data-modelling/slowly-changing-dimensions - Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 1: SQL and data modelling for analytics, Slowly changing dimensions - Parts of this module: Aggregates and window functions (https://tecyfy.com/data-engineering/course/sql-and-data-modelling); Facts, dimensions and grain (https://tecyfy.com/data-engineering/course/sql-and-data-modelling/star-schema); Slowly changing dimensions (https://tecyfy.com/data-engineering/course/sql-and-data-modelling/slowly-changing-dimensions); Build it: model the orders (https://tecyfy.com/data-engineering/course/sql-and-data-modelling/build-it) At the end of this part you can: - Say when a dimension needs type 1 and when type 2 - Build a type 2 dimension from a change log with lead(), and join facts to the version true at the time - Find the facts that match no version or more than 1, and stop the build with assert_true - Give each version a key that stays the same when the table is rebuilt [Part 2](/data-engineering/course/sql-and-data-modelling/star-schema) 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 dimension](https://tecyfy.com/concepts/slowly-changing-dimensions) 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: ```sql 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; ``` ```text +-------+-------+ |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: ```sql 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; ``` ```text +-----------+----+-------+-------------------+-------------------+ |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. ```sql 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; ``` ```text +-------+-------+ |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: ```sql 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; ``` ```text +--------+-----------+-------------------+--------+ |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: ```sql 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; ``` ```text +--------------------+-----------+----+-------+-------------------+-------------------+----------+ |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: ```sql 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; ``` ```text +-------+-------+ |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. (An interactive demo goes here on the page: https://tecyfy.com/data-engineering/course/sql-and-data-modelling/slowly-changing-dimensions) ## 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: ```sql 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 ); ``` ```text +----+ |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](/data-engineering/course/sql-and-data-modelling/build-it) puts the model together as 1 script. ## Check yourself 1. A type 2 customer dimension starts each version at the time the change was saved. A customer's record was saved at 12:00, and their first order was placed at 11:30 the same day. What happens to that order in a report that inner joins orders to the version true at the time? A. It joins to the customer's current version, because that is the closest one B. It joins to every version of the customer C. It matches no version, so the inner join drops it and its revenue is missing from every total D. Spark raises an error, because a join condition with ranges must match ### Answers 1. C. The join keeps the version with valid_from <= ordered_at < valid_to. The only version starts at 12:00, after the order, so nothing matches and an inner join leaves the order out without a word. Start each customer's first version at an early date such as 1900-01-01, and check that every order matches exactly 1 version. ## Questions ### What is the difference between SCD type 1 and type 2? Type 1 overwrites a changed value, so the dimension holds only the current state and every report, old ones included, shows today's values. Type 2 adds a new row for each change with the dates it was true, so a fact joins to the values that held when it happened. Use type 1 for corrections and for columns no report groups history by, type 2 where the past must stay as it was. ### How do I join a fact table to a type 2 dimension? Either store the version's surrogate key in the fact when it is loaded and join on that key, or join on the natural key plus the time, f.customer_id = c.customer_id AND f.ordered_at >= c.valid_from AND f.ordered_at < c.valid_to. A half-open range, closed at the start and open at the end, gives a time that equals a change exactly 1 version. ### Why use 9999-12-31 instead of NULL for valid_to? With a date far in the future on the current version, every version has an end, so the join is 1 plain range condition, and a filter such as valid_to > now() works without OR valid_to IS NULL. Keep an is_current column as well, so queries for the current state need no date. ### How do I make surrogate keys for a type 2 dimension in Spark SQL? A key from row_number() over the whole table renumbers rows when a new version arrives in the middle, so facts that stored the old key point at another customer. A hash of the natural key and the version's start, xxhash64(customer_id, changed_at), gives the same key every time the table is rebuilt; check that the keys are unique. On Delta tables, an identity column assigns lasting numbers as rows are added, which module 4 covers. ## Sources - [Type 1: Overwrite](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-1/) - [Type 2: Add New Row](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2/) - [lead function](https://docs.databricks.com/aws/en/sql/language-manual/functions/lead) - [xxhash64 function](https://docs.databricks.com/aws/en/sql/language-manual/functions/xxhash64) - [assert_true function](https://docs.databricks.com/aws/en/sql/language-manual/functions/assert_true) - [The AUTO CDC APIs: Simplify change data capture with pipelines](https://docs.databricks.com/aws/en/ldp/cdc)