Slowly Changing Dimensions

Customer 2 in the bookshop lives in Boston. Every order she’s placed for the last year sits in fct_order_items, and your regional-sales report faithfully counts them under New England. Then she moves to Denver, and the source system does the only thing it knows how to do — it overwrites city in place. Run the report again and a year of Boston orders have quietly relocated to the Mountain West. Nobody edited the facts. The dimension just forgot where she used to be.

That’s the whole problem in one sentence: when a dimension attribute changes, what happens to the history that pointed at the old value? Dimensional modeling has a taxonomy of answers, and they’re numbered — Slowly Changing Dimension types. Picking the right one per attribute is one of the few design decisions that’s genuinely hard to reverse, because once you’ve overwritten the past you can’t un-overwrite it.

The types are usually presented as a ladder, from “do nothing” to “do everything,” and read that way they invite the wrong instinct — that higher numbers are better. They aren’t. Each type is a specific bargain between how much history you keep and how much complexity you carry, and the craft is spending complexity only where the history earns it. What follows is the whole taxonomy — Types 0 through 4, the 6 hybrid, and the 7 dual — each with a bookshop attribute that actually wants it. The mechanics of the one you’ll use most, Type 2, get their own section afterward, because that’s where the sharp edges live.

Type 0 — retain the original

The attribute never changes, by rule. Customer 2’s signup_date is January 5th and will be January 5th forever; her acquisition_channel — the “organic search” that first brought her to the shop — stays “organic search” even after she’s spent three years arriving by newsletter link. The source might overwrite these; the warehouse refuses to.

customer_id	signup_date	acquisition_channel
2	2026-01-05	organic_search

Type 0 isn’t laziness or an accident of never updating — it’s a deliberate statement that the original value is the one with meaning. “How many of last quarter’s revenue came from customers we first acquired through paid search?” is a question about the channel at acquisition, not the channel they use now. Freeze the column and the question stays answerable. The failure mode is a Type 0 attribute that should have been Type 1 or 2 — a “primary address” frozen at signup that nobody meant to be historical — so use it only where “original, always” is the actual intent.

Type 1 — overwrite

The new value replaces the old and no history is kept. This is what the source system did to Boston, and it’s exactly right when history genuinely doesn’t matter: a misspelled name, a corrected email, a fixed phone number.

-- before: a typo
customer_id = 2, email = '[email protected]'

-- after: Type 1 overwrite, no trace of the old value
customer_id = 2, email = '[email protected]'

You’d never want to report “orders placed while the email was misspelled” — the misspelling was never a fact about the world, just a defect in the record. Type 1 is the simplest thing that works, and its cost is total amnesia: after the update, the warehouse cannot tell you the column was ever anything else. Reach for it when the current value is the only value anyone will ever ask about. Every dimension has a fistful of these columns, and pretending otherwise — versioning a corrected typo — buys you nothing but rows.

Type 2 — add a new versioned row

This is the workhorse, and the default whenever you care about as-of truth. Instead of overwriting, you close the old version and insert a new one, each carrying a time window — dbt_valid_from, dbt_valid_to — an is_current flag, and often a version number so you can talk about “her second address” without comparing dates:

customer_sk	customer_id	city	dbt_valid_from	dbt_valid_to	is_current	version
a1f…	2	Boston	2026-01-05	2026-04-18	false	1
7c9…	2	Denver	2026-04-18	9999-12-31	true	2

Boston’s row gets stamped with the day she left; Denver’s opens on the day she arrived. Both exist forever. Notice there’s now more than one row per customer_id, which means the primary key is no longer the natural key — it’s a surrogate, customer_sk, minted per version. The natural key customer_id is what stays constant across her versions and lets you gather them back together.

The payoff is in how facts join. A fact row records when it happened, so it joins not to “the customer” but to the version of the customer that was current at that event. Last year’s orders match the Boston row because their order dates fall inside Boston’s window; this month’s match Denver. History stops rewriting itself. The report tells the truth for every point in time, not just now. When someone says “we need to see it as it was,” they’re asking for Type 2 — and that versioned table is the dim_customer_history we build for real in the next chapter.

Type 3 — add a prior-value column

