Slowly Changing Dimensions (SCD 0–7)

Dimensions describe the world, and the world changes: a customer moves state, a store is re-districted, a subscriber switches tariff. Slowly changing dimensions (SCDs) are the named strategies for one question — when an attribute changes, what happens to history?

Apply each type to the same move below and watch two things move with it: the rows in dim_customer, and the answer to a Q1 question that has only one true answer. The selector opens on Type 1, the one you get by accident.

slowly changing dimension

A dimension whose attributes change over time, requiring a history strategy.

Type 1

Overwrite the attribute; no history is kept.

Type 2

Add a new row with effective dates; full history is preserved.

effective dating

valid_from / valid_to columns that mark when a row's values were true.

The move itself is not the problem. The problem is that four different teams will ask about C-7 this week, and they do not all want the same answer — so there is no single correct handling of the change, only a correct handling per question.

Decide each of the four requests below before reading its verdict. Two of them legitimately want the current value; getting those wrong is as damaging as losing history, and far less discussed.

as-was vs as-is

Reporting by the attribute value at event time vs its current value.

history preservation

Keeping prior attribute values so the past can be reported accurately.

silent re-attribution

Past facts shifting to new attribute values because an attribute was overwritten.

Neither of the two simplest strategies keeps row-level history. Type 0 declares the attribute immutable and ignores any new value that arrives; Type 1 overwrites in place, so only the current value survives. Type 1 is also what a plain UPDATE does, which makes it the policy you end up with when you never chose one.

Run the zoo registry feed below under each policy. Three changes arrive together — a corrected identification, a real move, and a corrupted date — and no single table-wide answer handles all three.

Type 0 (retain)

The attribute is fixed; new values are ignored.

Type 1 (overwrite)

Replace the value in place; no history is kept.

correction

Fixing a wrong value (a typo) where overwriting is the intended behavior.

Type 2 preserves history by adding a row rather than changing one: the same business key gains a second version, and three columns say which version was the truth when — valid_from, valid_to, and is_current. The surrogate key differs per version, which is what lets a fact point at the version it was loaded against and stay there forever.

The insert is not the hard part. Work the three switches below one at a time: each one is a piece of bookkeeping the load has to do itself, and each missing one breaks a different query without raising an error.

version row

One row per historical state of an entity in a Type 2 dimension.

valid_from / valid_to

The effective date range marking when a version was the truth.

is_current

A boolean flag marking the latest version, for fast current-state queries.

create table dim_customer (
  customer_key bigint generated always as identity primary key,  -- surrogate, per version
  customer_id  text    not null,                  -- business key, stable across versions
  state        text    not null,
  valid_from   date    not null,
  valid_to     date    not null default date '9999-12-31',
  is_current   boolean not null default true
);

The remaining types rearrange the same information rather than adding new ideas. Type 3 adds a previous-value column; Type 4 keeps the current version in the main table and pushes history to a side table; Type 6 is a Type 2 dimension that also carries a Type 1 current-value column, and optionally the Type 3 one — hence the arithmetic in the name, 1 + 2 + 3.

Build that hybrid a column at a time below, and leave the third switch off first: a Type 1 column inside a Type 2 dimension has to be rewritten on every version, and a stale one answers rather than errors.

Types 5 and 7 move information across tables instead. Type 5 adds a mini-dimension reached by a Type 1 outrigger key (the next section covers it in production depth). Type 7 puts both the Type 2 surrogate and a stable durable key on the fact: as-was joins the surrogate, as-is joins a current-only view on the durable key, and no fact row is ever updated.

Type 6

Hybrid (1+2+3): Type 2 rows plus an overwritten current-value column.

outrigger key

A Type 1 pointer on the main dimension that always references the current mini-dimension row.

durable key

A stable identifier shared by every version of an entity — the as-is join key in Type 7.

-- dim_customer_segment is a mini-dim with its own surrogate key.
-- dim_customer carries a Type 1 outrigger key pointing to the CURRENT mini-dim row.
-- The fact joins to the main dim (historical) AND to the mini-dim (current segment).
create table dim_customer_segment (
  segment_key   int  primary key,
  income_band   text not null,   -- e.g. 'Low', 'Mid', 'High'
  risk_tier     text not null
);

