Slowly Changing Dimensions (SCDs)

This lesson on Slowly Changing Dimensions (SCDs) is hands-on and example-driven. You will evaluate dimensional change patterns and select the optimal Slowly Changing Dimension (SCD) technique (Types 0 through 4 and hybrids) for business reporting requirements. You will design and implement schema structures with surrogate keys, tracking columns, and split mini-dimensions to preserve or overwrite historical states accurately.

What You'll Be Able To Do

• Select the appropriate SCD type (Type 0 through 4) based on historical reporting requirements.
• Implement SCD Type 1 overwrite logic for attributes where historical tracking is explicitly unnecessary.
• Design SCD Type 2 dimension schemas incorporating surrogate keys, effective date ranges, and active row flags.
• Structure SCD Type 3 tables with dedicated previous-value columns for single-level historical comparison.
• Architect SCD Type 4 models separating volatile attributes into mini-dimensions to preserve base table performance.

Detailed Concept Walkthrough

1. Non-Historical Strategies: Type 0 and Type 1

SCD Type 0 treats attributes as immutable permanent values, while SCD Type 1 treats attributes as current-state only by overwriting previous values in place.

• Mechanism: SCD Type 0 ignores incoming updates to preserve original recorded context, whereas SCD Type 1 updates the existing table row in place using an UPDATE statement.
• Under the Hood: SCD Type 1 destroys all previous attribute values upon update, effectively re-aligning all past fact table associations to the latest attribute value during aggregations.
• Best Practice: Reserve SCD Type 0 for immutable onboarding data (e.g., original registration date), and use SCD Type 1 only for data corrections or attributes where prior history has no analytical value.

-- SCD Type 1: In-place overwrite destroying historical value
UPDATE dim_customer
SET customer_address = '789 Elm St',
    last_updated_at = CURRENT_TIMESTAMP
WHERE customer_id = 101;

Key Takeaway: SCD Type 0 retains original truth forever, while SCD Type 1 retains only current truth by discarding past states.

2. SCD Type 2: Full History Tracking

SCD Type 2 preserves the complete history of attribute changes by inserting a new record for every modification while closing out the previous record.

• Mechanism: When an attribute changes, the ETL process closes the prior active row by setting its effective end date and flipping the active flag, then inserts a brand new row with a new surrogate key.
• Under the Hood: Fact tables link to the specific surrogate key valid at the transaction timestamp, enabling historical point-in-time reporting without modifying historical transaction keys.
• Best Practice: Always maintain strict date interval continuity (start date of new row equals end date of previous row) and avoid overlapping active date intervals for the same natural business key.

-- Step 1: Expire old active version
UPDATE dim_customer
SET effective_end_date = CURRENT_TIMESTAMP,
    is_current = FALSE
WHERE customer_id = 101 AND is_current = TRUE;

-- Step 2: Insert new version with new surrogate key
INSERT INTO dim_customer (customer_sk, customer_id, customer_address, effective_start_date, effective_end_date, is_current)
VALUES (DEFAULT, 101, '456 Oak Ave', CURRENT_TIMESTAMP, '9999-12-31'::TIMESTAMP, TRUE);

Key Takeaway: SCD Type 2 guarantees point-in-time analytical accuracy across time via surrogate keys and non-overlapping date spans.

3. SCD Type 3: Limited Historical Comparison

SCD Type 3 tracks a limited depth of history by adding dedicated columns directly to the existing row to hold immediate prior values.

• Mechanism: When an attribute value changes, the current value is copied into a previous-value column (e.g., previous_address) before the primary column is overwritten with the new value.
• Under the Hood: The table row count remains constant, avoiding surrogate key proliferation while enabling side-by-side comparative queries (current vs. previous) in a single row.
• Best Practice: Restrict SCD Type 3 to scenarios where business analysis only requires immediate past vs. present comparisons (e.g., sales territory realignments).

-- SCD Type 3: Shift current value to previous column before update
UPDATE dim_sales_rep
SET previous_territory = current_territory,
    current_territory = 'West Coast',
    territory_change_date = CURRENT_DATE
WHERE rep_id = 45;

Key Takeaway: SCD Type 3 provides simple, row-level comparison between current and immediate prior state without adding new rows.

4. SCD Type 4: Base and Mini-Dimensions

SCD Type 4 splits volatile, rapidly changing attributes into a separate mini-dimension table, keeping the primary base dimension compact and stable.

