Type 2 Slowly Changing Dimension

Data Warehousing > Concepts > Type 2 Slowly Changing Dimension

In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key.

In our example, this is the table entry when Christina first enters into the system:

Customer Key Name State Create_Date Current_Flag
1001 Christina Illinois 2018-05-01 Yes

After Christina moved from Illinois to California, we add the new information as a new row into the table:

Name State Create_Date Current_Flag
1001 Christina Illinois 2018-05-01 No
1001 Christina California 2020-01-15 Yes


- This allows us to accurately keep all historical information.


- This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.

- This complicates the ETL process.


Seen less often than Type 4, but more than other types.

When to use Type 2:

Type 2 slowly changing dimension should be used when it is necessary for the data warehouse to track historical changes, and you are not concerned that multiple rows in the dimension table may represent a single element. For example, more than two rows in the Customer table may refer to the same customer.

Copyright © 2024   1keydata.com   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.