TL;DR: Dimensional modeling splits analytics data into fact tables (measurable events like a sale, holding foreign keys and numeric measures) surrounded by dimension tables (the descriptive context: customer, product, date). A star schema keeps dimensions flat and denormalized (fewer joins, fast queries); a snowflake normalizes them into sub-tables (less redundancy, more joins). OLTP normalizes for write integrity, analytics denormalizes for read speed. Default to the star schema for warehouses.
How to approach it. Contrast the two goals up front: transactional systems (OLTP) optimize consistent writes and avoid update anomalies (so they normalize), while analytical systems (OLAP, the warehouse) optimize fast aggregate reads (so they denormalize). Then define facts vs dimensions, star vs snowflake, and make the call: star by default, with the reasoning.
A strong answer. Facts and dimensions. A fact table stores measurable business events at a defined grain: one row per sale, per page view, per shipment, holding numeric measures (amount, quantity) plus foreign keys to dimensions. It is tall and narrow and grows continuously. A dimension table holds the descriptive context you slice and filter by: customer, product, store, date, each with attributes (name, category, region). Dimensions are wide, relatively small, and change slowly (hence SCD techniques for tracking history).
Star vs snowflake. A star schema joins a central fact table directly to denormalized dimension tables. Each dimension is one flat table (the product dimension carries category, subcategory, and brand as columns, accepting redundancy). Fewer joins means simpler, faster analytical queries. A snowflake schema normalizes dimensions into sub-dimensions (product to category to department as separate tables). Less storage and redundancy, but queries pay for extra joins, which is slower and more complex.
| Axis | Star | Snowflake |
|---|---|---|
| Dimension shape | Flat, denormalized | Normalized into sub-tables |
| Joins per query | Fewer | More |
| Redundancy | Higher (accepted) | Lower |
| Query speed | Faster | Slower |
| Pick when | Default for warehouses | A dimension is huge or shared and redundancy genuinely costs |
Normalize vs denormalize (the why). OLTP normalizes to 3NF to avoid update anomalies and keep writes consistent: a customer's address lives in one place, so an update touches one row. Analytics is read-heavy, join-heavy aggregation over huge tables, and joins are expensive at scale, so warehouses denormalize (the star schema, or wide tables) to minimize joins and speed reads. The redundancy is safe because the data is largely append-only and rebuilt by pipelines, not edited in place. Columnar warehouses and lakehouses make wide denormalized tables even more attractive: columnar storage plus compression makes scanning a wide fact table cheap.
The defensible position: model facts and dimensions, default to a star schema, denormalize for read performance, and reach for snowflaking only when a specific dimension's redundancy is a real, measured problem.
Key takeaways.
- Define the fact-table grain first (what one row represents); it is the single most consequential modeling decision.
- Facts are measurable events with numeric measures; dimensions are the descriptive context you filter and group by.
- Default to the star schema for warehouses: fewer joins, faster reads, and columnar storage absorbs the redundancy.
- Snowflake only when one dimension's redundancy is genuinely expensive; do not normalize for purity.
What interviewers probe next.
- "What is the grain of a fact table?" The exact event one row represents (one order line, one daily snapshot); defining grain first drives every other choice.
- "Why not just use the normalized OLTP schema for analytics?" Too many joins and row-by-row structure make large aggregations slow; OLTP optimizes writes, OLAP optimizes reads.
- "Slowly changing dimensions?" Track dimension history with SCD (Type 2 keeps versioned rows with validity windows) so a sale joins the customer attributes as they were at the time.
- "Fact table types?" Transaction (per event), periodic snapshot (state at intervals), accumulating snapshot (a process with milestones).
Common mistakes.
- Applying OLTP normalization to an analytics warehouse, drowning queries in joins.
- Confusing facts (measurable events) with dimensions (descriptive context).
- Not defining the fact-table grain before modeling.
- Over-snowflaking dimensions for purity when a star schema would query far faster.