Dimensional modeling optimizes for analytics queries.
Star schema:
- Central fact table (events, transactions)
- Surrounded by dimension tables (users, products, dates)
- Dimensions connect directly to facts
Snowflake schema:
- Dimensions are normalized further
- Product → Category → Department
- More joins, less redundancy
When to use star: Most analytics. Simpler queries, faster performance.
When to use snowflake: Storage concerns, dimension data changes frequently.
Interview default: Propose star schema unless there's a specific reason not to.