Slowly Changing Dimensions: Types 1 Through 6, and the Two You Will Actually Use

The first Type 2 dimension I built was technically perfect. Surrogate keys, effective dates, an is_current flag, a nightly MERGE that closed out old versions. It captured eighteen months of history on customer segment changes.

Not one query ever used it. Every downstream model joined fct_orders to dim_customer on customer_id with where is_current, which meant every historical order was attributed to the customer’s segment as of last night. We were paying to store history and then throwing it away at read time. The bug was invisible for over a year because the numbers looked reasonable.

That is the thing nobody warns you about with SCDs. The hard part is not writing the MERGE. The MERGE is thirty lines and dbt will generate it for you. The hard part is that Type 2 changes how every fact table joins to that dimension, and if you do not make that change everywhere, you have built a very expensive audit log.

Here is every type with SQL that runs, and my honest assessment of which ones have ever earned their keep.

The types, briefly and without reverence

Type 0 — never changes. The date dimension, the original signup source. Real and useful; nobody thinks of it as a design decision.

Type 1 — overwrite. History is lost. Correct for typos, data entry fixes, and anything where the old value was simply wrong.

Type 2 — new row per change, with effective dates and a current flag. The only way to answer “what was true when this happened.”

Type 3 — add a previous_value column. Tracks exactly one change, one level deep.

Type 4 — current values in the main dimension, all history in a separate mini-dimension or history table.

Type 5 — Type 4 plus an outrigger: the main dimension carries a foreign key to a current mini-dimension row.

Type 6 — Type 1 plus 2 plus 3 in one table: versioned rows that also carry the current value on every row.

In roughly a decade of warehouse work I have shipped Type 1 in nearly every dimension, Type 2 in about a third of them, Type 6 twice, Type 4 once for a genuinely enormous dimension, Type 3 never, and Type 5 never. Weight your learning accordingly.

Type 1: the default, and there is no shame in it

merge into analytics.gold.dim_product t
using analytics.silver.stg_product s
    on t.product_id = s.product_id
when matched and (
    -- Only write when something actually changed. This keeps the
    -- micro-partitions stable and the update count meaningful.
    t.product_name is distinct from s.product_name
 or t.category    is distinct from s.category
 or t.list_price  is distinct from s.list_price
) then update set
    t.product_name = s.product_name,
    t.category     = s.category,
    t.list_price   = s.list_price,
    t.updated_at   = current_timestamp()
when not matched then insert
    (product_sk, product_id, product_name, category, list_price, updated_at)
values
    (md5(s.product_id), s.product_id, s.product_name, s.category,
     s.list_price, current_timestamp());

Two details worth copying. is distinct from handles nulls correctly where <> does not, and the change predicate means an unchanged row is not rewritten — which matters because a rewrite reshuffles micro-partitions and can degrade pruning on a table you clustered carefully.

Type 1 is the right answer far more often than dimensional modeling culture admits. If the old value was wrong, preserving it is not history, it is noise.

Type 2: correct, and more expensive than the MERGE suggests

-- Close out versions whose tracked attributes changed.
merge into analytics.gold.dim_customer t
using (
    select
        s.customer_id,
        s.customer_name,
        s.segment,
        s.region,
        s.is_trial,
        -- Hash only the columns whose history you want. Adding a
        -- volatile column here doubles your row count for nothing.
        md5(concat_ws('|', s.segment, s.region, coalesce(s.account_manager, ''))) as attr_hash
    from analytics.silver.stg_customer s
) src
    on t.customer_id = src.customer_id
   and t.is_current
when matched and t.attr_hash <> src.attr_hash then update set
    t.valid_to    = current_timestamp(),
    t.is_current  = false;

-- Insert the new current version for changed and brand-new keys.
insert into analytics.gold.dim_customer
    (customer_sk, customer_id, customer_name, segment, region, is_trial,
     attr_hash, valid_from, valid_to, is_current)
select
    md5(concat_ws('|', src.customer_id, to_varchar(current_timestamp()))) as customer_sk,
    src.customer_id, src.customer_name, src.segment, src.region, src.is_trial,
    src.attr_hash,
    current_timestamp() as valid_from,
    '9999-12-31'::timestamp_ntz as valid_to,   -- not NULL. see below.
    true as is_current
from ( /* same src query */ ) src
left join analytics.gold.dim_customer t
    on t.customer_id = src.customer_id and t.is_current
where t.customer_id is null or t.attr_hash <> src.attr_hash;

Use '9999-12-31' rather than NULL for the open end date. It makes every point-in-time predicate a plain BETWEEN instead of a coalesce or an OR ... IS NULL, and those OR branches are what defeat pruning and make Type 2 joins slow.

Hash only the attributes whose history you want. I once included last_login_at in the change hash by accident and turned a 2-million-row dimension into 400 million rows in three weeks.

In dbt, snapshots do all of this and you should use them:

