Slowly Changing Dimensions: Managing Historical Data in Data Warehouses

Data warehouses promise historical analysis, but dimensional data refuses to stay still. A customer relocates. A product changes categories. A salesperson transfers to a new region. These changes seem simple until you ask: should historical reports reflect the old state or the new one? Traditional databases handle changes through updates—overwrite the old value with the new. This works for transactional systems focused on current state, but destroys the historical context that makes data warehouses valuable. When a salesperson moves from California to Illinois, should their past sales appear under California or Illinois in regional reports? Slowly Changing Dimensions (SCDs) address this challenge. Unlike rapidly changing transactional data—customer IDs, quantities, prices—dimensional attributes like locations, names, and categories change infrequently but unpredictably. The “slowly” in SCD doesn’t mean changes are rare; it means they’re not part of normal transaction flow. Ralph Kimball’s Data Warehouse Toolkit popularized a classification system for handling these changes: Types 0 through 7. Each type represents a different trade-off between historical accuracy, data complexity, and query performance. Type 1 sacrifices history for simplicity. Type 2 preserves complete history at the cost of complexity. Type 6 combines multiple approaches for maximum flexibility. The challenge extends beyond technical implementation. SCDs must maintain referential integrity between fact and dimension tables. A sales fact table links to a supplier dimension. When the supplier relocates, historical sales must still connect to the correct supplier information without breaking database relationships. This article explores each SCD type, examines their trade-offs, and provides guidance on choosing the right approach for different scenarios.

The Historical Data Problem

Before exploring solutions, understanding the problem reveals why dimensional changes create complexity.

The Supplier Relocation Scenario

Consider a data warehouse tracking supplier relationships:

The Referential Integrity Challenge

Database relationships complicate matters further:

Why “Slowly Changing” Matters

The distinction between slowly and rapidly changing data is crucial:

SCD Type Classification

Ralph Kimball’s classification system provides a structured approach to handling dimensional changes.

The Type System Overview

Choosing Factors

The right type depends on specific requirements:

Type 0: Retain Original

Some attributes never change by definition.

Immutable Attributes

Type 0 applies to attributes with durable values:

Date Dimensions

Date dimensions typically use Type 0 for most attributes:

CREATE TABLE Date_Dimension (
    Date_Key INT PRIMARY KEY,
    Full_Date DATE,
    Day_Of_Week VARCHAR(10),
    Month_Name VARCHAR(10),
    Quarter INT,
    Year INT,
    Is_Holiday BOOLEAN
);

Business Value

Type 0 provides clarity and protection:

Type 1: Overwrite

The simplest approach: replace old values with new ones.

How Type 1 Works

Type 1 updates records in place: Initial State:

UPDATE Supplier
SET Supplier_State = 'IL'
WHERE Supplier_Code = 'ABC';

The old value disappears. The new value takes its place. No history, no complexity.

When Type 1 Makes Sense

Type 1 works for specific scenarios:

The Cost of Simplicity

Type 1’s simplicity comes with significant limitations:

Aggregate Recalculation Problem

Type 1 creates cascading updates:

Implementation Considerations

Type 1 requires careful planning:

Type 2: Add New Row

The most common SCD approach: create a new record for each change.

How Type 2 Works

Type 2 preserves complete history through multiple records: Initial State:

Implementation Variations

Type 2 has several implementation patterns:

Fact Table Integration

Type 2’s power comes from fact table integration:

Query Patterns

Type 2 supports multiple query patterns: Current State Query:

SELECT 
    s.Supplier_Name,
    s.Supplier_State,
    SUM(f.Sales_Amount) as Total_Sales
FROM Fact_Sales f
JOIN Supplier s ON f.Supplier_Key = s.Supplier_Key
WHERE s.Current_Flag = 'Y'
GROUP BY s.Supplier_Name, s.Supplier_State;

Historical State Query:

SELECT 
    s.Supplier_Name,
    s.Supplier_State,
    SUM(f.Sales_Amount) as Total_Sales
FROM Fact_Sales f
JOIN Supplier s ON f.Supplier_Key = s.Supplier_Key
WHERE f.Sale_Date BETWEEN s.Start_Date AND s.End_Date
GROUP BY s.Supplier_Name, s.Supplier_State;

