Chapter 07 · Slowly Changing Dimensions: Types 0–7, History, and Effective Dating
Hybrid SCD Patterns (Types 4–7), Mini-Dimensions, and Current-vs-Historical Attribute Requirements
Compare Kimball Types 4–7, mini-dimensions, and hybrid as-was/as-is reporting patterns so current and historical requirements are satisfied deliberately rather than accidentally.
Learning outcomes
Type 2 is not always sufficient or economical. AtlasMart's core customer identity is relatively stable, while profile attributes such as engagement band, value tier, or risk band can change much more frequently. At the same time, executives may want historical facts reported both by the profile that was true then and by today's profile. Types 4–7 are structured responses to those requirements.
Explain Type 4 mini-dimensions and why rapidly changing attribute groups can be separated from the base dimension.
Explain Type 5's current-profile Type 1 outrigger on top of a mini-dimension.
Explain Type 6 current attributes embedded across Type 2 rows and its overwrite implications.
Explain Type 7's dual durable/surrogate-key access paths for as-is and as-was reporting.
Choose among Types 4–7 from query requirements and maintenance cost rather than from numeric sequence.
Chapter 07 preserves the accepted AtlasMart fact controls from Chapters 01–06: paid sales remain 7 order lines, 4 paid orders, 9 sold units, 625 paid GMV, 380 cost-at-sale, and 245 gross profit. The September 20 inventory snapshot remains 137 units. Chapter 07 changes only the customer-history representation: prior chapters treated customer attributes as a current-state dimension; this chapter migrates that customer domain to versioned Type 2 rows so historical facts can bind to the customer version that was valid at business event time. The migration is explicit and must reconcile all previously accepted fact totals.
The mandatory lab uses Python's standard-library
sqlite3 module and synthetic local data. Record
your actual Python and SQLite versions before running it. The
lab uses business-effective dates and half-open intervals
[effective_from, effective_to). Those are
explicit course conventions, not universal database syntax or
legal-retention policy. Performance, cloud cost, CDC delivery
guarantees, and production concurrency behavior are outside
what this small fixture proves.
Kimball Type numbers 4–7 describe specific hybrid techniques. They are not a universal standard implemented automatically by database engines, and teams sometimes use the labels inconsistently. State the actual table/key behavior in design documents even when using the type number.
1. Type 4: split rapidly changing profile attributes
A mini-dimension extracts a cluster of frequently changing attributes from a large base dimension. The fact stores both the base customer key and the mini-dimension profile key that applied when the measurement occurred. This avoids creating a full new base-customer row for every profile change.
CREATE TABLE dim_customer_profile ( profile_sk INTEGER PRIMARY KEY, engagement_band TEXT NOT NULL, value_band TEXT NOT NULL, risk_band TEXT NOT NULL, UNIQUE(engagement_band,value_band,risk_band));-- A fact can carry both customer identity and the profile in effect.-- fact_sales(..., customer_sk, profile_sk, ...);The profile rows represent reusable combinations, not one row per customer. If two customers share the same engagement/value/risk combination they can point to the same mini-dimension member.
2. Type 5: historical mini-dimension plus current profile reference
Type 5 builds on Type 4 by storing the customer's current profile key as a Type 1 reference in the base dimension. Historical facts still retain their old profile key, while a current-customer report can follow the overwritten current-profile reference without first traversing a fact table.
| Question | Path |
|---|---|
| What profile applied when this sale occurred? | fact.profile_sk → mini-dimension |
| What is the customer profile now? | base customer.current_profile_sk → mini-dimension |
3. Type 6: Type 2 history plus current attributes on every version row
Type 6 retains the Type 2 historical attribute and also places a
current-version attribute alongside it. When the current value
changes, the current attribute is overwritten on every Type 2
row sharing the durable key. A fact joined by its historical
surrogate can therefore expose both
segment_as_was and segment_current.
The benefit is query convenience. The cost is update amplification and the need to label columns clearly so analysts do not accidentally group by the current column while believing it is historical.
4. Type 7: dual surrogate and durable keys
Type 7 reaches the same “as-was plus as-is” goal through two join paths rather than by overwriting current attributes into every historical row. The fact stores the historical surrogate key and the durable customer key. One semantic view joins surrogate-to-version for as-was attributes; another joins durable key to the current dimension row for as-is attributes.
| Perspective | Fact key used | Dimension filter | Meaning |
|---|---|---|---|
| As-was | customer_sk | none beyond version key | attributes true at event time |
| As-is | durable_customer_id | is_current = 1 | today/current governed attributes |
5. Deliberately wrong approach: use a hybrid because its number is “more advanced”
Types 5–7 add flexibility but also add key paths, overwrite behavior, ETL work, semantic-layer obligations, and testing surface. A team that only needs historical truth should not adopt dual current/historical machinery merely because Type 7 sounds newer.
AtlasMart keeps Chapter 07's mandatory loader as straightforward Type 2. The hybrid techniques are modeled and tested conceptually here, but they do not become prerequisites for later chapters unless a later requirement actually needs them.
6. Decision matrix for AtlasMart
| Requirement | Preferred starting technique | Reason |
|---|---|---|
| Never change original signup channel | Type 0 | original value is the business truth |
| Correct misspelled name with no historical reporting need | Type 1 | intentional history destruction |
| Report sales by segment valid at sale time | Type 2 | preserve version history |
| Carry current and previous classification only | Type 3 | bounded alternate reality |
| High-churn engagement/value/risk profile | Type 4 | separate rapidly changing profile combinations |
| Historical profile plus easy current-profile lookup | Type 5 | mini-dimension + current outrigger |
| Historical and current attribute on same Type 2 rows | Type 6 | current Type 1 attribute copied across history |
| Separate as-was and as-is semantic views | Type 7 | dual surrogate/durable key paths |
Knowledge check
Check your understanding
- What problem does a Type 4 mini-dimension address?
- What does Type 5 add to Type 4?
- What maintenance behavior distinguishes Type 6?
- How does Type 7 produce current versus historical views?
- Why does AtlasMart keep the mandatory Chapter 07 loader as Type 2?
Review the answers
1. A rapidly changing group of profile attributes that would otherwise cause excessive version growth in the base dimension.
2. A Type 1 current-profile reference from the base dimension to the mini-dimension.
3. Current attributes are overwritten across all Type 2 rows for the same durable entity while historical attributes remain version-specific.
4. Historical joins use the Type 2 surrogate key; current joins use the durable key constrained to the current dimension row.
5. The stated requirement is historical customer truth; adding hybrid complexity without a demonstrated current-plus-historical query requirement would be unnecessary.
Summary and next step
Advanced SCD patterns are combinations designed for specific current-versus-historical needs. Next, focus on the upstream problem that every SCD loader must solve: deciding whether an incoming source record is actually different, and whether that difference is a change, a correction, or noise.
Authoritative references
- Kimball Group — Dimensional Modeling Techniques — Authoritative index listing SCD Types 0 through 7 and their standard names.
- Kimball Group — Slowly Changing Dimensions — Why changing descriptive attributes require deliberate history policy.
- Kimball Group — Slowly Changing Dimensions, Part 2 — Type 2 new-row mechanics and Type 3 alternate-reality framing.
- Kimball Group — Design Tip #152 — Definitions and intent for advanced/hybrid SCD Types 4–7.
- SQLite — Partial Indexes — Used by the local lab to enforce one current row per durable customer.
- SQLite — CREATE TABLE — Constraint semantics used by the deterministic local lab.