Chapter 08 · Special Dimension Patterns: Date/Time, Role-Playing, Junk, Degenerate, Mini, and Inferred Members
Mini-Dimensions for Rapidly Changing Attributes and Profile/Segment History
Split rapidly changing customer-profile bands into a mini-dimension so facts preserve event-time profile context without exploding the core customer Type 2 dimension.
Learning outcomes
AtlasMart's core customer dimension already tracks governed identity, geography, lifecycle, and enterprise segment history. Marketing, however, recomputes spend band, engagement band, loyalty tier, and churn-risk band much more frequently. If every profile change creates a new core customer Type 2 row, the dimension grows for behavioral volatility rather than durable descriptive history.
Define a mini-dimension as a reusable set of rapidly changing profile combinations, not a second customer identity table.
Separate slow/governed customer history from high-churn behavioral bands without losing event-time profile context.
Resolve profile_sk by business-effective assignment and attach it directly to the order fact.
Compare historical profile-at-event queries with current customer attributes without conflating the two realities.
Diagnose uncontrolled Type 2 row explosion and inappropriate mini-dimension use.
Chapter 08 starts from the accepted Chapter 07 state. Canonical paid sales remain 7 order-line facts, 4 paid orders, 9 units, 625 paid GMV, 380 cost-at-sale, and 245 gross profit; the September 20 inventory control remains 137 units. Customer history keeps Chapter 07's half-open business-effective intervals and as-of key resolution. This chapter adds special dimensions around that model: one governed date dimension, order/ship/due date roles, a low-cardinality order-flags junk dimension, order-number degenerate dimensions, a rapidly changing customer-profile mini-dimension, and an inferred-customer workflow. None of these additions silently changes the accepted sales measures or customer-history intervals.
The mandatory lab uses Python's standard-library
sqlite3 module and synthetic local data. Record
the actual Python and SQLite versions before execution.
Calendar labels, fiscal-year rules, company holidays,
unknown/not-applicable members, profile bands, and
inferred-member completion policy are explicit AtlasMart
course contracts—not universal standards. Cloud-engine
performance, distributed concurrency, CDC ordering,
jurisdiction-specific holidays, and production security
behavior are not proven by this local fixture.
AtlasMart keeps the Chapter 07 governed customer
segment in the core SCD2 dimension for
continuity. The mini-dimension uses separate high-churn
profile fields such as spend/engagement bands. If the business
later redefines “segment” as a rapidly changing profile
attribute, that would be a governed model migration—not a
silent rename.
1. A mini-dimension row is a profile combination, not a person
| profile_sk | Spend band | Engagement band | Loyalty tier | Churn risk |
|---|---|---|---|---|
| 1 | Low | Low | Bronze | High |
| 2 | Medium | High | Silver | Low |
| 3 | High | High | Gold | Low |
| 4 | Medium | Medium | Silver | Medium |
Many customers can share the same profile row. The mini-dimension surrogate key identifies the descriptive combination. A separate effective-dated assignment says which profile applied to a durable customer at a given business time.
CREATE TABLE dim_customer_profile ( profile_sk INTEGER PRIMARY KEY, spend_band TEXT NOT NULL, engagement_band TEXT NOT NULL, loyalty_tier TEXT NOT NULL, churn_risk_band TEXT NOT NULL, UNIQUE(spend_band,engagement_band,loyalty_tier,churn_risk_band));CREATE TABLE customer_profile_assignment ( durable_customer_id TEXT NOT NULL, effective_from TEXT NOT NULL, effective_to TEXT NOT NULL, profile_sk INTEGER NOT NULL REFERENCES dim_customer_profile(profile_sk), CHECK (effective_from < effective_to), PRIMARY KEY(durable_customer_id,effective_from));2. Resolve profile at the fact's business event time
SELECT p.profile_skFROM customer_profile_assignment pWHERE p.durable_customer_id = :durable_customer_id AND p.effective_from <= :event_date AND :event_date < p.effective_toORDER BY p.effective_from DESCLIMIT 1;The same half-open interval discipline used in Chapter 07 makes profile resolution deterministic. O1001 (18 September) sees D-CUST-001 profile 2, while O1003 (19 September) sees profile 3. The customer core Type 2 row and mini-profile row are independent keys because they model different histories.
3. Controlled failure: put every profile change into dim_customer_history
Imagine engagement band recalculates daily. Creating a new customer Type 2 row for every behavioral band change duplicates stable name/geography/lifecycle attributes and multiplies customer versions. Queries for durable identity become harder, ETL change detection becomes noisy, and storage growth follows scoring cadence rather than business-history need.
Repair: keep rapidly changing, analytically useful profile bands in the mini-dimension and attach the appropriate mini key to facts. Keep the base customer dimension focused on durable/governed descriptive history.
4. Historical profile-at-order analysis
SELECT p.loyalty_tier, p.engagement_band, COUNT(*) AS orders, SUM(f.order_amount) AS amountFROM fact_order_milestone fJOIN dim_customer_profile p ON p.profile_sk=f.profile_skGROUP BY p.loyalty_tier,p.engagement_bandORDER BY p.loyalty_tier,p.engagement_band;This query asks “what profile did the customer have when the order occurred?” It does not reclassify all historical orders under today's profile. A separate current-profile analysis would intentionally resolve the latest assignment by durable customer ID and should be labeled as-is/current truth.
5. Mini-dimension boundaries
Do not create a mini-dimension merely because a dimension is wide. The pattern earns its complexity when a subset of attributes changes much faster than the base dimension, has reusable combinations, and users need profile-at-event analysis. High-cardinality raw scores may need banding first; if every row has a unique continuous score tuple, the “mini” dimension may not be mini.
Profile definitions also need governance. If the thresholds for Low/Medium/High change, decide whether to version the profile taxonomy, restate history, or preserve the old definitions. A surrogate key alone does not solve semantic versioning.
6. Production judgment
The operational cost of a mini-dimension is extra key resolution and semantic-layer exposure; the benefit is controlling base-dimension growth while preserving historical profile context. Measure real attribute volatility and query requirements before adopting it.
Chapter 07 Type 4/5 concepts and this lesson meet at the same principle: history strategy follows the reporting requirement, not a pattern number.
Knowledge check
Check your understanding
- What does profile_sk identify?
- Why is the assignment table effective-dated?
- What remains in AtlasMart core customer SCD2?
- Why can daily profile changes be harmful in the base Type 2 customer dimension?
- When is a mini-dimension a poor fit?
Review the answers
1. A reusable combination of rapidly changing profile attributes, not the customer identity.
2. The fact must resolve the profile combination that was valid at the business event time.
3. Durable/governed attributes such as identity, geography, lifecycle, and the established enterprise segment.
4. They duplicate stable attributes and make dimension growth track scoring volatility.
5. When profile combinations are nearly unique/high-cardinality, change history is not needed, or the extra semantic complexity outweighs the benefit.
Summary and next step
AtlasMart can now preserve high-churn profile context without destabilizing durable customer history. Lesson 5 handles a different temporal problem: the fact arrives before complete customer context exists.
Authoritative references
- Kimball Group — Dimensional Modeling Techniques — Primary technique index for date, role-playing, junk, degenerate, mini-, and late-arriving dimension patterns.
- Kimball Group — Calendar Date Dimensions — Date attributes, fiscal periods, special dates, and explicit unknown/to-be-determined rows.
- Kimball Group — Role-Playing Dimensions — One physical dimension reused through logically distinct roles.
- Kimball Group — Junk Dimensions — Combining miscellaneous low-cardinality flags into one dimension.
- Kimball Group — Degenerate Dimensions — Transaction identifiers kept directly in fact tables when no descriptive dimension row exists.
- Kimball Group — Type 4 Mini-Dimension — Separating rapidly changing profile attributes and linking both base and mini-dimension keys to facts.
- Kimball Group — Late Arriving Dimension — Placeholder/inferred members and later completion of descriptive context.
- SQLite — CREATE TABLE — Constraint semantics used by the local deterministic lab.
- SQLite — Foreign Key Support — Local referential-integrity behavior used in the inferred-member failure test.