create table dim_customer (
  customer_key      bigint primary key,  -- per-version (Type 2)
  customer_id       text   not null,
  state             text   not null,
  current_seg_key   int    references dim_customer_segment,  -- Type 1 outrigger
  valid_from        date   not null,
  valid_to          date   not null,
  is_current        boolean not null
);

-- The fact stores TWO keys, and neither is ever updated:
--   customer_hist_key    = the Type 2 surrogate that was
--     current when the event happened (as-was).
--   customer_durable_key = the stable durable key, identical
--     on every version of the customer (as-is).
create table fact_sales (
  sale_key             bigint primary key,
  customer_hist_key    bigint not null  -- as-was joins
    references dim_customer(customer_key),
  customer_durable_key bigint not null, -- as-is joins
  amount               numeric(10,2) not null
);

-- As-was: join dim on customer_hist_key.
-- As-is:  join a CURRENT-ONLY view (or is_current = true)
--         on customer_durable_key.

Type 2 costs one row per change, so the size of a dimension is its population multiplied by the total churn of everything inside it. That is fine for a home address and ruinous for a score the model rewrites every week — and the two attributes sit in the same table, so the fast one sets the price.

The fix is a mini-dimension: move the fast-churning attributes into their own small lookup of distinct combinations, store its key on the fact at load time, and keep a Type 1 outrigger key on the main dimension for current-state reads. Move the attributes below one at a time and watch which one was actually paying for the explosion.

version explosion

Rapid growth of Type 2 dimension rows caused by high-churn attributes.

mini-dimension

A small dimension holding rapidly-changing attributes, versioned independently of the main dim.

outrigger key

A Type 1 foreign key on the main dimension pointing to the current mini-dim row.

-- dim_customer_segment: small table of distinct attribute combinations.
-- dim_customer: only slow-changing attributes (state).
-- fact_sales: joins dim_customer (historical state) AND dim_customer_segment
--             (segment at sale time, stored as segment_key on the fact).

-- dim_customer now: 2 rows for customer 7 (one state change)
-- dim_customer_segment: shared lookup with a handful of distinct rows

select dc.state, dcs.income_band, sum(f.amount) as revenue
from fact_sales         f
join dim_customer       dc  on dc.customer_key  = f.customer_key
join dim_customer_segment dcs on dcs.segment_key = f.segment_key
group by dc.state, dcs.income_band;

A fact can arrive before its dimension row exists — a real-time event stream against a source that exports its master data nightly. Waiting is not an option and neither is dropping the row, so the load has to invent something to point at.

The answer is an inferred member: a placeholder row carrying only the business key and is_inferred = true, backfilled in place when the real data lands. Judge the four handlings below on both days, because two of them look identical tonight and diverge tomorrow. (Chapter 10 owns the mirror case, a late fact against a dimension whose history already exists.)

inferred member

A placeholder dimension row created when a fact arrives before its dimension data, identified by is_inferred = true.

retroactive SK resolution

Backfilling dimension attributes into an inferred placeholder; the surrogate key is unchanged so facts resolve automatically.

late-arriving dimension

A case where the dimension row itself has not arrived when the referencing fact is loaded.

-- Step 1: fact arrives; customer 42 is not in dim_customer yet.
-- Insert an inferred placeholder to unblock the fact load.
insert into dim_customer
  (customer_id, state, valid_from, valid_to, is_current, is_inferred)
values
  ('42', 'Pending', current_date, date '9999-12-31', true, true);
-- Returns surrogate key 999 (auto-generated).

-- Step 2: load the fact pointing to the placeholder key 999.
insert into fact_sales (customer_key, amount, sale_date)
values (999, 79.99, '2026-06-15');

-- Step 3: real customer row arrives in the nightly batch.
-- Backfill attributes in place (Type 1 — it was always true, we just did not know).
update dim_customer
set state       = 'TX',
    is_inferred = false
where customer_id = '42' and is_inferred;
-- Surrogate 999 is unchanged. The fact now resolves to real attributes.

Reading a Type 2 dimension on its own is easy: filter is_current for now, or filter a date between valid_from and valid_to for then. The example below shows both against the same two rows.

