What is the difference between a fact table and a dimension table in a star schema, and how does a star schema compare to a snowflake schema?

technical-conceptual · Junior level · data-ml

What the interviewer is really asking

Assess whether the candidate understands dimensional modeling fundamentals and can reason about the normalization trade-off between star and snowflake schemas.

What to say

• Define the two table roles: a fact table holds the measurable events or metrics (revenue, quantity, clicks) plus foreign keys to dimensions; a dimension table holds the descriptive context you slice and filter by (customer, product, date, location).
• Define the star schema: one central fact table surrounded by denormalized dimension tables, so most analytical queries join the fact to a few wide dimensions — fewer joins, fast and simple to query.
• Contrast snowflake: dimensions are normalized into sub-tables (e.g. product → category → subcategory), which saves storage and reduces redundancy but needs more joins, so queries are more complex and often slower; the trade-off is query simplicity/speed (star) vs storage/normalization (snowflake).

What to avoid

• Mixing up the two table types — putting descriptive attributes in the fact table or numeric measures as the core of a dimension.
• Claiming snowflake is always better because it's normalized, ignoring that the extra joins slow analytical queries.
• Treating the choice as purely academic rather than driven by query patterns, storage cost, and how the warehouse engine handles joins.

Example answers

Strong: For a sales mart I built a fact_sales table with the measures (amount, quantity) and foreign keys to dim_customer, dim_product, and dim_date, keeping the dimensions denormalized so the BI tool needed only single joins — analysts found it easy and queries were fast.

Weak: A fact table and a dimension table are the same; you just name them differently.