What does an SCD Type 6 hybrid dimension let an analyst report that Type 2 alone cannot?

## Why Type 2 alone is not enough Under Slowly Changing Dimension Type 2, each change inserts a new version row and the fact table stores the surrogate key of the version current at load time. That is exactly what you want for historically faithful reporting: a sale made while a customer lived in Texas stays credited to Texas forever. But it makes a second, equally legitimate question unanswerable by a simple grouping. The Oregon sales director wants the *total lifetime value of the customers she manages today*, including what they bought before they moved into her region. Under pure Type 2 that requires re-deriving each fact's member and looking up its current version — a self-join or a subquery on every report. The business is not asking a strange question; the model just does not surface it. ## The Type 6 construction Type 6 gets its number from 1 + 2 + 3 (and also, conveniently, from 1 × 2 × 3). It layers all three behaviours on one dimension: - **Type 2**, the backbone: a new row per change, so versions exist and facts point at them. - **Type 1**, applied unusually: a second column holds the *current* value of the attribute, and on every change that column is overwritten **on all rows for that member**, historical versions included. - **Type 3**, optionally: a prior-value column for the immediately preceding value, when the business wants a one-step comparison as well. ```text cust_sk | cust_id | region | current_region | is_current 501 | 4711 | Texas | Oregon | N 902 | 4711 | Oregon | Oregon | Y ``` The key observation about that sketch: the retired 2023 version still says `region = 'Texas'` — its historical truth is intact — but its `current_region` has been rewritten to Oregon. Every fact bound to version 501 can now be rolled up either way. ## Two semantics, one query shape ```sql -- as-was: credited where the customer lived at the time SELECT d.region, SUM(f.amount) FROM fact_sales f JOIN dim_customer d ON d.cust_sk = f.cust_sk GROUP BY d.region; -- as-is: the same facts rolled under today's region SELECT d.current_region, SUM(f.amount) FROM fact_sales f JOIN dim_customer d ON d.cust_sk = f.cust_sk GROUP BY d.current_region; ``` Same facts, same join, different totals — and both are right. That is the sentence to say in an interview: Type 6 turns a choice of *semantics* into a choice of *column*, which is something a self-service analyst can make without writing a correlated lookup. ## What it costs **Write amplification on change.** A Type 1 column that must reflect today's value on every historical row means each change touches all of that member's rows, not just the newest. For a dimension with many versions per member this is a materially heavier load step than plain Type 2, and it is the reason some teams push the as-is view into a view or a semantic-layer measure instead of materialising the column. **Ambiguity risk.** Two columns whose names differ by one word, both plausible in a report builder's field list, both returning sensible-looking numbers. There is no error message when someone picks the wrong one — only a different answer. This is a documentation and naming problem before it is a technical one: `region_at_time_of_sale` and `region_current` are much safer than `region` and `current_region`, and the model documentation should state which one each standard report uses. **Reproducibility of the past.** A report run last quarter that grouped by the current-value column will not reproduce today, because that column has since been rewritten. Only the versioned column is stable over time. Consumers who need a frozen number must use the as-was column or snapshot the result. ## When it is the right call Type 6 earns its complexity when *both* views are genuinely and repeatedly required over the same facts — sales territory analysis is the archetype, as is any organisational hierarchy where people ask both "what did this team achieve" and "what have the people now on this team achieved". When only one view is needed, Type 2 alone is simpler and cheaper; when the second view is occasional, a view or a semantic-layer measure that resolves the current version can serve it without carrying the write cost on every load. A final note on vocabulary: some teams describe any Type 2 dimension carrying a current-value column as Type 6 even without a prior-value column, and that usage is common enough to be safe in an interview — but say what the columns actually do rather than leaning on the number, because the number alone does not tell your interviewer whether you understand the write amplification.