a digital garden

Slowly Changing Dimensions (SCD)

6 min read

In data management and data warehousing, a Slowly Changing Dimension (SCD) is a database modeling technique used to manage and track data attributes that evolve gradually over time, often in an unpredictable manner. This contrasts with rapidly changing transactional data (like product prices or inventory quantities) which undergo constant updates.

Common examples of SCDs include geographic locations, customer details (like changing addresses), or shifting product attributes.

The Purpose of Slowly Changing Dimensions

Introduced by Ralph Kimball as a foundational concept of dimensional modeling, SCDs are designed to solve the problem of tracking historical “truth”.

A basic dimension table typically only shows the current state of data — what is true right now. While this is fine for answering simple questions, it cannot answer historical business questions such as:

  • “Which customers moved?”
  • “Which products changed ingredients last year?”
  • “How did a territory’s sales perform before the sales force reorganization?”

SCD techniques help organizations decide whether to overwrite old values or preserve a historical record, ensuring that Business Intelligence (BI) systems can perform accurate trend analysis without breaking the relationships between transactions and their historical context.

Types of Slowly Changing Dimensions

When an attribute changes in the real world, the database must respond. Ralph Kimball categorized these responses into different “Types,” each offering a trade-off between historical accuracy, system performance, and data complexity.

The primary SCD types include:

  • Type 0
  • Type 1
  • Type 2
  • Type 3
  • Type 4

To illustrate the differences between Slowly Changing Dimension (SCD) Types 1, 2, 3, and 4, we will use a Customer domain linked to a Sales Fact table.

In this scenario, we want to track a customer’s address and demographic profile over time. Notice how the database schema changes across the four types to handle updates to this information.

Examples are using Mermaid ER Diagrams

Entity Relationship Diagrams | Mermaid

Type 0 (Retain Original)

The dimension attribute value is never changed. It is used for permanent, durable values such as a customer’s “Original Credit Score” or a date of birth.

Type 1 (Overwrite)

In a Type 1 SCD, the existing data is simply overwritten with the new data. This method is easy to implement and saves storage space, but no historical data is preserved because the attribute always reflects the most recent assignment.

When a customer moves to a new location, the existing customer_address is simply overwritten with the new data.

  • Attention: all historical sales will appear as if they occurred at the customer’s new address.
