Understanding Slowly Changing Dimensions – Gethyn Ellis

Understanding Slowly Changing Dimensions

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

In this post, we explore the main types of SCD, 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: Type 2 Slowly Changing Dimensions.

What are Slowly Changing Dimensions?

A dimension becomes “slowly changing” when its attributes do not remain static. Customer names change, product descriptions evolve, and organizational structures change. Data warehouses require strategies to handle these changes in a predictable and auditable manner.

The most known types of SCD include:

• Type 0: Fixed — no changes allowed Type 0 dimension attributes are effectively read-only. Once loaded, it never changes. This is useful for values ​​that must remain permanently tied to the original record, such as the customer’s onboarding date or the original product launch category.
• Type 1: Overwrite — no history Type 1 simply overwrites the old values ​​with the new ones. Its historical value is lost. This approach is suitable for fixing errors or updating non-critical attributes such as standard name formats.
• Type 2: Add new record — complete history is saved Type 2 is the workhorse of dimensional modeling. Each time a change occurs, a new version of the record is inserted, and the old version is closed using the effective date. It preserves the complete history of how the entity evolved over time.
• Type 3: Add new column — limited history Here, the old value is stored in an additional column “previous value”. This only provides a brief overview of one historical circumstance. This is useful when you only need to compare “current” and “previous” attributes.
• Type 4: History table — archives old records Old versions are moved to a special history table. Current tables remain small and fast, while detailed historical notes remain accessible when needed.
• Type 5/6: Hybrid approach Large or complex organizations sometimes mix techniques, such as combining Type 1 and Type 2 behavior for different sets of attributes, or maintaining current and historical versions for performance reasons.

Why Type 2 Is the Most Common

Most real-world data warehouses favor SCD Type 2, especially for customer, product, and employee dimensions. The business world needs to analyze behavior and performance based on what was right at the time, not based on what is right now. For example:

• What product categories does this item fall into when the sale is made?
• What is the customer’s residential address when the invoice is issued?
• Which department was the employee assigned to when the project started?

SCD Type 2 allows reports to reflect the correct historical context by storing each version of the record along with its validity range.

Typical Type 2 Loading Workflow

The standard Type 2 SCD loading routine follows a clear and predictable pattern. The process often uses metadata fields such as ValidFrom, Valid Toand a At the moment flag.

The general workflow looks like this:

1. Identify changed records in the source system The ETL flow queries the source table for rows updated since the last load. This is usually done using a ModifiedDate or LastUpdated column.
2. Compare the incoming value with the current dimension record If no attributes change, do nothing. If one or more tracked attributes are different, the current record is closed by setting them Valid To date to the current timestamp.
3. Enter the new version A new row is inserted into the dimension table with the updated values. That ValidFrom set to load timestamp, temporary Valid To set to a high placeholder date (for example, 9999-12-31).
4. Track loading status The load statistics table or metadata table records how many rows were processed, updated, or inserted during the process. This is important for problem solving and operational visibility.

The result is a dimension table that behaves like a temporary record of business history. Analysts can reliably reconstruct a view of the world at any time through simple filtering by date.

Why SCD Matters

Slowly Changing Dimensions enable time-aware analysis—one of the main reasons organizations build data warehouses. Without SCD, historical analysis becomes unreliable. With them, organizations obtain:

• Accurate period-by-period comparisons
• Reliable trend analysis
• Confidence in the audit trail
• Support for full regulatory and financial reporting

As your warehouse grows, choosing the right SCD strategy becomes important. Understanding these patterns—and applying them consistently—sets the foundation for a strong analytics ecosystem.