Point-in-Time Query:

SELECT 
    s.Supplier_Name,
    s.Supplier_State
FROM Supplier s
WHERE s.Supplier_Code = 'ABC'
  AND '2003-06-15' BETWEEN s.Start_Date AND COALESCE(s.End_Date, '9999-12-31');

Type 2 Challenges

Despite its popularity, Type 2 has drawbacks:

Retroactive Change Problem

Type 2’s biggest challenge appears when the dimension model changes:

Best Practices

Type 2 works best with discipline:

Type 3: Add New Attribute

Type 3 tracks limited history through additional columns.

How Type 3 Works

Instead of adding rows, Type 3 adds columns: Initial State:

Limited History Trade-off

Type 3 provides fixed history depth:

Query Simplicity

Type 3 enables straightforward queries:

-- Current state analysis
SELECT 
    Current_State,
    SUM(Sales_Amount) as Total_Sales
FROM Supplier s
JOIN Fact_Sales f ON s.Supplier_Key = f.Supplier_Key
GROUP BY Current_State;
-- Original state analysis
SELECT 
    Original_State,
    SUM(Sales_Amount) as Total_Sales
FROM Supplier s
JOIN Fact_Sales f ON s.Supplier_Key = f.Supplier_Key
GROUP BY Original_State;
-- Transition analysis
SELECT 
    Original_State,
    Current_State,
    COUNT(*) as Suppliers_Moved
FROM Supplier
WHERE Original_State != Current_State
GROUP BY Original_State, Current_State;

No date range checks. No current flags. Simple column references.

When Type 3 Makes Sense

Type 3 works for specific analytical needs:

Previous Value Variation

An alternative tracks the most recent change:

Type 3 Limitations

The fixed structure creates problems:

Hybrid Column Approach

Type 3 often combines with other types:

Type 4: Add History Table

Type 4 separates current and historical data into different tables.

How Type 4 Works

Type 4 uses two tables: one for current state, one for history: Supplier Table (Current):

Database Audit Pattern

Type 4 resembles database audit tables:

Fact Table Integration

Type 4’s unique feature: facts can reference both tables:

CREATE TABLE Fact_Sales (
    Sale_ID INT PRIMARY KEY,
    Sale_Date DATE,
    Current_Supplier_Key INT,  -- References Supplier
    Historical_Supplier_Key INT, -- References Supplier_History
    Amount DECIMAL(10,2)
);

Query Patterns

Type 4 enables different analytical perspectives: Current State Analysis:

SELECT 
    s.Supplier_State,
    SUM(f.Amount) as Total_Sales
FROM Fact_Sales f
JOIN Supplier s ON f.Current_Supplier_Key = s.Supplier_Key
GROUP BY s.Supplier_State;

This query shows all sales grouped by current supplier state, regardless of when the sale occurred. Historical State Analysis:

SELECT 
    sh.Supplier_State,
    SUM(f.Amount) as Total_Sales
FROM Fact_Sales f
JOIN Supplier_History sh ON f.Historical_Supplier_Key = sh.Supplier_Key
GROUP BY sh.Supplier_State;

This query shows sales grouped by the supplier’s state at the time of sale.

ETL Considerations

Type 4 requires careful ETL design:

When Type 4 Makes Sense

Type 4 suits specific scenarios:

Type 4 Challenges

The dual-table approach creates complexity:

Maintenance Strategy

Type 4 requires ongoing maintenance:

Type 6: Combined Approach

Type 6 combines Types 1, 2, and 3 (1 + 2 + 3 = 6) for maximum flexibility.

How Type 6 Works

Type 6 uses Type 2’s row versioning with Type 3’s current value columns and Type 1’s overwrite strategy: Initial State:

