Changing dimensions (SCD types 1 and 2)

A *slowly changing dimension* (SCD) is a dimension whose attribute changes over time. The question is: should the March sale appear in the customer's old city or the new one?

• Type 0: never changes (date of birth).
• Type 1: overwrites. Simple, but the past starts showing the new city.
• Type 2: closes the old version and opens a new row, with a validity start and end. The past stays as it was.

Type 2 in SQL that runs here

Ana moved to Santos on 2024-06-03. The old version is closed the day before and a new one is opened.

CREATE TABLE dim_customer AS
SELECT id AS customer_id, name, city, '2024-01-01' AS valid_from, '9999-12-31' AS valid_to, 1 AS is_current
FROM customers;

UPDATE dim_customer SET valid_to = '2024-06-02', is_current = 0
WHERE customer_id = 1 AND is_current = 1;
INSERT INTO dim_customer VALUES (1, 'Ana', 'Santos', '2024-06-03', '9999-12-31', 1);

SELECT o.id AS order_id, o.order_date, d.city
FROM orders o
JOIN dim_customer d ON d.customer_id = o.customer_id AND o.order_date BETWEEN d.valid_from AND d.valid_to
WHERE o.customer_id = 1
ORDER BY o.order_date;

order_id | order_date | city
-------- | ---------- | ---------
101      | 2024-06-01 | São Paulo
103      | 2024-06-02 | São Paulo
110      | 2024-06-05 | Santos

Each order matches the version in effect on its date: the ones from June 1 and 2 stay in São Paulo; the one from June 5, in Santos. With type 1, all three would appear in Santos.

On Databricks

MERGE does type 1 directly. For type 2, the pattern is a MERGE that closes the old version and inserts the new one (the new row enters twice in the source batch, with different matching keys), or APPLY CHANGES from declarative pipelines, which does type 2 with STORED AS SCD TYPE 2.

• An "open" validity end with a far-off date (9999-12-31) keeps the BETWEEN simple; NULL forces you to handle the case in every query.
• Use dates of the same type on both sides of the BETWEEN: here, ISO text compares correctly; mixing text and date does not.