• Mechanism: Core demographic or profile attributes remain in the base dimension, while rapidly changing attributes (e.g., income band, credit score) are separated into a dedicated mini-dimension table.
• Under the Hood: Fact records capture two dimensional foreign keys: one pointing to the static base dimension row and another pointing to the active mini-dimension profile at transaction time.
• Best Practice: Deploy SCD Type 4 when high-frequency attribute updates would cause excessive row explosion and performance degradation in a standard Type 2 dimension.

-- Fact table references both base dimension and mini-dimension keys
SELECT 
    f.transaction_id,
    b.customer_name,
    m.credit_score_band,
    m.income_bracket,
    f.amount
FROM fact_sales f
JOIN dim_customer_base b ON f.customer_sk = b.customer_sk
JOIN dim_customer_profile_mini m ON f.profile_sk = m.profile_sk;

Key Takeaway: SCD Type 4 separates high-frequency volatility from stable entity identities to maintain query performance.

Topics Covered in Slowly Changing Dimensions (SCDs)

1. Introduction to SCDs (0:30 - 1:04) — Dimensional attributes evolve over time and require structured management techniques to maintain reporting consistency.
2. SCD Type 0: Fixed Attribute (1:06 - 1:44) — Attribute values remain permanently immutable after creation to preserve original baseline data.
3. SCD Type 1: Overwrite (1:46 - 2:23) — Existing values are updated in place, intentionally discarding historical values for current-state reporting.
4. SCD Type 2: Full History (2:25 - 3:32) — Changes trigger new row insertions using surrogate keys, active indicators, and effective date ranges to track full history.
5. SCD Type 3: Limited History (3:34 - 4:27) — A dedicated previous-value column is added to track single-level history without generating additional rows.
6. SCD Type 4: Mini Dimensions (4:29 - 6:05) — Volatile attributes are separated into a mini-dimension table linked independently to the fact table.
7. Hybrid SCD Approaches (6:08 - 6:29) — Techniques like Types 5 and 6 combine multiple SCD methods to satisfy complex enterprise data requirements.

Data Modeling & Warehousing Fundamentals Cheat Sheet

• SCD Type 0 — Preserves original value permanently; updates ignored

  -- No DDL/DML update allowed on original recorded value
  

• SCD Type 1 — Overwrites existing row in place, destroying history

  UPDATE dim_cust SET city = 'NY' WHERE id = 10;
  

• SCD Type 2 — Inserts new row with surrogate key for full history

  INSERT INTO dim_cust (sk, id, val, start_dt, is_curr) VALUES (2, 10, 'NY', NOW(), TRUE);
  

• SCD Type 3 — Stores immediate previous value in dedicated column

  UPDATE dim_cust SET prev_city = curr_city, curr_city = 'NY' WHERE id = 10;
  

• SCD Type 4 — Separates volatile attributes into a mini-dimension table

  CREATE TABLE dim_profile_mini (profile_sk INT, credit_band VARCHAR, income_band VARCHAR);
  

• Hybrid SCD (Type 6) — Combines Types 1, 2, and 3 attributes in one table

  ALTER TABLE dim_cust ADD COLUMN current_city VARCHAR, ADD COLUMN historical_city VARCHAR;
  

Comparison Table

Common Pitfalls

• Mistake: Overwriting data using Type 1 on attributes required for auditing. Avoid: Design historical attributes as Type 2 with effective start and end dates.
• Mistake: Overlapping effective date ranges or omitting is_current flags during Type 2 updates. Avoid: Run atomic transactions that close previous rows before inserting new versions.
• Mistake: Using Type 2 for rapidly changing high-volatility attributes. Avoid: Implement SCD Type 4 mini-dimensions to avoid exponential dimension table row bloat.

FAQs

• Why is a surrogate key mandatory for SCD Type 2 implementations? Because the natural business key remains identical across multiple versions, a distinct surrogate primary key is required to uniquely identify each historical state in fact tables.
• When should an engineer choose SCD Type 3 over SCD Type 2? Choose Type 3 when reporting strictly requires comparing the current state against the immediate prior state and full multi-version history is unnecessary.
• What is the primary trade-off when implementing SCD Type 4 mini-dimensions? Type 4 prevents dimension row explosion from volatile attributes, but requires managing two separate foreign keys inside the fact table schema.