Slowly Changing Dimensions Explained: SCD Types 0, 1, 2, 3 and 6 With SQL

Dimension attributes change. Customers move, products get recategorised, sales reps change territory, patients switch insurers. A slowly changing dimension (SCD) is a dimension with a defined rule for what happens to history when an attribute changes. The "slowly" means changes are occasional relative to the facts, not that the technique is slow.

The rule matters because it decides what your reports say about the past. If a customer moved from London to Manchester in June, should their January orders count as London or Manchester sales? Both answers are legitimate. The SCD type is how you choose one deliberately instead of by accident.

This guide covers the types you will actually use (0, 1, 2, 3 and 6), with DDL, a complete Type 2 MERGE, and the mistakes that most often corrupt history.

The running example

A SaaS company has a dim_customer with these attributes:

• customer_id (natural key from the CRM)
• company_name
• industry
• country
• segment (SMB, Mid-Market, Enterprise)
• signup_date

Different attributes need different rules. signup_date should never change. A typo fix in company_name should just overwrite. A move from SMB to Enterprise should preserve history, because sales and finance want to report revenue by the segment the customer was in at the time.

That last point is important: SCD type is chosen per attribute, not per table. Most real dimensions mix types.

Type 0: retain original

The attribute never changes after it is first loaded. Corrections in the source are ignored.

Use it for values that are true by definition at creation: original signup date, original acquisition channel, date of birth, first order date.

Implementation is simply excluding the column from update logic.

Type 1: overwrite

The new value replaces the old one. No history is kept. All past facts now report under the new value.

update dim_customerset company_name = s.company_namefrom stg_customer swhere dim_customer.customer_id = s.customer_id  and dim_customer.company_name <> s.company_name;

Use it for corrections (typos, formatting) and for attributes where nobody needs the old value, such as a contact phone number.

The risk: Type 1 silently restates history. If you Type 1 the segment column, last year's Enterprise revenue changes every time a customer is reclassified. Reports that were signed off no longer reproduce.

Type 2: add a new row

When a tracked attribute changes, the current row is closed and a new row is inserted with a new surrogate key. Facts recorded before the change keep pointing at the old row; facts after point at the new one. History is preserved exactly.

create table dim_customer (    customer_key      bigint        not null,  -- surrogate key, one per version    customer_id       varchar(40)   not null,  -- natural key, same across versions    company_name      varchar(200)  not null,    industry          varchar(100),    country           char(2),    segment           varchar(20)   not null,    signup_date       date          not null,    row_hash          varchar(64)   not null,  -- hash of Type 2 tracked columns    valid_from        timestamp     not null,    valid_to          timestamp     not null,  -- '9999-12-31' for the current row    is_current        boolean       not null,    constraint pk_dim_customer primary key (customer_key));

Sample rows after Acme moves from SMB to Mid-Market on 2026-06-01:

A few conventions worth standardising:

• Half-open intervals. A row is valid where valid_from <= t and t < valid_to. Using the same timestamp for one row's valid_to and the next row's valid_from means every point in time matches exactly one row.
• A far-future end date rather than null for the current row, so range joins do not need coalesce.
• is_current as a convenience for "as of now" queries.
• A hash of the tracked columns so change detection is one comparison instead of many, and null-safe.

Type 2 with MERGE

Most warehouses support MERGE, but a single MERGE cannot both update the old row and insert the new version for the same key in one pass. The standard trick is to feed the MERGE a source set where changed records appear twice: once to match and expire the current row, and once with a null merge key so it falls through to the insert branch.

This version runs on Snowflake and Databricks with minor syntax changes; BigQuery and Postgres 15+ support the same structure.

merge into dim_customer as tgtusing (    -- 1. every incoming record, keyed on natural key: matches the current row    select        s.customer_id as merge_key,        s.*    from stg_customer_hashed s​    union all​    -- 2. changed records again, with null merge key: forces an insert of the new version    select        null as merge_key,        s.*    from stg_customer_hashed s    join dim_customer d      on d.customer_id = s.customer_id     and d.is_current    where d.row_hash <> s.row_hash) as srcon tgt.customer_id = src.merge_keyand tgt.is_current​-- expire the current row when tracked attributes changedwhen matched and tgt.row_hash <> src.row_hash then update set    valid_to   = src.extracted_at,    is_current = false​-- brand new customers, and the new version of changed customerswhen not matched then insert (    customer_key, customer_id, company_name, industry, country, segment,    signup_date, row_hash, valid_from, valid_to, is_current) values (    src.customer_key, src.customer_id, src.company_name, src.industry, src.country,    src.segment, src.signup_date, src.row_hash, src.extracted_at,    timestamp '9999-12-31 00:00:00', true);

