Eight Ways to Remember: A Field Guide to Slowly Changing Dimensions (Types 0–7)
A customer moves from Austin to Denver. In the source system that's a one-line UPDATE, the old address disappears, and nobody thinks twice about it. Your warehouse has a harder job. Six months later someone runs a report and needs to know: when we sold this person a jacket last winter, where did they actually live?
Whether you can answer that comes down to a decision you made — or forgot to make — back when you built the customer table. That decision has a name. It's the Slowly Changing Dimension problem, and Ralph Kimball catalogued eight ways to handle it in The Data Warehouse Toolkit, numbered 0 through 7.
Nearly every tutorial teaches Type 1 and Type 2 and then sort of trails off, as if the rest are footnotes. They're not. The higher numbers are what you end up reaching for once Type 2 stops being enough, which happens sooner than you'd think. So let's walk the whole thing, one rung at a time, and be honest about when each one earns its keep.
One example, carried all the way through
We'll model exactly one thing: a customer. Here's the row we start with.
| Attribute | Value |
|---|---|
customer_id (natural key) |
C-4417 |
name |
Dana Ruiz |
email |
dana@example.com |
birth_date |
1990-03-12 |
city |
Austin |
segment |
Standard |
Over the next year Dana does three ordinary things:
- Fixes a typo in her name — it had been stored as "Dna Ruiz."
- Moves from Austin to Denver.
- Gets bumped from Standard to Premium.
To the source system these are the same event three times: somebody changed a column. The warehouse can't treat them the same, though, and that's really the whole point of the SCD types. They aren't about how data changes. They're about what you owe history when it does.
Type 0 — leave it alone
Type 0 says the value gets captured once and then never moves again, no matter what shows up in the source later.
birth_date is the easy example, since it can't genuinely change — any "update" is really just fixing a mistake. But Type 0 is less about immutability and more about intent. Think original_credit_score or signup_channel. A customer's credit score might bounce around every week, but the score they had when they signed up is a fixed historical fact, and you want it to stay put.
-- Type 0: written once at load, then the ETL simply never touches it.
INSERT INTO dim_customer (customer_id, original_segment, ...)
VALUES ('C-4417', 'Standard', ...);
-- Dana upgrades to Premium later. original_segment is still 'Standard'. Always will be.
Use it for things that are fixed by definition, or where "the value as first recorded" is the number people actually want. The mistake to avoid is treating Type 0 as the lazy option. It's a promise that updates to this field are meaningless, so don't slap it on something that legitimately evolves.
Type 1 — overwrite and move on
New value replaces old value. No history, no ceremony, no trace of what was there before.
This is exactly right for Dana's name typo. Nobody is ever going to want "Dna Ruiz" preserved as a meaningful past state of the world, because it was never a state of the world, it was a fat-fingered entry. Overwrite it and forget it.
UPDATE dim_customer
SET name = 'Dana Ruiz'
WHERE customer_id = 'C-4417';
Type 1 is fine for corrections and cosmetic fields. Where it gets people into trouble is when they use it on something that matters analytically. Say you Type-1 Dana's city. Now every order she ever placed from Austin reports as a Denver order, retroactively. Your Q1 regional numbers quietly change months after you closed the books, and next quarter they don't tie out to the report you already emailed the CFO. This is one of the most common ways a warehouse ends up with "the numbers moved and nobody can explain it." Be careful where you point it.
Type 2 — add a new row, keep everything
This is the one you'll use most, and it's the correct answer for Dana's move to Denver.
The thing that makes Type 2 work is the surrogate key — a warehouse-generated integer (customer_key) that's completely separate from the natural key (customer_id). One real customer can rack up several surrogate keys over time, one per version. Facts point at the surrogate key, so every order stays permanently attached to the version of the customer that was true the day it happened.
You keep track of versions with three columns: effective_date, end_date, and is_current.
customer_key |
customer_id |
city |
segment |
effective_date |
end_date |
is_current |
|---|---|---|---|---|---|---|
| 8801 | C-4417 | Austin | Standard | 2025-01-01 | 2025-06-14 | false |
| 9930 | C-4417 | Denver | Standard | 2025-06-15 | 9999-12-31 | true |
-- Close out the old version.
UPDATE dim_customer
SET end_date = '2025-06-14', is_current = false
WHERE customer_id = 'C-4417' AND is_current = true;
-- Open a new one with a fresh surrogate key.
INSERT INTO dim_customer
(customer_key, customer_id, city, segment, effective_date, end_date, is_current)
VALUES
(9930, 'C-4417', 'Denver', 'Standard', '2025-06-15', '9999-12-31', true);
And now the question we started with answers itself. That jacket order last winter points at customer_key = 8801, which is Austin. Today's orders point at 9930, which is Denver. Full history, all of it accurate, and nobody's past numbers budge.
The price you pay is rows. Every tracked change adds one. That's nothing for a customer who moves once a year. It's a disaster for an attribute that changes all the time — which is the exact problem Type 4 was invented to deal with.
One handy detail: "give me the current picture" is just WHERE is_current = true, and "give me the picture as of some date" is WHERE '2025-03-01' BETWEEN effective_date AND end_date. Being able to get both out of a single table is the whole reason Type 2 is worth the trouble.
Type 3 — keep the old value in a second column
Type 3 stashes history in a column instead of a row. You add a previous_<attr> field next to the current one, which buys you exactly one prior value.
It's the odd one out, because it isn't really about tracking change over time at all. It's about supporting two readings of the same attribute at once. The textbook case is a sales-territory realignment. The day the company redraws its regions, every analyst hits the same wall: do you report this year under the old map, so it lines up against last year, or under the new map, so it reflects the new org? Type 3's answer is to just keep both.
customer_key |
customer_id |
current_segment |
previous_segment |
|---|---|---|---|
| 8801 | C-4417 | Premium | Standard |
-- Push current into previous, then set the new current.
UPDATE dim_customer
SET previous_segment = current_segment, -- 'Standard'
current_segment = 'Premium'
WHERE customer_id = 'C-4417';
A report can now GROUP BY current_segment or GROUP BY previous_segment, and both roll up cleanly across everyone.
Good for a handful of planned realignments where people need the before and after side by side. Its ceiling is obvious the moment you hit it: it remembers one step back and no further. Dana's third segment change would overwrite any record of her first. So don't mistake Type 3 for a version history. It's a second column, not a timeline.
Type 4 — pull the fast-moving stuff into its own table
Type 4 is the escape hatch for Type 2's row explosion.
Some attributes churn too fast to live inside a Type 2 dimension. Suppose you want to track each customer's age_band, income_band, and credit_band, and each of those shifts a couple of times a year, across millions of customers. Do that with Type 2 and your dimension balloons into the billions of rows. Type 4 says: take those volatile fields out, put them in a separate "mini-dimension," and have the fact table hold a foreign key to it.
The neat part is that the mini-dimension isn't one row per customer. It's one row per unique combination of the banded values. There are only so many ways to combine a few bands, so the whole thing stays small — thousands of rows, not billions.
dim_customer (slow attrs) dim_demographics (the combos)
+-------------+----------+ +----------------+----------+--------+-------+
| customer_key| name ... | | demographic_key| age_band |income |credit |
+-------------+----------+ +----------------+----------+--------+-------+
| 8801 | Dana ... | | 512 | 30-39 | 50-75k | Good |
+-------------+----------+ | 513 | 30-39 | 75k+ | Good |
+----------------+----------+--------+-------+
fact_orders
+----------+-------------+-----------------+--------+
| order_id | customer_key| demographic_key | amount |
+----------+-------------+-----------------+--------+
| O-1 | 8801 | 512 | 120.00 |
| O-2 | 8801 | 513 | 80.00 | <- income band moved up
+----------+-------------+-----------------+--------+
When Dana's income band changes, her customer row doesn't change at all. The next order just points at a different demographic_key. The history lives in the fact table, encoded in which mini-dimension row each transaction happened to reference, and the main dimension stays small and calm.
Reach for this when you've got rapidly changing, low-cardinality (banded) attributes hanging off a big dimension. One warning on vocabulary: some teams say "Type 4" to mean a separate audit table that mirrors the dimension. Kimball's actual Type 4 is the mini-dimension shown here. If a coworker says "Type 4," it's worth thirty seconds to check which one they mean before you start building.
Type 5 — Type 4 plus a current pointer (4 + 1)
Type 4 leaves one thing awkward. The fact table knows which demographic combo applied at the time of each order, which is great for history. But ask "what's Dana's income band right now?" and you're stuck hunting down her most recent fact and reading its key. If she hasn't ordered in a while, that answer is stale or missing.
Type 5 fixes it by adding a current_demographic_key column onto the base customer dimension, maintained Type 1 style — overwritten every time it changes. That's the "outrigger," a foreign key from one dimension pointing at another.
customer_key |
customer_id |
name |
current_demographic_key |
|---|---|---|---|
| 8801 | C-4417 | Dana Ruiz | 513 (overwritten on each change) |
Now both answers are easy. For history, join fact_orders.demographic_key to get the profile at the time of each order. For "right now," join dim_customer.current_demographic_key and skip the fact table entirely.
The number is the memory aid: mini-dimension (4) plus a Type 1 outrigger (1) gives you 5. It isn't a new philosophy, it's Type 4 with a quality-of-life patch bolted on for when analysts keep asking for the current profile straight off the customer.
Type 6 — Type 1 and 2 and 3 all at once (1 + 2 + 3)
Type 6 is a Type 2 dimension where every versioned row also carries a Type 1 "current" column. As the name arithmetic suggests, 1 + 2 + 3 = 6.
Here's the itch it scratches. Plain Type 2 gives you flawless history, but one question turns painful: "show me all of Dana's orders, grouped by her current segment." With straight Type 2, her old orders are welded to the Standard version and her new ones to Premium, so rolling everything up under "Premium" takes some awkward gymnastics.
Type 6 keeps the full Type 2 row history and adds a current_segment column that gets overwritten across all of her rows whenever the value changes.
customer_key |
customer_id |
historical_segment |
current_segment |
effective_date |
end_date |
is_current |
|---|---|---|---|---|---|---|
| 8801 | C-4417 | Standard | Premium | 2025-01-01 | 2025-08-31 | false |
| 9930 | C-4417 | Standard | Premium | 2025-09-01 | 9999-12-31 | true |
Look at what differs. historical_segment varies per row, that's the Type 2 part. current_segment is identical on every one of Dana's rows and gets rewritten across all of them when she upgrades, that's the Type 1 part. And historical_segment sitting beside current_segment as an "as-was" versus "as-is" pair is the Type 3 flavor. All three, one table.
-- On upgrade, rewrite current_segment on EVERY one of Dana's rows (Type 1),
-- while the usual expire-and-insert handles historical_segment (Type 2).
UPDATE dim_customer
SET current_segment = 'Premium'
WHERE customer_id = 'C-4417'; -- current and expired rows alike
Both questions collapse to a single GROUP BY. Sales by segment at the time of sale is GROUP BY historical_segment. All-time sales by each customer's current segment is GROUP BY current_segment.
This lands in the sweet spot for a lot of real warehouses, where you want honest history and the convenience of recasting everything under the latest value. It isn't free: every change now writes to all of a member's rows instead of one, so your ETL does more work, and you have to stay disciplined about which columns are the "current" mirror and which aren't.
Type 7 — two views over one clean table
Type 7 goes after the same current-versus-historical need as Type 6, just with tidier plumbing. Instead of jamming "current" columns into every row, it leans on a durable key: a permanent, never-changing id for the member, distinct from both the natural key and the per-version surrogate key. The fact table then carries two keys:
- the surrogate key (
customer_key), which resolves to the historical version, and - the durable key (
customer_durable_key), which resolves through the current row to the member's current attributes.
fact_orders
+----------+--------------+---------------------+--------+
| order_id | customer_key | customer_durable_key| amount |
+----------+--------------+---------------------+--------+
| O-1 | 8801 | D-4417 | 120.00 |
+----------+--------------+---------------------+--------+
| |
| join on surrogate key | join on durable key, is_current = true
v (historical) v (current)
segment = 'Standard' segment = 'Premium'
It's the same single Type 2 table underneath. Join the fact on customer_key for the as-was picture, or on customer_durable_key filtered to is_current = true for the as-is picture. In practice teams usually wrap those two paths as two database views — a dim_customer_historical and a dim_customer_current — so BI users just grab whichever view fits their question and never think about the keys.
So how do you choose between 6 and 7, given they deliver the same thing? Type 6 widens every row with Type 1 mirror columns: denormalized, heavier writes, but dead-simple joins. Type 7 puts a second key on the fact and reads one table two ways: normalized, lighter dimension, but your fact loads have to capture the durable key and your BI layer has to expose both paths. Type 7 ages better when you've got many attributes to double-track. Type 6 is the simpler call when it's only one or two.
The whole ladder on one screen
| Type | Name | History? | How | Reach for it when… |
|---|---|---|---|---|
| 0 | Retain Original | Frozen at creation | Never update the column | The value is fixed by definition (birth date, original score) |
| 1 | Overwrite | None | UPDATE in place |
Corrections and cosmetic fields nobody asks about |
| 2 | Add New Row | Full | New row, surrogate key, date range | The default for attributes that genuinely evolve |
| 3 | Add New Attribute | One old value | A previous_ column |
Planned realignments needing as-was and as-is together |
| 4 | Add Mini-Dimension | In the fact table | Volatile attrs split into a combo dimension | Fast-changing, low-cardinality bands on a huge dimension |
| 5 | Mini-Dim + Outrigger | Fact + current pointer | Type 4 plus a current_*_key on the base dim |
You have Type 4 but also need the current profile directly |
| 6 | 1 + 2 + 3 | Full + current mirror | Type 2 rows plus an overwritten current_ column |
Full history and recasting under the latest value |
| 7 | Dual Type 1 & 2 | Full | One Type 2 table plus a durable key, read two ways | Same as 6, but many attributes or you want it normalized |
What it all comes down to
The eight types look like a menu, but they're really the same question asked with more and more precision: when a dimension changes, what do you owe the past?
Types 0 and 1 are the two extremes, freeze it or forget it. Type 2 is the honest middle: remember everything and never let old numbers shift under people. Type 3 gives you a second axis rather than a timeline. Type 4 exists so Type 2 doesn't explode on fast-moving fields, and Type 5 makes it pleasant to query. Types 6 and 7 are two roads to the same place — full history plus a current view — where you're really just choosing between denormalizing (6) and carrying a durable key (7).
And here's the part that trips people up: you don't pick a type for a table. You pick one for each attribute. On Dana's row, her name is Type 1, her birth date is Type 0, her city is Type 2, her income band is probably Type 4, and her segment might be Type 6. A good customer dimension is almost always a mix. Knowing all eight rungs is what lets you put each column on the right one, instead of forcing the whole table onto whichever type you happened to learn first.
The numbering follows Ralph Kimball's The Data Warehouse Toolkit (3rd ed.). Types 0 through 2 are pretty much universal. From 3 up, naming drifts between shops — "Type 4" especially — so check definitions with your team before you build. And whatever you land on, record the SCD type of every attribute in your data dictionary. It's the single most useful piece of documentation a dimensional model can have, and the first thing you'll wish you had when someone asks why the numbers moved.
0 Comments