In the world of data warehousing, one of the most critical challenges we face is handling data that changes over time. Enter Slowly Changing Dimensions (SCD) — a fundamental concept that every data engineer, analyst, and business intelligence professional must master.

What are Slowly Changing Dimensions?

Slowly Changing Dimensions refer to the problem of handling changes to dimension data in a data warehouse. Unlike fact tables that store transactional data, dimension tables contain descriptive attributes that can change over time, albeit slowly and unpredictably.

Think about it: employees get promoted, customers change addresses, products get updated descriptions, and organizational structures evolve. How do we capture these changes while maintaining historical accuracy and analytical integrity?

Why SCD Matters in Data Warehousing

Before diving into the types, let’s understand why SCD is crucial:

• Historical Accuracy: Maintaining correct historical context for analysis

• Regulatory Compliance: Meeting audit and compliance requirements

• Business Intelligence: Enabling accurate trend analysis and reporting

• Data Integrity: Ensuring consistency across time-dependent queries

The Six Types of Slowly Changing Dimensions

SCD Type 0: Retain Original (Fixed Dimension)

Concept: Some dimension attributes never change once set. These are considered fixed for the lifetime of the dimension.

Use Case: Birth dates, social security numbers, or any immutable identifiers.

Example: Let’s consider an employee master table where employee ID and birth date never change:

-- Employee Dimension Table
CREATE TABLE dim_employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    birth_date DATE,
    hire_date DATE
);

-- Initial Record
INSERT INTO dim_employee VALUES 
(101, 'Rajesh Kumar Sharma', '1985-03-15', '2010-07-20');

Even if Rajesh changes his name legally, his birth date remains constant — this is SCD Type 0.

SCD Type 1: Overwrite (Current Value Only)

Concept: Simply overwrite the old value with the new value. No history is maintained.

Pros: Simple implementation, minimal storage Cons: Loss of historical data

Example: Priya Mehta from Mumbai changes her phone number:

-- Before Update
employee_id | name        | phone        | city
101         | Priya Mehta | 9876543210   | Mumbai

-- After Update (Type 1)
UPDATE dim_customer 
SET phone = '9876543211' 
WHERE employee_id = 101;

-- Result
employee_id | name        | phone        | city
101         | Priya Mehta | 9876543211   | Mumbai

Real-world scenario: Ankit Patel’s company updates their employee records when someone corrects a typo in their address. The old incorrect address is simply overwritten since the historical “wrong” data has no business value.

SCD Type 2: Add New Record (Historical Tracking)

Concept: Create a new record for each change while keeping the old record. This maintains complete history.

Implementation techniques:

• Version numbers

• Effective/Expiry dates

• Current flag indicators

Example: Shreya Gupta gets promoted from Software Engineer to Senior Software Engineer:

-- Original Record
employee_id | name         | designation        | effective_date | expiry_date | current_flag
201         | Shreya Gupta | Software Engineer  | 2020-01-15     | 2023-06-30  | N

-- New Record Added
employee_id | name         | designation           | effective_date | expiry_date | current_flag
201         | Shreya Gupta | Software Engineer     | 2020-01-15     | 2023-06-30  | N
202         | Shreya Gupta | Senior Software Eng.  | 2023-07-01     | 9999-12-31  | Y

Business Value: This allows analysis like “How many promotions happened in Q2 2023?” or “What was Shreya’s designation during the ABC project timeline?”

SCD Type 3: Add New Attribute (Limited History)

Concept: Add new columns to track both current and previous values. Limited to storing just one or two historical values.

Example: Vikram Singh relocates from Delhi to Bangalore:

-- Table Structure
CREATE TABLE dim_employee (
    employee_id INT,
    name VARCHAR(100),
    current_city VARCHAR(50),
    previous_city VARCHAR(50),
    city_change_date DATE
);

-- After Vikram's relocation
employee_id | name         | current_city | previous_city | city_change_date
301         | Vikram Singh | Bangalore    | Delhi         | 2023-08-15

Use Case: When you need to track one level of history for specific attributes like “previous supervisor” or “last known address.”

SCD Type 4: History Table (Separate Historical Storage)

Concept: Maintain current data in the main dimension table and move historical records to a separate history table.

Example: Customer Meena Agarwal from Pune changes her address:

-- Current Dimension Table
CREATE TABLE dim_customer_current (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    address VARCHAR(200),
    city VARCHAR(50),
    last_updated DATE
);

-- History Table
CREATE TABLE dim_customer_history (
    customer_id INT,
    name VARCHAR(100),
    address VARCHAR(200),
    city VARCHAR(50),
    effective_date DATE,
    expiry_date DATE
);

-- Current Record
INSERT INTO dim_customer_current VALUES 
(401, 'Meena Agarwal', 'New Address, Baner', 'Pune', '2023-09-01');

-- Historical Record
INSERT INTO dim_customer_history VALUES 
(401, 'Meena Agarwal', 'Old Address, Kothrud', 'Pune', '2020-01-01', '2023-08-31');

SCD Type 6: Hybrid Approach (Combination Method)

Concept: Combines elements of Type 1, Type 2, and Type 3. Often called “Unpredictable Dimensions.”

Example: Arjun Reddy’s customer record tracking both current and original information:

CREATE TABLE dim_customer_hybrid (
    surrogate_key INT PRIMARY KEY,
    customer_id INT,
    name VARCHAR(100),
    current_address VARCHAR(200),
    original_address VARCHAR(200),
    current_city VARCHAR(50),
    original_city VARCHAR(50),
    effective_date DATE,
    expiry_date DATE,
    current_flag CHAR(1)
);

-- Arjun's records showing move from Hyderabad to Chennai
surrogate_key | customer_id | name        | current_address | original_address | current_city | original_city | effective_date | expiry_date | current_flag
501           | 401         | Arjun Reddy | Chennai Addr.   | Hyderabad Addr.  | Chennai      | Hyderabad     | 2020-01-01     | 2023-05-31  | N
502           | 401         | Arjun Reddy | Bangalore Addr. | Hyderabad Addr.  | Bangalore    | Hyderabad     | 2023-06-01     | 9999-12-31  | Y

Conclusion

Slowly Changing Dimensions are fundamental to effective data warehousing. The choice of SCD type depends on your specific business requirements, storage constraints, and analytical needs.

Key Takeaways:

• SCD Type 1: Simple overwrite, no history

• SCD Type 2: Complete history tracking (most common)

• SCD Type 3: Limited history with additional columns

• SCD Type 4: Separate current and historical tables

• SCD Type 6: Hybrid approach combining multiple methods

Whether you’re Priya tracking customer changes in Mumbai, Rajesh managing employee data in Bangalore, or Shreya building analytics in Chennai, understanding and implementing SCD correctly will ensure your data warehouse delivers accurate, reliable insights for business decision-making.

Remember: The goal isn’t just to store data — it’s to preserve the story that data tells over time. Choose your SCD strategy wisely, implement it consistently, and your future self (and business users) will thank you.

What SCD challenges have you faced in your data warehousing projects? Share your experiences in the comments below!