---
title: Data Modeling Interview Questions and Answers (2026)
description: Real data modeling interview questions — star vs snowflake, fact grain, SCD types, surrogate keys and the live design round — with answers and a prep plan.
url: https://usegreenroom.app/blog/data-modeling-interview-questions
last_updated: 2026-07-31
---
← Back to blog
Data Engineering
# Data modeling interview questions and answers
July 31, 2026 · 12 min read
Forty minutes into a design round, the whiteboard held eleven tables, four dotted lines and a rectangle labelled orders_final_v2. The interviewer asked the only question that mattered: "What does one row in your fact table mean?" The candidate looked at the board. The board, being a board, offered nothing. "...An order?" "But you've got a product column. So is it an order, or an order line?"
Grain. Almost every failed design round traces back to a grain that was never stated, and almost every strong one opens by stating it in the first ninety seconds. Data modeling interview questions reward a specific, teachable discipline more than raw knowledge — and this guide covers that discipline plus the concepts every loop asks about: star schemas, SCD types, keys, fact types, and the live design round.
## What data modeling interviews actually test
Data modeling interview questions appear in data engineer, analytics engineer, data analyst and BI loops, and they cluster into five areas:
- Fundamentals — normalization, OLTP vs OLAP, and why the answer differs by workload.
- Dimensional modeling — facts and dimensions, star vs snowflake, and grain above all.
- History — slowly changing dimensions, and the business question that decides the type.
- Keys — surrogate vs natural, and why warehouses prefer surrogates.
- The live design round — model this business on a whiteboard, out loud, in thirty minutes.
The last one is where offers are decided, and it is the one people practise least because it cannot be practised silently.
The five bands of data modeling interview questions, with the live design round deciding most offers.
## Normalization, OLTP and OLAP
The warm-up. Normalization organises data to eliminate redundancy: 1NF (atomic values, no repeating groups), 2NF (no partial dependency on part of a composite key), 3NF (no transitive dependency between non-key columns). Most interviewers want 3NF explained with an example rather than recited.
The real question is the follow-up: when would you deliberately denormalise? The answer is workload. OLTP systems take many small concurrent writes and need consistency and minimal update anomalies, so they normalise. OLAP systems take few large reads scanning millions of rows and need fewer joins and simpler queries, so they denormalise. Modern columnar warehouses make wide tables cheap, which pushes the balance further toward denormalisation than classic textbooks assume — saying that reads as current. Our DBMS interview questions guide covers the relational theory in depth.
## Star schema vs snowflake schema
A guaranteed question. A star schema has a central fact table joined directly to denormalised dimension tables — one join from fact to any dimension. A snowflake schema normalises those dimensions into sub-dimensions, so product joins to category, which joins to department.
- Star — fewer joins, simpler and faster queries, easier for analysts and BI tools to navigate. Costs some redundancy in dimensions. This is the default, and you should say so.
- Snowflake — less redundancy and easier maintenance of shared hierarchies, at the cost of more joins and more complexity for every consumer.
- In practice — start with a star; snowflake only a dimension that is genuinely large and volatile, or a hierarchy shared across many dimensions. Storage is cheap and analyst confusion is expensive.
Know the vocabulary around it: conformed dimensions (a shared dimension like date or customer used identically across several fact tables, which is what makes cross-process analysis possible), degenerate dimensions (an attribute like order number that lives on the fact with no dimension table), and junk dimensions (low-cardinality flags bundled into one small table).
## Grain, facts and fact table types
Back to the whiteboard. Grain is what one row represents, and Kimball's advice — declare the grain before anything else — is the single most useful thing you can bring into a design round. "One row per order line item" and "one row per order" produce different tables, different metrics and different bugs, and you cannot design the dimensions until you have chosen.
State it explicitly and early: "I'll model this at one row per order line, because we need product-level analysis. If we only ever reported at order level I'd use a coarser grain." That one sentence changes the interviewer's read of you.
Then the three fact table types, which come up constantly:
- Transaction fact — one row per event, at the moment it happens. Most common: a sale, a click, a payment. Additive, append-mostly.
- Periodic snapshot fact — one row per entity per period, capturing state at that time: daily account balance, monthly inventory level. Answers "what was it on this date?" cheaply. Semi-additive — you can sum balances across accounts but not across days.
- Accumulating snapshot fact — one row per process instance, with multiple date columns updated as it progresses: order placed, packed, shipped, delivered. Right for pipeline and lifecycle analysis, and the one type where rows are updated rather than only inserted.
Also know additive vs semi-additive vs non-additive measures. Revenue is additive across every dimension; a balance is semi-additive (not summable over time); a ratio or percentage is non-additive and must be recomputed from its components rather than averaged — a classic trap question.
## Slowly changing dimensions
The most-asked history question. A customer moves from Bengaluru to Pune. What happens to last year's sales report?
- Type 0 — never changes. Fixed attributes like date of birth.
- Type 1 — overwrite. History is lost; last year's report now says Pune. Right when the old value was simply wrong (a typo).
- Type 2 — add a new row with valid_from, valid_to and an is_current flag, keeping a new surrogate key. History is preserved; last year's sales stay attributed to Bengaluru. This is the default for anything analytically meaningful, and the one to know cold.
- Type 3 — add a previous_city column. Keeps only one prior value; rare, and useful for a limited "before and after" comparison.
- Type 4 / 6 — a separate history table, or a hybrid combining 1, 2 and 3. Worth naming, rarely required.
Interviewers often ask you to sketch the SCD2 table, so have the shape ready:
```
CREATE TABLE dim_customer (
customer_sk BIGINT NOT NULL, -- surrogate key, one per version
customer_id VARCHAR(32) NOT NULL, -- natural key, repeats across versions
customer_name VARCHAR(200),
city VARCHAR(100),
tier VARCHAR(20),
valid_from TIMESTAMP NOT NULL,
valid_to TIMESTAMP, -- NULL, or '9999-12-31', for the live row
is_current BOOLEAN NOT NULL,
PRIMARY KEY (customer_sk)
);
-- The fact joins on the surrogate key, so it stays bound to the version
-- of the customer that was true when the sale happened.
SELECT d.city, SUM(f.amount_paise) AS revenue
FROM fct_sales f
JOIN dim_customer d ON d.customer_sk = f.customer_sk
GROUP BY d.city;
```
The detail interviewers listen for: the fact joins on customer_sk, not customer_id. That is what keeps last year's revenue attributed to Bengaluru after the customer moves. Joining on the natural key instead quietly converts your SCD2 dimension back into a Type 1, which is a great bug to be able to describe.
The senior framing, and the thing to actually say: the type is a business decision, not a technical one. Ask the interviewer whether historical reports should reflect the world as it was or as it is now. That question is itself the answer they are scoring. On implementation, the dbt interview questions guide covers snapshots, which is how SCD2 is usually built in a modern stack.
## Surrogate keys vs natural keys
A natural key comes from the business — an email address, a PAN, an order number. A surrogate key is a meaningless system-generated integer or hash.
Warehouses prefer surrogate keys for reasons worth listing: business keys change (people change email addresses, source systems renumber), integer joins are faster and narrower than composite string joins, they insulate the warehouse from source-system changes and let you integrate the same entity from two systems, and — the decisive one — SCD type 2 requires them, because the same customer needs several rows with the same natural key and different surrogate keys.
The complete answer keeps the natural key on the dimension as an attribute for lineage and debugging, and mentions the modern variant: hash-based surrogate keys (a hash of the natural key plus the valid-from timestamp) are common in dbt projects because they are deterministic and can be computed in parallel without a sequence.
## The live design round
"Design a data model for a food delivery business." Thirty minutes, whiteboard or shared doc. Interviewers score the process, and it has a reliable shape:
- Ask what decisions it must support. Two minutes of questions — who uses this, what do they ask of it, what latency do they need? Diving straight into tables is the most common failure.
- Identify the business processes. Order placement, delivery, payment, restaurant onboarding. Each generally becomes its own fact table.
- Declare the grain, out loud, per fact. "One row per order line item." Write it on the board.
- Identify dimensions. Customer, restaurant, menu item, delivery partner, date, location, promotion. Note which need SCD2 — restaurant commission tier almost certainly does.
- Add the measures. Item price, discount, delivery fee, tip, commission. Say which are additive and which are not.
- Name what you deliberately left out. Real-time streaming, PII handling, late-arriving deliveries. Naming a deferred concern shows judgment; silently omitting it looks like you missed it.
Then expect the change: "restaurants now have multiple kitchens per location — what changes?" They are testing whether your model bends or breaks, which is the whole point of the round. The system design interview guide covers the adjacent architecture round, and the data engineer interview questions guide covers the pipeline questions that follow.
## Kimball, Inmon, Data Vault, ChatGPT — where each fits
- The Data Warehouse Toolkit (Kimball) — still the reference for dimensional modeling, and the source of most questions you will be asked. If you read one thing, read the first three chapters.
- Inmon vs Kimball — a real interview question. Inmon builds a normalised enterprise warehouse first and derives marts from it (top-down, slower, strong consistency); Kimball builds conformed dimensional marts directly (bottom-up, faster to value). Most modern stacks are Kimball-flavoured with a normalised staging layer, which is a fair and honest answer.
- Data Vault — hubs, links and satellites, built for auditability and many changing sources. Worth being able to describe in two sentences and to say when you would not use it: it adds significant complexity that a single-source startup does not need.
- GeeksforGeeks-style question dumps — fine for the definitions, useless for the design round, which is 100% spoken.
- Actually modelling something — highest yield. Pick a business you understand, model it end to end on paper, then hand it to someone and let them poke holes.
- ChatGPT — good for generating design prompts and critiquing a written schema. It will not stare at your eleven-table diagram and ask what one row means.
- Greenroom — the spoken layer. Ari, the AI interviewer, runs the design round out loud, asks for the grain when you skip it, and changes the requirement halfway through. Fair tradeoff: Ari will not draw the diagram for you.
> The core truth: data modeling rounds are decided in the first ninety seconds, by whether you ask what the model is for and then state the grain. Everything after that is craft. Candidates who start drawing tables immediately almost always end up with orders_final_v2.
## How to prepare for a data modeling interview
- Week 1: fundamentals — normalization with worked examples, OLTP vs OLAP, star vs snowflake. Explain each aloud in sixty seconds.
- Week 2: dimensional depth — grain, the three fact types, additive vs semi-additive measures, conformed and degenerate dimensions. Model one business per day on paper.
- Week 3: SCD types and keys. For each of five real attributes, decide the SCD type and justify it as a business decision, not a technical one.
- Final week: live design rounds out loud with someone who will change the requirement mid-way. Two full spoken mocks, and rehearse the opening two minutes of questions until they are automatic.
Building the rest of the stack? The dbt interview questions guide covers implementing these models, the Airflow interview questions guide covers orchestration, the PySpark interview questions guide covers distributed processing, and the Databricks interview questions guide covers the lakehouse these tables live in.
## Frequently asked questions
### What are the most common data modeling interview questions?
The most common questions cover normalization and when to denormalise, star versus snowflake schema, what grain means and how you declare it, the three fact table types, additive versus semi-additive measures, slowly changing dimension types and when to use each, surrogate versus natural keys, and a live round where you design a model for a business such as food delivery or e-commerce.
### What is the difference between a star schema and a snowflake schema?
A star schema has a central fact table joined directly to denormalised dimension tables, so any dimension is one join away, which makes queries simpler and faster and is easier for analysts and BI tools. A snowflake schema normalises those dimensions into sub-dimensions, reducing redundancy but adding joins and complexity. Star is the sensible default, and you snowflake only a dimension that is genuinely large, volatile, or a hierarchy shared across many dimensions.
### What is grain in data modeling?
Grain is what a single row in a fact table represents — for example one row per order, or one row per order line item. Declaring the grain before designing anything else is the most important step in dimensional modeling, because it determines which dimensions apply and which measures are valid. Most failed design rounds trace back to a grain that was never stated explicitly.
### What are the slowly changing dimension types?
Type 0 never changes. Type 1 overwrites the old value and loses history. Type 2 adds a new row with valid_from, valid_to and an is_current flag, preserving history and keeping historical reports accurate — this is the default for analytically meaningful attributes. Type 3 adds a previous-value column, keeping only one prior value. Types 4 and 6 use a separate history table or a hybrid. Which type to use is a business decision, not a technical one.
### Why do data warehouses use surrogate keys instead of natural keys?
Business keys change over time and differ between source systems, integer joins are faster and narrower than composite string joins, and surrogate keys insulate the warehouse from source-system changes so the same entity can be integrated from multiple systems. Decisively, slowly changing dimension type 2 requires them, because the same natural key needs several rows with different surrogate keys. Keep the natural key on the dimension as an attribute for lineage.
### How do you approach a live data modeling design round?
Spend the first two minutes asking what decisions the model must support and who uses it. Identify the business processes, since each usually becomes a fact table. Declare the grain out loud for each fact before drawing anything. Then identify dimensions and note which need SCD type 2, add the measures and say which are additive, and finally name what you deliberately left out. Expect the interviewer to change a requirement to see whether your model bends or breaks.
Design rounds are won in the first ninety seconds, out loud. Greenroom runs mock data modeling interviews with Ari — grain questions and mid-round requirement changes included — and scores structure, clarity and depth. Free to start. Curious how it works? See how AI mock interviews work.