D8LooP
Start building

Interviewing

How to Prepare for the Data Modelling Round

Grain, keys, history, facts. The four steps the round is actually made of.

4 parts37 steps~10h total
Start this plan

It opens on your dashboard and ticks itself off as you go. Every step below is a chapter or an exercise that already ships.

01

When you finish, you can

You can design a model from a vague prompt and defend every choice in it.

Design day You can model an unfamiliar domain

02

The plan, 4 parts

Ordered by what each one buys you, not by topic. Every step says why it is here.

  1. Part 01 · ~1h

    Say what one row means

    You declare the business process and the grain before drawing a table.

    Skip this if "one row per…" is the first sentence you say, every time.

    1. SectionThe universal modeling method7 minOne method for any domain. It is how you model the unfamiliar.
    2. SectionTurning a vague request into a spec6 minThey will not hand you requirements. Getting them is the answer.
    3. InterviewAnswering "design a model for X"6 minWorkload, grain, tables, split. Naming tables first loses marks.
    4. SectionDeclaring grain before you build6 minEvery later decision depends on it. Undeclared grain is the top defect.
    5. SectionFan-out: when joins double-count6 minThe SQL trap, one layer up, and much harder to undo.
    6. SectionAdditivity: what you can safely sum6 minA balance sums across accounts and not across days. Say which.
    7. ExerciseSubscription warehouse grain35 minin the studioOne subscription, many events, one number. Pick the grain.
    8. QuestionsData Modeling · Conceptual15 minThe vocabulary a modelling round is graded in. Say it precisely.

  2. Part 02 · ~2h

    Keys, identity and the joins between

    You can choose a key and say what happens when the source reuses it.

    Skip this if you always know why the key is surrogate rather than natural.

    1. SectionNatural vs surrogate keys7 minNatural keys change and get reused. That is the whole argument.
    2. SectionHash-based surrogate keys6 minDeterministic keys let two loads agree without a lookup.
    3. SectionWhen one column is not enough6 minThe uniqueness claim is the model. Getting it wrong duplicates rows.
    4. SectionMany-to-many & bridge tables6 minA bridge without weights double-counts. Interviewers reach for this.
    5. SectionThe unknown row pattern6 minA null foreign key drops rows from a join nobody checks.
    6. ExerciseProduct analytics identity40 minin the studioOne person, four devices, two logins. Identity, made concrete.
    7. ExerciseCustomer 360 tradeoffs45 minin the studioEveryone wants a customer 360. Say what it costs to keep one.
    8. InterviewDesign a financial ledger6 minAsked at BitGo. Double-entry, or unbalanced stays representable.
    9. QuestionsData Modeling · Model design15 minTwenty design prompts. Grain first, every time.

  3. Part 03 · ~3h

    History: the SCD they will press on

    You can pick an SCD type and defend it against the follow-up.

    Skip this if you can say when Type 2 is wrong, not just what it is.

    1. SectionType 2: full history with new rows7 minThe one they mean when they say SCD. Know its invariants.
    2. SectionChoosing the right SCD type7 min"Wrong SCD, unjustified schema" is a logged rejection, verbatim.
    3. SectionCurrent vs point-in-time6 minStoring history is half. Querying it as-of is the half they test.
    4. SectionLate-arriving dimensions6 minThe fact landed before its dimension. Standard follow-up.
    5. SectionValid time vs transaction time6 minWhen it was true, and when we knew. Two columns, two questions.
    6. ExerciseAddresses that stay true to the past40 minin the studioShip to the address they had then, not the one now.
    7. ExerciseGlobal pricing history45 minin the studioPrices that changed, in currencies that also changed.
    8. ExerciseTrades, currencies, and rates as of trade date50 minin the studioThe rate as of the trade date. As-of joins, at their hardest.
    9. InterviewThe source deleted a row6 minDeleting the fact makes last month unreproducible. Reverse instead.
    10. QuestionsData Modeling · Evolution & migration15 minModels change. Changing one without breaking a report is the skill.

  4. Part 04 · ~3h

    Facts, shape, and defending it

    You can choose a fact type, argue the shape, and critique a model.

    Nothing to skip — the round usually ends with a model to break.

    1. SectionMeasures & additivity6 minAdditive, semi-additive, non-additive. Say which before you store it.
    2. SectionChoosing between the three fact types7 minTransaction, periodic snapshot, accumulating. Pick one, out loud.
    3. SectionHeader/line grain and allocated facts6 minOrder-level shipping on a line-level fact. The classic mixed grain.
    4. SectionStar vs snowflake: the trade-offs6 minNamed in six separate company records. Have a real answer ready.
    5. SectionConformed dimensions & the bus matrix6 minHow two marts agree. The answer to "does this scale org-wide".
    6. SectionWhere the number lives6 minModel or metric layer. Two dashboards disagreeing starts here.
    7. ExerciseRefunds without double counting45 minin the studioRefunds against the original sale. Additivity, with money attached.
    8. ExerciseClaims, reserves, and payments that reverse55 minin the studioAmounts that get revised and reversed. The hardest fact to model.
    9. InterviewWide columns or a JSON payload6 minEvent payloads keep growing. The split is per attribute, not per table.
    10. QuestionsData Modeling · Critique an existing model15 minHere is a model. Say what breaks, and when. Round over.

03

Start it now

Reading is free and needs no account. The plan itself tracks on your dashboard.

Start Ace the Data Modelling roundTracked, 4 parts, ~10hSee the other plans16 more, by what you are aiming at