What is Slowly Changing Dimensions (SCD)?

Slowly Changing Dimensions (SCDs) are the pattern that separates "a warehouse that reports the truth at this instant" from "a warehouse that reports the truth as of any point in history." Every dimension attribute — customer address, employee department, product category, sales territory — drifts over time. Your warehouse has to decide: when the CRM updates a customer's city from Hyderabad to Bangalore, does every historical order for that customer now show Bangalore (Type 1, overwrite), or do orders before the move show Hyderabad and after show Bangalore (Type 2, versioned history)? That single decision ripples through every report, every ML feature, and every compliance audit you'll ever run.

When SCD decisions actually bite

The SCD choice feels academic until you hit one of these: (1) regional revenue reports that retroactively shift after a sales territory reorg, (2) ML training sets that leak future information into historical features, (3) auditors asking "what did this customer's KYC status look like on the day of the transaction," (4) churn analysis where "customer segment" keeps changing so cohort definitions drift. Type 1 is fine for corrections (fixing a misspelled name) but lethal for any attribute that influences metrics or ML. Type 2 is the right default for most business-meaningful dimensions in a Kimball-style warehouse.

Field story

A B2B SaaS client had 3 years of Type 1 dimensions in Redshift — everything overwritten, no history. When their sales org restructured regions, the quarterly board deck suddenly showed different regional revenue numbers for prior quarters than had been presented before. The CFO was livid. We spent 4 months retrofitting SCD Type 2 onto 12 core dimensions (customer, account, salesperson, territory, product). The hard part wasn't the dbt snapshot logic — it was backfilling history we didn't have (had to reconstruct from Salesforce audit logs) and updating 200+ downstream models to join on valid_from/valid_to with point-in-time correctness. Lesson: pick your SCD strategy before the first dashboard goes to an exec; retrofitting is 10x the work of doing it right up front.

Slowly Changing Dimensions (SCD) are a set of techniques for tracking historical changes in dimension tables within a data warehouse. When attributes of business entities change over time — a customer moves to a new city, an employee gets a new title, a product changes its price tier — you need a strategy for how the warehouse reflects those changes.

Why SCDs Matter

Without SCD strategies, your warehouse loses historical context:

• Analytics break: 'Revenue by region' changes retroactively if you just overwrite
• Audit requirements: Regulators may require you to show what data looked like at a point in time
• ML models: Training data needs point-in-time accuracy to avoid data leakage

SCD Types Explained

Type 0 — Retain Original

The attribute value never changes in the warehouse, even if it changes in the source.

• Use when: The value should remain as it was at the time of initial load (e.g., original sign-up date)
• Pros: Simple, no logic needed
• Cons: Warehouse diverges from source

Type 1 — Overwrite

Replace the old value with the new value. No history is kept.

UPDATE dim_customer
SET city = 'Bangalore'
WHERE customer_id = 101;

• Use when: Historical values are not needed (e.g., correcting a typo)
• Pros: Simple, no extra storage
• Cons: Loses all history

Type 2 — Add New Row (Most Common)

Insert a new row for each change, with validity dates to track history.

-- Before
| customer_id | name  | city       | valid_from | valid_to   | is_current |
|-------------|-------|------------|------------|------------|------------|
| 101         | Rahul | Hyderabad  | 2024-01-01 | 9999-12-31 | true       |

-- After city change
| customer_id | name  | city       | valid_from | valid_to   | is_current |
|-------------|-------|------------|------------|------------|------------|
| 101         | Rahul | Hyderabad  | 2024-01-01 | 2026-03-15 | false      |
| 101         | Rahul | Bangalore  | 2026-03-15 | 9999-12-31 | true       |

• Use when: Full history tracking is required
• Pros: Complete audit trail, point-in-time queries
• Cons: Table grows over time, joins need valid_from/valid_to logic

Type 3 — Add New Column

Keep the previous value in a separate column.

| customer_id | name  | current_city | previous_city |
|-------------|-------|-------------|---------------|
| 101         | Rahul | Bangalore   | Hyderabad     |

• Use when: You only need one level of history
• Pros: Simple to query
• Cons: Only tracks one prior value

Type 4 — History Table

Keep the current record in the main dimension and store all historical changes in a separate history table.

• Use when: You want fast lookups on current data while keeping full history accessible

Type 6 — Hybrid (1 + 2 + 3)

Combines Type 1, 2, and 3: add new rows (Type 2), keep a current-value column updated across all rows (Type 1), and store previous values (Type 3).

• Use when: You need both historical and current values easily accessible

Implementing SCD Type 2 in dbt

dbt has a built-in snapshot feature for SCD Type 2:

# models/snapshots/customer_snapshot.sql
{% snapshot customer_snapshot %}
  {{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at'
  ) }}
  SELECT * FROM {{ source('raw', 'customers') }}
{% endsnapshot %}

Choosing the Right SCD Type

SCD implementation traps that cause silent data corruption:

• Silently overwriting Type 2 history. An ELT job MERGE that doesn't check for changes in SCD Type 2 attributes will update the current row instead of closing it and inserting a new one. Always use explicit change-detection logic (dbt snapshot, or custom MERGE with WHEN MATCHED AND clauses).
• Point-in-time join bugs. Fact table joined to dim on surrogate key is fine; fact joined to dim on natural key + date range needs BETWEEN valid_from AND valid_to — getting the boundary conditions wrong (using <= vs <) duplicates or drops rows. Always test with a customer who changed on a boundary date.
• Open-ended valid_to = '9999-12-31' vs NULL inconsistency. Pick one convention and enforce it. Mixing both in the same table breaks WHERE valid_to > CURRENT_DATE and WHERE valid_to IS NULL filters simultaneously.
• Multiple current rows per natural key. A bug in the snapshot logic can leave two rows with is_current = true for the same customer. Add a uniqueness DMF or test that flags this immediately; if it accumulates, every aggregation doubles.
• Fast-changing dimensions disguised as slow. Session IDs, sensor readings, or pricing that changes hourly shouldn't be Type 2 dimensions — they'll blow up the table size. These need a separate mini-dimension or a fact-table-like design (Type 4).
• Type 2 without business-date accuracy. valid_from based on load timestamp, not business event timestamp, makes point-in-time joins wrong when the source system delivers changes late. Use the source's updated_at when available.
• Surrogate key reuse after Type 2 update. If you rebuild the dimension and reassign surrogate keys, every fact table that joined to old keys is now pointing at wrong rows. Surrogate keys must be stable across rebuilds.
• Forgetting that Type 1 updates break audit trails. Even a seemingly innocent Type 1 field (email address) can matter for compliance. If the regulator asks "what email was on file when this transaction happened," Type 1 can't answer.
• dbt snapshots on wrong strategy. strategy='timestamp' requires a reliable updated_at column; strategy='check' compares column values but gets slow on wide tables. Pick based on source reliability, not convenience.
• Type 2 row explosion from noisy source columns. Including last_login_at as a Type 2 tracked column creates a new row every login. Only track columns that have business meaning for historical analysis.