Dimensional Modeling: SCD Type 5: Add Mini-Dimension and Type 1 Outrigger

SCD Type 5 is one of the Slowly Changing Dimension (SCD) techniques used in data warehousing to track historical changes in dimension attributes, especially when you want to combine Type 1 and Type 2 behavior while supporting alternate current values. Let’s go step by step.

---

1. Purpose

• Some attributes change over time and you want to preserve history (Type 2 behavior) but also keep a current value snapshot (Type 1 behavior) in a separate column for easy querying.

• SCD Type 5 is often called “Type 1 + Type 2 hybrid with a current-value mini-dimension.”

---

2. How It Works

1. You have a main dimension table.

2. Frequently changing attributes are stored in a Type 2 mini-dimension to track history.

3. The main dimension also stores the current value of the attribute (Type 1 behavior) as a separate column for easier reporting.

So essentially:

• Mini-dimension → Tracks historical changes (SCD Type 2).

• Main dimension → Stores current value snapshot for quick reference (SCD Type 1).

---

3. Diagram (Conceptual)

Fact Table
  |
  |-- Mini-Dim Key (Type 2 history)
  |
Main Dimension
  |-- Current Attribute Value (Type 1)
  |-- Other static attributes
Mini Dimension
  |-- Historical attribute values (Type 2)

 

---

4. Example

Imagine a Customer dimension with a CustomerSegment attribute:

Mini Dimension (CustomerSegmentHistory):

SegmentID	SegmentName	StartDate	EndDate
1	Silver	2023-01-01	2023-06-30
2	Gold	2023-07-01	9999-12-31

Main Dimension (Customer):

CustomerID	Name	CurrentSegmentName
101	Alice	Gold

• Fact table links to Mini Dimension for historical reporting.

• Main dimension stores CurrentSegmentName for easy reporting without complex joins.

---

5. Benefits

• Combines Type 1 simplicity (current value snapshot) with Type 2 history tracking.

• Efficient for queries that need current value frequently.

• Reduces join complexity for common reporting scenarios.

---

6. Use Cases

• Customer segmentation that changes often.

• Product categories or pricing tiers that need both historical analysis and current reporting.

• Marketing campaigns where you need historical attribution and current view simultaneously.

---

In short:

SCD Type 5 = Main dimension with Type 1 current-value + Mini dimension with Type 2 historical attribute changes.