Keep the current value and one predecessor side by side, in two columns on the same row. The bookshop reorganizes its US sales regions — “New England” and “Mid-Atlantic” merge into a single “Northeast” — and merchandising needs to compare performance “under the old alignment” against “under the new one” for a transition quarter. Type 3 gives them exactly that, and nothing more:

customer_id	current_region	previous_region
2	Northeast	New England

One query, no self-join, no window predicates: sum(revenue) by current_region and sum(revenue) by previous_region are two columns of the same report. What you cannot do is ask for the region before the previous one — Type 3 remembers exactly one step back, and the moment a second reorg happens, “New England” is overwritten out of previous_region and gone.

That’s the whole character of Type 3: it’s not general-purpose history, it’s a named before-and-after for a specific, bounded transition. Reach for it when there’s one comparison analysts care about and it has a shelf life — a re-org, a re-branding, a pricing-scheme change — not when you want the full story. When you find yourself wanting previous_previous_region, you wanted Type 2 all along.

Type 4 — the mini-dimension

Some attributes change fast. A customer’s loyalty_tier recalculates monthly; their spend_band (the rolling-30-day bucket the marketing team segments on) can move every week; a review_count_band ticks up whenever they rate a book. Track those with Type 2 and dim_customer_history balloons: dozens of near-duplicate rows per customer a year, almost all of them identical except for one volatile column, all of them dragging the stable name and email and signup date along for the ride.

Type 4 splits the fast-movers into their own mini-dimension with its own surrogate key:

-- dim_customer_profile  (the mini-dimension)
customer_profile_sk | loyalty_tier | spend_band  | review_count_band
b2e…                     | silver       | $50–$100    | 1–5
f70…                     | gold         | $100–$250   | 6–20

The mini-dimension isn’t one row per customer — it’s one row per observed combination of the banded attributes, a fixed grid the whole customer base shares. The fact then carries two foreign keys: customer_sk to the stable dim_customer, and customer_profile_sk to whichever demographic row was current at the moment of the order:

select
    f.order_id,
    c.full_name,
    d.loyalty_tier,
    f.extended_price
from fct_order_items    as f
join dim_customer       as c on f.customer_sk = c.customer_sk
join dim_customer_profile as d on f.customer_profile_sk = d.customer_profile_sk

The volatile stuff gets its history — captured on the fact, at event time — without exploding the stable dimension, and because the banded values are a small grid, the mini-dimension stays tiny no matter how many customers churn through it. The trade is banding: spend_band buys the compression by giving up the exact dollar figure, which is usually fine, because you were going to segment on the band anyway. The one piece of work Type 4 pushes onto the fact load is resolving customer_profile_sk — the pipeline has to look up which demographic combination was current for that customer at that moment, the same as-of lookup Type 7 does for its version key. And when you want today’s demographics for a customer regardless of history, you add an “outrigger” foreign key straight from dim_customer to the current mini-dimension row, so a current-state query never has to touch the fact at all.

Type 6 — the hybrid (1 + 2 + 3)

The name is a wink: 1 + 2 + 3 = 6, and the type genuinely is all three at once. You keep full Type 2 version rows, and carry a Type 1 current-value column that’s overwritten on every version whenever the attribute changes:

customer_id	region (as-of, Type 2)	current_region (Type 1)	dbt_valid_from	dbt_valid_to
2	New England	Northeast	2026-01-05	2026-04-18
2	Northeast	Northeast	2026-04-18	9999-12-31

Read region and you get the value that was true at the event’s time (the honest as-of answer). Read current_region and every row — including the old Boston-era one — reports today’s value, because Type 1 overwrites it across all versions. So a single query can either respect the version (group by region) or roll all of history up to today’s alignment (group by current_region) without a self-join or a second pass to find the current row.

That second column is the whole point: it answers “what’s true now, applied to all of history” — “show me all-time revenue bucketed by customers’ current region” — which Type 2 alone makes awkward (you’d join the fact to the historical row for the window, then join again to the current row for its value). Type 6 is the answer when analysts keep asking both the as-of and the as-is question of the same attribute, often in the same dashboard.

Type 7 — the dual: report as-was or as-is from one fact

