See it as a diagram
Everything below, as a diagram you can edit. Describe yours and see it in seconds.
No account needed · Editable canvas, not a picture
1. Modeling Patterns
Star Schema
Central fact table + denormalized dimension tables. The default choice for analytics. Simple joins, predictable performance, easy for BI tools.
Snowflake Schema
Normalized dimension tables (dimensions have sub-dimensions). Saves storage but adds join complexity. Rarely needed with modern warehouse pricing.
Wide / OBT (One Big Table)
Pre-joined denormalized table with all dimensions flattened. Best for simple dashboards and when query simplicity matters more than flexibility.
Data Vault
Hub + Satellite + Link tables for full auditability and historical tracking. Complex to build but excellent for regulated industries.
2. Star vs Snowflake Schema
| Aspect | Star Schema | Snowflake Schema |
|---|---|---|
| Dimension Structure | Denormalized (flat) | Normalized (sub-tables) |
| Query Complexity | Simple | Complex |
| Storage | More (redundant data) | Less (normalized) |
| BI Tool Compatibility | Excellent | Good |
| Recommendation (2026) | Preferred | Use only when storage is expensive |
Hot Take: Just Use Star Schema
In 2026, cloud storage costs pennies per GB. The join simplicity and BI compatibility of star schemas outweigh any storage savings from snowflake schemas. Default to star.
3. Architecture Layers
Raw / Bronze
Ingested data as-is from sources. No transformations. Full history. Schema-on-read.
Cleaned / Silver
Deduplicated, typed, filtered. Business keys resolved. Ready for joining across domains.
Business / Gold
Star/snowflake models, aggregations, KPI tables. Consumed by BI tools, APIs, and ML.
4. Platform Comparison
| Feature | Snowflake | BigQuery | Redshift |
|---|---|---|---|
| Compute Model | Multi-cluster | Serverless | Provisioned |
| Best For | Multi-cloud, sharing | GCP, streaming | AWS-heavy orgs |
| Pricing Model | Credits (compute) | Per TB scanned | Per node/hour |
5. Design Best Practices
Default to star schema for analytical models
Separate raw, cleaned, and business layers
Use surrogate keys (hashed natural keys)
Partition large fact tables by date
Cluster/sort by frequently filtered columns
Document every table and key column
Test for grain (one row per entity per time period)
Version your models in Git (dbt or similar)
6. FAQ
What is a star schema?
Star schema vs snowflake schema?
How do you choose between Snowflake, BigQuery, and Redshift?
Design your warehouse architecture
AI-generated warehouse diagrams with star schemas, fact tables, and cloud service icons.
Try Datadef Free