Combining SCD Types for Hybrid Dimensions

In this lesson, you will learn how to mix multiple Slowly Changing Dimension (SCD) strategies within a single dimension table to meet conflicting requirements for historical accuracy and storage efficiency.

Why go Hybrid?

So far, we have looked at SCD Types in isolation: Type 1 overwrites everything, Type 2 preserves every version, and Type 3 keeps current and original values. In real-world data engineering, a single dimension often has attributes with different business needs.

For example, consider a Dim_Customer table. You might want to:

• Track history (Type 2): For the customer’s Credit_Limit so you can report on the historical risk profile of your loan portfolio.
• Overwrite (Type 1): For the customer’s Phone_Number because you only care about reaching the customer today, and old numbers are just noise.
• Compare current vs. original (Type 3): For the Preferred_Branch to quickly see where they started versus where they bank now, without managing thousands of historical row versions.

Implementing the Strategy

By applying these strategies to specific columns, you balance granularity with performance. Your table design might look like this:

-- Conceptual structure for a Hybrid Customer Dimension
CREATE TABLE Dim_Customer (
    Customer_SK INT PRIMARY KEY,      -- Surrogate Key
    Customer_ID INT,                  -- Natural Key
    
    -- Type 2 Attributes (History tracking)
    Credit_Limit DECIMAL(10,2),
    Valid_From DATETIME,
    Valid_To DATETIME,
    
    -- Type 1 Attribute (Always current)
    Phone_Number VARCHAR(20),
    
    -- Type 3 Attributes (Current vs Original)
    Current_Preferred_Branch VARCHAR(50),
    Original_Preferred_Branch VARCHAR(50)
);

Balancing the Trade-offs

When you update a Credit_Limit, you must insert a new row (Type 2). However, when updating the Phone_Number, you simply perform a UPDATE statement on the current record (Type 1). This keeps your table size manageable while ensuring the most critical analytical data—like risk exposure—remains historically accurate.

If you tracked every phone number change with Type 2, your dimension table would bloat unnecessarily, slowing down query performance across the entire star schema. Mixing strategies ensures that you only pay the "storage and complexity tax" for the attributes that truly require historical auditing.

Key Takeaway: Use Hybrid Dimensions to optimize storage and query performance by applying Type 2 tracking only to attributes that require full history, while using Type 1 or Type 3 strategies for attributes that do not.