D8LooP
Start building

Hands on Concepts

How to Design a Data Warehouse, End to End

Four decisions that outlive the design session, and how to load and defend them.

4 decisions36 steps~8h 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 own a model that loads nightly and that other teams trust.

Model owned People build on your tables

02

The plan, 4 decisions

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

  1. Decision 01 · ~2h

    What one row means

    You can declare a grain and choose keys that survive the source.

    Skip this if every table you build starts from a written grain statement.

    1. SectionConceptual, logical, physical6 minThree levels, three conversations. Mixing them is how designs stall.
    2. SectionOne, many, and how many6 minCardinality is what decides whether a join can inflate anything.
    3. SectionDeclaring grain before you build6 minWrite it down. Every later argument gets settled by that sentence.
    4. SectionNatural vs surrogate keys7 minSource keys get reused and reformatted. Yours should not.
    5. SectionDeliberate denormalization for read paths6 minWiden on purpose, with a reason. Not because a join was slow.
    6. ExerciseSubscription warehouse grain35 minin the studioOne subscription, many events, one number. Pick the grain.
    7. ExerciseOrders you can trust eight months later45 minin the studioOrders that still reconcile eight months later. That is the job.
    8. InterviewAnswering "design a model for X"6 minWorkload, grain, tables, split. The order is the method.
    9. QuestionsData Modeling · Conceptual15 minThe vocabulary the rest of this is argued in. Say it precisely.

  2. Decision 02 · ~2h

    How history is kept

    You can rebuild what a dimension said on any past date.

    Skip this if you already run snapshots and can query them as-of.

    1. SectionChoosing the right SCD type7 minType 2 everywhere is a decision nobody made. Make it deliberately.
    2. SectionEffective dating (valid time)6 minWhen it was true, stored as a range you can query against.
    3. SectionIntegrity invariants & dbt snapshots7 minOverlapping validity ranges are silent. Test for them.
    4. SectionSame changes, two different histories7 mintimestamp or check. The choice changes what history you end up with.
    5. SectionThe row that stops arriving6 minA deleted source row simply vanishes. Snapshots have to be told.
    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. InterviewThe source deleted a row6 minReverse it. Deleting the fact makes last month unreproducible.
    9. QuestionsData Modeling · Evolution & migration15 minModels change. Changing one without breaking a report is the skill.

  3. Decision 03 · ~2h

    How it actually gets loaded

    Your tables load incrementally and survive being run twice.

    Skip this if your loads are merges and you reconcile counts after each.

    1. SectionFull refresh, append, delete+insert, merge7 minFour strategies, four rerun behaviours. Pick with the rerun in mind.
    2. SectionMERGE: source, target, matched, not matched7 minThe workhorse. A duplicate in the source makes it fail or lie.
    3. InterviewAppend, merge or overwrite6 minRun it twice. Append duplicates; the other two converge.
    4. SectionView vs table: the trade, priced6 minOne word decides what exists, what it costs, and who waits.
    5. SectionFour strategies, six rows, four outcomes7 minSame six rows through four strategies. The differences are the lesson.
    6. SectionPartitioning & pruning huge tables6 minThe physical decision that makes the logical one affordable.
    7. ExerciseDedupe staging before merge20 minin the studioA duplicate in staging is what breaks a MERGE. Catch it first.
    8. ExerciseLoad reconciliation summary20 minin the studioProve the load landed. Counts, amounts, and what disagreed.
    9. QuestionsData Pipeline · Failure & recovery15 minReruns, replays and backfills against a load you own.

  4. Decision 04 · ~2h

    Who depends on it

    You can change or retire a table without breaking its consumers.

    Nothing to skip — a model nobody can depend on is not finished.

    1. SectionWhat a data contract actually is6 minA promise with a consequence. Without the consequence it is a wish.
    2. SectionA test asks, a constraint promises6 minOne fails after the build, one stops it. Know which.
    3. SectionGreen does not mean right6 minIt means nothing tested was wrong. Say what went untested.
    4. SectionAgreeing the handoff before you build6 minThe BI team will aggregate your columns. Agree how, in advance.
    5. SectionRetiring a dataset forty dashboards use7 minDeprecation is a modelling skill. Nobody teaches it and everyone needs it.
    6. InterviewWhere PII lives in the model6 minMasking is cheap when PII is concentrated, expensive when smeared.
    7. InterviewWide columns or a JSON payload6 minPromote what is queried. The split is per attribute, not per table.
    8. ExerciseThe table everyone depends on45 minin the studioForty consumers you did not know about. Change it anyway.
    9. QuestionsData Modeling · Critique an existing model15 minRead someone else's model the way a reviewer reads yours.

03

Start it now

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

Start Design the warehouseTracked, 4 decisions, ~8hSee the other plans16 more, by what you are aiming at