• Type 2: New row created (Row_Key 2)
• Type 3: Current_State column added
• Type 1: Current_State in Row_Key 1 overwritten to IL After Second Relocation: | Supplier_Key | Row_Key | Supplier_Code | Supplier_Name | Current_State | Historical_State | Start_Date | End_Date | Current_Flag | |--------------|---------|---------------|---------------|---------------|------------------|------------|----------|--------------| | 123 | 1 | ABC | Acme Supply Co | NY | CA | 2000-01-01 | 2004-12-22 | N | | 123 | 2 | ABC | Acme Supply Co | NY | IL | 2004-12-22 | 2008-02-04 | N | | 123 | 3 | ABC | Acme Supply Co | NY | NY | 2008-02-04 | 9999-12-31 | Y | All rows now show Current_State = NY (Type 1 overwrite), while Historical_State preserves the value that was current during each time period (Type 2 history).

The Three Techniques Combined

Type 6 applies each technique to different aspects:

Query Flexibility

Type 6 supports three query patterns: Current State Query:

SELECT 
    s.Current_State,
    SUM(f.Sales_Amount) as Total_Sales
FROM Fact_Sales f
JOIN Supplier s ON f.Supplier_Key = s.Row_Key
GROUP BY s.Current_State;

Shows all sales grouped by current supplier state. Simple join, no date filtering. Historical State Query:

SELECT 
    s.Historical_State,
    SUM(f.Sales_Amount) as Total_Sales
FROM Fact_Sales f
JOIN Supplier s ON f.Supplier_Key = s.Row_Key
GROUP BY s.Historical_State;

Shows sales grouped by the state that was current when the sale occurred. Also simple, no date filtering needed. Point-in-Time Query:

SELECT 
    s.Supplier_Name,
    s.Historical_State
FROM Supplier s
WHERE s.Supplier_Code = 'ABC'
  AND '2005-06-15' BETWEEN s.Start_Date AND s.End_Date;

Retrieves the dimension state as it existed on a specific date.

Kimball’s “Unpredictable Changes with Single-Version Overlay”

Ralph Kimball calls Type 6 “Unpredictable Changes with Single-Version Overlay”:

Implementation Complexity

Type 6 is the most complex SCD type:

When Type 6 Makes Sense

Type 6 justifies its complexity in specific scenarios:

Type 6 Best Practices

Managing Type 6 requires discipline:

CREATE VIEW Supplier_With_Current AS
SELECT 
    h.*,
    c.Supplier_State as Current_State
FROM Supplier h
JOIN Supplier c ON h.Supplier_Code = c.Supplier_Code
WHERE c.Current_Flag = 'Y';

This view gives each historical row access to the current state without storing it redundantly.

Type 7: Dual Key Strategy

Type 7 places both surrogate and natural keys in the fact table for maximum query flexibility.

How Type 7 Works

Type 7 uses Type 2 dimension structure but stores two keys in facts: Supplier Dimension (Type 2):

CREATE TABLE Fact_Sales (
    Sale_ID INT PRIMARY KEY,
    Sale_Date DATE,
    Supplier_Key INT,        -- Surrogate key (historical)
    Supplier_Code VARCHAR(10), -- Natural key
    Amount DECIMAL(10,2)
);

The fact stores both the surrogate key (Supplier_Key) that was current at transaction time and the natural key (Supplier_Code) that identifies the entity across all versions.

Query Flexibility

Type 7 enables three query patterns without date logic: Historical State Query:

SELECT 
    s.Supplier_State,
    SUM(f.Amount) as Total_Sales
FROM Fact_Sales f
JOIN Supplier s ON f.Supplier_Key = s.Supplier_Key
GROUP BY s.Supplier_State;

Join on surrogate key shows historical state. A sale from 2003 joins to Supplier_Key 123 (CA), showing California sales. Current State Query:

SELECT 
    s.Supplier_State,
    SUM(f.Amount) as Total_Sales
FROM Fact_Sales f
JOIN Supplier s ON f.Supplier_Code = s.Supplier_Code
WHERE s.Current_Flag = 'Y'
GROUP BY s.Supplier_State;

Join on natural key with current flag shows current state. All sales for ABC join to Supplier_Key 125 (NY), showing New York sales. Point-in-Time Query:

SELECT 
    s.Supplier_State,
    SUM(f.Amount) as Total_Sales
FROM Fact_Sales f
JOIN Supplier s ON f.Supplier_Code = s.Supplier_Code
WHERE f.Sale_Date BETWEEN s.Start_Date AND s.End_Date
GROUP BY s.Supplier_State;

Join on natural key with date range shows state at any point in time.

