SLOW CHANGING DIMENSIONS
Mustafa Qizilbash
Author, Data & AI Practitioner & CDMP Certified, Innovator of Four 4s Formula, DAC Architecture, PVP Approach
Slow Changing Dimensions is also known as SCD and is used in Data Warehouse concept. Folks in Data World are very much aware of this term.
Before we understand SCD, one must understand Fact and Dimensions. In dimensional Data Warehouse or in a Data Mart (explained in separate topic), there are two types of schema(s) i.e., Star and Snowflake Schema(s). Both are build based on Facts and Dimensions.
●???????Facts: This table keeps the transaction’s lowest level of data which can be numeric or non-numeric like sales numbers, number of clicks on social media portal, number of searches in google etc. It’s mainly loaded in append mode, means rows always gets inserted. This table is very fast-moving table and rows keeps coming in. There are hardly any organization which update and delete data from Fact Tables.
●???????Dimension: This table on the other hand keeps data element by which Fact can be analyzed e.g., sale by time, sales by region, sales by the product etc. So, Time, Regions, Products etc., are dimension. In contrast with Fact table, frequency of rows coming in these tables are very low. For Example, once we have loaded regions then when often regions will change ??. Once Time is loaded, it will never change. At the same time, Products definitions can change. A company today have 100 products, in future it can have more. Then attributes of one product can change in future a well, so there are three ways to keep the history of changed in Dimensions which are handled by principles managed by SLOW CHANGING DIMENSIONS (SCD).
Three types of SCD(s)
领英推荐
1.??????SCD 1: This type keeps only the latest status e.g., if product code was 001 and it changed to 002 then 003 and then 004. It will keep 004 only.
2.??????SCD 2: This type keeps complete history of every change e.g., if product code was 001 and it changed to 002 then 003 and then 004. It will keep all changes i.e., 001, 002, 003 and 004.
3.??????SCD 3: This type keeps first and latest status of changes e.g., if product code was 001 and it changed to 002 then 003 and then 004. It will keep 001 and 004 only.
Cheers.