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.
In such situations, you need to assess:
Do I overwrite the old data, or preserve history?
Handling SCDs correctly is critical for accurate historical reporting, trend analysis, and regulatory compliance.
What Is a Dimension Table?
A dimension table stores descriptive attributes (e.g., customer name, product category) that relate to facts in a fact table (e.g., sales, transactions).
Slowly changing dimensions require a strategy to manage data that changes gradually but must be tracked.
Types of Slowly Changing Dimensions (SCD)
| Type | Description | Use Case Example |
| Type 0 | No change is allowed (read-only) | Immutable attributes like Date of Birth |
| Type 1 | Overwrite the old value | Fixing a typo in the product name |
| Type 2 | Keep history by inserting a new row | Tracking customer address over time |
| Type 3 | Keep limited history in the same row (e.g., old & new) | Keep last and current region |
Implementing SCD Type 2 – Most Common Approach
Type 2 is the most frequently used for auditability and historical accuracy.
You maintain multiple versions of the same entity by adding:
- A surrogate key (unique ID for each version)
- Valid from and valid to timestamps
- A current flag (true/false)
Example: SCD Type 2 in SQL
Let’s say we’re tracking changes in an employee’s department.
-- Step 1: Check if any changes
SELECT * FROM employee_dim
WHERE employee_id = 123 AND current_flag = TRUE;
-- Step 2: If changed, mark old row as inactive
UPDATE employee_dim
SET current_flag = FALSE, valid_to = CURRENT_DATE
WHERE employee_id = 123 AND current_flag = TRUE;
-- Step 3: Insert new version of the row
INSERT INTO employee_dim ( employee_id, name, department, valid_from, valid_to, current_flag
)
VALUES ( 123, 'John Doe', 'Marketing', CURRENT_DATE, NULL, TRUE
);This preserves the full history of department changes over time.
SCD Handling in ETL Tools
| Tool | Feature |
| dbt | dbt_scd macros (custom or via packages) |
| Informatica | Out-of-the-box SCD management |
| Talend | Built-in SCD components |
| Airflow + SQL | Fully manual implementation |
Many enterprise-grade tools like Informatica offer built-in support for Type 1 and Type 2 SCDs. These features become even more effective when paired with platforms like Google BigQuery, a leading solution for serverless data warehousing and scalable ETL workflows.
Final Takeaway
Handling slowly changing dimensions is essential when:
- You need historical tracking
- You build dashboards comparing current vs. past attributes
- Your business must comply with audit requirements
Use Type 2 SCD to maintain history. Use Type 1 for simple corrections. And always design your ETL pipelines with the appropriate SCD strategy based on the business need.