Joining facts to it is where the money is lost. Four predicates are offered below against a dimension that is perfectly correct — two of them return wrong numbers, and one of the two wrong ones is the fix people reach for first.

current query

Filtering to is_current (or the open valid_to) for the present state.

point-in-time query

Filtering where a date falls in [valid_from, valid_to] for a past state.

surrogate key join

Joining a fact to the dimension version current at event time, via the stored key.

-- CURRENT: what is true now?
select customer_id, state
from dim_customer
where customer_id = '7' and is_current;          -- CA

-- POINT-IN-TIME: what was true on a past date?
select customer_id, state
from dim_customer
where customer_id = '7'
  and date '2026-03-10' between valid_from and valid_to;   -- NY

Three invariants make a Type 2 dimension trustworthy, and each needs its own assertion: gap-free (no period is unaccounted for), overlap-free (no two versions of a key share a day), and at most one is_current row per key. Audit the bus fleet below — it holds one instance of each defect, and every one of them passes the other two checks.

The fourth rule is a convention: pick one interval style and never mix. This chapter uses closed intervals [valid_from, valid_to], so valid_to is the last day the version applies, adjacent versions read ...03-14 / 03-15..., and point-in-time queries use BETWEEN. Chapter 15 uses the half-open alternative where valid_to equals the next valid_from and queries use >= and <.

The last hazard is timing: a retroactive correction cannot be appended, because the true value changed inside an interval that already exists. It has to split that interval, as the worked example below does.

gap-free invariant

No un-accounted time period exists between the first valid_from and the present for a given business key.

overlap-free invariant

No two version rows for the same business key have overlapping date ranges.

two current rows

The failure mode where two rows share is_current = true for the same business key, causing duplicates.

-- A correction arrives: customer 7 moved to TX on 2026-03-15,
-- not on 2026-06-01 as recorded. The NY row must be split.

-- Before: one NY row for customer 7.
-- customer_key=501, state='NY', valid_from='2026-01-01', valid_to='2026-05-31'

-- Step 1: Shrink the existing NY row to end just before the correction date.
update dim_customer
set valid_to = date '2026-03-14'
where customer_key = 501;

-- Step 2: Insert the TX intermediate version.
insert into dim_customer
  (customer_id, state, valid_from, valid_to, is_current)
values
  ('7', 'TX', date '2026-03-15', date '2026-05-31', false);

# dbt snapshot, CHECK strategy — the missed-columns bug:
# income_band is NOT in check_cols, so a change to it
# alone creates NO new version. Silent history loss.
snapshots:
  - name: dim_customer_snapshot
    config:
      unique_key: customer_id
      strategy: check
      check_cols: [state, segment]   # income_band missing!

# TIMESTAMP strategy avoids the column list entirely —
# it versions on any change to a reliable updated_at:
#   strategy: timestamp
#   updated_at: updated_at

The applied skill is choosing per attribute, not per table. Work through the six attributes of dim_customer below: the readout on the right prices each decision in version rows, and the panel under each attribute says what the wrong choice costs.

It opens with everything on Type 1, because that is what a table with no declared policy actually is. Four different policies end up in this one dimension.

per-attribute policy

Choosing an SCD type for each attribute, not one type for the whole table.

default to Type 2

Preferring full history for meaningful descriptive attributes when unsure.

irreversibility

Once overwritten, history cannot be recovered, which biases toward keeping it.

Every type leaves a different fingerprint in the rows, which is why an interviewer can hand you a table and ask what it is. Run the chapter backwards below: five dimensions after a change, named from the rows alone.

The tables underneath are the reference to come back to — mechanism, history kept, and the mistakes that cost the most.

version explosion

Rapid growth of Type 2 rows caused by high-churn attributes added to the main dim.

inferred member

A placeholder dim row for a fact whose dimension data has not yet arrived.

interval splitting

Splitting an existing version row to accommodate a retroactive backdated correction.

Six design scenarios for this chapter, on the ERD canvas. Each one is graded against a real rubric, seeded with real rows, and each has one fault planted in the data rather than in the diagram.

Build them with the chapter closed. If one goes wrong, come back to the section it belongs to rather than re-reading the whole thing.