Phase 1: Foundations

Slowly changing dimensions (SCD Types 1, 2, 3)

Beginner ~4 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine you have a library card. On it, the library keeps important information about you, like your name and your home address. Now, sometimes these things change, right? Maybe your family moves to a new house, so your address is different. The library needs to know your most recent address to send you a reminder about a book, but what if they also need to remember where you lived when you borrowed a book last year?

This is a bit like a puzzle that people who work with data, called "data engineers," have to solve. They manage huge collections of information, similar to a giant digital library. If you just update your address on your library card, the old address is gone forever. This is like a simple "update" rule. It’s useful if you only care about what’s true right now, like fixing a typo in your name. But if you want to know what was true at a specific time in the past, that old information is lost.

To solve this, there's a clever trick! When your address changes, instead of just erasing the old one, the library could make a brand new library card for you with your new address. On the old card, they'd make a note saying, "This address was valid until [date]," and on the new card, "This address is valid from [date]." Both cards exist! This way, if someone asks where you lived when you borrowed a book last year, they can look at your old card. If they ask where you live now, they look at your new card.

This smart way of keeping track of changing information means that when data engineers build systems to collect and store facts, they can ensure that every piece of history is saved accurately. So, when you look at reports about how many books were borrowed in a certain town last year, or how many people used a park facility when they lived on a specific street, you know the information is correct for that exact moment in time, even if people have since moved or changed.

In data warehousing, dimensions like customers, products, or locations aren't static; their attributes (e.g., a customer's address, a product's category) can change over time. If you simply update these changes in your dimension tables, you'll overwrite historical data, making it impossible to accurately analyze past events. For instance, if a customer's region changes, and you just update the record, past sales linked to that customer will now incorrectly appear to have occurred in the new region. Slowly Changing Dimensions (SCDs) are techniques used to manage these changes effectively within a data warehouse, ensuring historical accuracy for analytical reporting. The simplest method is SCD Type 1 (Overwrite): when an attribute changes, you just update the existing record with the new value. This means you lose the old historical value, making it suitable for minor corrections or attributes where historical tracking isn't critical.

SCD Type 2 (Add New Row) is the most common and powerful approach for preserving a complete history of changes. When a dimension attribute changes, instead of overwriting, a new record is created for that dimension with the updated attribute values. The old record is then marked as no longer current (e.g., by setting an EndDate and an IsCurrent flag). This allows you to see exactly what the dimension looked like at any point in time. For example, if a customer changes their address or region, you would create a new row for them with the new details and an updated StartDate, while the previous row gets an EndDate and IsCurrent set to FALSE. This method ensures that historical facts (like sales) can always be tied to the correct dimension state at the time the fact occurred.

Finally, SCD Type 3 (Add New Column) tracks a limited history by adding new columns to the existing dimension record. Typically, this involves keeping the current value in one column and the immediate previous value in another. For example, if you only need to know a customer's current region and their single previous region, you could use Type 3 by having CurrentRegion and PreviousRegion columns. This approach is less common than Type 2 as it only stores a fixed, limited number of past states, but it avoids the table growth associated with Type 2 by not creating new rows. Choosing the correct SCD type is a fundamental decision in data modeling, as it directly impacts your data warehouse's ability to support accurate historical and trend analysis.

Key Takeaways

  • SCDs are strategies to manage attribute changes in dimension tables without losing historical context.
  • SCD Type 1 (Overwrite) updates records directly, sacrificing historical data for the changed attribute.
  • SCD Type 2 (Add New Row) creates new records for changes, preserving a complete history of the dimension's evolution.
  • SCD Type 3 (Add New Column) tracks a limited history (e.g., current and previous state) within the same row.
  • Selecting the right SCD type is crucial for accurate 'as-of' reporting and analytical capabilities in a data warehouse.

Code Example

sql
-- Example Customer Dimension Table for SCD Type 2
CREATE TABLE DimCustomer (
    CustomerID INT,
    CustomerName VARCHAR(255),
    CustomerAddress VARCHAR(255),
    Region VARCHAR(100),
    StartDate DATE,
    EndDate DATE,
    IsCurrent BOOLEAN,
    PRIMARY KEY (CustomerID, StartDate) -- Composite key for unique historical versions
);

-- Initial record
INSERT INTO DimCustomer VALUES (101, 'Alice Smith', '123 Main St', 'North', '2020-01-01', '9999-12-31', TRUE);

-- Alice moves to 'South' region on 2023-05-16
-- 1. Inactivate the old record
UPDATE DimCustomer
SET EndDate = '2023-05-15', IsCurrent = FALSE
WHERE CustomerID = 101 AND IsCurrent = TRUE;

-- 2. Insert the new record
INSERT INTO DimCustomer VALUES (101, 'Alice Smith', '123 Main St', 'South', '2023-05-16', '9999-12-31', TRUE);

How this code works

This code demonstrates how to implement Slowly Changing Dimension (SCD) Type 2 for a DimCustomer table, preserving a full history of changes to dimension attributes over time. The CREATE TABLE statement defines DimCustomer with core customer details like CustomerName and CustomerAddress, alongside StartDate, EndDate, and IsCurrent to manage record validity. A PRIMARY KEY (CustomerID, StartDate) is essential, enabling multiple versions of the same customer based on their effective dates. The initial INSERT creates a customer record active from '2020-01-01', marked IsCurrent TRUE and EndDate as a far-future date like '9999-12-31' to signify it as the presently valid version.

When a customer's Region changes, the code performs two crucial steps. First, an UPDATE statement inactivates the existing current record by setting its EndDate to the day before the change and switching IsCurrent to FALSE. This carefully closes the historical period of the old data. Second, a new INSERT statement adds a fresh record for the same customer, incorporating the updated Region, a new StartDate reflecting the change date, and setting IsCurrent to TRUE. The subtle but important detail here is the precise setting of the EndDate on the old record to prevent any overlap or gap with the new record's StartDate, ensuring a continuous and accurate history.

Related Articles

Hand-picked reads to go deeper on data modeling.

Back to Data Engineer Roadmap