Slowly changing dimensions and loading patterns
Hook
A customer was in the silver tier all last year and moved to gold in April.
Finance reruns last year's loyalty report and every historical order now shows
gold, inflating the tier's revenue by a year of purchases that were never made
at that tier. Nothing crashed; the dimension simply overwrote its own history.
The report is now confidently wrong, and the fix is a modelling pattern you
choose before the first update lands.
Concept
A slowly changing dimension (SCD) is a dimension whose attributes change over time — a customer's tier, an employee's department, a product's category. The question is not whether they change but what your model does when they do. Two answers cover most cases.
SCD Type 1 overwrites the old value in place. The dimension always shows the current attribute, and history is gone. That is correct when the old value was simply wrong — a misspelled name, a bad country code — or when nobody ever needs to ask "what was it at the time." It is exactly what corrupted the loyalty report above when applied to an attribute that history depended on.
SCD Type 2 preserves history by adding a new row for each change. The old row is closed and a fresh one opens, so the dimension holds every version a thing ever had. Each row carries three bookkeeping columns:
| Column | Meaning |
|---|---|
valid_from | The date this version became true |
valid_to | The date it stopped being true (NULL while it is current) |
is_current | A flag, 1 for the live version, 0 for expired ones |
A crucial detail makes Type 2 work: the dimension has two keys. The
natural key (customer_id) identifies the real-world customer and repeats
across their versions. The surrogate key (customer_key) is a
warehouse-generated id, unique per version row, and it is what the fact table
stores. Because a fact points at a specific version, an order placed in March
joins to the silver row that was current in March, even after a gold row
exists.
Applying SCD Type 1 (overwrite) to an attribute that historical reports depend on silently rewrites the past — every old fact re-reports under the new value. Once the old value is overwritten it is unrecoverable. Decide Type 1 vs. Type 2 per attribute before the first load, not after finance notices.
That surrogate key connects to how the warehouse is loaded. Three patterns cover most tables, and the right one depends on what the table represents:
| Pattern | What it does | Fits |
|---|---|---|
| Full reload | Truncate and rebuild the whole table each run | Small dimensions cheap to rebuild |
| Insert-only (append) | Add new rows, never touch existing ones | Immutable event facts — a sale never un-happens |
| Upsert / SCD-aware | Update or expire matched rows, insert new ones | Dimensions that change: Type 1 overwrites, Type 2 expires-and-inserts |
Fact tables are usually append-only, because a measurement that happened does not change. Dimensions use upsert logic, and for a Type 2 dimension that upsert is specifically "expire the current row, insert the new version."
Worked example
Let me implement a Type 2 update and then prove a historical fact still joins to
the right version. Start with a customer dimension carrying the validity columns,
seeded with one silver customer:
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE dim_customer (
customer_key INTEGER PRIMARY KEY AUTOINCREMENT, -- surrogate key
customer_id INTEGER NOT NULL, -- natural key
name TEXT NOT NULL,
tier TEXT NOT NULL,
valid_from TEXT NOT NULL,
valid_to TEXT,
is_current INTEGER NOT NULL
);
INSERT INTO dim_customer
(customer_id, name, tier, valid_from, valid_to, is_current)
VALUES
(1, 'Priya Nair', 'silver', '2026-01-01', NULL, 1);
""")
The Type 2 update is two statements: close the current row, then insert the new version. Writing it as a function keeps the pattern in one place:
def scd2_update(con, customer_id, new_tier, change_date):
"""Expire the current version and insert a new one if the tier changed."""
current = con.execute("""
SELECT customer_key, tier FROM dim_customer
WHERE customer_id = ? AND is_current = 1
""", (customer_id,)).fetchone()
if current is None:
return
key, tier = current
if tier == new_tier:
return # unchanged — Type 2 inserts only on a real change
con.execute("""
UPDATE dim_customer
SET valid_to = ?, is_current = 0
WHERE customer_key = ?
""", (change_date, key))
con.execute("""
INSERT INTO dim_customer
(customer_id, name, tier, valid_from, valid_to, is_current)
SELECT customer_id, name, ?, ?, NULL, 1
FROM dim_customer WHERE customer_key = ?
""", (new_tier, change_date, key))
con.commit()
scd2_update(con, 1, 'gold', '2026-04-01')
for row in con.execute("""
SELECT customer_key, tier, valid_from, valid_to, is_current
FROM dim_customer WHERE customer_id = 1 ORDER BY customer_key
"""):
print(row)
(1, 'silver', '2026-01-01', '2026-04-01', 0)
(2, 'gold', '2026-04-01', None, 1)
Two rows now, one natural key. The silver version is closed on the change date;
the gold version is open. The real test is whether a March order finds the
value that was true in March. The join matches the fact date to the version's
validity window:
con.execute(
"CREATE TABLE fact_order "
"(order_id INTEGER, customer_id INTEGER, order_date TEXT, amount REAL)"
)
con.execute("INSERT INTO fact_order VALUES (900, 1, '2026-03-15', 50.0)")
tier_at_order = con.execute("""
SELECT d.tier
FROM fact_order f
JOIN dim_customer d
ON d.customer_id = f.customer_id
AND f.order_date >= d.valid_from
AND (f.order_date < d.valid_to OR d.valid_to IS NULL)
WHERE f.order_id = 900
""").fetchone()[0]
print("tier at order time:", tier_at_order)
con.close()
tier at order time: silver
The March order joins to silver, not gold, because the validity-window
condition selects the version that was current on 2026-03-15. History is
intact: last year's report stays correct while the dimension still knows the
customer is gold today. In a production loader the fact would store the
customer_key surrogate directly, making this lookup a simple key join; the
date-range join here shows the logic the surrogate encodes.
Hands-on
Your turn, on a different dimension. The exercise below gives you a
dim_employee with a department attribute and a stream of transfers. You will
implement the Type 2 update — expire the old department row, insert the new one
with fresh validity dates — then verify that a payroll fact dated before a
transfer still joins to the employee's former department.
Success criteria: after two transfers an employee has three versions with non-overlapping validity windows and exactly one current row, and a pre-transfer fact joins to the department that was current at its date. SQL Kingdom validates the version history and the point-in-time join automatically.
Recap
- You can choose SCD Type 1 or Type 2 per attribute, based on whether history matters for that column.
- You can implement a Type 2 update — expire the current row, insert a new
version — with
valid_from,valid_to, andis_currentbookkeeping. - You can explain why the surrogate key lets a historical fact join to the attribute value that was true when it happened.
- You can match a loading pattern — full reload, insert-only, or upsert — to what a table represents.
Next up: you put all of this together. The lab builds a full star schema from a normalized source — dimensions and a fact at a declared grain, loaded and verified — the artifact this course exists to teach.
- SCD Type 1 overwrites (history lost); Type 2 adds a versioned row (history kept) — choose per attribute.
- Type 2 uses
valid_from/valid_to/is_current, a repeating natural key, and a per-version surrogate key that facts store. - Facts append (insert-only); changing dimensions upsert — Type 2 upsert means expire the current row, then insert the new version.