AdBlock Detected!
Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.
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 |
Advantages:
- This allows us to accurately keep all historical information.
Disadvantages:
- 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.
Usage:
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.
Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.