9  Slowly Changing Dimensions (SCD)

The previous chapter implicitly assumed that dimensions are static: a student always belongs to the same major, and a product always belongs to the same category. In practice, this is not the case: a customer moves to a new city, a product changes categories, or an employee changes job titles.

Slowly Changing Dimensions (SCD) define how to handle changes in descriptive attributes without breaking the historical relationship between a fact event and its context at the time it occurred. The natural key identifies the entity in the source system; the surrogate key identifies a specific row version within the data warehouse.

Entity, Versions, and Historical Facts

Source Systemcustomer_id = C-42Stable natural key

→

Dimensioncustomer_key = 101, 208...One surrogate key per version

→

Fact Tablecustomer_key = 101Preserves context at event time

The surrogate key decouples history from the identifier used by the source system.

9.1 SCD Types

9.1.1 Type 1: Overwrite

SCD Type 1 updates the existing row in place. The new attribute value replaces the old value, and all historical queries—even those analyzing past transactions—reflect the updated or corrected attribute.

SCD Type 1: New Value Overwrites Old Value

BeforeC-42 | Monterrey

→ UPDATE →

AfterC-42 | GuadalajaraMonterrey is overwritten

→

Historical QueriesDisplay Guadalajara for all dates

Advantages

• Simplest pattern to implement, query, and maintain.
• Does not increase dimension row count.
• Instantly corrects data entry errors across the entire history.
• Requires no effective date ranges or temporal join logic.

Disadvantages

• Erases historical context; cannot reconstruct past states.
• Can re-attribute historical facts to states that did not exist when the event occurred.
• Unsuitable for auditing, traceability, or trend tracking.

When to Use: When changes represent spelling corrections, data standardization, or attributes whose historical changes carry no business value. Avoid Type 1 when attribute changes alter the interpretation of historical fact records (e.g., job titles, sales territories, customer segments).

merge into dim_customer as target
using stg_customer as source
  on target.customer_id = source.customer_id
when matched then update set
  name = source.name,
  city = source.city
when not matched then insert (customer_id, name, city)
values (source.customer_id, source.name, source.city);

9.1.2 Type 2: Add a New Version Row

SCD Type 2 closes out the current record version and inserts a new row. The natural key stays the same, but each version receives a unique surrogate key and effective date interval.

SCD Type 2: Every Change Creates a Version Row

Jan 1, 2024

customer_key: 101
customer_id: C-42
city: Monterrey
valid_to: 2024-06-30
is_current: false

Jul 1, 2024

Change detected
Key 101 closed
Key 208 inserted

Active Version

customer_key: 208
customer_id: C-42
city: Guadalajara
valid_to: 9999-12-31
is_current: true

May Sale → key 101 → Monterrey  |  August Sale → key 208 → Guadalajara

Advantages

• Preserves a full audit history of selected attribute changes over time.
• Preserves accurate point-in-time context without requiring complex date-range join logic, provided the ETL process assigns the active surrogate key during load time.
• Enables point-in-time reconstruction and transition tracking.
• Explicitly models validity via valid_from, valid_to, and is_current.

Disadvantages

• Expands dimension storage footprint as entities generate multiple version rows.
• ETL complexity increases: pipelines must detect changes, expire prior versions, and insert new versions atomically.
• Demands clear policies for late-arriving data, retroactive corrections, and boundary dates.

When to Use: When answering queries like “Which sales region was assigned to the product when the transaction occurred?” or “Where was the customer living at the time of purchase?”.

update dim_customer as d
set valid_to = current_date - interval '1 day',
    is_current = false
from stg_customer as s
where d.customer_id = s.customer_id
  and d.is_current
  and (d.name is distinct from s.name
       or d.city is distinct from s.city);

insert into dim_customer
  (customer_id, name, city, valid_from, valid_to, is_current)
select s.customer_id,
       s.name,
       s.city,
       current_date,
       date '9999-12-31',
       true
from stg_customer as s
left join dim_customer as d
  on d.customer_id = s.customer_id
 and d.is_current
where d.customer_id is null
   or d.name is distinct from s.name
   or d.city is distinct from s.city;

In production environments, expiration and insertion steps should execute within a single transaction. Surrogate keys can be generated via identity columns, database sequences, stable version hashes, or cloud data platform primitives.

