A star schema has one fact table surrounded by denormalized dimension tables, so most queries need a single join per dimension. Snowflaking normalizes a dimension into sub tables, which saves storage and centralizes shared attributes but adds joins. On a columnar warehouse, storage is cheap and joins cost more than duplication, so star is the default. Snowflake a dimension when it is genuinely large, hierarchical, or shared across many marts.
Why interviewers ask this
Interviewers use this to test whether you can reason about physical trade offs rather than recite Kimball. The best answers note that columnar compression makes denormalized dimensions cheap, that query engines handle broadcast joins on small dimensions well, and that the real argument for star is analyst usability rather than raw performance.
How to structure your answer
- Describe the star shape and why it is analyst friendly.
- Explain snowflaking as normalization of dimensions.
- Argue the trade off in terms of joins versus storage on columnar engines.
- Give a concrete case where snowflaking is right.
- Mention grain as the decision that precedes both.
Example answer
A star is a fact table at a defined grain with denormalized dimensions hanging off it, so an analyst writes one join per dimension and gets readable column names. I default to that, because on a columnar warehouse repeating a country name across two million rows compresses to almost nothing, and the small dimension gets broadcast anyway. Snowflaking makes sense when the dimension is actually large and hierarchical, for example a product dimension with a category tree that several marts need to share, since maintaining that hierarchy in one place beats duplicating it in five. The thing I would raise before either is grain. Deciding that the fact table is one row per order line rather than per order determines everything downstream, and getting it wrong is what produces the classic double counted revenue bug. I have had to rebuild a mart because the grain was mixed, and that is much more expensive than any normalization debate.
Walking into this interview soon? GhostPilot listens to your live call, spots the question the moment it is asked, and puts a structured answer on your screen in real time. Try it on your next mock, or grab a $29 Session Pass, no subscription, for the real thing.
See how it worksFollow-up questions to expect
- How do you decide the grain of a fact table?
- What is a factless fact table and when would you use one?
- How do you model a many to many relationship between fact and dimension?
Related data engineer questions
Your interviewer will ask their own version of this. Paste your actual job description into the free Question Predictor and get the 20 questions that role is most likely to ask, with what each one is really probing.
Predict my questions