# snapshots/dim_customer.yml
snapshots:
  - name: dim_customer_snapshot
    relation: source('crm', 'customers')
    config:
      unique_key: customer_id
      strategy: check
      check_cols: ['segment', 'region', 'account_manager']
      hard_deletes: new_record        # emits a deletion version
      dbt_valid_to_current: "'9999-12-31'::timestamp_ntz"

hard_deletes: new_record is the setting people miss. Without it, a customer deleted at the source keeps an open Type 2 version forever and appears current in perpetuity.

The point-in-time join is the whole point

This is the part that makes Type 2 real, and the part my first implementation skipped.

flowchart TD
  fact["Fact row, event_ts"] --> pit{"Join on natural key AND event_ts in [valid_from, valid_to)"}
  pit --> ver["The dimension version in effect at event time"]
  fact -.-> wrong{"Join on is_current"}
  wrong -.-> today["Today's attributes on historical facts"]

Both joins run. Only one answers the question the Type 2 table was built for.

-- Correct: attributes as of the order date.
select
    d.segment,
    date_trunc('month', f.ordered_at) as mth,
    sum(f.net_revenue) as revenue
from analytics.gold.fct_order_lines f
join analytics.gold.dim_customer d
    on d.customer_id = f.customer_id
   and f.ordered_at >= d.valid_from
   and f.ordered_at <  d.valid_to        -- half-open interval, always
group by 1, 2;

Half-open intervals — >= on the low side, < on the high side — are non-negotiable. Use BETWEEN and a fact landing exactly on a version boundary matches two rows, silently doubling that row’s revenue. This is the same fan-out failure described in SQL joins and fan-out, and it is harder to spot because the duplicate depends on a timestamp collision.

The better pattern is to stop making analysts get this right. Resolve the surrogate key once, in the fact model:

-- models/gold/fct_order_lines.sql
select
    f.order_line_id,
    f.ordered_at,
    f.net_revenue,
    -- Frozen at load: the dimension version in effect at event time.
    d_hist.customer_sk as customer_sk,
    -- Also carry the durable key so current-state joins stay easy.
    f.customer_id
from {{ ref('stg_order_lines') }} f
left join {{ ref('dim_customer') }} d_hist
    on d_hist.customer_id = f.customer_id
   and f.ordered_at >= d_hist.valid_from
   and f.ordered_at <  d_hist.valid_to

Now join dim_customer using (customer_sk) gives historical truth and join dim_customer on customer_id where is_current gives current truth, and neither requires an analyst to remember interval logic. Offering both is the point: “revenue by the segment they were in at the time” and “revenue by the segment they are in now” are both legitimate questions and they have different answers.

Types 3, 4, 5, 6: what they are for and why you will skip most

Type 3 adds previous_segment alongside segment. It answers exactly one question — what changed in the most recent reorganization — and it answers it badly, because two changes in a quarter lose the first one. Every time I have seen Type 3 proposed, the real requirement was Type 2 with a LAG window function at read time.

Type 4 splits rapidly-changing attributes into a mini-dimension so the main dimension stays stable. I used it once, on a 60-million-row customer dimension where six behavioral scores updated daily and would have produced billions of Type 2 rows. Threshold: if Type 2 on the full dimension would grow faster than roughly a million rows a month, consider splitting. Otherwise the extra join is not worth it.

Type 5 is Type 4 plus a foreign key from the base dimension to the current mini-dimension row, so you can get current behavioral attributes without a point-in-time join. It is a real optimization for a real problem. I have never had that problem.

Type 6 is the one worth knowing. Versioned rows that also carry the current value of key attributes on every row:

-- Type 6: historical rows, each also stamped with today's value.
create or replace table analytics.gold.dim_customer_t6 as
select
    h.customer_sk,
    h.customer_id,
    h.segment       as segment_at_time,      -- Type 2 behavior
    c.segment       as segment_current,      -- Type 1 behavior, on every row
    h.region        as region_at_time,
    c.region        as region_current,
    h.valid_from,
    h.valid_to,
    h.is_current
from analytics.gold.dim_customer h
join analytics.gold.dim_customer c
    on c.customer_id = h.customer_id
   and c.is_current;

That one table answers “revenue by segment at the time” and “revenue by current segment” from the same join, which is what finance and sales ops respectively always want, in the same meeting. The cost is that the whole table gets rewritten whenever any current value changes. On a dimension under a few million rows, that rebuild is under a minute and the ambiguity it eliminates is worth it. Above that, materialize the current columns as a view instead.

Late-arriving dimensions will ruin a weekend

A fact arrives for a customer that does not exist in the dimension yet. Or a dimension change arrives dated three days ago, after facts have already been assigned to the wrong version.

For unknown keys, never drop the fact and never leave the key null. Insert an inferred member:

-- Inferred member: the fact is real, the attributes are not known yet.
insert into analytics.gold.dim_customer
    (customer_sk, customer_id, customer_name, segment, region,
     valid_from, valid_to, is_current, is_inferred)
select distinct
    md5(f.customer_id), f.customer_id, 'Unknown', 'Unknown', 'Unknown',
    '1900-01-01'::timestamp_ntz, '9999-12-31'::timestamp_ntz, true, true
