SCD Type 6 is a hybrid that combines Types 1, 2, and 3 in one dimension - and the number is literally 1 + 2 + 3 = 6. It keeps full version history (Type 2), while also stamping every row with the entity's current attribute value (Type 1) so you can report by either the historical or the present value without changing the query's grain. It's the go-to when stakeholders need to slice the same facts both "as it was at the time" and "as it is now."

The three techniques in one row

Each historical version is a separate row (Type 2), with effective-date ranges and a current-flag. On every one of those rows, a second set of columns holds the value as it stands today (Type 1), overwritten across all of an entity's rows whenever it changes. An optional prior-value column (Type 3) can record the immediately preceding value.

dim_sales_rep (
  rep_key,            -- surrogate, one per version (Type 2)
  rep_id,            -- durable natural key
  region_historical, -- value at the time (Type 2)
  region_current,    -- today's value, overwritten everywhere (Type 1)
  valid_from, valid_to, is_current
)

A rep who moved from West to East has two rows. Both rows' region_current read "East"; their region_historical read "West" and "East" respectively.

Both views from one join

Because each fact points to the version-specific row, grouping by region_historical gives the classic Type 2 answer - credit each sale to the region in effect when it happened. Grouping by region_current instead restates all of that rep's history under their present region - useful for "show me this year's and last year's numbers under the current org chart." One dimension, one fact join, two analytical perspectives.

Cost and when to use it

The maintenance burden is the Type 1 part: when an attribute changes, the loader must overwrite the "current" column on every historical row for that entity, not just the latest. That's more update work than plain Type 2. Use Type 6 when the business genuinely asks for both as-was and as-is reporting on the same attribute; if it only ever wants one, plain Type 2 (or Type 1) is simpler. The alternative that avoids the bulk overwrite is Type 7, which gets both views through dual keys instead.

Frequently asked questions

Why is it called Type 6? Because it combines Type 1 + Type 2 + Type 3, and 1 + 2 + 3 = 6 - it's sometimes written as the "1-2-3 hybrid."

What does Type 6 let you do? Report the same facts by either the historical attribute value (as it was at event time) or the current value (as it is now), from a single dimension and join.

What's the main drawback? Updating a "current" attribute means overwriting that column across all of an entity's historical rows, which is more load-time work than a pure Type 2.

Type 6 vs. Type 7? Both give as-was and as-is views. Type 6 stores current values redundantly on every history row; Type 7 instead puts two keys on the fact and resolves current values through a separate current view, avoiding the bulk overwrite.