Data Modeling Interview Questions: 16 Real Questions with Answers

Fact tables store measurable business events at a declared grain — order lines, payments, page views — as numeric measures plus foreign keys to dimensions; they are long, narrow, and grow continuously, often to billions of rows. Dimension tables store the descriptive context — who, what, where, when — and are wide, comparatively small, and slowly changing. The quick test: you aggregate facts (SUM, COUNT, AVG) and you filter, group, and label by dimensions. The follow-up interviewers probe: some attributes can be either — price is a measure on the fact, but 'price band' is a dimension attribute — and a fact table with no measures at all (a factless fact) is still valid for recording that an event occurred, like student attendance.

Grain is the precise definition of what one fact row represents — 'one row per order line', not vaguely 'orders'. Kimball's first rule is to declare it before choosing measures or dimensions, because every measure and foreign key must be true at exactly that grain. The classic bug: putting order-level shipping cost on an order-line-grain table, so it double-counts the moment anyone sums it; the fix is allocating it across lines or moving it to an order-grain fact. Mixed grain also breaks joins — a dimension that only applies to some rows signals two facts trying to live in one table. Most real fact-table defects an interviewer will probe are grain violations, which is why the answer should start with 'first, declare the grain'.

Use surrogate keys — meaningless warehouse-generated integers or hashes — as dimension primary keys, and keep the natural business key (employee_id, SKU) as an ordinary attribute. Surrogates insulate the model from source-system key reuse, format changes, and multi-source integration where two systems disagree on customer IDs. They are mandatory for SCD Type 2, where one business key maps to several historical versions, so the natural key alone can no longer be a primary key. They also give you a home for special rows like the 'unknown' member (key -1). Natural keys still matter operationally: they are the lookup handle during ETL and the join column for late-arriving data. The trap: joining facts to dimensions on the natural key in an SCD2 model — it fans out.

1NF: values are atomic with no repeating groups — one value per cell, no comma-separated lists or phone1/phone2/phone3 columns. 2NF: 1NF plus no partial dependency — every non-key attribute depends on the whole composite key, not a subset (in an order_id+product_id table, product_name violates 2NF because it depends only on product_id). 3NF: 2NF plus no transitive dependency — non-key attributes depend on 'the key, the whole key, and nothing but the key', so city → state belongs in its own table. OLTP systems target 3NF to avoid update anomalies; warehouses deliberately denormalize dimensions back toward star schemas because analytical reads outnumber writes. Knowing why you would break the rules is what interviewers listen for.

Transaction facts record one row per event (an order line) — the most granular and most common, insert-only. Periodic snapshot facts record state at fixed intervals (daily account balance, month-end inventory), needed when the business reports levels rather than deltas; you cannot reconstruct a balance cheaply by replaying every transaction at query time. Accumulating snapshot facts hold one row per process instance with several date columns filled in as the workflow advances — placed, packed, shipped, delivered — making lag and pipeline analysis a simple column subtraction; they are the only fact type routinely updated in place. The interviewer's follow-up: snapshots are usually semi-additive (balances don't sum over time), which shapes how you aggregate them.

Additive measures sum correctly across every dimension — revenue, quantity, cost — and are what fact tables should store wherever possible. Semi-additive measures sum across some dimensions but not time: you can add account balances across branches for one day, but summing a month of daily balances is meaningless — use the average, or the last value per period (typically via a window function picking the latest snapshot). Non-additive measures — ratios, percentages, unit prices — cannot be summed at all; the correct pattern is to store the numerator and denominator as additive facts and compute the ratio at query time, otherwise you get an average of averages. The classic trap interviewers set: 'why is average order value dangerous to store on the fact row?'

Inmon is top-down: first build a normalized 3NF enterprise data warehouse as the single integrated source of truth, then spin off dimensional data marts for consumption — more upfront modeling, strong governance, slower first delivery. Kimball is bottom-up: build star-schema marts one business process at a time, integrating them through conformed dimensions on a bus matrix — faster time to value, with integration discipline enforced by conformance rather than a central 3NF layer. In practice the argument is settled by hybrids: most modern dbt/lakehouse stacks keep an Inmon-flavoured cleaned integration layer (staging/intermediate) feeding purely Kimball-style serving marts. Saying 'we do both, at different layers' is the mature answer.

Columnar engines read only the columns a query touches, so a wide denormalized table is cheap to scan, and removing joins makes BI-tool setup and ad-hoc SQL simpler — which is why one-big-table (OBT) marts are popular as a serving layer. But OBT alone fails in predictable ways: an SCD2 change forces a rewrite of every affected fact row, there is no conformance across marts so metrics drift, and repeated dimension text bloats storage and makes updates expensive. The pragmatic recommendation: model facts and dimensions dimensionally in dbt — that is where history, conformance, and tests live — then materialize purpose-built wide tables on top for each dashboard or team. 'Model dimensional, serve wide' answers both halves of the question.

An early-arriving fact is a fact that lands before its dimension row — a new customer's first order arrives before the CRM syncs. Don't drop or park it: point it at a placeholder, either the default 'unknown' member (surrogate key -1) or, better, an inferred member created on the fly from the natural key with null attributes; when the real dimension record arrives you update the inferred row in place (a deliberate Type 1 exception). A late-arriving dimension change is harder: the change itself shows up after facts have already been keyed, so you insert an SCD2 version with back-dated effective dates and re-point affected fact rows so history is correct again. Interviewers probe whether your pipeline does this automatically or quietly loses referential integrity.