from analytics.silver.stg_order_lines f
left join analytics.gold.dim_customer d
    on d.customer_id = f.customer_id
where d.customer_id is null;

valid_from of 1900 means any point-in-time join finds it. The is_inferred flag is what you monitor: a rising count means the upstream dimension feed is late or broken, and it is one of the most reliable early warnings I have on a warehouse.

For back-dated changes, you have to split an existing version — close the old one at the true effective date, insert the new one starting there, and re-resolve surrogate keys on any facts in that window. Getting this right requires the source to carry a real effective date rather than a load timestamp, which is a data contract question before it is a modeling one. If your source only gives you load time, be honest that your Type 2 records “when we learned it,” not “when it happened,” and document that in the model description.

Test it or it will rot

Three tests, non-negotiable on every Type 2 table.

-- 1. No overlapping versions per natural key.
select customer_id, valid_from, valid_to
from (
    select customer_id, valid_from, valid_to,
           lead(valid_from) over (partition by customer_id order by valid_from) as next_from
    from analytics.gold.dim_customer
)
where next_from is not null and next_from < valid_to;

-- 2. Exactly one current row per natural key.
select customer_id, count(*) as current_rows
from analytics.gold.dim_customer
where is_current
group by 1
having count(*) <> 1;

-- 3. No facts orphaned by the point-in-time join.
select count(*) as unmatched
from analytics.gold.fct_order_lines
where customer_sk is null;

All three go in dbt as singular tests and run on every build. Test 1 is the one that catches the subtle breakage — an interrupted merge, a back-dated correction applied twice, a source that emitted the same change with two timestamps. See dbt testing strategy for where these sit in a build.

Where teams get this wrong

Building Type 2 and joining on is_current. The most common and most expensive mistake. Resolve the surrogate key in the fact model so the correct join is the default one.

Using BETWEEN on the validity window. A fact timestamp landing exactly on a boundary matches two versions and doubles. Half-open intervals, always.

Nullable valid_to. Every downstream predicate grows an OR valid_to IS NULL branch, pruning degrades, and someone eventually forgets it. Use a far-future sentinel.

Hashing every column into the change detector. One volatile attribute turns your dimension into a event log. Hash only what you intend to version.

No handling for hard deletes. A row that vanishes from the source stays is_current = true forever and quietly inflates every current- state count.

Treating time travel as history. Warehouse time travel tells you what the table looked like last Tuesday for a limited retention window. It does not model business validity, does not survive a full refresh, and cannot be joined to. It is an operational safety net, not an SCD.

FAQ

Should I choose an SCD type per table or per column?

Per column. Almost every dimension I have built is Type 1 for corrections like name spellings and Type 2 for the two or three attributes that segment reporting — region, segment, tier. Declaring the whole table “Type 2” is what produces dimensions with a hundred million rows of noise.

Do dbt snapshots scale?

Up to a few million rows comfortably. Past that the full-table scan that snapshots perform to detect changes gets expensive, and I move to a hand-written MERGE driven by change data capture or by streams and tasks. The snapshot logic is the same; the change detection gets cheaper.

Can I get Type 2 for free from Delta or Iceberg history?

No. Table format versioning records commits, not business validity, and it is bounded by retention and vacuum settings. It is superb for recovering from a bad write — see the Delta transaction log — and useless as a dimension you can join to.

What surrogate key should I use?

A deterministic hash of the natural key plus the effective timestamp. It survives a full rebuild, it is reproducible across environments, and it does not require a sequence generator that behaves differently in dev. Avoid auto-incrementing keys in a warehouse; the parallelism makes them non-deterministic anyway.

How does this interact with one big table?

Badly, and predictably. Materializing a fact joined to a Type 2 dimension freezes point-in-time values into the flat table, so a retroactive dimension correction requires a rewrite. It is a real argument for keeping the star as the source of truth, which I go through in star schema vs one big table.

What if the business asks for history retroactively?

You cannot manufacture it, and you should say so plainly. What you can do is start Type 2 today and reconstruct partial history from source system audit tables, CDC logs, or old snapshots if any exist. Set the expectation that history begins at the date you started capturing it, and put that date in the model description.

What this means for your pipelines

SCD types are usually taught as a taxonomy to memorize, which makes them feel more complicated than they are. The actual decision is two questions asked per column: does anyone need to know what this value used to be, and does anyone need to attribute a past event to the value in effect at that time. If both answers are no, overwrite it. If both are yes, version it. That is Type 1 and Type 2, and it covers almost everything.

The engineering weight is not in the merge statement. It is in the read path and the edge cases: resolving surrogate keys at fact load time so analysts cannot get the join wrong, handling inferred members so late facts are never dropped, and testing for overlapping versions so the table does not quietly rot. Those three things are what separate a Type 2 dimension that is trusted from one that is merely present.

Start narrow. Pick the two or three attributes that actually drive reporting, version those, and leave everything else Type 1. You can always widen the change hash later; you cannot easily undo a dimension that grew forty times larger than it needed to be because someone versioned a timestamp.