In this post we are going to cover slowly changing dimensions, and the four SCD types every interviewer expects you to know: Type 0, 1, 2 and 3.
A slowly changing dimension (SCD) is a dimension whose attributes change over time, but not often. A customer moves to a new city, a product changes category, an employee changes department. The SCD type is simply the rule for what we do with the old value when the new one arrives.
We will take one customer who moves, and handle that move as Type 1, Type 2 and Type 3 in SQL, so we can see exactly what each type keeps and what it loses.
Question 32 of the Real DWH Question and Answers series on TechBrothersIT.
The interview answer
A slowly changing dimension (SCD) is a dimension whose attributes change over time, like a customer's city. Type 0 keeps the original value. Type 1 overwrites it, so history is lost. Type 2 adds a new row with a new surrogate key and validity dates, keeping full history. Type 3 adds a previous-value column, keeping one level of history.
The change: a customer moves
Here is the change we will use for every type. Customer C101, Sara Khan, lives in Toronto and has sales in January and February. On March 20 she moves to Vancouver, and she buys again in April.
Now the business question: when we report sales by city, should Sara's January and February sales show Toronto or Vancouver?
There is no single right answer. It depends on what the business needs, and each SCD type gives a different answer.
Type 0: retain the original
Type 0 is the simplest: the value never changes after it is first loaded. We use it for attributes that should stay exactly as they were first recorded, like a date of birth or the original signup channel. There is no SQL to write, because the load just never updates these columns.
Let's create some sample data
We store the same two customers three ways, one table for each type we will run: dim_customer_t1, dim_customer_t2 with validity dates and a current flag, and dim_customer_t3 with a prev_city column.
DROP TABLE IF EXISTS dim_customer_t1;
DROP TABLE IF EXISTS dim_customer_t2;
DROP TABLE IF EXISTS dim_customer_t3;
-- the same customers, stored three ways
CREATE TABLE dim_customer_t1 (customer_key INT PRIMARY KEY, customer_id VARCHAR(10), name VARCHAR(50), city VARCHAR(50));
INSERT INTO dim_customer_t1 VALUES (1, 'C101', 'Sara Khan', 'Toronto'), (2, 'C102', 'Omar Ali', 'Dallas');
CREATE TABLE dim_customer_t2 (
customer_key INT PRIMARY KEY,
customer_id VARCHAR(10),
city VARCHAR(50),
valid_from DATE,
valid_to DATE,
is_current CHAR(1)
);
INSERT INTO dim_customer_t2 VALUES
(1, 'C101', 'Toronto', '2025-01-01', '9999-12-31', 'Y'),
(2, 'C102', 'Dallas', '2025-01-01', '9999-12-31', 'Y');
CREATE TABLE dim_customer_t3 (customer_key INT PRIMARY KEY, customer_id VARCHAR(10), city VARCHAR(50), prev_city VARCHAR(50));
INSERT INTO dim_customer_t3 VALUES (1, 'C101', 'Toronto', NULL), (2, 'C102', 'Dallas', NULL);The Type 1 table is a plain dimension with one row per customer.
SELECT * FROM dim_customer_t1 ORDER BY 1;| customer_key | customer_id | name | city |
|---|---|---|---|
| 1 | C101 | Sara Khan | Toronto |
| 2 | C102 | Omar Ali | Dallas |
The Type 2 table has valid_from, valid_to and is_current. Both rows are open-ended, with valid_to set to 9999-12-31.
SELECT * FROM dim_customer_t2 ORDER BY 1;| customer_key | customer_id | city | valid_from | valid_to | is_current |
|---|---|---|---|---|---|
| 1 | C101 | Toronto | 2025-01-01 | 9999-12-31 | Y |
| 2 | C102 | Dallas | 2025-01-01 | 9999-12-31 | Y |
The Type 3 table has an empty prev_city column, ready for one level of history.
SELECT * FROM dim_customer_t3 ORDER BY 1;| customer_key | customer_id | city | prev_city |
|---|---|---|---|
| 1 | C101 | Toronto | NULL |
| 2 | C102 | Dallas | NULL |
Step 1: Type 1 - overwrite
Now let's handle Sara's move as Type 1. It is one update statement that sets her city to Vancouver.
UPDATE dim_customer_t1
SET city = 'Vancouver'
WHERE customer_id = 'C101';Now let's check the result.
SELECT * FROM dim_customer_t1 ORDER BY customer_key;| customer_key | customer_id | name | city |
|---|---|---|---|
| 1 | C101 | Sara Khan | Vancouver |
| 2 | C102 | Omar Ali | Dallas |
Toronto is gone. Sara still has customer key 1, so every past sale that points to key 1 now reports as Vancouver. Sales by city are restated as if she had always lived there.
Type 1 is the right choice for corrections, like fixing a misspelled name, and for attributes where only the current value matters.
Step 2: Type 2 - add a new row
Type 2 keeps full history. It takes two statements: first we expire the current row by setting valid_to to the day before the move and is_current to N, then we insert a new row with a new surrogate key for Vancouver.
UPDATE dim_customer_t2
SET valid_to = '2026-03-19', is_current = 'N'
WHERE customer_id = 'C101' AND is_current = 'Y';
INSERT INTO dim_customer_t2 VALUES
(3, 'C101', 'Vancouver', '2026-03-20', '9999-12-31', 'Y');Now let's check the result.
SELECT * FROM dim_customer_t2 ORDER BY customer_key;| customer_key | customer_id | city | valid_from | valid_to | is_current |
|---|---|---|---|---|---|
| 1 | C101 | Toronto | 2025-01-01 | 2026-03-19 | N |
| 2 | C102 | Dallas | 2025-01-01 | 9999-12-31 | Y |
| 3 | C101 | Vancouver | 2026-03-20 | 9999-12-31 | Y |
Sara now has two rows. Key 1 is Toronto, valid until 2026-03-19, and key 3 is Vancouver, valid from 2026-03-20 and marked as current. Omar's row (key 2) is untouched.
Old sales keep pointing to key 1, so they stay in Toronto. New sales get key 3, so they land in Vancouver. Reports show each sale where it really happened. This is why Type 2 needs a surrogate key: the natural key C101 now appears twice.
Type 2 is the most used type in warehouses. It costs more rows and a more careful load, which is the next question in the series.
Step 3: Type 3 - add a previous-value column
Type 3 keeps one level of history in an extra column. One update copies the current city into prev_city and sets the new city, in the same statement.
UPDATE dim_customer_t3
SET prev_city = city,
city = 'Vancouver'
WHERE customer_id = 'C101';Now let's check the result.
SELECT * FROM dim_customer_t3 ORDER BY customer_key;| customer_key | customer_id | city | prev_city |
|---|---|---|---|
| 1 | C101 | Vancouver | Toronto |
| 2 | C102 | Dallas | NULL |
Sara still has one row and one key. city is Vancouver and prev_city is Toronto, so we can report by either one. That is useful for a single before and after comparison, for example old and new sales regions after a reorganization.
The catch: if Sara moves again, Toronto is overwritten in prev_city. Type 3 only remembers one change back.
Side by side: which type when
| Type | What happens | History | Use for |
|---|---|---|---|
| 0 | never changes | original only | date of birth |
| 1 | overwrite | none | corrections |
| 2 | new row + new key | full | city, segment |
| 3 | previous column | one level | before / after |
- Type 0 - never changes, for fixed values like date of birth.
- Type 1 - overwrite, for corrections and attributes where only the current value matters.
- Type 2 - new row and new key, for anything we analyze over time, like city, segment or price tier.
- Type 3 - previous column, for one before and after comparison.
In real tables we mix them: choose the SCD type per column, not per table. Saying this in an interview shows you have actually built one.
Follow-up questions interviewers ask
Which SCD type is most common?
Type 2 for analytical attributes, often mixed with Type 1 for corrections in the same table.
Why does Type 2 need a surrogate key?
The natural key repeats across versions, so each version needs its own key for facts to point to.
What is the downside of Type 3?
It keeps only one previous value; a second change loses the older history.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #32 - Slowly changing dimensions: Types 0, 1, 2 and 3
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q32; SET search_path TO dwh_q32;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS dim_customer_t1;
DROP TABLE IF EXISTS dim_customer_t2;
DROP TABLE IF EXISTS dim_customer_t3;
-- the same customers, stored three ways
CREATE TABLE dim_customer_t1 (customer_key INT PRIMARY KEY, customer_id VARCHAR(10), name VARCHAR(50), city VARCHAR(50));
INSERT INTO dim_customer_t1 VALUES (1, 'C101', 'Sara Khan', 'Toronto'), (2, 'C102', 'Omar Ali', 'Dallas');
CREATE TABLE dim_customer_t2 (
customer_key INT PRIMARY KEY,
customer_id VARCHAR(10),
city VARCHAR(50),
valid_from DATE,
valid_to DATE,
is_current CHAR(1)
);
INSERT INTO dim_customer_t2 VALUES
(1, 'C101', 'Toronto', '2025-01-01', '9999-12-31', 'Y'),
(2, 'C102', 'Dallas', '2025-01-01', '9999-12-31', 'Y');
CREATE TABLE dim_customer_t3 (customer_key INT PRIMARY KEY, customer_id VARCHAR(10), city VARCHAR(50), prev_city VARCHAR(50));
INSERT INTO dim_customer_t3 VALUES (1, 'C101', 'Toronto', NULL), (2, 'C102', 'Dallas', NULL);
-- Step 1: Type 1: overwrite the old value
UPDATE dim_customer_t1
SET city = 'Vancouver'
WHERE customer_id = 'C101';
-- check the result
SELECT * FROM dim_customer_t1 ORDER BY customer_key;
-- Step 2: Type 2: expire the current row, then insert a new version
UPDATE dim_customer_t2
SET valid_to = '2026-03-19', is_current = 'N'
WHERE customer_id = 'C101' AND is_current = 'Y';
INSERT INTO dim_customer_t2 VALUES
(3, 'C101', 'Vancouver', '2026-03-20', '9999-12-31', 'Y');
-- check the result
SELECT * FROM dim_customer_t2 ORDER BY customer_key;
-- Step 3: Type 3: keep the previous value in its own column
UPDATE dim_customer_t3
SET prev_city = city,
city = 'Vancouver'
WHERE customer_id = 'C101';
-- check the result
SELECT * FROM dim_customer_t3 ORDER BY customer_key;
Key points
- Type 0: retain the original value
- Type 1: overwrite, no history
- Type 2: new row + new surrogate key, full history
- Type 3: previous-value column, one level
- Choose the type per column
Question 33 — SCD Type 2 deep dive How do you implement SCD Type 2?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.