Slowly Changing Dimensions: Type 1 vs Type 2, and When History Matters

A slowly changing dimension is a dimension whose attributes change over time, and the SCD type is your decision about what happens to the old value. Type 1 overwrites it and forgets; Type 2 keeps every version as a dated row, so facts can join to the attributes that were true when the event happened. If historical reports must be historically accurate, you need Type 2.

A slowly changing dimension (SCD) is a dimension table whose descriptive attributes change over time: an account moves to a different branch, a customer changes segment, a product gets reclassified. “Slowly” just means slower than the facts; the account changes branch once in years while its payments arrive daily. The design question is what the warehouse does when the change happens, and the numbered SCD types, formalized in Ralph Kimball’s dimensional modeling, are the standard menu of answers. The choice looks like plumbing but decides something business-visible: whether last year’s regional report still says what it said last year. That makes it an analyst concern, not just an engineering one, because the analyst is who gets asked why the Q1 numbers changed in July.

What are the SCD types?

Four types cover practice; the exotic ones (4, 6) are combinations. The decision is per attribute, not per table: one dimension can hold Type 1 and Type 2 attributes side by side.

Type	On change	History	Use when
Type 0	Do nothing, keep original	Original only	The attribute must never change (original open date)
Type 1	Overwrite in place	None	Corrections, and attributes where history has no value (a typo fix in a name)
Type 2	Insert a new versioned row	Full	Reports must reflect what was true at the time (branch, segment, risk rating)
Type 3	Add a “previous value” column	One step	You only ever need current vs prior (rare in practice)

Type 1 is the default teams drift into because it is the least work, and it is the right answer for corrections: when the branch code was wrong, you want it overwritten everywhere, including the past. The trap is applying Type 1 to real change. When an account genuinely moves from the Lyon branch to the Paris branch and you overwrite, every historical payment now joins to Paris, and the Lyon branch’s last-year volume quietly migrates to Paris in this year’s run of the same report. Nobody is alerted; the numbers are simply, retroactively, different. That is the failure Type 2 exists to prevent.

How does a Type 2 dimension actually work?

A Type 2 dimension keeps one row per version of an entity’s attributes, each bounded by validity dates. The moving parts: a surrogate key unique to the version, the natural key identifying the business entity, the attributes, valid_from and valid_to bounding when the version was true, and conventionally an is_current flag.

Here is account ACC-42 moving branch on 2026-03-15:

account_sk  account_id  branch   segment  valid_from   valid_to     is_current
1041        ACC-42      Lyon     Retail   2019-06-01   2026-03-15   false
2318        ACC-42      Paris    Retail   2026-03-15   9999-12-31   true

On the change, the load process closes the old row (sets valid_to) and inserts the new one with a fresh surrogate key. Nothing is deleted, nothing overwritten; the dimension is an append-mostly history of what was true when. Fact rows stamp the surrogate key current at the event: a February payment carries account_sk = 1041, an April payment carries 2318. That stamp is the whole trick, and it is why Type 2 requires surrogate keys at all: ACC-42 now matches two rows, so the business key alone can no longer tell a fact which version of the truth it belongs to.

You rarely hand-build this machinery anymore, dbt snapshots generate exactly this shape (their dbt_valid_from and dbt_valid_to columns are the same pattern), but you query what it produces constantly, so you need to read it fluently. Whether a given attribute is Type 1 or Type 2 in your warehouse should be recorded in the data dictionary; when it is not, the dimension’s row count per business key tells you.

How do you query a Type 2 dimension without lying?

Match the join to the question. Current-state questions (“which segment is each account in now”) filter the dimension to is_current = true and join on the business key. Historical questions (“Q1 volume by branch, as it was”) must join each fact to the version valid at the fact’s date:

select f.payment_date, d.branch, sum(f.amount) as volume
from fct_payments f
join dim_account d
  on d.account_id = f.account_id
 and f.payment_date >= d.valid_from
 and f.payment_date <  d.valid_to
group by f.payment_date, d.branch;

If the fact table stores the surrogate key, the plain f.account_sk = d.account_sk join is already point-in-time correct, which is the payoff for stamping it at load time.

The classic mistake is joining historical facts to is_current rows because it is the easy join: the query runs, the totals reconcile, and every payment ACC-42 ever made is now attributed to Paris. The report is confidently wrong in a way no error message will ever flag, which is why this join choice belongs in code review for every report against a Type 2 dimension. The second trap is double counting: joining on the business key without the date bounds matches every version, and volume multiplies by version count. If a reconciliation shows the fact-to-dimension join returning more rows than the fact table has, a versioned dimension joined without its dates is the first suspect, the same row-count check that catches most join bugs.

When is Type 2 worth it, and when is Type 1 honest enough?

Type 2 is worth its complexity when someone will ever ask a question about the past that the changed attribute would distort: regional and branch reporting across reorganizations, segment migration analysis, risk ratings at the time of the decision, anything audited or regulated. In banking the reorg case alone settles it, because branch hierarchies change and regulators expect last year’s report to be reproducible. Type 1 is honest enough for attributes where the current value is the only value anyone will ever want, and for genuine corrections, where rewriting the past is the point.

The cost of choosing wrong is asymmetric. Type 2 where you needed Type 1 costs some storage and join care. Type 1 where you needed Type 2 destroys history unrecoverably: once overwritten, “what branch was this account in last March” is unanswerable by any query, and rebuilding it means archaeology in source system logs, if they still exist. When in doubt on an attribute that describes organizational structure, classification, or risk, version it.

The takeaway

A slowly changing dimension is a dimension whose attributes change, and the SCD type decides the fate of the old value: Type 1 overwrites and forgets, Type 2 keeps every version as a row bounded by valid_from and valid_to under a surrogate key. Use Type 1 for corrections, Type 2 for real change that historical reports must survive. Query Type 2 with the join the question deserves, is_current for now, date-bounded joins for the past, and treat a fact-to-dimension join without date bounds as a bug until proven otherwise. The past should only change when it was wrong.