Slowly changing dimension is a concept in data warehousing, where the attribute values in a dimension table change over-time but gradually. A real-world example of this can be how a customer could want to change their address, or an employee might need to switch departments.<\/p>\n\n\n\n
In such situations, you need to assess:<\/p>\n\n\n\n
Do I overwrite the old data, or preserve history?<\/strong><\/p>\n\n\n\n Handling SCDs correctly is critical for accurate historical reporting, trend analysis, and regulatory compliance.<\/p>\n\n\n\n A dimension table<\/strong> stores descriptive attributes (e.g., customer name, product category) that relate to facts<\/strong> in a fact table (e.g., sales, transactions).<\/p>\n\n\n\n Slowly changing dimensions require a strategy to manage data that changes gradually<\/strong> but must be tracked.<\/p>\n\n\n\n Type 2 is the most frequently used for auditability and historical accuracy<\/strong>.<\/p>\n\n\n\n You maintain multiple versions of the same entity by adding:<\/p>\n\n\n\n Let\u2019s say we\u2019re tracking changes in an employee\u2019s department.<\/p>\n\n\n\n This preserves the full history of department changes over time.<\/p>\n\n\n\nWhat Is a Dimension Table?<\/h2>\n\n\n\n
Types of Slowly Changing Dimensions (SCD)<\/h3>\n\n\n\n
Type<\/strong><\/td> Description<\/strong><\/td> Use Case Example<\/strong><\/td><\/tr> Type 0<\/strong><\/td> No change is allowed (read-only)<\/td> Immutable attributes like Date of Birth<\/td><\/tr> Type 1<\/strong><\/td> Overwrite the old value<\/td> Fixing a typo in the product name<\/td><\/tr> Type 2<\/strong><\/td> Keep history by inserting a new row<\/td> Tracking customer address over time<\/td><\/tr> Type 3<\/strong><\/td> Keep limited history in the same row (e.g., old & new)<\/td> Keep last and current region<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n Implementing SCD Type 2 \u2013 Most Common Approach<\/h3>\n\n\n\n
\n
Example: SCD Type 2 in SQL<\/h3>\n\n\n\n
-- Step 1: Check if any changes\nSELECT * FROM employee_dim\nWHERE employee_id = 123 AND current_flag = TRUE;\n\n-- Step 2: If changed, mark old row as inactive\nUPDATE employee_dim\nSET current_flag = FALSE,\n valid_to = CURRENT_DATE\nWHERE employee_id = 123 AND current_flag = TRUE;\n\n-- Step 3: Insert new version of the row\nINSERT INTO employee_dim (\n employee_id, name, department, valid_from, valid_to, current_flag\n)\nVALUES (\n 123, 'John Doe', 'Marketing',\n CURRENT_DATE, NULL, TRUE\n);<\/code><\/pre>\n\n\n\nSCD Handling in ETL Tools<\/h3>\n\n\n\n
Tool<\/strong><\/td> Feature<\/strong><\/td><\/tr> dbt<\/strong><\/td> dbt_scd macros (custom or via packages)<\/td><\/tr> Informatica<\/strong><\/td> Out-of-the-box SCD management<\/td><\/tr> Talend<\/strong><\/td> Built-in SCD components<\/td><\/tr> Airflow + SQL<\/strong><\/td> Fully manual implementation<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n