How classic Kimball design techniques — beyond facts and SCD2 — still add value in a model-driven, business-friendly methodology.
Summary — Key Takeaways
- Kimball’s dimensional modeling patterns remain a rich toolkit — from fact table types and conformed dimensions to hierarchy handling and surrogate keys.
- Facts and dimensions still matter — but must be enhanced with dual SCD2 historization and stable Access Views.
- Patterns such as role-playing dimensions, accumulating snapshots, conformed dimensions, degenerate/junk dims, and bridge tables integrate well with a model-driven, time-aware pipeline.
- The goal is not to rebuild classic warehouses, but to adapt proven design ideas for reproducible, semantic, and AI-friendly data platforms.
Why Revisit Kimball Today?
Our methodology focuses on:
- Model-Driven Engineering — universal supertypes/subtypes, business-aligned semantics.
- Business-Friendly Mapping — versioned, text-based mapping in Git.
- Access Views (AVs) — contract layers shielding consumers.
- Dual SCD2 — functional & technical time for auditability and reproducibility.
But good architecture doesn’t mean ignoring decades of best practice.
Kimball’s patterns remain powerful design heuristics — if we reinterpret them for today’s time-aware, versioned, and AI-assisted world.
Kimball Fundamentals Still Hold
- Business process first: Model data around real processes (Orders, Shipments, Payments).
- Declare the grain: Know exactly what each row means — include temporal grain (validity & technical version).
- Dimensions provide context: “Who / what / where / when” still drives understandability.
- Facts measure events: But in a time-aware platform, facts themselves can be versioned or snapshot-based.
Lesson: Kimball’s process–grain–dimension–fact discipline fits perfectly with our Access View contracts and dual-time historization.
Fact Table Patterns Worth Keeping
Press enter or click to view image in full size
Caution: avoid mixing multiple grains; ensure fact–dimension joins respect functional & technical time.
Dimension Patterns That Still Shine
- Surrogate keys: Essential for joining facts to the correct dimension version.
- Role-playing dimensions: Reuse a single Date or Customer dimension with multiple semantic roles (e.g., order date vs. ship date).
- Degenerate & junk dimensions: Clean up stray attributes and transactional flags.
- Flattened dimensions: Still the best user experience for reporting; AVs can expose flattened views.
- Mini-dimensions (Type 5 hybrids): Useful when some fast-changing attributes should be split off for performance.
Tip: combine surrogate keys with dual time columns so Access Views can always pick the right version.
Conformed Dimensions & Bus Architecture
Kimball’s conformed dimensions — shared, consistent dimensions across processes — align with our universal models.
- Use AVs as the conformance enforcement point.
- Maintain versioned mappings so downstream users stay stable when conformed dims evolve.
Adaptation: Keep temporal conformance — the “same customer” must resolve consistently across time.
Beyond SCD2 — Other Slowly Changing Dimension Techniques
Kimball lists SCD Types 0–7. Our architecture already uses dual SCD2 (functional + technical).
- Type 1 (overwrite): Only for non-critical attributes; document clearly.
- Type 3 (previous value columns): Rarely needed; limited history.
- Type 5/6 hybrids: Can help performance but require version control.
- Mini-dims: Effective for rapidly changing, non-key attributes.
Guideline: Default to Type 2 with functional & technical time; mix hybrids only when there’s a proven performance or usability need.
Handling Hierarchies
Kimball offers:
- Fixed-depth or leveled hierarchies
- Bridge tables for ragged/variable depth
- Snowflaking or outriggers for sub-dimensions
In our methodology:
- Model parent–child relationships explicitly, with validity periods.
- AVs can flatten or expose bridge tables depending on consumer need.
- Use graphs/knowledge layers for deep or complex hierarchies.
Integrating Kimball Into a Modern Architecture
- Start with semantic models — process & grain as Kimball suggests.
- Overlay time — every table and relationship carries functional & technical validity.
- Use Access Views — provide clean, stable contracts while hiding complex SCD logic.
- Selective adoption — role-playing dims, snapshots, conformed dims add value; avoid overcomplicating with exotic hybrids.
- Govern & document — annotate mapping files with Kimball pattern names (e.g., “Accumulating Snapshot Fact”, “Mini-Dim Type 5”).
- Leverage modern tech — Delta Lake time travel + AV contracts make these patterns easier to implement and evolve.
Why This Matters
Kimball’s work remains an essential vocabulary and set of mental models. But we now combine it with model-driven contracts, dual-time historisation, and semantic/AI layers to build platforms that are reproducible, business-friendly, and future-proof.
Knowing these patterns lets us design smarter AVs, better mappings, and more comprehensible data for consumers and AI tools alike.