If you've worked in data warehousing for more than a week, you've encountered the challenge of tracking how dimension data changes over time. A customer moves to a new address. A product gets recategorized. An employee changes departments. These changes seem simple on the surface, but how you handle them has profound implications for your analytics, storage costs, and query complexity.

Enter Slowly Changing Dimensions (SCDs)—a set of design patterns that have been the backbone of dimensional modeling since Ralph Kimball first codified them decades ago. Despite their age, these patterns remain remarkably relevant in modern data architectures, from traditional data warehouses to cloud-native data lakes.

Let's cut through the academic theory and focus on what actually matters: which SCD type to use, when, and why.

The Core Problem: Change Is Inevitable

Imagine you're building a sales analytics platform. Last quarter, your sales rep Sarah Johnson was in the Western region. This quarter, she moved to the Eastern region. Now someone asks: "What were Sarah's sales numbers by region over the past year?"

This seemingly simple question becomes complex:

Your answer to these questions determines your SCD strategy.

Type 0: The Purist (Retain Original)

Type 0 is the simplest approach: once a value is set, it never changes. This isn't about being lazy—it's a deliberate design choice for attributes that represent immutable facts.

When to use it: Birth dates, social security numbers, original hire dates, or any attribute that represents a point-in-time fact that should never change.

Implementation: Simply ignore updates to these fields in your ETL pipeline or actively reject them with validation rules.

-- Type 0: Reject changes
CREATE TABLE employee (
  employee_id INT PRIMARY KEY,
  birth_date DATE NOT NULL,
  hire_date DATE NOT NULL
  -- These fields will never be updated
);

The tradeoff: Zero complexity and perfect data integrity, but it requires careful thought about which attributes truly belong here. I've seen teams treat mutable fields as Type 0, only to discover later they need change history.

Type 1: The Pragmatist (Overwrite)

Type 1 is the most common approach in practice: simply overwrite old values with new ones. No history, no complexity, no questions asked.

When Sarah moves from Western to Eastern region, you update her record. Her entire sales history now appears under Eastern region. History is rewritten.

-- Type 1: Simple overwrite
UPDATE employee 
SET region = 'Eastern',
    updated_at = CURRENT_TIMESTAMP
WHERE employee_id = 12345;

When to use it: Correcting data errors, updating attributes where history doesn't matter (email addresses, phone numbers), or when you're confident no analytical question will ever need historical values.

The tradeoff: Simplicity and minimal storage, but you're making an irreversible decision. Once you overwrite, that history is gone forever. This is fine for correcting typos, dangerous for attributes that drive business logic.

My take: Type 1 is underrated. Teams often default to more complex solutions when Type 1 would suffice. If your business genuinely doesn't need history, don't build complexity you don't need.

Type 2: The Historian (Add New Row)

Type 2 is the gold standard for preserving full history. Instead of updating a record, you insert a new one and mark the old one as inactive. Each row represents a version of the dimension member valid during a specific time period.

-- Type 2: Track full history
CREATE TABLE employee (
  employee_key INT PRIMARY KEY,        -- Surrogate key
  employee_id INT NOT NULL,            -- Natural key
  name VARCHAR(100),
  region VARCHAR(50),
  effective_date DATE NOT NULL,
  end_date DATE,                       -- NULL = current
  is_current BOOLEAN DEFAULT TRUE
);

-- Sarah's history:
-- Row 1: employee_key=1, employee_id=12345, region='Western', 
--        effective_date='2023-01-01', end_date='2024-03-31', is_current=FALSE
-- Row 2: employee_key=2, employee_id=12345, region='Eastern', 
--        effective_date='2024-04-01', end_date=NULL, is_current=TRUE

When to use it: Any attribute where you need accurate point-in-time reporting, compliance requirements demand audit trails, or historical analysis is a core business requirement.

The tradeoff: Complete historical accuracy, but at the cost of complexity. Your dimension table grows over time. Queries become more complex—you need to join on surrogate keys (employee_key) instead of natural keys (employee_id), and you must filter for the appropriate time period. Storage costs increase, though in the cloud era, this is usually acceptable.

Type 2 is my default choice for any dimension attribute that matters to the business. The complexity is manageable, and you'll never regret having the history when someone asks for it three years later.

Type 3: The Compromiser (Add New Column)

Type 3 adds a column to track the previous value. It's a middle ground—preserving one level of history without the complexity of multiple rows.

-- Type 3: Limited history
CREATE TABLE employee (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  current_region VARCHAR(50),
  previous_region VARCHAR(50),
  region_change_date DATE
);

When to use it: Honestly? Almost never. Type 3 was more relevant in the era of expensive storage and limited processing power. It made sense when tracking "current" vs. "previous" was enough for business needs.

The tradeoff: You get one level of history with simple queries, but what happens when a third change occurs? You overwrite the previous value, losing history. It's the worst of both worlds: more complex than Type 1, less capable than Type 2.

My take: Skip Type 3 in modern architectures. If you need history, go with Type 2. If you don't, use Type 1. Type 3 creates technical debt.

Type 4: The Separatist (Historical Table)

Type 4 splits current and historical data into separate tables. The main dimension table contains only current values (Type 1 style), while a separate history table tracks all changes (Type 2 style).

-- Type 4: Split approach
CREATE TABLE employee_current (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  region VARCHAR(50),
  updated_at TIMESTAMP
);

CREATE TABLE employee_history (
  history_id INT PRIMARY KEY,
  employee_id INT,
  region VARCHAR(50),
  effective_date DATE,
  end_date DATE
);

When to use it: When most queries need current data only, but you occasionally need full history. This optimizes for the common case while preserving history for audit or compliance.

The tradeoff: Queries on current data are simple and fast. Historical analysis requires joining to the history table or querying it separately. You're managing two tables per dimension, which adds ETL complexity.

I've found Type 4 useful in high-performance scenarios where most dashboards query current state, but regulatory requirements demand full audit trails.

Types 5 & 6: The Hybrids

Type 5 combines Types 1 and 4—tracking history in a separate table while also embedding current values in the fact table. Type 6 (sometimes called Type 1+2+3) combines multiple techniques in a single table.

Here's my honest take: These hybrid approaches are academic exercises that create unnecessary complexity. In 15 years of data engineering, I've never encountered a scenario where these types provided value that couldn't be achieved more elegantly with standard Type 1 or Type 2.

Practical Decision Framework

Here's how I approach SCD decisions:

Use Type 1 when:

Use Type 2 when:

Use Type 0 when:

Use Type 4 when:

Modern Considerations

The cloud data warehouse era has changed some traditional tradeoffs. Storage is cheap. Compute scales elastically. This makes Type 2 more attractive than ever—the storage cost argument against it has largely evaporated.

However, new patterns are emerging. Delta Lake, Apache Iceberg, and similar technologies provide time travel capabilities at the table level, essentially giving you Type 2 behavior without manual implementation. Data vault modeling offers alternative approaches to tracking history.

But fundamentally, the questions remain the same: Do you need history? How much? How will you query it? SCDs are design patterns that encode your answers to these questions.

The Bottom Line

Don't overthink it. In most modern data warehouses, you'll use Type 1 for about 30% of attributes and Type 2 for the remaining 70%. Type 0 appears occasionally for truly immutable data. Everything else is rarely worth the complexity.

The key is understanding your business requirements and being honest about whether you need history. When in doubt, ask: "If someone asks me next year what this value was today, will I regret not tracking it?" If the answer is yes, use Type 2. If not, Type 1 is your friend.

Remember: the best SCD strategy is the one that's simple enough for your team to implement correctly and maintain consistently. Complexity is a cost—pay it only when the value is clear.