Type 6 solves the both-questions problem by widening the dimension. Type 7 solves it by widening the fact, and it’s the one that was missing from most treatments of this list. The idea: put two keys on the fact row — the durable natural key customer_id, which never changes, and customer_version_sk, the surrogate of the version that was current when the event happened, resolved and stored at load time. One fact, two keys, and then the join you choose decides which history you see.

-- fct_order_items carries BOTH keys
order_id | customer_id | customer_version_sk | order_date | extended_price
88213    | 2           | a1f…  (Boston-era)  | 2026-02-11 | 24.00
90551    | 2           | 7c9…  (Denver-era)  | 2026-05-02 | 31.50

As-was (point-in-time) — join on the version surrogate. Order 88213 finds the Boston version because that’s the customer_version_sk frozen onto it at load, and February revenue reports as Boston forever:

select h.city, sum(f.extended_price) as revenue
from fct_order_items as f
join dim_customer_history as h on f.customer_version_sk = h.customer_sk
group by 1

As-is (current) — join on the durable natural key and filter to the current row. Now both orders find Denver, because you deliberately ignored which version was live at event time and asked for today’s:

select h.city, sum(f.extended_price) as revenue
from fct_order_items as f
join dim_customer_history as h
  on f.customer_id = h.customer_id and h.is_current
group by 1

Same fact table, same dimension table, two joins, two truths — and crucially, the consumer picks which one per query rather than the modeler baking it in. That’s what makes Type 7 the most flexible option on the list, and also the reason it’s the design chapter 9 leans toward when it weighs storing the version key on the fact: carry the durable key alongside it and you’ve paid for Type 7 almost by accident. The cost is that the fact must resolve customer_version_sk at load with a windowed lookup, and — the subtlety chapter 10 is entirely about — a stored version key is a bet that the dimension’s windows were correct at load time.

Type 2 mechanics: the close-and-insert contract

Every type above either keeps no history (0, 1), keeps a bounded slice (3, 4), or leans on Type 2’s spine (6, 7). So it’s worth being precise about what Type 2 actually does on a change, because the whole thing rests on one invariant that’s easy to state and easy to break.

When city moves from Boston to Denver on April 18th, two writes happen, in this order:

Close the old row. Boston’s dbt_valid_to, which was the open sentinel 9999-12-31, gets stamped with the change moment: 2026-04-18. Its is_current flips to false.
Open the new row. A fresh Denver row is inserted with dbt_valid_from = 2026-04-18, dbt_valid_to = 9999-12-31, is_current = true, and the next version number.

The load-bearing detail is in the boundary: Boston’s dbt_valid_to equals Denver’s dbt_valid_from, exactly. The windows are half-open — dbt_valid_from inclusive, dbt_valid_to exclusive — which means the instant 2026-04-18 belongs to Denver and only Denver. No gap (there’s no sliver of time between the windows with no version covering it) and no overlap (there’s no instant two versions both claim). That’s the no-overlap/no-gap rule, and it’s what lets the point-in-time join use >= valid_from and < valid_to and be certain every event date lands in exactly one version. Use between instead, with matching bounds, and a fact stamped precisely on the boundary joins to two rows and double-counts.

The retroactive change snapshots can’t handle

Here’s where the clean picture cracks. Suppose it’s June, and you learn that customer 2 didn’t move on April 18th at all — she moved on March 2nd, and the source only got around to overwriting city in April. Every order she placed in March was, as-of-truth, a Denver order, but they’re currently pinned to Boston’s window (which runs to April 18th).

Fixing this means rewriting an existing window: Boston’s dbt_valid_to must move back to March 2nd, and a Denver version must be spliced in starting March 2nd — a version that opens before the one already sitting in the table. A plain dbt snapshot cannot do this. A snapshot only ever compares today’s source against the latest version it recorded and appends a new row dated to now (or to the source’s updated_at); it has no concept of “this change was effective in the past” and no mechanism to reach back and re-cut a window it already closed. The change either arrives on time or gets mis-dated to whenever the snapshot happened to notice it. This retroactive, late-arriving case — and the reason a hand-derived, rebuildable history handles it where a snapshot can’t — is the entire subject of chapter 10. For now, just register the shape of the limit: snapshots date history to observation, and retroactivity is a change to the past that observation can’t express.

