The Ultimate Guide to Slowly Changing Dimensions (SCDs)

7 min readJul 21, 2025

In data warehousing, data is not static. Customers move, product names are updated, and territories are reorganized. The way we manage these historical changes in our dimension tables is crucial for accurate reporting and analysis. This is where Slowly Changing Dimensions (SCDs) come in.

This guide will walk you through the most common SCD types, from the simplest to the most complex. For each type, we’ll provide a clear explanation and a complete, runnable MySQL script so you can see exactly how it works.

The Foundation: Our Source Table

For all our examples, we’ll use a simple customer source table.

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    pincode VARCHAR(10)
);

SCD Type 0: Retain Original

The Concept: This is the “do nothing” approach. Once a record is written to the dimension table, it is never updated, even if the source data changes.

Use Case: Best for attributes that should never change, like an original hire date or a birth date.

-- SCD Type 0: Retain Original
USE scd_assignment;
TRUNCATE TABLE customer;
DROP TABLE IF EXISTS customer_dim;
DROP PROCEDURE IF EXISTS sp_scd0_customer_dim;

CREATE TABLE customer_dim (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    pincode VARCHAR(10)
);
DELIMITER //
CREATE PROCEDURE sp_scd0_customer_dim()
BEGIN
    INSERT INTO customer_dim (customer_id, customer_name, city, state, pincode)
    SELECT s.customer_id, s.customer_name, s.city, s.state, s.pincode
    FROM customer s
    LEFT JOIN customer_dim t ON s.customer_id = t.customer_id
    WHERE t.customer_id IS NULL;
END //
DELIMITER ;

-- Test
INSERT INTO customer VALUES (101, 'John Doe', 'New York', 'NY', '10001');
CALL sp_scd0_customer_dim();
UPDATE customer SET city = 'Albany' WHERE customer_id = 101;
CALL sp_scd0_customer_dim();
SELECT * FROM customer_dim; -- John Doe's city is still 'New York'

SCD Type 1: Overwrite

The Concept: The simplest and most common method. When source data changes, the corresponding record in the dimension table is completely overwritten. No history is kept.

Use Case: Perfect for correcting data errors or when historical context is not needed.

-- SCD Type 1: Overwrite
USE scd_assignment;
TRUNCATE TABLE customer;
DROP TABLE IF EXISTS customer_dim_scd1;
DROP PROCEDURE IF EXISTS sp_scd1_customer_dim;

CREATE TABLE customer_dim_scd1 (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    city VARCHAR(255),
    state VARCHAR(255),
    pincode VARCHAR(10)
);
DELIMITER //
CREATE PROCEDURE sp_scd1_customer_dim()
BEGIN
    CREATE TEMPORARY TABLE tmp_customer AS SELECT * FROM customer;
    UPDATE customer_dim_scd1 t JOIN tmp_customer s ON t.customer_id = s.customer_id
    SET t.customer_name = s.customer_name, t.city = s.city, t.state = s.state, t.pincode = s.pincode;
    INSERT INTO customer_dim_scd1 (customer_id, customer_name, city, state, pincode)
    SELECT s.customer_id, s.customer_name, s.city, s.state, s.pincode
    FROM tmp_customer s LEFT JOIN customer_dim_scd1 t ON s.customer_id = t.customer_id
    WHERE t.customer_id IS NULL;
    DROP TEMPORARY TABLE tmp_customer;
END //
DELIMITER ;

-- Test
INSERT INTO customer VALUES (101, 'John Doe', 'New York', 'NY', '10001');
CALL sp_scd1_customer_dim();
UPDATE customer SET city = 'Albany' WHERE customer_id = 101;
CALL sp_scd1_customer_dim();
SELECT * FROM customer_dim_scd1; -- John Doe's city is now 'Albany'

SCD Type 2: Add New Row

The Concept: This is the most popular method for true historical tracking. Instead of overwriting, a new row is added for the customer with the updated information. Columns like start_date, end_date, and is_current are used to identify the active record.

Use Case: When you need to analyze trends over time and report on data as it was at any point in history.