Advantages Over Type 2

Type 7 adds flexibility to Type 2:

The Retroactive Change Advantage

Type 7’s biggest benefit appears when dimensions evolve:

Type 7 Challenges

The dual key approach has significant drawbacks:

The Referential Integrity Problem

Type 7’s most serious issue is the inability to enforce referential integrity:

When Type 7 Makes Sense

Type 7 suits specific scenarios:

Type 7 Best Practices

Type 7 requires careful implementation:

Type 7 vs Type 6

Both provide flexibility but through different mechanisms:

Choosing the Right Type

Different dimensions and attributes require different SCD types.

Decision Framework

Selecting the appropriate SCD type requires evaluating multiple factors:

Type Selection Matrix

A practical guide for choosing SCD types:

Hybrid Approaches

Most data warehouses use multiple SCD types:

Common Patterns by Industry

Different industries have typical SCD patterns:

Performance Considerations

SCD type affects query and ETL performance:

Storage Considerations

Different types have different storage profiles:

Migration Strategies

Changing SCD types requires planning:

Anti-Patterns to Avoid

Common SCD mistakes:

Practical Recommendations

Guidelines for real-world implementation:

Conclusion

Slowly Changing Dimensions represent one of data warehousing’s fundamental challenges: balancing historical accuracy with practical implementation constraints. The choice between SCD types isn’t about finding the “best” approach—it’s about matching technique to business requirements, team capabilities, and system constraints. The journey through SCD types reveals important lessons: Simplicity Has Costs: Type 1’s simplicity comes at the price of lost history. What seems like a minor trade-off during design becomes a major limitation when business needs evolve. The question “what were our sales in California in 2005?” becomes unanswerable when suppliers relocate and Type 1 overwrites their locations. Historical data, once lost, cannot be recovered. History Requires Structure: Type 2’s popularity stems from its balanced approach. Complete history preservation, reasonable query complexity, and well-understood implementation make it the default choice. The pattern of creating new rows for changes, maintaining temporal columns, and using surrogate keys has proven itself across countless data warehouses. Flexibility Demands Complexity: Type 6 and Type 7 provide maximum flexibility but require sophisticated ETL processes and careful query design. The complexity is justified only when simpler approaches prove insufficient. Starting with Type 2 and evolving to more complex types as needs emerge prevents premature optimization. Context Matters: No single SCD type fits all scenarios. Customer addresses might need Type 2 for historical accuracy while phone numbers use Type 1 for simplicity. Product categories might require Type 2 while product descriptions use Type 1. The ability to mix types within a single data warehouse—even within a single dimension—enables practical solutions. Trade-offs Are Inevitable: Every SCD type represents trade-offs between competing concerns:

• Historical accuracy vs. query simplicity
• Storage efficiency vs. temporal precision
• ETL complexity vs. query flexibility
• Current state access vs. historical state access Understanding these trade-offs enables informed decisions rather than following patterns blindly. Implementation Discipline: Successful SCD implementation requires discipline. Consistent date handling, clear naming conventions, comprehensive documentation, and robust ETL processes separate working implementations from problematic ones. The technical pattern matters less than the rigor of its application. The SCD type classification system provides a vocabulary for discussing dimensional change strategies. Type 0 through Type 7 aren’t rigid rules but rather a toolkit. The art lies in selecting the right tool for each situation, combining techniques when appropriate, and maintaining the discipline to implement chosen approaches consistently. Modern data warehouses often use Type 2 as the foundation, adding Type 1 for attributes where history doesn’t matter and Type 6 or Type 7 only when specific requirements justify the additional complexity. This pragmatic approach balances the competing demands of historical accuracy, query performance, storage efficiency, and maintainability. The ultimate measure of success isn’t which SCD type you choose but whether your dimensional model enables the business to answer its questions accurately. When historical reports reflect actual historical state, when current reports show current state, and when the distinction between the two is clear and intentional, the SCD implementation succeeds regardless of which specific types it employs. As data warehouses evolve and business requirements change, SCD strategies may need adjustment. The flexibility to migrate between types, the discipline to document decisions, and the wisdom to choose simplicity over complexity when possible determine long-term success more than any specific technical choice.