SCD Type 4 splits dimension attributes into a base table for current values and a separate history table or mini-dimension to track changes over time. SCD Type 6 combines Type 1, Type 2, and Type 3 patterns into a single table (1 + 2 + 3 = 6) to support both historical as-was and current as-is reporting.
Type 4 mini-dimensions and history
In SCD Type 4, the primary table (dim_customer) holds only the current state of each entity with one row per natural key, keeping lookups and standard joins fast. Historical profile changes are written to a companion history table (dim_customer_history) or extracted into a mini-dimension for volatile attributes like credit score band:
- The base dimension stays compact, with exactly one row per customer.
- The history table records changes with valid date ranges for deep audits.
- Fact tables can link directly to the mini-dimension key active at event time.
Type 6 hybrid pattern
Type 6 merges three techniques on a single row:
- Type 2: Inserts a new row for every attribute change, with
valid_from,valid_to, and a surrogate key. - Type 1: Overwrites a
current_statecolumn across all historical rows of that entity whenever an update occurs. - Type 3: Stores a
previous_statecolumn to capture the prior value.
customer_sk | customer_id | historical_tier | current_tier | valid_from | valid_to 101 | C-900 | Silver | Gold | 2025-01-01 | 2025-06-30 102 | C-900 | Gold | Gold | 2025-07-01 | 9999-12-31
A short look at the query flexibility:
- As-was reporting: Group by
historical_tierto see revenue categorized by the customer status at purchase time. - As-is reporting: Group by
current_tierto evaluate historical sales under the customer status today.
Baseline Type 0
For completeness, SCD Type 0 represents fixed attributes that never change after creation, such as original loan origination date or applicant date of birth. Combining these techniques allows data engineers to tailor dimension structures to exact reporting and audit requirements.