ServerVultr

Understanding Slowly Changing Dimensions

Slowly Changing Dimensions (SCDs) are a cornerstone of data warehousing design. They ensure that the descriptive attributes of business entities—customers, products, employees, locations—are handled correctly when they change over time. Without a clear strategy for tracking changes in dimension data, analytical systems either lose valuable history or become inconsistent, leading to inaccurate reporting.

In this post, we explore the major SCD types, what they mean in practice, and how they fit into a typical dimension-loading routine. We also take a closer look at the most commonly used approach: the Type 2 Slowly Changing Dimension.

What Are Slowly Changing Dimensions?

A dimension becomes “slowly changing” when its attributes do not remain static. Customer names change, product descriptions evolve, and organisation structures shift. A data warehouse needs a strategy for handling these changes in a predictable, auditable manner.

The most recognised SCD types include:

• Type 0: Fixed — no changes allowed A Type 0 dimension attribute is effectively read-only. Once loaded, it never changes. This is useful for values that should remain permanently tied to the original record, such as a customer’s join date or the original product launch category.
• Type 1: Overwrite — no history Type 1 simply overwrites old values with new ones. The historical value is lost. This approach is suitable for correcting errors or updating non-critical attributes such as a standardised name format.
• Type 2: Add a new record — full history preserved Type 2 is the workhorse of dimensional modelling. Each time a change occurs, a new version of the record is inserted, and the old version is closed off using effective dates. This preserves a complete history of how the entity evolved over time.
• Type 3: Add a new column — limited history Here, the old value is stored in an additional “previous value” column. This gives only a snapshot of one historical state. It is useful when you only need to compare “current” versus “prior” attributes.
• Type 4: History table — archive old records Older versions are moved into a dedicated history table. The current table stays small and fast, while detailed historical records remain accessible when required.
• Type 5/6: Hybrid approaches Large or complex organisations sometimes blend techniques, such as combining Type 1 and Type 2 behaviours for different sets of attributes, or maintaining both current and historical versions for performance reasons.

Why Type 2 Is the Most Common

Most real-world data warehouses favour Type 2 SCDs, particularly for customer, product, and employee dimensions. Businesses need to analyse behaviour and performance based on what was true at the time, not what is true today. For example:

• What product category did this item belong to when the sale was made?
• What address was the customer living at when the invoice was issued?
• Which department was the employee assigned to when the project started?

Type 2 SCDs allow reports to reflect the correct historical context by storing every version of the record along with a validity range.

A Typical Type 2 Loading Workflow

A standard SCD Type 2 loading routine follows a clear and predictable pattern. The process often uses metadata columns such as ValidFrom, ValidTo, and an IsCurrent flag.

A typical workflow looks like this:

1. Identify changed records in the source system The ETL pipeline queries the source tables for rows updated since the last load. This is usually done using a ModifiedDate or LastUpdated column.
2. Compare the incoming values with the current dimension records If no attribute has changed, do nothing. If one or more tracked attributes differ, the current record is closed off by setting its ValidTo date to the current timestamp.
3. Insert the new version A new row is inserted into the dimension table with the updated values. The ValidFrom is set to the load timestamp, while ValidTo is set to a high placeholder date (for example, 9999-12-31).
4. Track load status A load statistics table or metadata table records how many rows were processed, updated, or inserted during the run. This is essential for troubleshooting and operational visibility.

The result is a dimension table that behaves like a temporal record of business history. Analysts can reliably reconstruct what the world looked like at any point in time through simple date-based filtering.

Why SCDs Matter

Slowly Changing Dimensions enable time-aware analytics—one of the main reasons organisations build data warehouses in the first place. Without SCDs, historical analysis becomes unreliable. With them, organisations gain:

• Accurate period-over-period comparisons
• Reliable trend analysis
• Confidence in audit trails
• Support for full regulatory and financial reporting

As your warehouse grows, choosing the right SCD strategy becomes vital. Understanding these patterns—and implementing them consistently—sets the foundation for a robust analytical ecosystem.

If you’d like help designing or implementing your dimensional models or data loading routines, feel free to get in touch and we can explore how to apply these patterns in your environment.