Data Modeling Interview Questions: defend the schema, don't recite it

Data Modeling Interview Questions: defend the schema, don't recite it

Most candidates do not fail data modeling interview questions on SQL. They fail because they cannot explain why a schema looks the way it does. The interviewer asks "why did you choose a star schema here?" and the answer is a blank stare or a textbook definition that never touches the trade-off. The questions are really probing one thing: do you have a repeatable method for turning a messy business problem into a clean, queryable model, and can you defend each decision when someone pushes back?

This guide is built around that. It covers the dimensional-modelling concepts the questions are drawn from — facts and dimensions, grain, star versus snowflake, slowly changing dimensions, and the specialised dimensions that separate seniors from juniors — and the four-step methodology interviewers actually want to hear. Treat it as data engineering interview prep for the one round where talking through your reasoning matters more than any single query.

The methodology that answers most modelling questions

Almost every open-ended modelling question — "design a schema for X" — has the same correct shape of answer, and reciting it is half the battle:

1. Identify the business process. Orders, shipments, returns, page views — each process becomes a fact table.
2. Define the grain. State explicitly what one row represents: "one row per order line per day." This is the single most important decision; everything downstream depends on it.
3. Choose the dimensions. The who, what, where and when — customer, product, store, date — and make the shared ones conformed from day one.
4. Then, and only then, write the DDL. Column types, keys, partitioning.

Before any of that, clarify cardinality. Modelling a one-to-many relationship as one-to-one silently drops rows; modelling one-to-one as many-to-many inflates every aggregate. Asking "can a customer have more than one of these?" before you draw a line signals that you think before you code — which is exactly what the round rewards.

Facts, dimensions and the grain decision

A fact table stores measurable events — order_amount, click_count, shipment_weight — and its columns are numeric and additive. A dimension table stores the descriptive context: customer_name, product_category, store_location, the who/what/where/when/why of analysis. The most common beginner mistake is stuffing descriptive attributes into the fact table "to save a join", which bloats storage and makes updates painful.

The grain is the level of detail in each fact row, and getting it wrong is the most damaging mistake in modelling. Too coarse and you lose detail you need; too fine and performance suffers; ambiguous and you get duplicate rows that inflate every metric. Always state the grain out loud before describing a fact table, and keep a grain-violation check in your back pocket:

-- grain check: find rows that violate the stated grain
SELECT order_id, product_id, store_id, order_date, COUNT(*) AS rows
FROM fact_sales
GROUP BY order_id, product_id, store_id, order_date
HAVING COUNT(*) > 1;

There are three fact types, and naming all three with a use case is a strong signal of depth:

Fact type	What it stores	Example grain
Transaction	One row per event	One row per sale
Periodic snapshot	State captured at regular intervals	One row per account per month-end
Accumulating snapshot	A process with a start and end, updated as it moves	One row per order (placed → shipped → delivered)

The trap is defaulting to transaction facts for everything. If the question is "what is the current inventory level", a periodic snapshot is the right answer.

Star vs snowflake (and when to break the rule)

A star schema puts the fact table in the centre with dimensions one hop away. It is the default for analytical warehouses because it minimises joins and BI tools consume it easily. A snowflake schema normalises dimensions into sub-dimensions — splitting dim_product into dim_category and dim_department — which saves storage but adds joins. In modern columnar warehouses storage is cheap and joins are expensive, so the right interview answer is: "I default to a star schema unless I have a specific reason to snowflake, such as a very large dimension with highly redundant hierarchical data."

Two more trade-offs come up constantly. Kimball versus Inmon: Kimball builds dimensional marts bottom-up, conformed by shared dimensions, for fast time-to-value; Inmon builds a normalised enterprise warehouse first, for a single source of truth in heavily governed orgs. OLTP versus OLAP: transactional systems normalise to 3NF for write integrity, while warehouses denormalise into star schemas for read performance. Knowing when each applies — and mentioning Data Vault or a One Big Table serving layer where relevant — is what senior answers sound like.

Slowly changing dimensions: the history question

The classic question is: a customer moves from New York to Chicago — do you overwrite the old address, keep both, or store a previous value? That is a slowly changing dimension (SCD), and the answer depends on whether historical analysis needs the old value.

SCD type	Behaviour	When to use
Type 1	Overwrite, no history	The old value never needs to be queried
Type 2	New row per change, with effective_date, end_date, is_current	Default — full history matters
Type 3	Add a previous_value column (one prior value)	Only the most recent change matters
Type 6	Hybrid of 1 + 2 + 3	Full history and easy current/previous comparison

Type 2 is the default because it preserves history, at the cost of table growth and date-range join logic. The biggest mistake is defaulting to Type 1 everywhere because it is simpler, then being asked six months later "what region was this customer in when they placed that order?" — and the data is gone. Be ready for the follow-up on late-arriving dimensions too: when a fact arrives before its dimension, insert a placeholder row and update it later, backdating the effective date under Type 2.

The dimensions that separate seniors

Past the basics, a handful of specialised dimensions show up as "do you know this?" questions:

• Conformed dimensions — one dim_customer shared identically across marts. Skip them and the sales team counts 50,000 customers while support counts 48,000; "two versions of the truth" is a top real-world failure.
• Degenerate dimension — a key in the fact table with no attribute table of its own: invoice numbers, receipt numbers, tracking codes.
• Date dimension — almost always the first one you build, roughly 7,300 rows for 10–20 years, pre-computing fiscal_quarter, is_holiday, day_of_week so date logic is not scattered across queries.
• Role-playing dimension — one dim_date used as order_date, ship_date and delivery_date in the same fact, usually exposed through views.
• Junk dimension — low-cardinality flags (is_gift_wrapped, payment_method) combined into one small table so the fact stays narrow at billions of rows.

And know the four mistakes interviewers love to hear you name: undefined grain (duplicates and inflated metrics), skipped conformed dimensions (conflicting counts), premature denormalisation (you can never restructure), and NULL foreign keys in facts (silently dropped rows — point them at an "Unknown" row instead). Each maps to the four dimensions a scored modelling round grades: problem solving (the right grain and dimensions), communication (justifying the trade-offs out loud), technical execution (clean, correct DDL and SQL), and time management (reaching a defensible model in the time given).

Data engineering
Company-agnostic · Data modelling · 30 min
Design and defend a dimensional model out loud

Practise on Zynter →

How to prepare for a data modelling interview

Definitions are table stakes; the round is won on method and defence.

• Rehearse the four-step method on a scenario. Take "design a warehouse for an e-commerce company" and talk through business process, grain, dimensions, then DDL — out loud, as if to an interviewer.
• State the grain first, every time. Make "one row per … " the first sentence of any fact-table answer.
• Prepare the trade-off answers. Star vs snowflake, Kimball vs Inmon, which SCD type and why — each in one or two sentences.
• Name the mistakes. Being able to say "the common failure here is skipping conformed dimensions" reads as real experience.
• Draw while you talk. The modelling round is a conversation over a diagram, so practise sketching a star schema and narrating it at the same time.

If you want structured, guided practice building schemas before you rehearse the round, PipeCode is a sister platform with hands-on data-engineering exercises; use it to drill the mechanics, then come back here to practise defending a model out loud.

Data engineering
Company-agnostic · Data modelling · 30 min
Talk through grain, SCD type and star-vs-snowflake with follow-ups

Practise on Zynter →

Frequently asked questions

What is the difference between a fact table and a dimension table?

A fact table stores measurable events at a defined grain — numeric, additive values like order_amount, click_count or shipment_weight, with foreign keys to its dimensions. A dimension table stores the descriptive context around those measurements: customer_name, product_category, store_location, the who, what, where and when. Facts are skinny and long; dimensions are wide and short. The common mistake is pushing descriptive attributes into the fact table to save a join, which bloats it and makes updates painful.

What is grain, and why does it matter so much?

Grain is the level of detail stored in each row of a fact table — for example, "one row per order line per day." It is the single most important decision in dimensional modelling because everything downstream depends on it: too coarse and you lose detail, too fine and performance suffers, ambiguous and you get duplicate rows that inflate every metric. Always state the grain explicitly before describing a fact table, and keep a grain-violation query (group by the grain columns, having count greater than one) ready to prove it is clean.

What are the SCD types, and which should I use?

Slowly changing dimensions handle attribute changes over time. Type 1 overwrites with no history; Type 2 adds a new row per change with effective and end dates and an is-current flag; Type 3 keeps a single previous-value column; Type 6 is a hybrid of all three. Type 2 is the default because it preserves full history, at the cost of larger tables and date-range join logic. Reach for Type 1 only when the old value truly never needs querying, and be ready to explain late-arriving-dimension handling as a follow-up.

Star schema or snowflake schema — which should I choose?

Default to a star schema. It keeps dimensions one hop from the fact, minimises joins, and is easy for BI tools to consume, which matters because modern columnar warehouses make storage cheap and joins expensive. Choose a snowflake only when you have a specific reason — typically a very large dimension with highly redundant hierarchical data where normalising saves meaningful storage. In an interview, state the default and the exception in one sentence; that shows you understand the trade-off rather than a rule.

What are the most common data modelling mistakes?

Four come up again and again: undefined grain, which produces duplicate rows and inflated metrics; skipped conformed dimensions, where two teams build different dim_customer tables and leadership gets two different customer counts; premature denormalisation, which flattens everything too early and makes restructuring impossible; and NULL foreign keys in fact tables, which silently drop rows on joins — the fix is a dedicated "Unknown" dimension row. Naming mistakes you have personally caught is far more convincing than listing textbook anti-patterns.

How do I practise data modelling interviews out loud?

Reading definitions does not prepare you to defend a grain choice while someone asks "why not a snapshot fact here?" On Zynter, the Data engineering track runs dedicated data-modelling rounds — design a dimensional model for a scenario like e-commerce sales or logistics, then defend your grain, dimensions and SCD choices to a voice interviewer, and get a debrief on problem solving, communication, technical execution and time management. You get a genuine data-engineer mock interview on demand, and the score is a practice signal, not a hiring decision.

Practise it on Zynter

Anyone can define a star schema. The round is won on "why that grain?" — answered out loud.

Design a dimensional model for a real scenario, then defend your grain, your SCD type and your star-versus-snowflake call to a voice interviewer that keeps pushing on the trade-offs — and read the debrief. Zynter.ai is on-demand mock interviews modelled on real company loops, with 150+ rounds across 20 companies (Sep 2026) — about $8–$12 for a full 60-minute mock, and two free credits to start.

Practise a data modelling round →
Browse all interview tracks