Slowly Changing Dimensions: SCD Types 0 Through 7, With dbt Examples

Types 0 through 7, side by side — what each SCD type stores, what it forgets, when to reach for each one, and how to implement Type 2 cleanly with dbt snapshots.

By Rakesh Madala — 10 years in the data field

A customer moves cities. A product gets recategorised. A salesperson changes territory. In the source system this is a routine UPDATE — one row changes and life goes on. In the warehouse it's a question with real money attached: what should happen to every fact row that referenced the old value?

That question — how a warehouse handles attribute change — is the whole of slowly changing dimensions. There are seven canonical answers, numbered 0 through 7, plus a couple of hybrids, and picking the right one per attribute is one of the higher-leverage modeling decisions a team makes. Choose Type 1 for something that should have been Type 2, and last quarter's revenue-by-region report suddenly reflects this month's territory map. Choose Type 2 for something that should have been Type 1, and your dimension quietly grows without limit.

This is a walk through every SCD type with a running example, tables you can read, dbt code for the ones that need it, and a decision framework for picking the right type per column — not per table.

Why SCDs Matter

A dashboard that answers "what was revenue by customer segment last quarter?" only produces the right number if the segment your query pulls is the one that was in effect at order time — not the one that's in effect today. That property is called time consistency, and it's what SCDs exist to preserve.

Break time consistency and every historical comparison silently drifts. Segment definitions get restated. Sales-by-region charts reorganize themselves whenever a rep changes territory. Cohort analyses stop being reproducible — the same query returns different numbers on Tuesday and Thursday, and nobody can tell why. None of this shows up as an error; it shows up as an argument in a review meeting six months later, and by then the source data has moved on.

SCDs are how you decide, per attribute, whether the warehouse remembers the past. "Per attribute" is the important part: a dimension row usually has some columns that should track history (segment, city, price band) and others that should not (email address correction, cleaned-up name capitalisation). A well-modeled dimension is a mix of SCD types, chosen column by column.

The Test Question — One Customer, One Move

The example that runs through the whole post: customer C-8842, whose city changes from Mumbai to Bengaluru on 2026-03-10, and again to Hyderabad on 2026-09-20. Every SCD type is a different answer to "what does dim_customer look like today, and what happens when you ask about an order placed in July 2025?"

Nothing in this scenario is exotic. Every warehouse has millions of tiny updates like this a year. The SCD choice decides which of them leave a scar on the data.

{{ config(materialized='incremental', unique_key='customer_sk') }}

select
  {{ dbt_utils.generate_surrogate_key(['source_system','customer_id']) }} as customer_sk,
  customer_id,
  customer_name,
  email,
  city,
  segment,
  current_timestamp() as updated_at
from {{ ref('stg_customers') }}
{% if is_incremental() %}
where _loaded_at > (select max(updated_at) from {{ this }})
{% endif %}

-- Every order picks up the customer version that was in effect at order_date
select
  o.order_id,
  o.order_date,
  o.net_amount_inr,
  c.city              as city_at_order_time,
  c.segment           as segment_at_order_time
from {{ ref('fct_orders') }} o
left join {{ ref('dim_customer') }} c
  on  c.customer_id = o.customer_id
  and o.order_date >= c.valid_from
  and o.order_date <  c.valid_to

Choosing an SCD Type — Per Column, Not Per Table

Textbook diagrams often show a dimension "of Type 2", as though the whole table has one type. Real dimensions almost never do. A useful mental model is: for each column, ask two questions.

1. Does anyone group by, filter on, or report against this column over time? If no → Type 1 is fine. If yes → probably Type 2.
2. Would restating history be wrong or misleading? If yes → Type 2. If it doesn't matter (typo fix, phone normalisation) → Type 1.

A few practical rules that fall out:

• Identifiers → Type 0. The business key never changes; the surrogate key on a Type 2 row is version-specific but the business key is stable.
• Descriptive attributes that shape reporting → Type 2. Segment, category, tier, territory, region.
• Cleaned-up display fields → Type 1. Normalized name, corrected email, canonicalised phone.
• Very-wide, mostly-current dimensions → Type 4 or 6. Product masters with 60 columns where only a handful change historically.
• Anchoring attributes → Type 0. Original acquisition channel, first-order date, sign-up geography, birth city.
• Attributes that change more than once a week → question whether they belong on the dimension. High-velocity change is usually a measurement, not a description.

Implementing SCD Type 2 with dbt Snapshots

dbt bakes Type 2 into a first-class feature called snapshots. A snapshot is a small YAML/SQL config that, on each run, compares the source data against the snapshot table and inserts new rows for any records whose tracked columns changed. It handles the versioning, the dbt_valid_from/dbt_valid_to/dbt_scd_id plumbing, and closing off the previous row automatically.

