When a customer moves or a product gets recategorized, what happens to your historical reports? This lesson explains how Slowly Changing Dimensions work — and how to choose between Type 1, Type 2, and Type 3 to keep your analytics warehouse honest.
Imagine you're an analytics engineer at a retail company, and your sales team just flagged a problem: the regional revenue report shows that your top-performing salesperson, Maria Gonzalez, has virtually no sales in the Northeast region — but her manager swears she spent two years there before transferring to the West Coast six months ago. The data isn't wrong. Maria did move to the West Coast. The problem is that when her record was updated, her old region was simply overwritten. Every historical sale now looks like a West Coast sale, and two years of accurate regional reporting just vanished.
This is the central challenge that Slowly Changing Dimensions (SCDs) solve. In data warehousing, a dimension is a table that describes who or what is involved in a business event — customers, products, employees, locations. These attributes don't change frequently, but they do change: customers move, products get recategorized, employees transfer. How you handle those changes determines whether your historical analysis is trustworthy or quietly broken.
By the end of this lesson, you'll understand the three most common SCD strategies, know exactly when to apply each one, and be able to write the SQL to implement them. This is foundational knowledge for anyone building an analytics warehouse — and it pairs directly with the dimensional modeling concepts you'll find in Data Modeling for Analytics: Dimensional Modeling vs One Big Table.
What you'll learn:
You should be comfortable reading SQL (SELECT, JOIN, WHERE clauses), and you should have a basic understanding of what a data warehouse is and how fact and dimension tables relate to each other. If you're new to dimensional modeling concepts like star schemas, take a few minutes to read Understanding Data Warehouse Schemas: Star Schema vs Snowflake Schema for Analytics first — it'll make the examples here click faster.
A dimension in a data warehouse describes the context around a measurable business event. When a customer places an order, the order is the fact — the thing that happened. The customer, product, and store are dimensions — they describe who bought what, where.
Most dimension attributes are relatively stable. A customer's name doesn't change often. A product's category is usually consistent. But "relatively stable" is not the same as "never changes." Over months and years, things shift. Customers move. Products get rebranded. Employees get promoted. These are slowly changing dimensions — dimensions where the descriptive attributes evolve over time, just not rapidly.
The problem is that your fact table records events using a reference to the dimension at the time the event happened. If you later update the dimension table to reflect new information, you risk corrupting the historical meaning of those fact rows. The question every data engineer has to answer is: when a dimension attribute changes, what should you do with the old value?
There are three main answers, each representing a different philosophy:
Let's build each one from the ground up.
Type 1 is the simplest approach: when an attribute changes, update the row in place. The old value is gone.
Type 1 is appropriate when the old value was simply wrong — a data entry error, a misspelling, a misclassification that nobody should ever analyze against. If a customer's email was entered incorrectly and you fix it, you don't need a historical record of the bad email. Overwrite it.
It's also appropriate for attributes that have no analytical relevance to the past. If your product catalog adds a new column called supplier_code that wasn't previously tracked, backfilling and overwriting is reasonable because there's no prior analysis to preserve.
Suppose you have a dim_customers table:
-- dim_customers (before update)
customer_id | customer_name | email | city
---------------------------------------------------------------------------
1001 | Maria Gonzalez | m.gonzalez@email.com | Boston
1002 | James Park | jpark@workmail.com | Chicago
Maria calls in to update her email address. With Type 1, you simply run an UPDATE:
UPDATE dim_customers
SET email = 'maria.gonzalez@newemail.com'
WHERE customer_id = 1001;
-- dim_customers (after update)
customer_id | customer_name | email | city
-------------------------------------------------------------------------------
1001 | Maria Gonzalez | maria.gonzalez@newemail.com | Boston
1002 | James Park | jpark@workmail.com | Chicago
The old email is gone. Any fact table row that references customer_id = 1001 now joins to the new email. If you ran a campaign report from last year, it would show Maria's current email — which may be fine, because the old email is irrelevant to that report anyway.
Warning: Type 1 is a one-way door. Once you overwrite an attribute, you cannot reconstruct the historical value from the warehouse alone. Only use Type 1 when you're genuinely certain that historical analysis will never need the old value.
The scenario from our introduction — Maria's region being overwritten — is exactly what happens when someone applies Type 1 to an attribute that does have historical analytical relevance. The city an employee worked in, the price tier of a product, the segment a customer belonged to — these are attributes where analysts legitimately need to ask "what was it then?" Type 1 silently destroys that ability.
Type 2 is the approach most analytics engineers reach for when history matters. Instead of overwriting the changed row, you close out the old row with an end date and insert a new row representing the new state of the dimension member. This gives you a complete timeline.
Type 2 requires a few additional columns beyond what you'd have in a simple dimension table:
valid_from — the date this version of the record became activevalid_to — the date this version expired (NULL or a far-future date means "currently active")is_current — a boolean flag for easy filtering (optional but very convenient)Let's revisit Maria's situation with Type 2. Her initial record when she joined:
-- dim_employees (initial state)
surrogate_key | employee_id | employee_name | region | valid_from | valid_to | is_current
---------------------------------------------------------------------------------------------------
101 | EMP-5501 | Maria Gonzalez | Northeast | 2021-03-01 | 9999-12-31 | TRUE
The valid_to date of 9999-12-31 is a common convention meaning "this record is currently active." When Maria transfers to the West Coast, you do two things:
Step 1: Update the existing row to mark it as expired.
UPDATE dim_employees
SET
valid_to = '2023-06-30',
is_current = FALSE
WHERE surrogate_key = 101;
Step 2: Insert a new row for the new state.
INSERT INTO dim_employees
(surrogate_key, employee_id, employee_name, region, valid_from, valid_to, is_current)
VALUES
(102, 'EMP-5501', 'Maria Gonzalez', 'West Coast', '2023-07-01', '9999-12-31', TRUE);
Now the table looks like this:
-- dim_employees (after Maria's transfer)
surrogate_key | employee_id | employee_name | region | valid_from | valid_to | is_current
----------------------------------------------------------------------------------------------------
101 | EMP-5501 | Maria Gonzalez | Northeast | 2021-03-01 | 2023-06-30 | FALSE
102 | EMP-5501 | Maria Gonzalez | West Coast | 2023-07-01 | 9999-12-31 | TRUE
The crucial insight is that your fact table must store the surrogate key, not the business key (employee_id). When a sale is recorded, the ETL process looks up which version of the employee dimension was active at that point in time and stores that surrogate key.
A sale made by Maria in February 2022 (when she was in the Northeast) would store surrogate_key = 101. A sale made in August 2023 (after her transfer) would store surrogate_key = 102. The regional attribution is baked in.
Here's how a historical sales report joins correctly:
SELECT
e.region,
SUM(f.sale_amount) AS total_sales
FROM fact_sales f
JOIN dim_employees e
ON f.employee_surrogate_key = e.surrogate_key
GROUP BY e.region;
This query will correctly attribute Maria's pre-transfer sales to Northeast and her post-transfer sales to West Coast — no manual date filtering required.
Key insight: In a Type 2 dimension, the surrogate key is what makes time travel possible. Each version of a dimension record gets its own surrogate key, and the fact table's foreign key points directly to the right version. This is why surrogate keys are non-negotiable in Type 2 implementations.
If you want to see only the current state of all employees (for operational purposes, not historical analysis), use the is_current flag:
SELECT *
FROM dim_employees
WHERE is_current = TRUE;
Or filter by date if you want a specific point in time:
-- What did the employee dimension look like on 2022-09-15?
SELECT *
FROM dim_employees
WHERE '2022-09-15' BETWEEN valid_from AND valid_to;
Type 2 is powerful but comes with real overhead. The dimension table grows over time as history accumulates. Pipelines become more complex because you need to correctly identify which version to link to new fact records. And if your source system sends you a changed record without a reliable change date, you have to make assumptions about when the change occurred.
These operational realities are worth understanding — especially as your models scale. The article on Slowly Changing Dimensions in Practice: Handling Historical Data Changes in Your Warehouse goes deep on the engineering patterns for managing this at scale.
Tip: Tools like dbt have built-in snapshot functionality that automates Type 2 SCD logic. Instead of writing manual UPDATE/INSERT pairs, you define a
dbt snapshotblock, point it at your source data, and dbt handles thevalid_from,valid_to, andis_currentcolumns for you. If you're using dbt, check out dbt Fundamentals: Transform Data with SQL in Your Warehouse to understand how to integrate snapshots into your project structure.
Type 3 is a middle-ground approach. Instead of adding new rows (Type 2), you add new columns to store the previous value of an attribute alongside the current one. It's simpler to query than Type 2, but it only preserves one level of history — the current value and the most recent previous value.
Type 3 is useful when:
A classic use case is a company reorganization. Before the reorg, analysts want to see performance by the new department structure. But they also want a quick way to compare against the old structure — without having to do complex date-range joins.
Suppose your product table tracks a sales category:
-- dim_products (Type 3, initial state)
product_id | product_name | current_category | previous_category | category_change_date
-----------------------------------------------------------------------------------------------
P-2201 | Wireless Headphones | Consumer Audio | NULL | NULL
P-2202 | USB-C Hub | Accessories | NULL | NULL
When the company rebrands "Consumer Audio" to "Personal Electronics":
UPDATE dim_products
SET
previous_category = current_category,
current_category = 'Personal Electronics',
category_change_date = '2024-01-01'
WHERE current_category = 'Consumer Audio';
-- dim_products (after rebrand)
product_id | product_name | current_category | previous_category | category_change_date
--------------------------------------------------------------------------------------------------
P-2201 | Wireless Headphones | Personal Electronics| Consumer Audio | 2024-01-01
P-2202 | USB-C Hub | Accessories | NULL | NULL
Now analysts can compare old vs. new category performance with a simple query:
SELECT
p.current_category,
p.previous_category,
SUM(f.revenue) AS total_revenue
FROM fact_sales f
JOIN dim_products p ON f.product_id = p.product_id
WHERE f.sale_date >= '2023-01-01'
GROUP BY p.current_category, p.previous_category;
This is much simpler to write than the date-range joins required by Type 2.
Warning: Type 3 only stores one previous value. If the category changes again — say from "Personal Electronics" to "Smart Devices" — you'll overwrite
previous_categorywith "Personal Electronics" and permanently lose "Consumer Audio." If multiple changes are plausible over the data's lifetime, Type 2 is almost always the right choice.
It helps to see all three approaches summarized against the same decision criteria:
| Criterion | Type 1 | Type 2 | Type 3 |
|---|---|---|---|
| History preserved | None | Full | One prior value |
| Table growth | Static | Grows with each change | Static (new columns only) |
| Query complexity | Simple | Moderate (date-range joins or surrogate key lookups) | Simple |
| Surrogate key required | No | Yes | No |
| Use when... | Error corrections or irrelevant history | Accurate historical analysis is critical | One "before vs. after" comparison needed |
Note: In practice, most mature data warehouses use different SCD types for different attributes within the same dimension. A customer dimension might use Type 1 for
cityandcustomer_segment(historical analysis matters), and Type 3 for nothing at all. Mixing types within a single table is valid and often the right design.
Let's put this all together. You'll work through a scenario using SQL you can run in any cloud warehouse (Snowflake, BigQuery, or Redshift).
Scenario: You're building a dim_customers table for an e-commerce company. Customers have: customer_id (business key), full_name, email, city, and loyalty_tier (Bronze, Silver, Gold).
Business rules:
email corrections are Type 1 (overwrite)city and loyalty_tier changes are Type 2 (preserve history)Step 1: Create the dimension table with Type 2 columns.
CREATE TABLE dim_customers (
surrogate_key INT PRIMARY KEY,
customer_id VARCHAR(20), -- business key
full_name VARCHAR(100),
email VARCHAR(150),
city VARCHAR(100),
loyalty_tier VARCHAR(20),
valid_from DATE,
valid_to DATE,
is_current BOOLEAN
);
Step 2: Insert a starting record for a new customer.
INSERT INTO dim_customers VALUES
(1, 'CUST-4400', 'Priya Nair', 'priya.nair@gmail.com', 'Austin', 'Bronze', '2023-01-15', '9999-12-31', TRUE);
Step 3: Priya moves from Austin to Denver on March 1, 2024. Apply a Type 2 change.
-- Close the existing record
UPDATE dim_customers
SET valid_to = '2024-02-29', is_current = FALSE
WHERE customer_id = 'CUST-4400' AND is_current = TRUE;
-- Insert the new version
INSERT INTO dim_customers VALUES
(2, 'CUST-4400', 'Priya Nair', 'priya.nair@gmail.com', 'Denver', 'Bronze', '2024-03-01', '9999-12-31', TRUE);
Step 4: Priya's email was entered wrong — it should be priya.nair@outlook.com. Apply a Type 1 correction to both rows (we want the correct email everywhere, since it was simply wrong).
UPDATE dim_customers
SET email = 'priya.nair@outlook.com'
WHERE customer_id = 'CUST-4400';
Step 5: Write a query to see Priya's full history.
SELECT
surrogate_key,
city,
loyalty_tier,
email,
valid_from,
valid_to,
is_current
FROM dim_customers
WHERE customer_id = 'CUST-4400'
ORDER BY valid_from;
You should see two rows: one for Austin (expired) and one for Denver (current), both with the corrected email.
Mistake 1: Using the business key as the foreign key in facts
If your fact table stores customer_id instead of surrogate_key, your joins won't distinguish between versions. Any join will pick up all rows for that customer, or you'll have to add date filtering logic everywhere. Always use the surrogate key in fact tables for Type 2 dimensions.
Mistake 2: Applying Type 1 to historically significant attributes This is the Maria Gonzalez problem from the introduction. Before deciding on Type 1, ask: "If I overwrite this, can any historical report produce a misleading result?" If yes, Type 1 is wrong for that attribute.
Mistake 3: Forgetting to close the old record before inserting the new one
If you insert a new row without expiring the old one, you'll end up with two "current" records for the same business entity. Queries that filter is_current = TRUE will return duplicate rows. Always UPDATE before you INSERT in a Type 2 change.
Mistake 4: Assuming source systems tell you when things changed Often they don't. A nightly sync might deliver a changed record with no timestamp for when the change happened. This is where Change Data Capture becomes valuable — CDC tracks changes at the database level, giving you accurate timestamps for SCD processing.
Mistake 5: Using Type 3 for attributes that change more than once Type 3 feels elegant until it isn't. After the second change to an attribute, you've already lost a generation of history. If there's any reasonable chance an attribute will change multiple times over the data's lifespan, use Type 2 from the start.
Tip: When in doubt between Type 1 and Type 2, choose Type 2. Storage is cheap. Lost history is gone forever. It's far easier to simplify a query against a Type 2 dimension than to reconstruct history that was never captured.
Slowly changing dimensions are one of those foundational concepts that seem theoretical until you encounter a broken report — and then they suddenly feel very urgent. Here's what you've learned:
The right choice depends on how your analysts will query the data, how often the attribute changes, and how important historical accuracy is to the business.
Where to go from here:
Getting SCDs right is one of the most tangible ways an analytics engineer improves the trustworthiness of a data warehouse. Once your stakeholders know that historical reports are stable and accurate — that changing a customer's city won't rewrite the past — they stop second-guessing the data. That credibility is worth every extra surrogate key.