Why Slowly changing dimensions (SCD Type 2) in dbt? - Purpose & Use Cases

The Big Idea

What if you could see every change your data ever went through, like a time machine for your database?

The Scenario

Imagine you have a customer database where people move, change jobs, or update their preferences. You try to track these changes by manually updating records in spreadsheets or simple tables.

Every time a customer changes their address, you overwrite the old one, losing the history of where they lived before.

The Problem

Manually updating records means you lose important history. You can't see what the data looked like before the change.

This makes it hard to answer questions like "Where did the customer live last year?" or "How did their preferences evolve?"

Also, manual updates are slow and prone to mistakes, especially when data grows large.

The Solution

Slowly changing dimensions Type 2 solves this by keeping a full history of changes.

Instead of overwriting, it adds new records with start and end dates, so you can see every version of the data over time.

This makes tracking changes easy, accurate, and automated using dbt models.

Before vs After

✗ Before

UPDATE customers SET address = 'New Address' WHERE customer_id = 123;

✓ After

UPDATE customers_scd2 SET end_date = '2023-12-31', current_flag = FALSE WHERE customer_id = 123 AND current_flag = TRUE;
INSERT INTO customers_scd2 (customer_id, address, start_date, end_date, current_flag) VALUES (123, 'New Address', '2024-01-01', NULL, TRUE);

What It Enables

It enables you to track and analyze how data changes over time, unlocking powerful historical insights.

Real Life Example

A retail company uses SCD Type 2 to track customer address changes, so they can send promotions based on where customers lived during past holiday seasons.

Key Takeaways

Manual updates overwrite history and lose valuable data.

SCD Type 2 keeps full change history with start/end dates.

dbt automates this process for reliable, scalable data tracking.