Slowly Changing Dimensions

Data Warehousing > Concepts > Slowly Changing Dimensions

The "Slowly Changing Dimension" problem is a common one particular to data warehousing. In a nutshell, this applies to cases where the attribute for a record varies over time. We give an example below:

Christina became a customer with ABC Inc. in 2018. She first lived in Chicago, Illinois. So, the original entry in the customer lookup table has the following record:

Customer Key Name State
1001 Christina Illinois

At a later date, she moved to Los Angeles, California on January, 2020. How should ABC Inc. now modify its customer table to reflect this change? This is the "Slowly Changing Dimension" problem.

There are several ways to solve this type of problem, and they are categorized as follows:

Type 0: The record never changes. In this way, changes are not captured at all.

Type 1: The new record replaces the original record. No trace of the old record exists.

Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.

Type 3: The original record is modified to reflect the change.

Type 4: The dimension table is updated to the latest value when the change occurs. At the same time, there is a history table that records the change.

In addition, there are Type 5, Type 6, and Type 7 slowly changing dimensions. These are used rarely in practice and hence we do not describe them here except to mention that they do exist.

We next take a look at each of the scenarios and how the data model and the data looks like for each of them.

Copyright © 2024   All Rights Reserved     Privacy Policy   About   Contact

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.