{% snapshot customer_snapshot %}
{{
  config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='check',
    check_cols=['city', 'segment', 'tier', 'sales_territory'],
    invalidate_hard_deletes=True
  )
}}

select
  customer_id,
  customer_name,
  email,
  city,
  segment,
  tier,
  sales_territory,
  is_active
from {{ source('crm', 'customers') }}
{% endsnapshot %}

Notes on the config that matter:

• strategy='check' compares the columns in check_cols row-by-row. Use 'timestamp' instead if your source guarantees a reliable updated_at — it's cheaper on wide tables but only correct if the timestamp is actually updated whenever any of the tracked columns change.
• check_cols is the explicit list of Type-2 columns. Everything not listed is effectively Type 1 — the snapshot doesn't watch it, so a change there doesn't create a new row and the value gets overwritten on the current row.
• invalidate_hard_deletes=True closes the current row when a customer disappears from the source. Without this, deleted rows quietly stay marked as current forever.
• Snapshots write to a dedicated snapshots schema. Treat that schema as append-only ground truth — restoring from a bad snapshot backfill is much harder than restoring a mart.

Downstream, a Type 2 dimension is a thin model on top of the snapshot that renames the dbt columns to the ones your team standardises on:

{{ config(materialized='table') }}

select
  {{ dbt_utils.generate_surrogate_key(['customer_id','dbt_valid_from']) }} as customer_sk,
  customer_id                     as customer_bk,
  customer_name,
  email,
  city,
  segment,
  tier,
  sales_territory,
  dbt_valid_from                  as valid_from,
  coalesce(dbt_valid_to, '9999-12-31') as valid_to,
  (dbt_valid_to is null)         as is_current
from {{ ref('customer_snapshot') }}

union all

-- The unknown member row — catches orphan fact FKs at build time
select
  '-1'            as customer_sk,
  '-1'            as customer_bk,
  'Unknown'       as customer_name,
  null::string,
  'Unknown'       as city,
  'Unknown'       as segment,
  'Unknown'       as tier,
  'Unknown'       as sales_territory,
  '1900-01-01'::date,
  '9999-12-31'::date,
  true

Two properties fall out of this shape. First, customer_sk is version-specific (it's derived from customer_id plus valid_from), so it's stable across snapshots and joins reliably from facts. Second, is_current is derived from dbt_valid_to being null, not stored separately — that avoids the classic bug where is_current = true on two rows because a load half-succeeded.

Snapshot early, even before anyone has asked for history. You can't reconstruct changes that happened before you started capturing them, and "we need to know what the customer looked like six months ago" is one of those requests that always shows up eventually.

Common Mistakes

1. Type 2-ing the whole table. If every column is versioned, the dimension explodes at the rate of the noisiest column. Pick which columns need history and Type 1 the rest via the check_cols list.
2. Closed-closed validity windows. Using valid_from <= t <= valid_to either double-counts on boundary dates (both endpoints inclusive) or drops them (both exclusive). Half-open valid_from <= t < valid_to is the only shape that partitions time cleanly.
3. Missing the unknown member. Without a customer_sk = '-1' row, a fact FK that doesn't match any dimension row produces a silent inner-join drop. Report totals get quietly short, and nobody notices until an auditor does.
4. Both is_current flags = true. Derive it from valid_to is null or a sentinel '9999-12-31'. Storing it as a real column that a load has to update makes it possible for two rows to be current after a failed run.
5. Joining facts to where is_current = true for historical reports. That's an accidental Type 1. If the report is about the past, join on the interval or on the version SK the fact carries.
6. Snapshotting from a mart instead of from a source. Snapshots should watch the closest-to-raw representation of the entity. Snapshot a mart and you're capturing history of your own transformations, which changes every time you deploy.

Wrapping Up

SCDs sound like a taxonomy question and are really a decision framework. Every attribute on every dimension has an answer: this one keeps history, that one doesn't, this other one keeps just the previous value. Get those answers right and time-consistent reporting is free; get them wrong and you spend the next year explaining why the same query returned different numbers this week and last.

Type 2 is the workhorse and the default for anything you'll group by over time. Type 1 is the right answer for anything whose old value was wrong or uninteresting. Type 0 is for the small set of attributes anchored to a moment. Type 3 is a two-column snapshot for reorganisations. Type 4 splits current from history when the dimension is wide. Types 5, 6, and 7 are hybrids the rest of the world uses when a single approach isn't enough.

Pick per column, not per table. Start snapshotting early. Test that is_current is unique per business key and that intervals don't overlap. And when someone asks why the segment on this dashboard doesn't match what's in the CRM today — you'll have the right answer, because you designed for it on purpose.

— This article is part of an ongoing data-engineering series on techedge.in. Wrestling with an SCD decision? Drop a comment, I read every one.