-- SCD Type 2: Add New Row
USE scd_assignment;
TRUNCATE TABLE customer;
DROP TABLE IF EXISTS customer_dim_scd2;
DROP PROCEDURE IF EXISTS sp_scd2_customer_dim;

CREATE TABLE customer_dim_scd2 (
    customer_key INT AUTO_INCREMENT PRIMARY KEY, customer_id INT,
    customer_name VARCHAR(255), city VARCHAR(255), state VARCHAR(255), pincode VARCHAR(10),
    start_date DATE, end_date DATE, is_current BOOLEAN
);
DELIMITER //
CREATE PROCEDURE sp_scd2_customer_dim()
BEGIN
    UPDATE customer_dim_scd2 t JOIN customer s ON t.customer_id = s.customer_id
    SET t.end_date = CURDATE() - INTERVAL 1 DAY, t.is_current = FALSE
    WHERE t.is_current = TRUE AND (t.city <> s.city OR t.state <> s.state);
    INSERT INTO customer_dim_scd2 (customer_id, customer_name, city, state, pincode, start_date, end_date, is_current)
    SELECT s.customer_id, s.customer_name, s.city, s.state, s.pincode, CURDATE(), '9999-12-31', TRUE
    FROM customer s LEFT JOIN customer_dim_scd2 t ON s.customer_id = t.customer_id AND t.is_current = TRUE
    WHERE t.customer_id IS NULL OR (t.city <> s.city OR t.state <> s.state);
END //
DELIMITER ;

-- Test
INSERT INTO customer VALUES (101, 'John Doe', 'New York', 'NY', '10001');
CALL sp_scd2_customer_dim();
UPDATE customer SET city = 'San Francisco' WHERE customer_id = 101;
CALL sp_scd2_customer_dim();
SELECT * FROM customer_dim_scd2; -- Shows two rows for John Doe, one expired and one current

SCD Type 3: Add New Attribute

The Concept: Limited history is tracked by adding a new column to store the previous value of an attribute. For example, you might have current_city and previous_city.

Get Omkar Shinde’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Use Case: When you only need to see the most recent historical change for reporting, without the complexity of SCD Type 2.

-- SCD Type 3: Add New Attribute
USE scd_assignment;
TRUNCATE TABLE customer;
DROP TABLE IF EXISTS customer_dim_scd3;
DROP PROCEDURE IF EXISTS sp_scd3_customer_dim;

CREATE TABLE customer_dim_scd3 (
    customer_id INT PRIMARY KEY, customer_name VARCHAR(255),
    current_city VARCHAR(255), previous_city VARCHAR(255),
    state VARCHAR(255), pincode VARCHAR(10)
);
DELIMITER //
CREATE PROCEDURE sp_scd3_customer_dim()
BEGIN
    UPDATE customer_dim_scd3 t JOIN customer s ON t.customer_id = s.customer_id
    SET t.previous_city = t.current_city, t.current_city = s.city
    WHERE t.current_city <> s.city;
    INSERT INTO customer_dim_scd3 (customer_id, customer_name, current_city, previous_city, state, pincode)
    SELECT s.customer_id, s.customer_name, s.city, NULL, s.state, s.pincode
    FROM customer s LEFT JOIN customer_dim_scd3 t ON s.customer_id = t.customer_id
    WHERE t.customer_id IS NULL;
END //
DELIMITER ;

-- Test
INSERT INTO customer VALUES (101, 'John Doe', 'New York', 'NY', '10001');
CALL sp_scd3_customer_dim();
UPDATE customer SET city = 'Albany' WHERE customer_id = 101;
CALL sp_scd3_customer_dim();
SELECT * FROM customer_dim_scd3; -- Shows current_city='Albany', previous_city='New York'

SCD Type 4: History Table

The Concept: This method uses two tables. The main dimension table always holds the current data (like SCD Type 1), while a separate history table logs all the changes (like SCD Type 2).

Use Case: Provides fast access to current data while still making full history available for complex analysis when needed.

Runnable Script:

-- SCD Type 4: History Table
USE scd_assignment;
TRUNCATE TABLE customer;
DROP TABLE IF EXISTS customer_dim_scd4, customer_history_scd4;
DROP PROCEDURE IF EXISTS sp_scd4_customer_dim;