Where stg_customer_hashed computes the hash and a new surrogate key:

create or replace view stg_customer_hashed asselect    nextval('seq_customer_key')  as customer_key,  -- or a sequence/identity per platform    customer_id,    company_name,    industry,    country,    segment,    signup_date,    extracted_at,    md5(concat_ws('||',        coalesce(industry, ''),        coalesce(country, ''),        coalesce(segment, '')    )) as row_hashfrom stg_customer;

Some notes on this pattern:

• Only the Type 2 columns go into row_hash. Type 1 columns like company_name are handled by a separate update that touches all versions (or just the current one, depending on your rule).
• The staging set must have one row per customer_id. If the source can deliver several changes for one customer in a batch, deduplicate to the latest first, or process changes in order.
• Identity columns or sequences differ across platforms. On BigQuery, teams often use a hash of customer_id plus valid_from as the surrogate key instead.
• In dbt, snapshots implement this pattern for you with strategy: check and a list of check_cols.

Joining facts to a Type 2 dimension

When loading facts, look up the version valid at the event time:

select    o.order_id,    d.customer_key,    o.amountfrom stg_orders ojoin dim_customer d  on d.customer_id = o.customer_id and o.ordered_at >= d.valid_from and o.ordered_at <  d.valid_to;

The fact stores customer_key, so reports join on the surrogate key with no date logic, and each fact automatically gets the attributes as they were.

Type 3: add a previous-value column

Instead of new rows, add a column holding the prior value.

alter table dim_sales_rep add column previous_territory varchar(50);

Type 3 keeps exactly one level of history and lets users compare "current territory" with "previous territory" side by side. It is useful for one-off reorganisations where the business wants to report both the old and new structure for a transition period. It does not scale to repeated changes.

Type 6: hybrid of 1, 2 and 3

Type 6 (1 + 2 + 3) adds Type 2 rows for history and a current-value column that is overwritten (Type 1) across every version of the entity.

create table dim_customer (    customer_key      bigint not null,    customer_id       varchar(40) not null,    segment           varchar(20) not null,   -- historical: value at the time    current_segment   varchar(20) not null,   -- Type 1: overwritten on all versions    valid_from        timestamp not null,    valid_to          timestamp not null,    is_current        boolean   not null);

After Acme moves to Mid-Market:

Now one fact join supports both questions: revenue by segment at the time (segment) and all historical revenue restated by today's segment (current_segment). Finance often wants exactly this pair.

Choosing a type

Types 4 (a separate history table) and 7 (dual foreign keys on the fact) exist and are occasionally useful, but the five above cover most designs.

Common mistakes

Tracking every column as Type 2. Every change creates a row, so a dimension where the last_login_at column is Type 2 grows with every login. Track only attributes that people actually slice historical facts by. Fast-changing values usually belong in a fact or a mini-dimension.

Closed intervals. Using valid_to = next_valid_from - 1 day and between creates gaps for intraday changes and overlaps if timestamps collide. Use half-open intervals.

Null-unsafe change detection. where old.industry <> new.industry returns null, not true, when either side is null, so changes to or from null are missed. Hash with coalesce, or use is distinct from.

Joining facts on natural key. Joining a fact to dim_customer on customer_id instead of customer_key fans out across every version and multiplies totals. Facts should carry the surrogate key. This is one of the main reasons surrogate keys exist; see surrogate keys vs natural keys.

Late-arriving dimension changes. If a segment change is backdated, facts already loaded point to the wrong version. Decide upfront whether you will re-point historical facts or accept the version known at load time, and document the choice.

Practise SCDs in offdataai Learn

The Dimensional Modeling (Kimball) track in offdataai Learn has hands-on lessons on slowly changing dimensions: you choose a type per attribute for a realistic scenario, build the dimension in the Model Builder, and an instant check confirms your keys, validity columns and change rules. It is included in the Explorer plan at $1/month.

For more background, see the dimensional modeling overview, or open a template to see SCD columns in a complete model.