Slowly Changing Dimensions(SCDs) with examples; Complete Guide

What are dimensions?

Dimensions are descriptive data elements used to classify or categorize data. For example in a sales database, dimensions might include product, customer, store. Dimensions include attributes, for instance product dimension will have attributes such as product name, product category, product price.

What is Slowly Changing Dimensions(SCDs)?

Some common scenarios such as a customer moving from one place to another, a product price being revised after sometime or a user changes their email address. How would you go about in updating all this information to the data warehouse.

Slowly Changing Dimensions are strategies or techniques used to update data that change over time but not frequently without having to affect the integrity of the final data.

Types of SCDs

1. SCD Type 1 : The Override(No History)

The majority of databases have this default behavior: when a value changes, it is overwritten. It is easy. rapid. Additionally, the previous value is lost forever.

Initial value:

Updated value(Email changed):

Code sample:

UPDATE dim_customers 
SET email = '[email protected]' 
WHERE Id = 101;

This is easy to execute and fast but it is not suitable when historical data is considered important. Best for correcting typos or non-critical fields.

2. SCD Type 2 : Row Versioning(Full History)

This is the most used technique in SCD. It depends on timestamps to show when a certain information was valid. Incase of a change a new row is added to indicate the new information as valid.

Initial value:

Updated value: (City updated):

Code sample:

UPDATE dim_customers 
SET Is_Current = FALSE, Valid_To = CURRENT_DATE() 
WHERE Id = 101 AND Is_Current = TRUE; 

INSERT INTO dim_customers (C_Key, Id, Name, Email,City, Valid_From, valid_to,Is_Current) 
VALUES (nextval('seq_cust'), 101, 'John Doe','[email protected]', 'Nakuru', CURRENT_DATE(), NULL, TRUE, );

This is best for audit trails, use this whenever historical tracking affects business metrics such as performance by region.

The trade-off is that the dimension grows over time and for records changing frequently this may not be efficient as it may bloat the warehouse.

3. SCD Type 3 : Previous Value Column(Limited History)

In this strategy, instead of adding a new row when there is a change, another column is used to show the previous value. This would only show one previous change and it does not show all the history.

Initial value:

Updated value: (Changed City)

Code sample:

UPDATE dim_customers 
SET Previous_City = Current_City, Current_City = 'Nakuru' 
WHERE Id = 101;

This is best when you only care about recent change and not the whole history.

The trade off is that you can only check the last change made so if more than one change is done some information is lost.

Other SCD types

Although Types 0 through 3 are the most widely discussed and used, there are other SCD types that are intended for more specific situations. Types 4 and 6 are usually utilized in advanced data warehouse installations and offer several methods for handling historical data.

1. SCD Type 4

This maintains the main table as current values. In case of change a historical table is added with the previous data then the main table is updated to have the current information.

2. SCD Type 6

Type 6 gets it’s name because it combines type 1, type 2 and type 3 (1+2+3=6). This is a complex hybrid transition technique, it gives the best of both worlds. It shows an employee’s current department, but also includes columns tracking their original department and a timestamp log of when they transitioned.

Choosing the right type

SCDs have been part of data warehousing for a long time. The choice of which to pick heavily depends on what question you are trying to answer or the goal of the data.

• SCD Type 1 when history genuinely doesn’t matter
• SCD Type 2 when you need full row-level versioning in a single table
• SCD Type 3 when you only care about the most recent change

If you have just been overwriting data then now is a time to step back and think if that is the best policy or you have to adapt one one of the SCD techniques.

It is very important to thoroughly think of the value of data, both short-term and long-term, before deciding in which is the best technique for changing data.

Originally published at https://braeson.hashnode.dev on July 5, 2026.