Slowly Changing Dimensions: SCD Type 1, 2 and 3 Explained

A slowly changing dimension (SCD) is a dimension table whose attributes change occasionally: a customer moves city, a product changes category, an employee changes team. The SCD type decides what the warehouse does with the old value. Type 1 overwrites it, Type 2 keeps every version as a separate row, and Type 3 keeps the previous value in an extra column. The choice determines whether last year's report still shows last year's truth.

The main SCD types

How SCD Type 2 works

Type 2 is the one interviews focus on, because it's the one that keeps history correctly. Each version of an entity gets its own row with its own surrogate key, plus columns describing when that version was valid:

• customer_id is the natural key from the source; it's the same across versions.
• customer_key is the surrogate key; facts store this, so an order placed in 2025 points at the Pune row forever.
• valid_to of the open row is a far-future date (or null), and is_current makes "current state" queries cheap.
• Use half-open intervals (valid_from <= t < valid_to) so a timestamp matches exactly one version.

The fact table's foreign key is what makes Type 2 work. When a fact loads, it looks up the dimension version valid at the event time, not the current one. Getting that lookup wrong silently rewrites history.

Loading a Type 2 dimension

Each load compares incoming source rows with the current dimension rows by natural key. Detect a change by comparing tracked columns, often with a hash of them. Then:

1. New natural key: insert a row with valid_from = effective date and is_current = true.
2. Tracked attribute changed: set the current row's valid_to to the effective date and is_current = false, then insert the new version.
3. Only Type 1 attributes changed (for example a corrected email): update in place on the current row (or all rows, by policy).
4. No change: do nothing.

-- Step 1: close current rows whose tracked attributes changed
merge into dim_customer d
using staged_customers s
  on d.customer_id = s.customer_id and d.is_current
when matched and d.attr_hash <> s.attr_hash then
  update set valid_to = s.effective_date, is_current = false;

-- Step 2: insert a new current version for new or changed customers
insert into dim_customer (customer_key, customer_id, city, attr_hash, valid_from, valid_to, is_current)
select next_key(), s.customer_id, s.city, s.attr_hash, s.effective_date, date '9999-12-31', true
from staged_customers s
left join dim_customer d on d.customer_id = s.customer_id and d.is_current
where d.customer_id is null;

Run the two steps in one transaction, or make the job idempotent so a retry after a failure between them doesn't create duplicate current rows. Assert after every load that each natural key has exactly one current row and that version intervals don't overlap.

Beyond Type 2: types 4, 5, 6 and 7

Type 2 handles history for attributes that change occasionally. Two problems remain: attributes that change too often for a new row each time, and reports that need the current value alongside the historical one. The higher-numbered types are combinations that solve these.

• Type 4, mini-dimension: volatile attributes (age band, credit score band, loyalty tier) move into a small dimension of banded combinations. The fact references it directly, so each event records the profile at that moment, while the main customer dimension stays stable. See dimension table techniques.
• Type 5, mini-dimension plus Type 1 outrigger: the base dimension also holds a key to the customer's *current* mini-dimension row, overwritten on change. Reports can group by the profile at the time of the fact or by today's profile.
• Type 6, the hybrid (1 + 2 + 3): each Type 2 row also carries current-value columns that are overwritten across all versions on every change. A fact joined to its historical row can still show where the customer lives now.
• Type 7, dual keys: the fact stores the Type 2 surrogate key *and* the entity's durable key. Joining on the surrogate key gives history; joining on the durable key to a view of current rows gives today's values. It is the most flexible option and avoids rewriting old rows, at the cost of two join paths users must understand.

In interviews, Type 2 is expected; naming Type 4 for fast-changing attributes and Type 6 or 7 for "as it was versus as it is now" reporting shows depth.

Pitfalls

• Late-arriving data: a change dated last week arrives today. The load must insert the version in the right place in the timeline and possibly re-point facts, not just append.
• Late-arriving facts: a fact for a date before the current version must join to the version valid then, which is why lookups use event time.
• Change explosions: tracking a frequently changing attribute (like last_login) creates a new version every day. Keep such attributes out of Type 2 tracking or in a separate fact.
• Deletes: decide whether a source delete closes the current row, flags it, or is ignored, and handle it explicitly.
• Source snapshots vs CDC: with full daily snapshots you detect changes by comparison; with change data capture you get each change event, including several in one day, and must decide whether intra-day versions matter.