Data Modeling Case Studies
Each case follows the modeling interview script: business questions → processes → grains → ERD → design decisions → validate with SQL → trade-offs → follow-ups → rubric.
How to practise: read only the prompt and business questions, spend 30–40 minutes drawing your model (grain first!), then compare.
| # | Case | Difficulty | Key concepts |
|---|---|---|---|
| 1 | Ride-hailing marketplace | Medium | Accumulating snapshot, role-playing geo/date, payments vs trips |
| 2 | E-commerce orders, returns, inventory | Medium | Line vs order grain, allocation, semi-additive snapshots, factless coverage |
| 3 | Video streaming | Medium | Heartbeats → sessions, title hierarchy, subscription snapshots |
| 4 | Social network | Hard | Symmetric edges, engagement ratios, impressions at scale |
| 5 | Music streaming & royalties | Hard | Bridge tables, allocation factors, non-additive uniques |
| 6 | Accommodation bookings | Hard | Booking vs stay date, nightly grain, occupancy denominators |
| 7 | Banking accounts & balances | Medium | Balance snapshots, ADB, joint-account bridge, as-of reporting |
| 8 | SaaS subscriptions & MRR | Medium | MRR normalisation, movements, NRR cohorts |
| 9 | Food delivery lifecycle | Medium | Milestones, header/line/sub-line, batching |
| 10 | Customer 360 with Data Vault | Hard | Hubs/links/satellites, identity resolution, survivorship |
Concepts: Modeling process · Dimensional modeling · SCD types · Modern modeling