Dimensional Modeling Interview Questions and How to Answer Them

Data modeling questions appear in almost every data engineering loop: as a dedicated round ("model the data for a ride-sharing app"), as the middle of a pipeline design round, or as SQL follow-ups ("now compute repeat purchase rate from your model"). Interviewers are rarely checking whether you know the Kimball vocabulary. They are checking whether your tables produce correct numbers for the questions the business asks, whether they keep history honestly, and whether you can explain the trade-offs of each choice. This guide gives a method for the round, the questions to ask, what interviewers probe, and model answers to the questions that come up most.

What the interviewer is evaluating

A method for the round

1. Clarify the business and its questions (5 minutes). List four to six concrete questions the model must answer. They become your test cases.
2. Name the business processes and, for a multi-process problem, sketch a small bus matrix.
3. Declare the grain of the first fact table, in one sentence, at the most atomic level available.
4. List the dimensions (who, what, where, when, how, why) and check each is single-valued at the grain.
5. List the facts true to the grain and classify each as additive, semi-additive or non-additive.
6. Decide history for each important dimension attribute: Type 1, Type 2, or a mini-dimension.
7. Add the other fact tables the questions need: an accumulating snapshot for lifecycles, a periodic snapshot for levels.
8. Prove it with SQL: answer two of your original questions against the model, out loud.
9. Cover operations: sizing, partitioning, incremental loads, late data, and data quality checks.

On DesignRounds, draw the model as data model tables: open Data model in the component library (or press D), add fact and dimension tables, and mark keys on each column. The interviewer reads your columns and keys, and the review judges the grain and keys.

Questions to ask before designing

• What decisions will this data support, and which three or four metrics matter most?
• How are the numbers sliced: by time, product, customer segment, location, channel?
• How fresh must the data be: real-time, hourly, daily by a deadline?
• How far back do people look, and must old reports stay exactly as they were?
• When an attribute changes (a customer moves, a product changes category), should past numbers move with it?
• What does the source record, at what level of detail? Can records be updated or deleted later? Do events arrive late?
• Roughly how many events a day, and how many customers or products? (This decides partitioning and whether snapshots are affordable.)

Common questions and model answers

What is the grain of a fact table, and why does it matter?

The grain is what one row represents, such as one order line or one account per day. Everything else depends on it: which dimensions fit (they must be single-valued per row) and which facts fit (they must be measured at that level). Mixing grains, like order totals on order-line rows, is the main cause of double counting. I declare it in words before choosing any columns, and I prefer the most atomic grain the source supports because it can always be rolled up but never drilled down.

Star schema or snowflake schema?

Star by default: denormalised dimensions mean fewer joins, simpler SQL and better BI tool behaviour, and columnar storage compresses the repeated text cheaply. I'd snowflake only a genuinely large or shared hierarchy, and even then usually expose a flattened view to users.

Why use surrogate keys instead of the source ids?

Three reasons. Type 2 history needs several rows per entity, each needing its own key. Different sources may use different or colliding ids for the same entity. And source systems sometimes reuse or change ids. Surrogate keys also keep joins on small integers. I keep the natural key and a durable key in the dimension so we can still trace and count entities.

How would you track history of a customer's address?

SCD Type 2: when the address changes, close the current row (valid_to = change time, is_current = false) and insert a new row with a new surrogate key. Facts store the surrogate key of the version valid at the event time, so last year's sales stay attached to last year's city. If reports also need the current city, I add a current-value column (Type 6) or let facts carry the durable key to join a current view (Type 7).

Transaction, periodic snapshot or accumulating snapshot?

Transaction for events (sales, clicks), periodic snapshot for levels at a point in time (balances, stock on hand), accumulating snapshot for a process with milestones where people ask how long stages take (order to delivery, application to approval). Many models need all three over the same dimensions. See fact table types.

How do you handle a many-to-many relationship, such as accounts with several owners?

First I check whether changing the grain removes it, for example one row per rider instead of per shared trip. If not, a bridge table: the fact stores a group key, the bridge lists each group's members with a weighting factor that sums to one, so weighted reports add up to the true total while per-member impact reports can still use the full amount. See bridge tables.

A refund arrives two weeks after the sale. What happens?

I don't update the original sale. Either refunds are their own process (a returns fact dated on the refund, with reasons) or they're negative rows on the sales fact dated on the refund. Either way, a closed month's reported revenue doesn't change, and net revenue for any period is reproducible.

How do you handle late-arriving data?

For a late fact, I look up the dimension version valid at the event time, not today's, and write it to the correct date partition with an idempotent load. For a fact that references a dimension member we haven't seen yet, I insert an inferred member (natural key plus Unknown attributes) so the fact loads, then fill in the real attributes when the member arrives.

When would you use one big table instead of a star schema?

For a specific, heavily used dashboard or ML feature set where avoiding joins matters and the attributes rarely change. I'd build it in the gold layer from the star schema, so a renamed category is a rebuild rather than a rewrite of every historical row. The star stays the source of truth.

How do you make the model fast at scale?

Partition facts by date so time-filtered queries prune, cluster or sort by the most filtered keys, keep dimensions small enough to broadcast in joins, build aggregate tables or materialised views for the hottest dashboards, and keep the atomic table as the source of truth that aggregates reconcile against.

How do you compute a ratio like conversion rate or margin percentage?

Store the additive parts as facts (conversions and sessions, revenue and cost), sum them at the level the user asks for, then divide. Averaging precomputed ratios weights every row equally and gives the wrong answer.

Traps interviewers set

A 45-minute plan

Practice with full examples

Work through the two complete models, then practise a round: the e-commerce warehouse example (orders, returns, fulfilment and inventory) and the ride-hailing example (trip lifecycle, role-playing locations, surge and driver activity).

• Design a data warehouse for an online storeData engineering · Beginner · 40 min · Free
• Design a customer warehouse with SCD Type 2Data engineering · Intermediate · 45 min · Free
• Design analytics for a food delivery platformData engineering · Intermediate · 45 min