Data Modeling Interview Questions cheat sheet
Star schemas, slowly changing dimensions, grain, normalization, and the modeling trade-offs interviewers use to separate levels.
Fundamentals and normalization
Explain 1NF, 2NF, and 3NF in plain terms.- First normal form means atomic values with no repeating groups. Second means no non-key column depends on only part of a composite key. Third means no non-key column depends on another non-key column. The practical summary interviewers want: every non-key column depends on the key, the whole key, and nothing but the key.
Why do warehouses deliberately denormalize?- Normalization minimizes update anomalies, which matters for transactional writes. Analytical workloads are read-heavy and append-mostly, so the cost of many joins outweighs the storage saved, and columnar compression makes repeated values nearly free. The trade-off is that a changed attribute must be restated wherever it was copied.
What is the grain of a table and why define it first?- The grain is exactly what one row represents — one order line, one daily account balance, one page view. Declaring it first determines which dimensions apply and which measures are additive. Almost every broken fact table traces back to a mixed grain, where order-level and line-level rows coexist and every sum double counts.
What is the difference between a natural key, a surrogate key, and a composite key?- A natural key comes from the business (an email, an ISBN) and can change or be reused. A surrogate key is a meaningless generated identifier, stable forever, which is what lets slowly changing dimensions keep multiple versions of one business entity. A composite key combines columns; it works but propagates width into every fact table that references it.
What is a conformed dimension?- A dimension shared across multiple fact tables with the same keys and meaning, such as one date or customer dimension used by sales and support. Conformance is what makes cross-process analysis possible — without it, two teams' definitions of customer diverge and no query can join them honestly.
Dimensional modeling
Compare star and snowflake schemas.- A star keeps each dimension in one denormalized table joined directly to the fact; a snowflake normalizes dimensions into sub-tables such as product to category to department. Star wins on query simplicity and fewer joins, which matters to BI tools; snowflake saves storage and eases maintenance of large hierarchies. Modern warehouses make star the default answer.
What are the types of fact tables?- Transaction facts record one row per event and are the most common. Periodic snapshot facts record state at regular intervals, such as daily inventory, and are the right answer for balances. Accumulating snapshot facts have one row per process instance with milestone date columns updated as it progresses, which suits order fulfillment or hiring funnels.
Explain additive, semi-additive, and non-additive measures.- Additive measures sum across every dimension, like revenue. Semi-additive measures sum across some but not time — an account balance can be summed across accounts but not across days, only averaged or taken at period end. Non-additive measures like ratios and percentages must be recomputed from their components, never summed.
What is a factless fact table?- A fact table with only foreign keys and no measures, recording that an event or a coverage relationship happened — student attendance, a promotion being active for a product. You analyze it by counting rows, and coverage versions answer negative questions such as which products were on promotion but never sold.
What is a degenerate dimension?- A dimension attribute with no other attributes worth a table, so it lives in the fact table itself — most often an order number or invoice number. It is useful for grouping line items back into their transaction and for tracing to the source system, and creating a separate table for it would add a join with no benefit.
How do you handle a many-to-many relationship between a fact and a dimension?- With a bridge table carrying the fact key, the dimension key, and usually an allocation weight — for example multiple diagnoses per hospital visit or several sales reps on one deal. Interviewers want you to name the double-counting risk: summing through a bridge without applying the weights inflates every total.
Slowly changing dimensions
Describe SCD types 0 through 4 and when each fits.- Type 0 never changes (a date of birth). Type 1 overwrites and keeps no history, right for correcting typos. Type 2 adds a new row with validity dates, the standard for tracking real change. Type 3 adds a previous-value column for one specific attribute. Type 4 splits fast-changing attributes into a separate mini-dimension.
What columns does a type 2 dimension need?- A surrogate key, the natural key, valid_from and valid_to timestamps, and a current flag for convenient filtering. Many teams add a hash of the tracked attributes for change detection. Decide whether valid_to is inclusive or exclusive and whether the open row uses NULL or a far-future date, then apply it consistently or point-in-time joins will double count.
How do you join a fact to a type 2 dimension correctly?- Store the dimension surrogate key on the fact at load time, so the fact permanently references the version that was true when the event occurred. Joining on the natural key and filtering current_flag gives you today's attributes instead, which is a legitimate but different question — as-was versus as-is reporting, and you should ask which one is wanted.
What is a late-arriving dimension and how do you handle it?- A fact arrives referencing a dimension member not yet loaded. Options are to hold the fact in a quarantine and reprocess, or to insert an inferred member row with the key and placeholder attributes that a later load updates. Never drop the fact and never point it at a generic unknown row silently, because both distort totals.
How would you implement type 2 loading efficiently at scale?- Compute a hash of the tracked columns on incoming rows, join to the current rows, and process only where the hash differs. Then a single MERGE closes changed rows and inserts new versions. This avoids column-by-column comparison across a wide table and makes the change-detection column set an explicit, reviewable decision.
Modern modeling approaches
What is Data Vault and when is it justified?- Hubs hold business keys, links hold relationships, and satellites hold descriptive attributes with history — all insert-only and load-parallel. It shines with many volatile source systems, strict auditability, and heavy regulatory requirements. The cost is a large number of tables and mandatory presentation views on top, so it rarely pays off for a single-source startup warehouse.
Explain the medallion architecture in modeling terms.- Bronze holds raw ingested data with source fidelity and no business logic, silver holds cleaned, conformed, deduplicated entities, and gold holds business-facing aggregates and dimensional models. It is a layering convention rather than a modeling technique — you still choose star, wide table, or vault for the gold layer.
One Big Table versus a star schema — which do you choose?- One Big Table pre-joins everything into a wide denormalized table, which is fast to query and simple for analysts, and columnar engines skip unread columns so width is cheap. Star schemas stay better for governed shared definitions, many facts per dimension, and slowly changing history. Common practice is a star as the model with wide tables materialized for specific consumers.
What is a semantic layer and why did it become important?- It centralizes metric definitions, joins, and dimensions so every tool computes revenue the same way rather than each dashboard reimplementing it. Options include the dbt Semantic Layer, Cube, and native BI models. It matters most when metric definitions are contested, and its real value is governance, not query performance.
How do activity schemas or event-based models differ?- They store a single stream of entity, activity, timestamp, and attributes rather than separate fact tables, and derive relationships with temporal joins at query time. The advantage is one model that absorbs new behaviors without schema changes; the disadvantage is expensive self-joins and a mental model most analysts find unfamiliar.
Practical design decisions
How do you model currency and units of measure?- Store the original amount with its currency code and the converted amount with the rate and rate date applied at fact load. Converting only at query time makes historical reports change as rates move; converting only at load loses the source value. Keeping both is the answer, and the follow-up is which rate — daily close, transaction time, or period average.
How do you model time zones in a warehouse?- Store timestamps in UTC with an explicit type, keep the source time zone or a location key on the row, and derive local time in a view or the semantic layer. Then keep a date dimension defined in the business reporting time zone, because otherwise a UTC date boundary silently splits a business day and daily totals will not reconcile.
How do you handle hard deletes from a source system?- Capture them through change data capture as delete events and record them as soft deletes with a deleted_at column rather than physically removing rows, so history and reconciliation still work. If the source only sends full snapshots, deletion is inferred from absence, which requires a full-key comparison per load and cannot distinguish a delete from a failed extract.
How do you know a dimensional model is going wrong?- Symptoms include analysts routinely joining fact to fact, measures that need a distinct count to be correct, a dimension table growing faster than the fact, and BI users maintaining private spreadsheets to fix numbers. Each usually points at a grain problem or a missing conformed dimension rather than a performance problem.
How would you model a slowly changing hierarchy such as a sales org?- Either a flattened level-per-column dimension, which is fast and simple but breaks when depth changes, or a bridge table with parent, child, and depth for a ragged hierarchy that supports arbitrary depth roll-ups. Add type 2 handling and you must also decide whether reassigning a rep restates history or applies going forward.
From DataLane — tutorials at/blog, practice SQL live in theplayground.