Type 6: Add Type 1 Attributes to a Type 2 Dimension

Type 6 achieves the same dual reporting goal as Type 5, providing both current and historical views of the same attribute. However, it does this inside a single dimension table rather than using a separate Mini-Dimension. The numbering serves as a mnemonic: Type 1 + Type 2 + Type 3 = Type 6.

The dimension uses three mechanisms at once to achieve this:

• Type 2: Each change inserts a new row with its own surrogate key and validity window, preserving the value as it was at the time.
• Type 3: An additional column stores the entity's current value alongside the historical one.
• Type 1: That additional column is overwritten on every row belonging to the entity whenever the value changes, so it always reflects the present.

The last point is what makes Type 6 work and what makes it expensive. A single change does not update one row - it updates every historical row for that natural key.

NOTE

Despite borrowing the extra-column idea from Type 3, the column holds a different thing. In Type 3 it stores the entity's previous value; in Type 6 it stores the entity's current value, repeated identically across all of that entity's rows.

Example (Type 6)

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

Before the move:

customer_key	customer_id	name	state	current_state	valid_from	valid_to	is_current
101	C-101	Alice	NY	NY	2023-01-15	9999-12-31	TRUE

After the move:

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

Row 101 keeps state = NY, so a sale made in 2023 still reports under New York. Its current_state has been rewritten to CA, so the same sale can also be grouped under Alice's present location. The two columns answer different questions from a single join.

Trade-offs (Type 6)

The main appeal is that analysts get both current and historical views from a single dimension, with no outrigger and no second join path to reason about.

Unlike Type 5, Type 6 is not primarily intended to simplify dimension-only queries about an entity's current state. Those queries can typically be handled by filtering the dimension on is_current = TRUE. Instead, Type 6 is particularly useful for Fact-based queries that need to group or filter historical facts using the entity's current state. Because current_state is repeated across all historical rows for the entity, the Fact table can join directly to the dimension and use the current value without requiring an additional lookup or join path.

For example, a query such as "Total historical sales by customers' current state" can use current_state directly from the dimension:

-- Without Type 6: re-join the dimension to itself on the natural key
SELECT c2.state, SUM(f.sales_amount) AS total_sales
FROM fact_sales f
JOIN dim_customer c1 ON f.customer_key = c1.customer_key
JOIN dim_customer c2 ON c1.customer_id = c2.customer_id
    AND c2.is_current = TRUE
GROUP BY c2.state;

-- With Type 6: current_state is maintained for all rows
SELECT
    d.current_state,
    SUM(f.sales_amount) AS total_sales
FROM fact_sales f
JOIN dim_customer d
    ON f.customer_key = d.customer_key
GROUP BY d.current_state;

The cost is the retrospective update. An entity with fifty historical rows requires fifty row updates for one attribute change. Modern columnar data warehouses typically process updates by rewriting entire files rather than editing data in place. This makes the Type 6 overwrite strategy significantly more expensive at scale than a simple Type 2 insert.