Most of us who’ve worked in data platforms—whether in a traditional warehouse or a modern lakehouse—have faced the same question:
And almost immediately, familiar contenders come to mind:
- Star schema
- Snowflake schema
- Data Vault
- One Big Table (OBT)
- Bigtable-style designs
Each comes with strong opinions, historical baggage, and a long list of “pros and cons.”
But here’s the uncomfortable truth:
Yet, time and again, teams try to standardize on one modeling philosophy across the entire stack—and that’s where things start to break.
The usual suspects (and why they all fall short alone)
Let’s quickly level-set on the common patterns.
Snowflake schema — elegant, but overly academic
The snowflake schema extends the star schema by normalizing dimensions into multiple related tables.
Strengths:
- Reduced redundancy
- Strong data integrity
- Explicit hierarchical modeling
- Easier maintenance of shared attributes
Reality check:
- Disk cost is no longer a primary concern in modern cloud environments
- Query performance suffers due to excessive joins
- Complexity kills usability for analysts
- ETL becomes heavier and more fragile
This model made a lot of sense in the era of expensive storage and constrained compute. Today, those constraints have shifted.
Star schema — still the workhorse
The star schema remains the most widely adopted dimensional model.
Strengths:
- Fast query performance
- Simple and intuitive
- Works seamlessly with BI tools
- Enables self-service analytics
Trade-offs:
- Hard to manage complex hierarchies
- Limited flexibility for evolving data structures
- Can introduce inconsistencies if not carefully governed
- Not ideal for raw ingestion or ML workflows
It’s still incredibly effective—but only in the right context.
Data Vault — powerful, but heavy
The Data Vault approach is built for enterprise-scale integration and auditability.
Strengths:
- Handles multiple sources elegantly
- Full historical tracking (audit-ready)
- Highly scalable
- Excellent for lineage and compliance
Challenges:
- Querying is complex and slow
- Explosion of tables (hubs, links, satellites)
- Requires automation to be viable
- Not analyst-friendly without downstream models
For large, regulated environments, it’s incredibly valuable. For smaller teams, it can feel like overengineering.
One Big Table (OBT) — fast, until it isn’t
OBT flattens everything into a single wide table.
Strengths:
- No joins → fast reads (initially)
- Easy for analysts
- Quick to implement
But then reality kicks in:
- Data duplication → incorrect metrics
- Loss of referential integrity
- Poor scalability as dimensions increase
- Debugging becomes painful
- Security becomes a nightmare
It’s a shortcut—not a foundation.
Bigtable / key-value designs — built for access patterns
Bigtable-style modeling flips the paradigm:
Strengths:
- High performance at scale
- Optimized for specific access patterns
- Great for operational and real-time systems
Limitations:
- Requires upfront understanding of access patterns
- Not flexible for exploratory analytics
- Hard to retrofit into BI workflows
The real problem: we apply one model everywhere
Here’s where most architectures go wrong:
That’s like using the same blueprint for:
- raw ingestion
- transformation
- analytics
- machine learning
It doesn’t work—because each stage has different constraints, users, and goals.
A better approach: model by data layer
Instead of asking “Which model is best?”, ask:
Let’s break that down.
Bronze layer — preserve reality (not structure)
Goal: Capture raw data with minimal transformation.
This is not where you optimize for:
- query performance
- usability
- reporting
This is where you optimize for:
- fidelity
- traceability
- ingestion speed
Recommended approach:
- Semi-structured or raw formats (JSON, Parquet)
- Minimal normalization
- Append-only patterns
- Schema-on-read
What NOT to do:
- Don’t force a star schema here
- Don’t over-model
- Don’t “clean” too early
Silver layer — normalize and integrate
Goal: Create a clean, consistent, and integrated view of the data.
This is where things start to get interesting.
Recommended approaches:
- Data Vault (for complex, multi-source systems)
- 3NF / normalized models
- Light relational modeling
Why?
- You need to reconcile multiple systems
- You need consistent definitions
- You need auditability and lineage
This layer acts as the system of integration.
Gold layer — optimize for consumption
Goal: Deliver data that is easy, fast, and intuitive to use.
This is where star schemas shine.
Recommended approaches:
- Star schema
- Aggregated marts
- Carefully designed OBT (with guardrails)
Why?
- BI tools expect simple structures
- Analysts need speed and clarity
- Business logic should be explicit
Important nuance: OBT can be useful here, but only:
- for specific use cases
- with strong governance
- and not as the system of record
The key insight: coexistence, not competition
The biggest mental shift is this:
A mature architecture might look like:
- Bronze: raw ingestion (semi-structured)
- Silver: Data Vault / normalized integration
- Gold: star schema / marts / curated tables
Each layer solves a different problem. Each model plays a different role.
So… should you replace what you have?
Almost never.
Most of us don’t get to build from scratch. We inherit:
- legacy marts
- partially modeled warehouses
- inconsistent pipelines
The real question is:
In most cases:
- Keep what works
- Stabilize what’s fragile
- Introduce new patterns where needed
This avoids:
- massive rewrites
- disruption to the business
- unnecessary risk
Final thought: be intentional, not dogmatic
Data modeling has always been full of strong opinions.
But modern platforms have changed the game:
- Storage is cheap
- Compute is elastic
- Tools are flexible
So clinging to a single modeling philosophy no longer makes sense.
Design each layer for its purpose. Let models coexist.
And most importantly:
Stop asking “Which model is best?” Start asking “Best for what?”