Data Modeling interview questions
Data Modeling questions a Data engineer can be asked, from first job to senior. Open one to see the strongest answer and why it works.
Where should a “revenue” definition live — in the ingest or in a model?
The strongest answer: In a modelled layer on top of raw — ingest lands what the source said; definitions are a second, owned step
Why it works: Exactly. Raw is archaeology. Definitions are products. Dashboard dialects are the problem.
- Land raw as the source said.
- Define revenue in a model.
- Do not bake the metric into ingest.
- Do not leave it to each chart.
Star schema or snowflake schema for the analytics layer — which do you default to, and why?
The strongest answer: Star: one fact joined to flat, denormalised dimensions — fewer joins and simpler for analysts and BI tools; I snowflake a dimension only when a large hierarchy is shared across dimensions
Why it works: Right — a star trades some repetition in dimensions for simple, predictable joins, which is what analysts and BI tools want.
- Default to a star.
- One fact, flat dimensions.
- Snowflake only a heavy shared hierarchy.
- Never ship raw source shapes.
A customer changes country. Why might you keep the old row?
The strongest answer: So facts can join the country that was true then — type 2 is history, type 1 is “now only”
Why it works: Exactly. As-was versus as-is. Growth for its own sake is not a reason.
- Type 2 = as-was.
- Type 1 = now.
- Join on key + valid time.
- Do not grow the table as a hobby.
Where do you put “current plan name” versus “amount charged”?
The strongest answer: Amount in a fact at charge grain; plan name in a dimension you join — I do not denormalise every adjective onto every charge unless I mean to
Why it works: Exactly. Facts are events; dims are who/what. Blanket denormalise is a choice you pay for on every update.
- Facts = events at a grain.
- Dims = attributes.
- Denormalise on purpose.
- Do not model in Excel.
An event has created_at without a timezone. What do you store?
The strongest answer: UTC plus the source offset if they sent one — I do not guess the producer’s city
Why it works: Exactly. Instant in UTC. Guessed cities and worker zones are how Sunday moves.
- Store UTC instants.
- Keep source offset if given.
- Do not guess a city.
- Do not use the worker’s zone.
Why do dimension tables get a surrogate key instead of just using the source system’s customer id?
The strongest answer: A surrogate key is the warehouse’s own: it lets type-2 history give one customer several rows, survives source ids changing or colliding across systems, and keeps facts joined to the right version
Why it works: Exactly — the surrogate decouples the warehouse from the source and is what makes SCD type 2 possible at all.
- Surrogate keys are the warehouse’s own.
- They enable type-2 history.
- They survive merged or changed source ids.
- Keep the natural key as an attribute.
How do you join a fact to a type-2 dimension?
The strongest answer: fact.event_time between dim.valid_from and valid_to (with a clear end-of-time) — I do not join on id only
Why it works: Exactly. A time predicate. DISTINCT after a fan-out is a coin flip.
- Key + valid interval.
- Define end-of-time.
- Id-only fans out.
- DISTINCT is not a time join.
A many-to-many (user ↔ account) changes over time. What do you build?
The strongest answer: A timed bridge — user, account, valid_from, valid_to — not a current-only map that rewrites history
Why it works: Exactly. Relationships are facts with time. CSV columns and hope are not models.
- Timed bridge table.
- Do not CSV the keys.
- Do not type-1 the relation.
- “Ever” ≠ “then.”
Training features were computed in a notebook; serving uses a different SQL. What is the risk?
The strongest answer: Train/serve skew — I will share one definition (or generate both) and I will not “fix” production with a notebook one-off
Why it works: Exactly. One definition. Offline AUC on a cousin is a compliment to the cousin.
- One feature definition.
- No notebook one-off in prod.
- Offline AUC can lie.
- Do not add a third dialect.
You need “orders placed”, “stock on hand each night” and “time from order to delivery.” Are those one fact table?
The strongest answer: Three kinds: a transaction fact per order event, a periodic snapshot per product per night, and an accumulating snapshot per order whose milestone dates are updated as it progresses
Why it works: Right — the grain and the update behaviour differ: transactions append, snapshots repeat every period, accumulating snapshots are updated in place as milestones land.
- Match the fact type to the question.
- Transaction: one row per event.
- Periodic snapshot: one row per period.
- Accumulating: one row updated per milestone.
Type 2 exploded into billions of almost-duplicate rows. What do you change?
The strongest answer: Track only attributes that change meaning for a join, and close rows on real changes — I do not version a last_seen heartbeat as SCD
Why it works: Exactly. SCD is for meaning. Heartbeats and JSON blobs are how you pay and cannot join.
- Version attributes that matter.
- Do not SCD a heartbeat.
- Size last.
- No JSON-as-history.
Finance and product still do not match after a year of “alignment.” What do you fund?
The strongest answer: A owned bridge table and a dual-run change process — more meetings without a system are how a year passes
Why it works: Exactly. A system. Louder weeks, vetoes, and third numbers are politics.
- Fund the bridge table.
- Dual-run changes.
- No peace-third-number.
- No “stop measuring.”
A platform lead proposes replacing the star schema with one wide denormalised table per domain, “because storage is cheap and joins confuse people.”
What do you agree to, and what do you keep?
The strongest answer: Wide tables as a serving layer built from the dimensional model, yes; the facts and conformed dimensions stay the source of truth, so an attribute fix is applied once, not in every wide table
Why it works: Right — the wide table is a product built from the model, not a replacement for it; that is what keeps one definition per attribute.
- Wide tables serve; the model owns.
- Build wide tables from the star.
- Change an attribute once.
- Do not delete the source model.