DE Slowly Changing Dimensions
Real-world data changes over time. Customers move to new cities. Products change categories. Employees transfer to different departments. In an operational database, the current value is all that matters. In a data warehouse, history matters too. An analyst asking "what region was this customer in when they made this purchase in 2022?" needs the answer from 2022, not from today. Slowly Changing Dimensions (SCDs) define the patterns data engineers use to handle dimension changes in a data warehouse.
What Is a Slowly Changing Dimension
A slowly changing dimension (SCD) is a dimension table where attribute values change infrequently over time. "Slowly" does not mean the changes happen rarely — it means changes happen on an irregular, non-daily basis. A customer changes their city once in several years. A product changes its category twice in its lifetime. These changes are slow compared to the constant stream of new transaction records in a fact table.
Why It Is a Problem
Without a strategy, a pipeline simply overwrites the old value with the new one when a dimension attribute changes. The history disappears. Any historical analysis that depends on the old value now returns incorrect results. Reports showing "sales by customer city in 2022" would incorrectly use the customer's current city, not their city in 2022.
The Employee File Analogy
A human resources department keeps a file for each employee. An employee starts in the Mumbai office, then transfers to Bangalore in 2023. If HR throws away the old form and replaces it with a new one showing Bangalore, there is no record that the employee was ever in Mumbai. Historical performance reviews, payroll records, and project assignments no longer match the actual location at the time. SCD strategies are the equivalent of filing both the old and new forms with clear dates on each.
SCD Type 0: Never Change
Type 0 keeps the original value forever and ignores any updates. Use this only for attributes that genuinely should never change in the warehouse — like a customer's birth date or the date a product was first launched. If the source system reports a different value, the warehouse ignores it.
SCD Type 1: Overwrite
Type 1 simply overwrites the old value with the new one. No history is kept. This is the simplest approach and the right choice when historical accuracy for that attribute does not matter.
Before change: +----------+------+-------+ | cust_key | name | city | +----------+------+-------+ | C001 | Sara | Delhi | +----------+------+-------+ After Sara moves to Mumbai (Type 1): +----------+------+--------+ | cust_key | name | city | +----------+------+--------+ | C001 | Sara | Mumbai | +----------+------+--------+ History lost. Old "Delhi" value is gone.
When to Use Type 1
Use Type 1 when the attribute represents the current state that all analysis should use, regardless of when the transaction happened. Correcting a data entry mistake — for example, fixing a misspelled name — is a typical Type 1 use case.
SCD Type 2: Add a New Row
Type 2 preserves full history by inserting a new row for each change. The old row remains with an end date. The new row receives a new surrogate key and a start date. A flag or date column marks which row is the currently active record.
Before change: +----------+------+-------+------------+-----------+--------+ | cust_key | name | city | valid_from | valid_to | active | +----------+------+-------+------------+-----------+--------+ | 1 | Sara | Delhi | 2020-01-01 | 9999-12-31| Y | +----------+------+-------+------------+-----------+--------+ After Sara moves to Mumbai (Type 2): +----------+------+--------+------------+------------+--------+ | cust_key | name | city | valid_from | valid_to | active | +----------+------+--------+------------+------------+--------+ | 1 | Sara | Delhi | 2020-01-01 | 2024-03-15 | N | | 2 | Sara | Mumbai | 2024-03-16 | 9999-12-31 | Y | +----------+------+--------+------------+------------+--------+
A fact table row from 2022 references cust_key 1 (Delhi). A fact table row from 2024 references cust_key 2 (Mumbai). Historical queries return the correct city for each time period automatically.
When to Use Type 2
Use Type 2 whenever historical accuracy matters for analysis. This is the most commonly used SCD type in data warehouses. Customer location, employee department, product pricing tier, and sales territory are all typical Type 2 candidates.
SCD Type 3: Add a New Column
Type 3 adds a new column to store the previous value alongside the current value. It preserves one level of history — useful when analysts need to compare "where was this customer before their last move" without needing full multi-year history.
+----------+------+-------------+--------+ | cust_key | name | prev_city | city | +----------+------+-------------+--------+ | C001 | Sara | Delhi | Mumbai | +----------+------+-------------+--------+
When to Use Type 3
Use Type 3 when only the most recent change matters, not a full audit trail. It is simpler than Type 2 but limited to tracking one level back. If the attribute changes a third time, Type 3 loses the original value.
SCD Type 6: Combined Approach
Type 6 combines Types 1, 2, and 3. Each row gets a new surrogate key on change (Type 2), the current value overwrites on all historical rows (Type 1), and a previous value column stores the last state (Type 3). This hybrid lets analysts query either historical or current context from the same table without complex logic.
Choosing the Right SCD Type
Need | SCD Type ------------------------------------------|---------- Never store history, always overwrite | Type 1 Full history needed for all past queries | Type 2 Only most recent previous state needed | Type 3 Full history + current value on all rows | Type 6 Attribute genuinely never changes | Type 0
Summary
Slowly Changing Dimensions solve the problem of tracking how dimension attributes change over time in a data warehouse. Type 1 overwrites without history. Type 2 preserves full history with new rows and validity dates. Type 3 tracks one level of previous value. Data engineers choose the right SCD type based on whether historical accuracy matters for that attribute and how many levels of history the business needs.