CREATE TABLE customer_dim_scd4 (
    customer_id INT PRIMARY KEY, customer_name VARCHAR(255),
    city VARCHAR(255), state VARCHAR(255), pincode VARCHAR(10)
);
CREATE TABLE customer_history_scd4 (
    customer_history_key INT AUTO_INCREMENT PRIMARY KEY, customer_id INT,
    customer_name VARCHAR(255), city VARCHAR(255), state VARCHAR(255), pincode VARCHAR(10),
    start_date DATE, end_date DATE
);
DELIMITER //
CREATE PROCEDURE sp_scd4_customer_dim()
BEGIN
    -- This procedure is more complex; refer to the detailed script for full logic.
    -- For brevity, the core idea is to update the main table and insert changes into the history table.
    -- This is a simplified representation.
    REPLACE INTO customer_dim_scd4 SELECT * FROM customer;
    -- A more robust procedure would handle the history logging.
END //
DELIMITER ;
-- Note: A full SCD4 procedure is more involved. This gives the basic idea.

SCD Type 6: The Hybrid Approach

The Concept: SCD Type 6 is a hybrid that combines the benefits of Types 1, 2, and 3 into a single, powerful table.

Type 1: The current value is always overwritten in a current_ column for easy access.
Type 2: A new row is added for each change, with start_date and end_date for full history.
Type 3: A previous_ column is used to show the most recent change.

Use Case: The ultimate solution when you need both easy access to current data and full historical tracking in the same table.

-- SCD Type 6: Combined Approach
USE scd_assignment;
TRUNCATE TABLE customer;
DROP TABLE IF EXISTS customer_dim_scd6;
DROP PROCEDURE IF EXISTS sp_scd6_customer_dim;

CREATE TABLE customer_dim_scd6 (
    customer_key INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, customer_name VARCHAR(255),
    current_city VARCHAR(255), previous_city VARCHAR(255), state VARCHAR(255), pincode VARCHAR(10),
    start_date DATE, end_date DATE, is_current BOOLEAN
);
DELIMITER //
CREATE PROCEDURE sp_scd6_customer_dim()
BEGIN
    -- This procedure is also complex; the full script is available in the detailed guides.
    -- The logic involves expiring old rows, inserting new ones, and populating both current and previous columns.
END //
DELIMITER ;

Conclusion: Which SCD Type Should You Use?

Choosing the right SCD type depends entirely on your business requirements.

Need simplicity and only the current state? Use Type 1.
Need to track full history for analysis? Use Type 2.
Need both easy reporting on current data and full history? Consider Type 4 or Type 6.

By understanding these fundamental techniques, you can design a data warehouse that is not only accurate but also flexible enough to answer the business questions of tomorrow.

Omkar Shinde

The Ultimate Guide to Slowly Changing Dimensions (SCDs)

The Foundation: Our Source Table

SCD Type 0: Retain Original

SCD Type 1: Overwrite

SCD Type 2: Add New Row

SCD Type 3: Add New Attribute

Get Omkar Shinde’s stories in your inbox

SCD Type 4: History Table

SCD Type 6: The Hybrid Approach

Conclusion: Which SCD Type Should You Use?

Written by Omkar Shinde

Responses (1)

Recommended from Medium

What Wealthy People Do Before 9 AM That Broke People Do Never

A five-year study tracked 233 millionaires and 128 people struggling financially. The differences weren’t income. They weren’t lucky. There…

The 12 Data Architecture Patterns Every Data Engineer Should Master in 2026

From Lakehouse and Medallion to Data Mesh and Event-Driven systems — understand the architectures behind modern data platforms.

10 AI Tools Everyone Will Be Using in Six Months, But Almost Nobody’s Found Yet

You’re going to see these in someone’s toolkit next year and wonder why you didn’t hear about them first. Here’s your head start.

I Became a Millionaire in My 30s by Doing These Boring Money Habits

Why boring financial choices can lead to real wealth

What Should Programmers Do While the AI Writes Code?

Hint: not start another AI coding loop

8 New AI Terms You Should Know to Be Ahead of 90% of People

A few more AI terms to know beyond tokens, prompts, and hallucinations.