AdBlock Detected!
Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.
Type 4 Slowly Changing Dimension |
In Type 4 Slowly Changing Dimension, the dimension table always holds the latest data. At the same time, there is a history table that tracks the change. The history table will have some of the same columns as the dimension table, but there will be some other columns, such as update_time, that are used to track the changes.
In our example, recall we originally have the following table:
| Customer Key | Name | State |
| 1001 | Christina | Illinois |
In our example, recall we originally have the following table:
Customer Table
| Customer Key | Name | State |
| 1001 | Christina | Illinois |
Customer History
| Customer Key | Name | State | Update_Date |
| 1001 | Christina | Illinois | 2018-05-01 |
After the customer moves to Los Angeles, California on January, 2003, the two tables now become:
Customer Table
| Customer Key | Name | State |
| 1001 | Christina | California |
Customer History
| Customer Key | Name | State | Update_Date |
| 1001 | Christina | Illinois | 2018-05-01 |
| 1001 | Christina | California | 2020-01-15 |
Advantages:
- This allows each row in the dimension table to represent each data element
- This retains all change history.
Disadvantages:
- Higher data storage requirements from the introduction of the history table.
- More complex ETL is needed to update / append the tables when a change occurs.
Usage:
This is one of the most common ways to handle slowly changing dimensions.
When to use Type 4:
Ideal when you want to make sure you are able to track all change history, and you want to make sure each row in the dimension table represent each element (for example, each row in the Customer table actually represents a customer).
Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.