Dimensional Modeling - SCD Type 7: Hybrid SCD with a history table

SCD Type 7 is a less common and more advanced type of Slowly Changing Dimension (SCD). It is essentially a hybrid SCD approach designed to combine Type 1, Type 2, and sometimes Type 3 behavior while offloading historical tracking to a separate “history table”. This is sometimes called a “hybrid SCD with a history table”.

Let’s break it down carefully.

---

1. Purpose

• Provides current attribute values for easy reporting (Type 1).

• Maintains full historical changes in a separate table (Type 2).

• Allows tracking of previous values if needed (Type 3).

• Improves query performance because the main dimension stays small and current while history is stored elsewhere.

---

2. How It Works

1. Main Dimension (Type 1 behavior): Stores only the current/latest value for each attribute. This is used for most reporting queries.

2. History Table (Type 2 behavior): Tracks all historical changes in a separate table. Fact tables can optionally link to this history table for historical reporting.

3. Optionally, previous value columns can be maintained in the main dimension (Type 3 behavior) for easy comparison.

---

3. Example

Suppose a Customer dimension with a CustomerSegment attribute changes over time.

Main Dimension (Type 1 current values):

CustomerID	Name	CurrentSegment
101	Alice	Gold

History Table (Type 2 full history):

CustomerID	Segment	StartDate	EndDate
101	Silver	2023-01-01	2023-06-30
101	Gold	2023-07-01	9999-12-31

• Fact tables usually link to the main dimension for current reporting.

• Historical reports join with the history table.

---

4. Benefits

• Keeps the main dimension small and fast for queries.

• Maintains full history for detailed analytics.

• Combines Type 1, Type 2, and optional Type 3 approaches.

• Makes the ETL process more modular: updating current vs. maintaining history is separated.

---

5. Use Cases

• Large customer, product, or account dimensions where current values are frequently queried, but historical analysis is still needed.

• Scenarios where history is rarely queried but must be preserved.

• Organizations that want better performance on current-value reporting while maintaining full history separately.

---

In short:

SCD Type 7 = Hybrid SCD where the main dimension stores only current values (Type 1), historical changes are stored in a separate history table (Type 2), and optionally previous values are tracked (Type 3).