Dimensional Modeling - SCD Type 6: Hybrid SCD

SCD Type 6 is another type of Slowly Changing Dimension (SCD) that combines Type 1, Type 2, and Type 3 strategies to provide both historical tracking and current value snapshots in a single dimension. It’s sometimes called “Hybrid SCD”. Let’s break it down carefully.

---

1. Purpose

SCD Type 6 is designed to handle scenarios where you want:

1. Full historical tracking (like Type 2).

2. Current value snapshot (like Type 1).

3. Previous value tracking (like Type 3).

It’s useful when fact tables or reports need historical context, current status, and previous values all at once.

---

2. How It Works

• Type 2 behavior: Keep a new row in the dimension for every change (preserves history).

• Type 1 behavior: Update columns in the main dimension with the latest/current value for easy querying.

• Type 3 behavior: Add a “previous value” column in the main dimension to store the last value before the change.

So effectively, SCD Type 6 = Type 1 + Type 2 + Type 3.

---

3. Example

Suppose you have a Customer dimension with a CustomerSegment attribute:

Main Dimension Table (SCD Type 6):

CustomerID	Name	CurrentSegment	PreviousSegment	StartDate	EndDate
101	Alice	Gold	Silver	2023-07-01	9999-12-31
101	Alice	Silver	NULL	2023-01-01	2023-06-30

• CurrentSegment → Type 1 column (always has latest value).

• PreviousSegment → Type 3 column (keeps the last segment).

• StartDate / EndDate → Type 2 tracking (historical changes).

• Fact tables link to this dimension for historical analysis.

---

4. Benefits

• Provides full history for historical reporting.

• Easy to query current values without joining multiple tables.

• Stores previous values for comparison or trend analysis.

• Avoids complex joins between mini-dimensions and main dimensions (as in Type 5) in some cases.

---

5. Use Cases

• Customer behavior analysis where both current segment and previous segment matter.

• Product pricing where you need current price, previous price, and historical price tracking.

• Marketing campaigns where you want current offer, previous offer, and full history.

---

In short:

SCD Type 6 = Type 1 (current) + Type 2 (history) + Type 3 (previous value), providing full history, current snapshot, and previous value tracking in a single dimension table.