In this article
Foundations
What is data modeling, and what does a dimensional model add?
A data model is the plan of which tables a database holds, what each row means and how the tables relate, and data modeling is the work of drawing it. A dimensional model is a design for analytics that sorts every table into one of two kinds: dimension tables describe business entities, and fact tables store observations or events. Microsoft's guidance describes this classification as the heart of star schema design (Microsoft Learn, 2024).
Why it is asked: it checks that you can name the two table kinds before drawing anything.
Read the full explanation: the data engineering hub.
What is a fact table?
A fact table is a table that stores measurements of events or observations, such as sales orders, stock balances or temperature readings. It holds dimension keys that point to descriptive tables and numeric measure columns that queries summarise. Microsoft's guidance notes that fact tables are narrow but can grow to billions of rows (Microsoft Learn, 2025).
Why it is asked: it separates people who can describe a table's job from people who can only list its columns.
Read the full explanation: the data engineering hub.
What is a dimension table?
A dimension table is a table that describes a business entity, such as a product, a customer, a place or a date. It has a unique key and descriptive columns used for filtering and grouping. Microsoft's guidance says dimension tables support filtering and grouping while fact tables support summarisation, and that dimension tables usually hold relatively few rows (Microsoft Learn, 2024).
Why it is asked: it tests whether you know where descriptive attributes belong.
Read the full explanation: the data engineering hub.
What is a star schema, and how does it differ from a snowflake schema?
A star schema is a design with one fact table at the centre, joined to one denormalised dimension table per business entity. A snowflake schema splits a dimension into several normalised tables, so category and subcategory sit in their own tables. Microsoft's guidance says that, generally, a single denormalised table gives more benefit, because snowflaking adds tables, longer filter paths and a less intuitive field list (Microsoft Learn, 2024).
Why it is asked: it tests trade-off thinking about simplicity against redundancy.
Read the full explanation: the data engineering hub.
Text description of the diagram
- A star schema is a design with one fact table at the centre, joined to one denormalised dimension table per business entity.
- Dimension tables support filtering and grouping while fact tables support summarisation.
What is the grain of a fact table, and why decide it first?
The grain is the level of detail that one fact row represents, for example one order line or one product per month. Microsoft's guidance says the dimension key values set the granularity, and that while higher-granularity facts can be stored, it is not easy to split measure values back out to lower levels (Microsoft Learn, 2025). Deciding the grain first fixes which questions the table can answer and prevents mixed-level rows.
Why it is asked: a vague grain is the commonest design error.
Read the full explanation: the data engineering hub.
Fact tables and measures
What are the three types of fact table?
The three types are transaction, periodic snapshot and accumulating snapshot fact tables. A transaction fact table stores one row per event and never changes except to fix errors. A periodic snapshot stores measurements at set intervals, such as end-of-day stock. An accumulating snapshot has one row per process, updated as each milestone happens (Microsoft Learn, 2025).
Why it is asked: it tests whether you pick the table type to fit the question being asked.
Read the full explanation: the data engineering hub.
What is the difference between additive, semi-additive and non-additive measures?
An additive measure can be summed across any dimension, a semi-additive measure across only some, and a non-additive measure across none. Microsoft's guidance gives order quantity as additive, a stock balance as semi-additive because it cannot be summed across time, and temperature or a unit price as non-additive (Microsoft Learn, 2025). It advises storing the values behind a ratio so the ratio can be recomputed.
Why it is asked: summing the wrong measure is a classic silent reporting bug.
Read the full explanation: the data engineering hub.
What is a factless fact table?
A factless fact table is a fact table with no measure columns, only dimension keys. Counting its rows is the measurement, such as how many students attended a class. Microsoft's star-schema guidance also recommends it for many-to-many relationships between dimensions, where it acts as a bridging table holding, for example, a salesperson key and a region key (Microsoft Learn, 2024).
Why it is asked: it tests whether you know that an event can be a fact with nothing to add up.
Read the full explanation: the data engineering hub.
What is a degenerate dimension?
A degenerate dimension is an identifier, such as an order number, that sits in the fact table because it needs filtering or grouping but has no attributes of its own. Creating a separate one-column table would add storage and clutter. Microsoft's guidance gives the sales order number as the example, and says that when both an order number and a line number are needed, a degenerate dimension table can be a good design (Microsoft Learn, 2024).
Why it is asked: it checks that you know when not to create a table.
Read the full explanation: the data engineering hub.
Knowing it is half the job. Saying it is the other half.
Interview Clozo is an AI interview copilot for real interview rounds: it hears the question and puts a strong answer on your screen, and you say it in your own words. See the features or how it works.
Start Free Trial →Dimensions, keys and change
What is a surrogate key, and why not use the natural key?
A surrogate key is a unique identifier added to a dimension table that is not defined or stored in the source data. It is used because a source business key may not be unique across versions of a row or across systems. Microsoft's guidance says surrogate keys are commonly added to warehouse dimension tables, and that a versioned dimension needs one because the business key will not be unique (Microsoft Learn, 2024).
Why it is asked: it tests whether you understand why history needs a key you control.
Read the full explanation: the data engineering hub.
What is a slowly changing dimension?
A slowly changing dimension (SCD) is a dimension whose attributes change occasionally in an unplanned way, such as a customer's email address, and that must be handled so reports stay correct. The Kimball Group lists techniques from type 0, retain original, to type 7 (Kimball Group, read October 2026). Microsoft's guidance says the two common types are type 1 and type 2.
Why it is asked: history handling separates a reporting table from a warehouse.
Read the full explanation: the data engineering hub.
How do SCD type 1 and type 2 differ?
Type 1 overwrites the old value, so the table always shows the latest and history is lost. Type 2 keeps history by adding a new row for each version, with start and end dates and often a current-row flag. Microsoft's guidance gives a salesperson who moves region as the example: a new version keeps old sales tied to the old region, while type 1 would rewrite them (Microsoft Learn, 2024).
Why it is asked: it tests whether you can say what each choice does to past reports.
Read the full explanation: the data engineering hub.
What is a role-playing dimension?
A role-playing dimension is one physical dimension table that a fact table references more than once, each time in a different role. The standard example is a date table used for order date, ship date and delivery date. In a warehouse there is a single date table, and the role is set by which fact column joins to it (Microsoft Learn, 2024).
Why it is asked: it tests whether you avoid duplicating a table for every use.
Read the full explanation: the data engineering hub.
What is a junk dimension?
A junk dimension is a single dimension that combines several small, low-cardinality attributes, such as order status and delivery status, so the model does not carry a table for each. Microsoft's guidance describes it as typically the Cartesian product of all attribute members, with a surrogate key for each row, and says its goal is to reduce storage and field-list clutter (Microsoft Learn, 2024).
Why it is asked: it tests whether you tidy a model with many flags.
Read the full explanation: the data engineering hub.
How do you model a many-to-many relationship, such as salespeople and regions?
Put the relationship in its own bridging table, a factless fact table holding the two keys, one row per pairing. Microsoft's guidance describes a salesperson who can be assigned to several regions, modelled as a two-column table of salesperson key and region key, and calls the bridging table the best practice for relating two dimensions (Microsoft Learn, 2024). The Kimball Group names the technique "multivalued dimensions and bridge tables".
Why it is asked: many-to-many is where naive joins double-count.
Read the full explanation: the data engineering hub.
What happens when a fact arrives before its dimension row?
Load the fact anyway; never drop it. The usual technique, a late-arriving dimension, inserts a placeholder dimension row that holds the natural key and Unknown attributes, flagged as inferred, and overwrites it type 1 style when the details arrive (Kimball Group, late arriving dimensions, read October 2026). Microsoft's load guidance describes this inferred member and a second option: point the fact at a single Unknown member and re-key it later (Microsoft Learn, load tables, 2025).
Why it is asked: late data is the everyday reality of pipelines.
Read the full explanation: the data engineering hub.
Design and trade-offs
What is the difference between normalisation and denormalisation?
Normalisation stores data so that repetition is reduced, for example keeping product details once in a product table and only a product key in the sales table. Denormalisation stores the details beside the key, repeating them. Microsoft's guidance describes both and says a sales table that carries product details beyond the key is denormalised (Microsoft Learn, 2024). Analytics models usually normalise between facts and dimensions and denormalise inside a dimension.
Why it is asked: it tests whether you can name the cost of each.
Read the full explanation: the data engineering hub.
How would you design a model for e-commerce orders?
State the grain first: one row per order line. Make the fact table hold the order-line measures, quantity and amount, with keys to date, product, customer and store dimensions. Keep the order number as a degenerate dimension, use the date dimension twice for order and ship date, and version the customer dimension if history matters. Then name the questions the model can and cannot answer.
Why it is asked: it tests whether you can combine the concepts into one design.
Read the full explanation: the data engineering hub.
Does a fact table need a primary key?
The grain gives a fact table a logical key: its dimension keys plus any degenerate dimensions, such as order number and line number. Microsoft's fact-table guidance leaves its sample table without a declared primary key, because one rarely helps queries and adds storage (Microsoft Learn, 2025). The Kimball Group says a single-column fact surrogate key is not required but useful during loading: to identify a row, restart an interrupted load and turn updates into deletes plus inserts (Kimball Group, fact surrogate keys, read October 2026).
Why it is asked: it tests whether you treat textbook rules as defaults and not laws.
Read the full explanation: the data engineering hub.
When would you store a periodic snapshot instead of every transaction?
Store snapshots when recording every event is expensive and the analysis only needs levels or trends over time. Microsoft's guidance gives inventory as the example: there may be millions of stock movements a day, but if analysis only concerns end-of-day stock, a daily snapshot table can replace the transaction table (Microsoft Learn, 2025). The cost is that you cannot recover the individual movements.
Why it is asked: it tests whether you design from the question being asked.
Read the full explanation: the data engineering hub.
For where modeling fits in the wider process, see the machine learning system design hub and the machine learning operations (MLOps) hub; for the round itself, the interview loops guide, behavioral interviews, the tell me about yourself guide and the questions to ask the interviewer.
Follow-up questions
How does a fact row find the right version of a type 2 customer?
Use a time-based lookup: the loading process matches the natural key and picks the dimension row whose validity dates include the fact date. Microsoft's star-schema guidance says related facts must use a time-based lookup to retrieve the dimension key value relevant to the fact date (Microsoft Learn, 2024).
What if a type 2 change arrives late, after its facts were loaded?
Insert the new dimension row with the correct dates, then restate the fact rows that belong to it so they point at the new version (Kimball Group, late arriving dimensions, read October 2026).
Why is a stock balance additive across products but not across days?
Adding balances for different products at one date gives a meaningful total, while adding the same balance over several days counts the same stock repeatedly. The Kimball Group says balance amounts are additive across all dimensions except time (Kimball Group, additivity, read October 2026).
- Decide the grain of a fact table before choosing any columns.
- Facts store events and measures; dimensions describe entities and support filtering.
- Keep history with a type 2 dimension and a surrogate key; overwrite with type 1 when history does not matter.
- Check whether a measure is additive before summing it.
- Name the trade-off of every design choice, because that is what the question tests.
Sources
- Understand star schema and the importance for Power BI — Microsoft Learn (ms.date 2024-12-30, read 2026-10-24), 30 Dec 2024
- Modeling fact tables in Warehouse, Microsoft Fabric — Microsoft Learn (ms.date 2025-04-06, read 2026-10-24), 6 Apr 2025
- Dimensional modeling techniques — Kimball Group (undated page, read 2026-10-24), 24 Oct 2026
- Fact surrogate keys — Kimball Group (undated page, read 2026-10-24), 24 Oct 2026
- Late arriving dimensions — Kimball Group (undated page, read 2026-10-24), 24 Oct 2026
- Additive, semi-additive and non-additive facts — Kimball Group (undated page, read 2026-10-24), 24 Oct 2026
- Load tables in a dimensional model, Microsoft Fabric — Microsoft Learn (ms.date 2025-04-06, read 2026-10-24), 6 Apr 2025
