Star vs Data Vault vs 3NF
Model choice is a systems decision, not a style preference.
Choose based on ingestion volatility, analytics requirements, governance, and team operating maturity.
Decision Matrix #
| Criterion | Star Schema | Data Vault | 3NF EDW (Inmon) |
|---|---|---|---|
| Analyst usability | High | Low (raw), medium (with marts) | Low-medium |
| Source change tolerance | Medium | High | Medium |
| Auditability/lineage | Medium | High | Medium |
| Implementation complexity | Medium | High | Medium |
| Query simplicity | High | Low | Medium |
| Best fit | BI/analytics marts | enterprise integration layer | centralized integration warehouse feeding marts |
The 3NF column means an Inmon-style normalized enterprise data warehouse. Source OLTP systems are also 3NF, but that is application design, not a warehouse-architecture choice.
Recommended Architectures #
- Startup / small team: star-first marts for fast delivery
- Regulated multi-source enterprise: vault ingestion + dimensional marts
- Application core systems: 3NF operational model + analytical serving layer
Advanced Tradeoff Questions #
Ask before choosing:
- How frequently do source schemas change?
- Do we need replayable lineage and historization guarantees?
- How many analysts build self-serve models?
- Are metric definitions currently fragmented?
- What is acceptable freshness and compute spend?
Migration Strategy #
You do not need a big-bang switch.
Typical path:
- stabilize source contracts
- introduce canonical keys and model tests
- build marts on highest-value domains first
- retire duplicated legacy KPI logic gradually
Interactive SQL Check #
Run this policy-level rollup to practice separating entity-level pre-aggregation from final product-line reporting grain.
Loading SQL editor...
Related Lessons #
Warning
There is no perfect model.
Pick the architecture that minimizes your highest business risk, then evolve deliberately.