6 Different Types of Slowly Changing Dimensions and How to Apply Them?
Learn How Best to Apply These History Maintenance Methods Here
Slowly Changing Dimensions (SCD from now on!) is not a new concept; in fact, I remember as a BI developer many years ago, this was a key concept I was taught.
When creating a data warehouse, it is paramount that certain attributes on its management are agreed upon from the offset.
History maintenance is one of them. Whether you want to overwrite a column or create a new record profoundly impacts your Data Pipelines.
Let’s dive into learning about SCD and their different applications.
#1 SCD Type 0 — Dimension is never updated
#2 SCD Type 1 — Dimension is overwritten
#3 SCD Type 2 — Maintain all the old records for the dimension by versioning the row
#4 SCD Type 3 — Maintain an old attribute but in the same row (by adding a previous value column)
#5 SCD Type 4 — Maintain current record and older record in two different tables
#6 SCD Type 6 — Type 6 is a hybrid of 1,2,3 i.e. 1+2+3 = 6
Type 0,1 & 2 are the most common.