Hard deletes: when the row simply vanishes

The other case the mechanics have to answer: the source deletes customer 2 — she closes her account and her row disappears entirely. A Type 2 dimension shouldn’t pretend she’s still current, but it also shouldn’t erase her, because her old orders still need a version to join to. There are three honest options:

Ignore it — leave her last version open. Almost always wrong: she now looks eternally current despite not existing.
Invalidate it — close her open version with dbt_valid_to set to the moment you noticed she was gone. The history reads “true until we stopped seeing her,” which is usually the right semantics and keeps her old facts joinable.
Tombstone it — close the last version and insert a marker version flagged deleted, for when downstream logic must distinguish “deleted” from “we merely stopped hearing about it,” or when a deleted customer might resurrect and you want that visible.

dbt’s snapshot has a config knob for exactly this choice, with invalidate as the sane default — the specifics are next chapter’s build detail. The conceptual point is that a disappeared source row is a change like any other, and a Type 2 dimension has to close a window for it rather than let a stale row hang open forever.

A decision guide

The types aren’t a menu where fancier is better; they’re per-attribute bargains. Real dimensions mix them by column, and the honest way to design dim_customer is to walk its attributes one at a time and ask how much history each actually deserves:

Attribute	Change it undergoes	SCD type	Why
`signup_date`	never (by rule)	0	the original date is the fact
`acquisition_channel`	source may overwrite	0	analysis is about first-touch, not now
`email`, `full_name`, `phone`	corrections	1	nobody reports “while it was wrong”
`city`, `region`	genuine relocation	2	drives regional reporting; as-of matters
`sales_region` (one re-org)	a single realignment	3	one named before/after, bounded shelf life
`loyalty_tier`, `spend_band`	monthly/weekly	4	too volatile for Type 2 without exploding the dim
`region` (both questions asked)	relocation	6 or 7	when as-of and as-is are both routine

Two rules of thumb fall out. First, default an attribute to Type 1 and promote it to Type 2 only when someone can name a report that needs the old value at its old time — history you keep but never query is pure cost. Second, when an attribute changes faster than your reporting grain (multiple times a period), Type 4 is protecting the stable dimension from that attribute’s churn; don’t let a volatile column into the Type 2 spine.

And whatever mix you land on, one test protects every Type 2 attribute you chose: the no-overlap/no-gap window check. Because the point-in-time join assumes the windows tile time perfectly — no instant uncovered, no instant double-covered — a corrupted window is a silently wrong report, not an error. The check is a single window function that flags any customer whose consecutive windows don’t meet exactly:

with windows as (
  select
      customer_id,
      dbt_valid_from,
      dbt_valid_to,
      lead(dbt_valid_from) over (
          partition by customer_id order by dbt_valid_from
      ) as next_valid_from
  from dim_customer_history
)
select *
from windows
where next_valid_from is not null
  and next_valid_from <> dbt_valid_to   -- a gap or an overlap

Any row it returns is a break in the contract: next_valid_from > dbt_valid_to is a gap (facts in that slice join to nothing and vanish from reports); next_valid_from < dbt_valid_to is an overlap (facts in that slice join to two versions and double-count). On a healthy Type 2 dimension this returns zero rows, always. The next chapter wires the same invariant up as a real dbt test with dbt_utils.mutually_exclusive_ranges so a break turns the build red instead of surfacing quarters later in front of an executive — which is the traditional venue for discovering unprotected window corruption.

Final thoughts

The SCD types aren’t a ladder where you climb toward the best answer — they’re a set of honest trade-offs, and the skill is matching each attribute to the amount of history it actually deserves. Freeze the original channel, overwrite the corrected email, version the region that drives your reports, band the loyalty tier into a mini-dimension, and put both keys on the fact when analysts want the story told both ways. The number you’ll reach for most is Type 2, because “show me the numbers as they were understood at the time” is the question a warehouse exists to answer — and it’s the one question a source system, forever overwriting the present, can never answer for you. The next chapter stops talking about the contract and builds it: a Type 2 dim_customer_history in dbt, windows, flags, tests, and all.

Next: Type 2 in Practice: dbt Snapshots