Slowly Changing Dimensions (SCD) in Data Warehousing

In the world of data warehousing and business intelligence, Slowly Changing Dimensions (SCD) play a pivotal role in managing how data evolves over time. When businesses track information about customers, products, employees, and more, it’s critical to manage how this data changes. This is where SCDs come into the picture.

This blog will explore the concept of Slowly Changing Dimensions, their various types, why they are important, and real-world examples illustrating how they are used. We will also provide illustrations to visualize these concepts.

What is Slowly Changing Dimensions (SCD)?

Slowly Changing Dimensions (SCD) refers to the method used to manage and track changes in dimensional data over time in data warehouses. Dimensional data typically refers to descriptive attributes of facts in a data warehouse (such as customer names, addresses, or product details) that change over time but at a slower rate compared to transactional data.

Changes in these dimensions, though infrequent, need to be recorded efficiently to preserve historical accuracy for reporting, analytics, and decision-making.

Why are SCDs Important?

In analytics and business intelligence, historical data is often crucial for:

• Trend analysis: How have customer preferences or behavior changed over time?
• Historical reporting: What did the business landscape look like at a specific point in the past?
• Data accuracy: Ensuring accurate representation of data changes in reports and dashboards.

If a company only keeps the latest data without recording historical changes, it becomes difficult to answer these questions.

Types of Slowly Changing Dimensions (SCD)

There are primarily three types of SCDs that are used in data warehousing. Each type reflects a different approach to managing changes to dimensional data over time.

1. SCD Type 0: No Changes (Fixed Dimension)

In this approach, once a record is inserted into the data warehouse, it remains unchanged. No historical data is tracked. This is rarely used but can be helpful when dealing with data that should remain static or unchanged, such as immutable reference data.

Example:

• Product SKU (Stock Keeping Unit): The SKU of a product typically does not change, so there’s no need to track history.

Illustration:

Original Customer Data:
| Customer ID | Name      | City    | Email            |
|-------------|-----------|---------|------------------|
| 101         | John Doe  | Seattle | john@example.com |

After a change (Email is updated):

| Customer ID | Name      | City    | Email             |
|-------------|-----------|---------|-------------------|
| 101         | John Doe  | Seattle | john.doe@abc.com  |  --> No change, email remains same.

2. SCD Type 1: Overwriting the Old Data

In this method, the existing record is simply updated with new information, and no history is preserved. It is most appropriate when the historical value of the dimension is not relevant.

Example:

• Customer Address Change: If we only care about the current address of a customer, the old address is overwritten by the new one.

Illustration:

Original Customer Data:

| Customer ID | Name      | City    | Email            |
|-------------|-----------|---------|------------------|
| 101         | John Doe  | Seattle | john@example.com |

After a change (City is updated):

| Customer ID | Name      | City    | Email            |
|-------------|-----------|---------|------------------|
| 101         | John Doe  | New York| john@example.com | --> City updated, no history retained.

3. SCD Type 2: Creating New Records (Versioning)

This is the most commonly used type. In SCD Type 2, a new row is added with a new version or a timestamp whenever a change occurs. This method keeps historical data and allows us to track changes over time.

Example:

• Customer Address Change: When a customer moves to a new city, the old record is kept, and a new row is inserted with the updated information.

Illustration:

Original Customer Data:

| Customer ID | Name      | City    | Email            | Valid From | Valid To   |
|-------------|-----------|---------|------------------|------------|------------|
| 101         | John Doe  | Seattle | john@example.com | 2020-01-01 | 9999-12-31 |

After a change (City is updated):

| Customer ID | Name      | City    | Email            | Valid From | Valid To   |
|-------------|-----------|---------|------------------|------------|------------|
| 101         | John Doe  | Seattle | john@example.com | 2020-01-01 | 2023-01-01 |
| 101         | John Doe  | New York| john@example.com | 2023-01-01 | 9999-12-31 | --> History maintained with new row.

In this way, we can track how the customer’s address has evolved over time.

4. SCD Type 3: Adding New Columns

In this method, an additional column is added to track only the most recent change. This type captures the history to a limited extent because it doesn’t add new rows but simply records the old value in a separate column.