erDiagram
    CUSTOMER_DIMENSION {
        int customer_key PK "Surrogate Key"
        string customer_id "Natural Key"
        string customer_name
        string customer_address "Overwritten when changed"
    }
    SALES_FACT {
        int fact_id PK
        int customer_key FK
        float sales_amount
    }
    
    CUSTOMER_DIMENSION ||--o{ SALES_FACT : "Filters / Groups"

Type 2 (Add New Row)

This is the primary workhorse technique for accurately tracking history. Every time an attribute changes, a new row is inserted into the dimension table with a new surrogate key to reflect the updated profile.

To keep track of which version is active, Type 2 tables use metadata columns such as valid_from (effective date), valid_to (expiration date), and a current_row_indicator.

When a customer’s address changes, the old record is expired, and a new row is inserted with a brand new surrogate customer_key.

To manage these different profile versions, administrative columns are added:

  • an effective date
  • an expiration date
  • and a current row indicator.
erDiagram
    CUSTOMER_DIMENSION {
        int customer_key PK "Surrogate Key"
        string customer_id "Natural Key"
        string customer_name
        string customer_address "Updated via new row"
        datetime row_effective_date "Start date of version"
        datetime row_expiration_date "End date of version"
        boolean current_row_indicator "True if active version"
    }
    SALES_FACT {
        int fact_id PK
        int customer_key FK "Matches active key at time of sale"
        float sales_amount
    }
    
    CUSTOMER_DIMENSION ||--o{ SALES_FACT : "Filters / Groups"

Type 3 (Add New Attribute)

Instead of creating a new row, a new column is added to the existing table to preserve the old attribute value while the primary column is overwritten with the new value.

This is useful for maintaining “alternate realities,” allowing a business to roll up facts using either the current or prior attribute values, though it only preserves a limited history.

If a customer moves, the old address is pushed into a prior_address attribute, and the current_address is overwritten with the new value.

erDiagram
    CUSTOMER_DIMENSION {
        int customer_key PK "Surrogate Key"
        string customer_id "Natural Key"
        string customer_name
        string current_address "Overwritten with new address"
        string prior_address "Preserves the previous address"
    }
    SALES_FACT {
        int fact_id PK
        int customer_key FK
        float sales_amount
    }
    
    CUSTOMER_DIMENSION ||--o{ SALES_FACT : "Filters / Groups"

This enables “alternate reality” reporting, allowing the business to roll up sales using either the current or previous address.

Type 4 (Add Mini-Dimension)

This technique is used for “rapidly changing monster dimensions,” where certain attributes (like customer age bands or income levels) change frequently within a massive dimension table. To prevent explosive table growth, the volatile attributes are split off into a separate, smaller “mini-dimension”.

Type 4 is used when certain attributes — like a customer’s age_band, income_level, or purchase_frequency_score — change rapidly. Tracking these inside a massive, multi-million-row customer table using Type 2 logic would cause explosive, unmanageable table growth.

Instead, Type 4 splits these volatile attributes into a separate mini-dimension containing fixed, banded profile combinations. The fact table captures history by holding foreign keys to both the stable Customer base dimension and the active Demographics mini-dimension at the time of the transaction.

erDiagram
    CUSTOMER_DIMENSION {
        int customer_key PK "Surrogate Key"
        string customer_id "Natural Key"
        string customer_name
        string customer_address "Stable attributes"
    }
    DEMOGRAPHICS_MINI_DIMENSION {
        int demographics_key PK "Surrogate Key"
        string age_band "e.g., 21-25"
        string income_level "e.g., <$30,000"
        string purchase_frequency_score "e.g., High, Medium, Low"
    }
    SALES_FACT {
        int fact_id PK
        int customer_key FK "Links to base customer"
        int demographics_key FK "Links to profile active AT TIME OF SALE"
        float sales_amount
    }
    
    CUSTOMER_DIMENSION ||--o{ SALES_FACT : "Makes Purchase"
    DEMOGRAPHICS_MINI_DIMENSION ||--o{ SALES_FACT : "Profile During Purchase"

Through a Factless Fact Table (Comprehensive History)

What happens if a customer’s demographic profile changes (like getting a raise), but they don’t make a purchase? To capture profile changes that occur outside of a business event, organizations introduce a supplemental factless fact table. This table explicitly tracks the relationship between the customer and their assigned demographic profile over time using effective and expiration dates.

erDiagram
    CUSTOMER_DIMENSION {
        int customer_key PK "Surrogate Key"
        string customer_id "Natural Key"
        string customer_name
        string customer_address "Stable attributes"
    }
    DEMOGRAPHICS_MINI_DIMENSION {
        int demographics_key PK "Surrogate Key"
        string age_band "e.g., 21-25"
        string income_level "e.g., <$30,000"
        string purchase_frequency_score "e.g., High, Medium, Low"
    }

    CUSTOMER_DEMO_HISTORY_FACT {
        int history_id PK 
        int customer_key FK
        int demographics_key FK
        datetime effective_date "Start of profile assignment"
        datetime expiration_date "End of profile assignment"
    }
    
    CUSTOMER_DIMENSION ||--o{ CUSTOMER_DEMO_HISTORY_FACT : "Tracks Profile History"
    DEMOGRAPHICS_MINI_DIMENSION ||--o{ CUSTOMER_DEMO_HISTORY_FACT : "Assigned Profile"

Hybrid Approaches (Types 5, 6, and 7)

These combine the basic techniques (such as combining Types 1, 2, and 3) to handle complex scenarios. For example, they allow an organization to preserve historically accurate dimension attributes for past transactions while simultaneously providing the option to report on those historical facts using current attribute values.

Meta

  • Maturity
    🌳