Star vs Snowflake Schema: Database Design for Analytics
After Analytics Engineering & the Metrics Layer explained why the 'Revenue' metric must mean the same thing in Tableau, Python, and the data warehouse, the next question is how analytics-ready tables should be designed. Star Schema and Snowflake Schema are practical database design choices for analytics, especially when balancing faster BI queries with storage and JOIN complexity. In interviews, this matters because schema design directly affects dashboards, aggregated queries, and data warehousing decisions.
- Star Schema: Central Fact table (Sales, Orders, Events) connected to multiple Dimension tables (Customer, Product, Time, Location, Promotion). Simple, fast for BI queries.
- Snowflake Schema: Dimension tables further normalised into sub-dimensions. Saves storage but requires more JOINs - slower for analytics.
- Rule of thumb: Use Star Schema for analytics/BI (query performance > storage). Use Snowflake Schema when storage is a concern or dimensions are very large.
- Star Schema is FAST - few JOINs needed, while Snowflake Schema is SLOWER - more JOINs.
- Star Schema has more redundancy, higher storage, while Snowflake Schema has less redundancy, compact.
- OLAP (Online Analytical Processing) uses Denormalised (Star/Snowflake) for speed.
Big Picture: Analytics Schema Choices
Database design for analytics compares Star Schema, Snowflake Schema, and Data Lakehouse structures by structure, query speed, storage, complexity, best use case, and Indian examples. The core trade-off is simple: Star Schema prioritises query performance for analytics/BI, while Snowflake Schema reduces redundancy but adds JOIN logic.
Use Star Schema for analytics/BI (query performance > storage). Use Snowflake Schema when storage is a concern or dimensions are very large.
Star Schema Architecture
Star Schema: Central Fact table (Sales, Orders, Events) connected to multiple Dimension tables (Customer, Product, Time, Location, Promotion). Simple, fast for BI queries.
The structure is a Denormalised fact + flat dimensions. It is FAST - few JOINs needed, has more redundancy, higher storage, and is simple - easy for analysts.
Star Schema is best for BI dashboards, aggregated queries. The Indian example is Flipkart Redshift analytics DW.
Snowflake Schema
Snowflake Schema: Dimension tables further normalised into sub-dimensions. Saves storage but requires more JOINs - slower for analytics.
The structure is a Normalised fact + sub-dimensions. It is SLOWER - more JOINs, has less redundancy, compact storage, and more complex JOIN logic.
Snowflake Schema is best for large dimensions, data warehousing. The Indian example is Banks' Teradata data warehouse.
Where Data Lakehouse Fits
Data Lakehouse appears as a third architecture in the same analytics design comparison. Its structure is Delta Lake / Iceberg tables, medallion architecture.
It is FAST - columnar + partitioned, highly efficient (Parquet/ORC), and moderate - requires infrastructure. It is best for mixed ML + BI workloads, petabyte scale, with Swiggy's Delta Lake on S3 as the Indian example.
OLTP vs OLAP Context
OLTP means Online Transactional Processing, used to record day-to-day business transactions. OLAP means Online Analytical Processing, used to analyse large volumes of historical data.
This distinction matters because OLTP uses highly normalised schemas to avoid redundancy, while OLAP uses Denormalised (Star/Snowflake) for speed.
Structuring a Star vs Snowflake Schema Interview Answer
"How would you choose between a Star Schema and a Snowflake Schema for analytics/BI?"
Do not frame the choice as normalisation versus denormalisation only. Tie it back to the analytics trade-off: query performance > storage for Star Schema, and storage concern or very large dimensions for Snowflake Schema.
The most frequent error is choosing Snowflake Schema only because it saves storage, without explaining that it requires more JOINs and is slower for analytics. In interviews, this costs points because the rule of thumb is to use Star Schema for analytics/BI when query performance > storage.
Conclusion
Star Schema and Snowflake Schema are analytics design trade-offs: Star Schema is simple and fast for BI queries, while Snowflake Schema is compact but more complex. The final takeaway is to choose based on query performance, storage concern, and dimension size.