Example:

• Employee Job Title Change: Instead of adding a new row, we keep a column for "Previous Job Title" and update the current job title.

Illustration:

Original Employee Data:

| Employee ID | Name         | Job Title      | Previous Job Title |
|-------------|--------------|----------------|--------------------|
| 201         | Sarah Brown  | Analyst        |                    |

After a change (Job Title is updated):

| Employee ID | Name         | Job Title      | Previous Job Title |
|-------------|--------------|----------------|--------------------|
| 201         | Sarah Brown  | Manager        | Analyst            |

In SCD Type 3, we can only keep track of the current and previous state.

5. SCD Type 4: Using a Separate Historical Table

In SCD Type 4, a separate table is created to store historical data, while the main dimension table holds only the current information. This method is useful when you want to segregate the current state from historical records for performance reasons.

Example:

• Customer Data: The current table will have the most recent details, while the historical table will have all the previous records.

Illustration:

Current Customer Table:

| Customer ID | Name      | City    | Email            |
|-------------|-----------|---------|------------------|
| 101         | John Doe  | New York| john@example.com |

Historical Table:

| Customer ID | Name      | City    | Email            | Valid From | Valid To   |
|-------------|-----------|---------|------------------|------------|------------|
| 101         | John Doe  | Seattle | john@example.com | 2020-01-01 | 2023-01-01 |

6. SCD Type 6: Hybrid Method (1+2+3)

SCD Type 6 is a combination of Types 1, 2, and 3. It involves adding new records for every change (like Type 2), tracking previous information in a separate column (like Type 3), and sometimes overwriting non-critical attributes (like Type 1).

Example:

• Employee Data: Track changes over time by adding new records while maintaining a column for the previous job title, and updating minor fields directly.

Illustration:

Original Employee Data:

| Employee ID | Name         | Job Title      | Previous Job Title | Status  |
|-------------|--------------|----------------|--------------------|---------|
| 201         | Sarah Brown  | Analyst        |                    | Active  |

After a change:

| Employee ID | Name         | Job Title      | Previous Job Title | Status  |
|-------------|--------------|----------------|--------------------|---------|
| 201         | Sarah Brown  | Manager        | Analyst            | Active  | --> Historical and current data maintained.

When and Why to Use Different SCD Types?

SCD Type 0:

• When to use: For immutable data where changes should not be allowed (e.g., product SKUs, historical classifications).
• Why: Ensures data consistency when no changes are expected.

SCD Type 1:

• When to use: For non-critical information that doesn't require history (e.g., spelling corrections).
• Why: Simplifies updates without needing additional storage or complexity.

SCD Type 2:

• When to use: When historical accuracy is critical (e.g., tracking customer address changes).
• Why: Allows detailed tracking of changes over time.

SCD Type 3:

• When to use: When only the previous state is important (e.g., recent promotions).
• Why: Provides some history without the complexity of Type 2.

SCD Type 4:

• When to use: When performance is a concern and current and historical data need separation.
• Why: Helps manage large datasets efficiently.

SCD Type 6:

• When to use:

When you need a hybrid approach to maintain a full history along with some summarized historical data.

• Why: Combines the strengths of different types for flexible reporting.

Conclusion

Slowly Changing Dimensions are a fundamental concept in data warehousing that helps maintain the integrity of dimensional data as it evolves over time. By carefully selecting the appropriate SCD type, businesses can balance historical accuracy with performance and complexity.

Each type serves a specific purpose depending on the nature of the data and the reporting requirements. Whether it's maintaining full historical details (SCD Type 2), tracking the most recent changes (SCD Type 3), or optimizing for performance (SCD Type 4), SCDs ensure that data is accurate, reliable, and insightful.

Understanding the use cases and implications of each SCD type is crucial for data architects, engineers, and business analysts to create effective and scalable data models.

Looking to supercharge your team with a seasoned Data Engineer? Let’s connect on LinkedIn or drop me a message — I’d love to explore how I can help drive your data success!

Do you want to dive deeper into any particular SCD type or need assistance with implementing them? Feel free to ask!