9.1.3 Type 3: Add Previous Value Column

SCD Type 3 stores the current value and the immediately preceding value within separate columns on the same row. It does not track an unlimited history: when a new change occurs, the previous value is overwritten.

SCD Type 3: Single Row Tracks Current and Previous Values

Before Changecurrent = Monterreyprevious = NULL

→

After Changecurrent = Guadalajaraprevious = MonterreyA third change overwrites Monterrey

Advantages

• Simplifies year-over-year or current-vs-previous comparisons.
• Maintains a single row per entity, avoiding row count expansion.
• Simple query patterns without date-range logic.

Disadvantages

• Captures only a fixed, limited history (typically one previous state).
• Requires modifying table DDL to add specific historical columns.
• Fails to scale when multiple attributes change independently.

When to Use: When business requirements call strictly for comparing current values against prior values (e.g., current sales territory vs. previous sales territory). It does not replace Type 2 for full audit compliance.

update dim_customer as d
set previous_city = d.current_city,
    current_city = s.city,
    change_date = current_date
from stg_customer as s
where d.customer_id = s.customer_id
  and d.current_city is distinct from s.city;

9.2 Implementation with dbt

dbt materializes dimensional models from staging models. Incremental models handle Type 1 updates; for Type 2, dbt snapshots automatically track and version detected changes.

SCD Type 2 Flow with dbt

Stagingstg_customersNormalized data

→

Snapshotunique_keycheck_colsDetects and versions changes

→

Final Dimensioncustomer_keyvalid_from / toInterface for fact tables and queries

Data tests run after materializing the dimension, verifying keys and temporal integrity.

-- snapshots/customers.sql
{% snapshot customers_snapshot %}
{{
  config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='check',
    check_cols=['name', 'city']
  )
}}

select customer_id, name, city
from {{ ref('stg_customers') }}
{% endsnapshot %}

The snapshot appends metadata columns: dbt_valid_from, dbt_valid_to, and dbt_scd_id. The downstream dimension model exposes surrogate keys and validity windows:

-- models/marts/dim_customer.sql
select
  dbt_scd_id as customer_key,
  customer_id,
  name,
  city,
  dbt_valid_from as valid_from,
  coalesce(dbt_valid_to, timestamp '9999-12-31') as valid_to,
  dbt_valid_to is null as is_current
from {{ ref('customers_snapshot') }}

9.2.1 Advantages of dbt Snapshots

• Centralizes change detection and versioning logic.
• Eliminates manual procedural SQL.
• Generates temporal validity metadata consistently.
• Integrates natively with dbt lineage, documentation, and data testing frameworks.

9.2.2 Trade-offs and Considerations

• Correct behavior relies on configuring unique_key, strategy, and check_cols accurately.
• Including volatile columns (such as load timestamps) creates unwanted version rows on every run.
• Snapshots record states observed during execution runs; changes occurring and reverting between runs are lost.
• Retroactive updates and hard deletions require explicit policies.

9.3 Data Quality and Integrity Testing

Declarative testing verifies surrogate key uniqueness, non-null constraints, and natural key presence.

models:
  - name: dim_customer
    columns:
      - name: customer_key
        tests: [unique, not_null]
      - name: customer_id
        tests: [not_null]

To guarantee that exactly one row remains active per natural key, a custom singular test can be added:

-- tests/one_active_version_per_customer.sql
select customer_id
from {{ ref('dim_customer') }}
where is_current
group by customer_id
having count(*) > 1

Additionally, validate that effective date ranges do not overlap and that valid_from is strictly less than valid_to.

9.4 Comparison and Decision Criteria

Comparison of Core SCD Types

Type	History Preserved	Rows per Entity	Complexity	Primary Use Case
Type 1	None	One	Low	Corrections and attributes without historical value
Type 2	Complete	One per version	High	Auditability and precise historical context
Type 3	Limited	One	Medium	Comparing current state against previous state

SCD types do not need to be applied uniformly across an entire dimension table. A single dimension table can mix strategies: applying Type 1 to corrective fields, Type 2 to historically significant fields, and Type 3 to specific comparative fields. The key is defining policies attribute by attribute, documenting trade-offs, and enforcing integrity through automated tests.