SCD type 1 or type 2? How to decide, one dimension at a time

The slowly changing dimension question is asked once per dimension, early, and is expensive to change later. Here is the one test that answers it, and the three ways it goes wrong.

What the two types actually do

A dimension row describes something — a customer, a product, a store — and things change. The slowly changing dimension types are the standard answers to what happens to the row when they do.

Type 1: overwrite

The old value is replaced and gone. One row per customer, always showing the current state. Every fact that ever pointed at that customer now reports under the new value, including facts from before the change.

DimCustomer  (type 1)
  CustomerKey   CustomerNumber   Region
  1001          C-4471           South     <- was 'North' until March; nobody can tell

Type 2: add a row

The old row is closed and a new one opened. Several rows per customer, each valid for a period. A fact points at whichever version was current when it happened, so last year’s sales stay in last year’s region.

DimCustomer  (type 2)
  CustomerKey  CustomerNumber  Region  ValidFrom   ValidTo     IsCurrent
  1001         C-4471          North   2019-01-01  2026-03-14  0
  2387         C-4471          South   2026-03-14  9999-12-31  1

Note that the surrogate key changed and the business key did not. That is the whole trick: the business key says which customer, the surrogate key says which version of them. It is also why type 2 is impossible without surrogate keys.

Types 3 and 6, briefly

Type 3 keeps a PreviousRegion column beside the current one: exactly one step of history, for the case where a reorganisation needs both views side by side for a while. Type 6 is a type 2 dimension with a type 1 “current value” column added to every historical row, so you can report by either. Both are real and both are far rarer than the literature implies. Reach for them only when you can say which question they answer that 1 and 2 cannot.

How to decide, attribute by attribute

The decision is made per dimension, but the evidence comes from its attributes. Walk the columns and ask three things:

1. Does this attribute change at all? A date of birth does not. A product’s launch date does not. If nothing in the dimension changes, the type 2 machinery is pure cost.
2. When it changes, does the past need the old value? Region, segment, sales territory, price band, manager — almost always yes, because reports are grouped by them and last year’s numbers must not move. Email address, phone number, a spelling correction to a name — almost always no.
3. Is the attribute even the business’s? ModifiedDate, RowVersion, LoadBatchID record that a system touched the row, not that anything about the customer moved. See the third mistake below.

One volatile attribute whose history matters is enough to make the dimension type 2. If none qualifies, type 1. If the volatile attribute is one nobody groups by — a phone number — type 1 with a clear conscience.

What type 2 costs

Type 2 is the right default for anything people group by, and it is not free. Know what you are buying.

That last row is the asymmetry that settles most arguments: a type 2 dimension can always be read as type 1 (WHERE IsCurrent = 1), but a type 1 dimension cannot be turned into type 2 after the fact, because the history was never stored. When genuinely unsure, type 2 is the reversible mistake.

The three expensive mistakes

Making everything type 2 to be safe

It feels cautious and it is not. A dimension with twelve attributes that all open new versions gains rows for every trivial edit, the fact load slows down resolving date-ranged keys, and analysts who forgot IsCurrent = 1 double-count everything. Type 2 the dimensions whose history someone will actually ask for; type 1 the rest.

Retrofitting type 2 after a year of type 1

By then the history is gone. The dimension can be switched to type 2 from today onward, but every fact before the switch points at a single version that claims to have been true forever. Decide up front, which is why the question belongs in the design and not in the second sprint.

Letting the change test watch housekeeping columns

This is the quiet one. If a type 2 dimension compares every column to detect change, and the source carries a ModifiedDate or a rowversion, then every batch that touches the source row opens a new version of the customer. After a year the dimension holds a detailed history of the source system’s maintenance schedule and almost nothing about the business — and it is enormous.

The fix is to restrict the change test to the attributes that describe the business, while still writing every column. Netune marks those columns tracked, hashes only them when deciding whether to open a version, and names the ones it ignored in a comment at the top of the load procedure, so the decision is visible.

What a type 2 load looks like

For anybody writing it by hand, the shape is always the same and every step matters:

1. Stage the incoming rows.
2. For each incoming row, find the current version by business key (IsCurrent = 1).
3. Compare the tracked attributes — a hash of them is cheaper than a column-by-column comparison and is not fooled by column order.
4. Where they differ: set the old row’s ValidTo to now and IsCurrent to 0, insert the new row with ValidFrom now, ValidTo far future, IsCurrent 1.
5. Where the key is new: insert as a first version.
6. Where nothing tracked changed but an untracked column did: update in place — that is the type 1 part of a type 2 dimension.
7. Keep the -1 unknown row, so a fact whose customer cannot be resolved lands somewhere and is still counted.

Which methodologies ask this question at all? Kimball and Galaxy. Data Vault, Anchor and Inmon’s core keep every version by construction, so there is nothing to decide; One Big Table keeps none.

Questions people ask