In this lesson, you will learn how to leverage Type 6 Hybrid dimensions to seamlessly report on both current and historical states within a single analytical query.
The Hybrid Advantage
When we use Type 6 (SCD 1+2+3), we are essentially merging three strategies into one dimension record. A typical Type 6 dimension includes:
- Historical attributes (Type 2 row versions for tracking history).
- Original attributes (Type 3 column capturing the first value).
- Current attributes (Type 1 column reflecting the most recent value).
By structuring our data this way, we avoid having to perform complex self-joins or multiple lookups just to see how a "current" attribute relates to a "past" event.
Querying in Practice
Imagine you are querying sales performance. You want to see the product category as it was at the time of sale, but you also want to group the results by the current category name for a board report. Because of our Type 6 setup, we don't need to join to a "current" version of the table—we have the data ready in our columns.
SELECT
f.sale_amount,
d.product_name_historical, -- The name when the sale happened
d.product_name_current -- The name as it is today (Type 1 attribute)
FROM fact_sales f
JOIN dim_product d ON f.product_sk = d.product_sk
WHERE d.is_current_row = 'Y';
In this example, product_name_historical is tracked via our Type 2 versioning, while product_name_current is the Type 1 column that overwrites across all rows for that specific business key. This allows the business to compare "as-was" reporting against "as-is" strategic groupings without massive performance overhead.
When you query these tables, remember that the "current" columns are consistent across all historical versions of that product record. This makes filtering and grouping by modern business definitions extremely fast, even when the underlying fact data is several years old.
Key Takeaway: Type 6 Hybrid dimensions empower your queries by providing both point-in-time accuracy and current-state consistency within the same row, eliminating the need for complex, resource-heavy joins.