Slowly Changing Dimensions (SCDs)/Capture Changed data
Peter Wainaina
Data Engineer | Pyspark | Airflow | AWS Certified Solutions Architect |Datacamp Data Engineer Assessment Writer
SCDs refer to a data warehousing concept where dimensions contain both current AND historical data. This is a common approach to maintaining historical details in a data warehouse but their use will depend on both the business needs and purpose of the data warehouse.
At a basic level, the dimension will change slowly over time rather than at a scheduled interval — there is no fixed pattern in the frequency of the dimension changes.
Type of SCDs
1) TYPE 0-Passive method. Values remain as they were at the time the dimension record was first inserted.
2) TYPE 1-No need to store historical data in the dimension table.
3) TYPE 2 -No need to delete old record.
4) TYPE 3 -History added as new column.
5) TYPE 4 -A new dimension will be added.
6) Type 6 -Combination of Type 2 and Type 3.