Data Modeling Interview Questions: 16 Real Questions with Answers

A star schema has a central fact table joined directly to denormalized dimensions — one hop per dimension. A snowflake schema normalizes dimensions into sub-dimensions (product to category to department), saving some storage but adding joins. In modern columnar warehouses storage is cheap and joins cost query time, so star is the default; snowflaking earns its place mainly for very large dimensions with hierarchies maintained by separate teams.

Facts store measurable events at a declared grain — order lines, payments, clicks — with numeric measures and foreign keys to dimensions; they are long, narrow, and grow continuously. Dimensions store descriptive context — who, what, where, when — and are wide, comparatively small, and slowly changing. A quick test: you aggregate facts (SUM, COUNT) and you filter and group by dimensions.

Grain is exactly what one fact row represents — 'one row per order line', not vaguely 'orders'. Kimball's rule is to declare it before choosing measures or dimensions, because every measure must be true at that grain: putting order-level shipping cost into an order-line-grain table double-counts on aggregation. Most real fact-table bugs interviewers probe are grain violations.

Use surrogate keys — meaningless integers or hashes generated in the warehouse — as dimension primary keys, and keep the natural business key (employee_id, SKU) as an attribute. Surrogates insulate you from source-system key reuse and format changes, and they are required for SCD Type 2, where one business key maps to multiple historical versions. Natural keys still matter as the lookup handle during ETL.

1NF: atomic values with no repeating groups — one value per cell. 2NF: 1NF plus no partial dependency — every non-key attribute depends on the whole composite key, not part of it. 3NF: 2NF plus no transitive dependency — non-key attributes depend on 'the key, the whole key, and nothing but the key'. OLTP systems target 3NF; warehouses deliberately denormalize dimensions.

Type 1 overwrites the old value — no history, right for corrections like a misspelled name. Type 2 inserts a new row per change with effective_from/effective_to dates, an is_current flag, and a new surrogate key — the default whenever history matters, like a customer moving cities. Type 3 adds a 'previous value' column holding exactly one prior state — rare, used for cases like a one-time sales territory realignment where both views are needed simultaneously.

Transaction facts record one row per event (an order line) — the most granular and common. Periodic snapshot facts record state at fixed intervals (daily account balance), needed when you report levels rather than deltas. Accumulating snapshot facts hold one row per process instance with multiple date columns updated as the workflow progresses (placed, packed, shipped, delivered) — ideal for lag analysis, and the only fact type routinely updated in place.

Additive measures sum correctly across all dimensions — revenue, quantity. Semi-additive measures sum across some dimensions but not time: account balances can be summed across branches for one day, but summing daily balances across a month is meaningless — use AVG or last-value per period. Non-additive measures like ratios and percentages cannot be summed at all; store the numerator and denominator as additive facts and compute the ratio at query time.

A conformed dimension is a single shared version of a dimension — customer, product, date — used identically across multiple fact tables, so 'revenue by customer' from sales and 'tickets by customer' from support agree row for row. They are the backbone of Kimball's bus architecture and what makes drill-across reporting possible. Without them, every team builds its own dim_customer and dashboards stop agreeing — the classic symptom interviewers want you to diagnose.

Inmon is top-down: build a normalized 3NF enterprise warehouse first, then spin off dimensional marts — more upfront modeling, strong single source of truth. Kimball is bottom-up: build star-schema marts per business process, integrated through conformed dimensions on a bus matrix — faster time to value. Most modern dbt/lakehouse stacks are pragmatic hybrids: an Inmon-ish integration layer feeding purely Kimball-style serving marts.

A degenerate dimension is a dimension key stored on the fact with no dimension table — like invoice_number: useful for grouping and drill-through but with no attributes of its own. A junk dimension bundles unrelated low-cardinality flags (payment type, is_gift, channel) into one small table to avoid several tiny foreign keys. A role-playing dimension is one physical dimension used in multiple roles — dim_date joined as order_date, ship_date, and delivery_date via views or aliases.

Columnar engines only read the columns you touch, so wide denormalized tables are cheap to scan, and eliminating joins simplifies BI-tool usage — which is why one-big-table (OBT) marts are popular as the serving layer. But OBT alone breaks down on SCD history, repeated dimension updates, and conformance across marts. The pragmatic pattern: keep facts and dimensions modeled dimensionally in dbt, then materialize purpose-built wide tables on top — model dimensional, serve wide.

Each load, compare incoming rows to current dimension rows on the natural key, using a hash of tracked columns to detect changes; for changed keys, close the current row (effective_to = load_date, is_current = false) and insert a new row with a fresh surrogate key and effective_from = load_date. This is typically one MERGE statement, or dbt snapshots with strategy='timestamp' or 'check' doing it declaratively. Facts then join on the natural key with the event date between effective_from and effective_to, so each transaction picks the version that was true at the time.

When a fact arrives before its dimension row — a new customer's first order lands before CRM syncs — don't drop or park the fact: point it at a placeholder, either a default 'unknown' row (surrogate key -1) or an inferred member created from the natural key with null attributes. When the real dimension data arrives, update the inferred row in place. Late-arriving dimension changes are harder: insert an SCD2 version with back-dated effective dates and re-point the affected fact rows to restore historical correctness.

Use a bridge table: the fact carries a diagnosis_group_key, and the bridge maps group_key to diagnosis_key with a weighting_factor whose allocations sum to 1 per group. The weighting factor prevents double-counting — without it, a claim with three diagnoses counts three times when you sum across the bridge. Also mention the alternatives: collapsing to a 'primary diagnosis' if the business accepts it, or an array column with UNNEST in modern warehouses, trading model purity for simplicity.

Standard layering: staging models (one per source table — rename, cast, light cleanup, materialized as views), intermediate models for reusable business logic, and marts holding facts, dimensions, or wide serving tables materialized as tables or incremental. Incremental models process only new data: materialized='incremental' with a unique_key and an is_incremental() filter like WHERE loaded_at > (SELECT MAX(loaded_at) FROM {{ this }}), compiling to an insert or merge. Interviewers probe the failure modes — late data missed by the filter (add a lookback window) and schema drift (on_schema_change) — plus tests: unique and not_null on keys, relationships between facts and dims.