Pull-quote: “There is no single right model. There is the right model for the workload.”
Why this matters
Dimensional modeling is a forty-year-old discipline. Lakehouse architecture is a five-year-old discipline. Most teams import their old habits into the new platform and produce models that work but underperform — or models that look modern but break under load.
The right approach is workload-driven.
Four patterns to choose from
| Pattern | When to use | Strengths | Weaknesses |
|---|---|---|---|
| Star schema | Reporting and dashboards dominate; Power BI / Tableau is the primary consumer | Familiar; BI-tool friendly; fast slicing on Photon-enabled Delta | Less agile to change; many-to-many requires bridge tables |
| Data Vault 2.0 | Many sources; auditability is required; the model needs to evolve continuously | Auditable; agile; handles many sources; clear separation of business keys, satellites, and links | More tables; queries usually need a presentation layer |
| One Big Table | API-driven sub-second queries dominate; consumers are applications, not analysts | Sub-second queries; simple semantics for app developers | Joins move into ETL; updates can be expensive |
| Lakehouse Federation | Cross-system reporting without governance ownership | No data movement; fast to deliver | Performance depends on source; governance has to be explicit |
Decision tree
Primary consumer of the model?
├── Analysts / BI tools ──► Star schema (consider Direct Lake)
├── Apps / APIs ──► One Big Table or Star with caching
├── Many sources, audit ──► Data Vault 2.0
└── Cross-system reporting, no copy possible ──► Lakehouse FederationHow we structure the medallion architecture
Regardless of model pattern, we maintain a Bronze/Silver/Gold separation:
| Layer | Purpose | Typical retention |
|---|---|---|
| Bronze | Raw + arrival timestamp + source ID; immutable | Long (years) |
| Silver | Parsed, conformed, deduplicated; data quality enforced | Medium (months to years) |
| Gold | Business-ready aggregates / dimensions / facts | Short to medium |
The model pattern (star, vault, OBT) lives in Gold.
When to mix
Mixing is normal. A typical enterprise customer ends up with:
- Data Vault 2.0 for the foundational integration of multiple sources
- Star schema in Gold for analytical consumers
- One Big Table in Gold for app consumers
- Lakehouse Federation for occasional cross-system reporting
Closing
Dimensional modeling on Delta Lake is dimensional modeling, with new physics. Photon, liquid clustering, and Z-order are the storage primitives that change query performance economics. The choice of model still depends on the workload — but the trade-offs are different now than they were a decade ago.
