Slowly Changing Dimensions (SCD) in Data Warehousing | Types, Examples & Use Cases

Slowly Changing Dimensions (SCD)

Data in a business is not always static. Customer addresses, employee departments, product prices, and supplier information may change over time. In a data warehouse, it is important to decide how these changes should be handled.

Slowly Changing Dimensions (SCD) is a data warehousing concept used to manage changes in dimension data while preserving historical information when required.

For example, if a customer moves to a new city, you may want to do the following:

• Update the city with the new value
• Keep both the old and new city records
• Store only limited history

SCD provides different techniques to handle these scenarios.

What is a Slowly Changing Dimension?

A Slowly Changing Dimension (SCD) is a dimension whose attribute values change occasionally rather than frequently.

Examples:

• Customer Address
• Employee Department
• Product Category
• Supplier Location

Instead of changing every transaction, these values change only from time to time.

Why is SCD Important?

• Maintains historical records
• Tracks business changes over time
• Improves reporting accuracy
• Supports business intelligence and auditing

Without SCD, historical reports may become inaccurate because old values are overwritten.

Types of Slowly Changing Dimensions

The most commonly used Slowly Changing Dimension types are:

• Type 0
• Type 1
• Type 2
• Type 3

SCD Type 0 – Fixed Dimension

Type 0 does not allow updates. Once the data is stored, it remains unchanged.

Example

Employee	Joining Date
John	10-Jan-2022

Use Cases

• Date of Birth
• Joining Date
• Product Creation Date

SCD Type 1 – Overwrite

Type 1 replaces the old value with the new value. Historical information is not maintained.

Before Update

Customer	City
John	Chennai

After Update

Customer	City
John	Hyderabad

Use Cases

• Correcting spelling mistakes
• Fixing incorrect values
• Updating non-historical information

SCD Type 2 – Maintain History

Type 2 creates a new record whenever a change occurs, preserving the previous record.

Customer ID	Customer	City	Start Date	End Date	Current
101	John	Chennai	01-Jan-2023	31-May-2024	No
101	John	Hyderabad	01-Jun-2024	NULL	Yes

Use Cases

• Customer address history
• Employee department history
• Product price history

SCD Type 3 – Limited History

Type 3 stores the previous value in an additional column. Only limited history is maintained.

Customer	Current City	Previous City
John	Hyderabad	Chennai

Use Cases

• Comparing previous and current values
• Simple reporting scenarios

Difference Between SCD Types

Type	History Maintained	Old Value	New Record
Type 0	No Changes Allowed	Preserved	No
Type 1	No	Overwritten	No
Type 2	Yes	Preserved	Yes
Type 3	Limited	Stored in another column	No

Which SCD Type is Most Common?

Among all slowly changing dimension types, Type 2 is the most widely used because it preserves complete historical information and supports accurate reporting over time.

Best Practices

• Use Type 1 for correcting incorrect data.
• Use Type 2 when historical tracking is required.
• Use Type 3 when only the previous value needs to be retained.
• Avoid storing unnecessary historical data unless required.

Conclusion

Slowly Changing Dimensions (SCD) help manage changes in dimension data while preserving the level of history required by the business.

Type 0 keeps data unchanged.
Type 1 overwrites existing values.
Type 2 preserves complete history by creating new records.
Type 3 stores limited historical information.

Selecting the appropriate SCD type ensures accurate reporting, reliable historical analysis, and effective data warehouse design.