SCD Type 7 delivers both historical ("as it was") and current ("as it is") views of a dimension by putting two keys on the fact table: the version-specific surrogate key and the entity's durable natural key. Queries choose their perspective by which key they join on - no redundant current-value columns to maintain, which is what sets it apart from the otherwise-similar Type 6.

Dual keys on the fact

The dimension is a standard Type 2 table: one row per version, each with its own surrogate key, effective-date range, and current flag. The trick is in the fact table, which carries two foreign keys - the surrogate key of the version in effect at event time, and the durable key that identifies the entity across all its versions.

-- Type 2 dimension with both keys
dim_product (product_key,        -- surrogate, per version
             product_id,         -- durable natural key
             category, valid_from, valid_to, is_current)

-- fact carries BOTH
fact_sales (..., product_key,    -- join here for "as it was"
                 product_id)     -- join here for "as it is now"

Picking the view at query time

Join the fact's surrogate key to the dimension and you get the historical view - each sale tied to the product category in effect when it sold. Join the durable key to a current-only view of the dimension (or filter is_current = true) and you get the present view - every sale restated under the product's category today. The same fact rows, two perspectives, selected purely by join choice.

-- as-was: historical category
JOIN dim_product d ON f.product_key = d.product_key

-- as-is: current category
JOIN (SELECT * FROM dim_product WHERE is_current) c
  ON f.product_id = c.product_id

Type 7 vs. Type 6

Both give as-was and as-is reporting, but they pay for it differently. Type 6 stores the current value redundantly on every historical row and must overwrite all of them when an attribute changes. Type 7 stores no redundant values - the dimension is plain Type 2 - and shifts the cost to carrying an extra key on the fact and resolving current values through a current view. Type 7 keeps the dimension clean and loads simpler; Type 6 keeps the fact narrower. The right pick depends on whether you'd rather absorb the cost in the fact's width or the dimension's update logic.

Frequently asked questions

What is SCD Type 7? A technique that puts both a version surrogate key and a durable natural key on the fact table over a Type 2 dimension, so queries get historical or current attribute views by choosing which key to join on.

How does it differ from Type 6? Type 6 adds redundant current-value columns to each history row (overwritten on change); Type 7 keeps a pure Type 2 dimension and resolves current values through a current view via the durable key - no bulk overwrites.

What's the durable key? The natural/business identifier for the entity that stays constant across all its versions (e.g. product_id), as opposed to the per-version surrogate key.

What's the trade-off? Type 7 keeps the dimension and its loads clean but widens the fact with a second key and needs a current view; Type 6 keeps the fact narrow but adds redundant columns and heavier dimension updates.