Type 7: Dual Type 1 and Type 2 Dimensions

Type 7 achieves the exact same goal as Type 6, allowing analysts to report on both the historical and current state of an entity. However, it moves the mechanism from the dimension to the fact table. Instead of maintaining an overwritten column on every historical row, the fact table carries two keys to the same entity:

• Surrogate key (e.g., customer_key): This points to the specific Type 2 row that was in effect at the time of the event. This is the historical path.
• Durable key (e.g., customer_id): A value that never changes for an entity across all its versions. This is the current path.

NOTE

A durable key (often called a natural or business key) is an identifier that remains associated with the same entity and never changes. Unlike a surrogate key that changes every time a new historical row is created, a durable key remains exactly the same across all versions of that entity.

The dimension itself remains a standard Type 2 table with no Type 1 current-value columns and no retrospective updates. The durable key connects to the current dimension row by joining to a view of that same dimension filtered only for active rows (is_current = TRUE).

Example (Type 7)

Alice moves from New York (NY) to California (CA) on May 10, 2024.

Dimension (dim_customer) - unchanged Type 2, no current_state column:

customer_key	customer_id	name	state	valid_from	valid_to	is_current
101	C-101	Alice	NY	2023-01-15	2024-05-09	FALSE
102	C-101	Alice	CA	2024-05-10	9999-12-31	TRUE

Fact Table (fact_sales) - carries both keys:

fact_sales_id	customer_key	customer_id	sales_amount
9901	101	C-101	$50.00

Joining on customer_key reports the 2023 sale under New York. Joining on customer_id to the current view reports it under California. The dimension was never rewritten to make this possible.

NOTE

While fact tables normally use surrogate _key columns to join to dimensions, Type 7 introduces a deliberate exception. The durable customer_id is stored on the fact table specifically to provide a join path to the Current-State view.

Trade-offs (Type 7)

The advantage over Type 6 is that there are zero retrospective updates. The dimension table remains an append-only Type 2 table, making it highly efficient for modern columnar data warehouses to process at scale.

The cost is increased complexity for the end user. Because the Fact table offers two distinct join paths to the customer data, analysts must clearly understand the data model to choose the correct one. To prevent confusion, data teams typically hide this architecture behind a semantic layer in their Business Intelligence tool or they provide pre-built database views so users never have to write the joins by hand.