Design — twenty-three industry data models, end to end.
A separate workspace from Q&A practice. The Systems at the Whiteboard long-form series works each design end to end — the question and its back-of-envelope math, the data flow, the schema, the atomic-consistency SQL, the streaming Python, the aggregation layer, and the dashboard that proves it works. Each reads like the whiteboard session a senior loop actually runs. The full twenty-three-scenario dimensional-modeling catalog sits below as a quick reference.
The Dispatch Problem
Driver matching, the location firehose & surge — availability everywhere except the one compare-and-swap that owns truth.
The Stacked-Delivery Problem
Three-sided dispatch with batched legs; the bridge's payout shares must sum to the dispatch payout.
The Auction Problem
Auction → impression → click → conversion threaded on one decision id; re-runnable attribution.
The Attribution Problem
Cross-device identity graph, match-at-read, and the structural SKAN wall — credit resolved as-of event time.
The Inventory Problem
CTV pacing & frequency capping with unfilled opportunities as first-class rows; make-good liability.
The Fulfillment Problem
Orders, returns & multi-warehouse inventory as immutable, append-only movements; 1P/3P carve-out.
The Feed Problem
Impression-as-fact at 100B/day; ranker versioning for replay; the celebrity hot-partition, keyed away.
The Royalty Problem
Pool-model royalties — the per-stream rate doesn't exist until the period closes; SCD2 rights bridge.
The Ledger Problem
Double-entry, append-only, SUM=0 per txn; idempotency on source_event_id; FX locked at event time.
The Booking Problem
Calendar-as-fact with snapshot_date in the PK for price time-travel; refund frozen at cancel time.
The Metering Problem
Subscription snapshot + hourly meter at separate grains; the invoice is a SUM, never a recompute.
The Recommendation Problem
Freeze the prediction before the outcome; the 5% exploration table is the non-negotiable counterfactual.
The Hierarchy Problem
Account tree + materialized path + 3-layer tenant isolation; org_id rides every query or it fails.
The Bingewatch Problem
Late-arriving dimensions — the play fact never waits for its title; per-session vs per-day binge.
The Integration Problem
Golden records + strangler dual-write; every merge reversible via an append-only crosswalk.
The Fraud Problem
Typed-strength identity graph → connected components; every decision replayable to the byte.
The Growth Problem
DAU/WAU/MAU as a new/retained/resurrected/churned state machine; the quick ratio; cohort LTV.
The Telemetry Problem
A sensor firehose with biometric-consent gates; device + session grains; the attention signal.
The Autonomy Problem
Disengagements & incident replay; the safety ledger; liability frozen at decision time.
The Recurrence Problem
Google Calendar, end to end — recurrence-as-rule + occurrence-as-fact to a rolling horizon; UTC derived per occurrence; free/busy as a bitmap.
Data Modeling — Twenty-Three Industry Scenarios
Ride-share (Uber/Lyft with multi-driver convoys, surge, real Delhi NCR dataset), 3-sided marketplaces (DoorDash stacked dispatch), ads (Google auction chain, Meta cross-device attribution, Netflix CTV inventory + pacing), e-commerce (Amazon orders/returns/inventory with 1P/3P carve-out), social feed (Instagram with ranker A/B), streaming (Spotify pool-model royalties), payments (Stripe double-entry ledger), marketplaces (Airbnb calendar-as-fact), SaaS (subscription + hourly metering), Spotify royalty pool distribution, TikTok For-You-Page recommendation feedback loop, Stripe double-entry ledger deep-dive, Salesforce account hierarchy + multi-tenant CRM, Netflix streaming + series + bingewatching + late-arriving dimensions, M&A integration with Adobe-Figma / Microsoft-Activision / Cisco-Splunk / Salesforce-Slack case studies, fraud detection (identity graph + decision replay), growth accounting (DAU/WAU/MAU + cohort LTV), spatial computing / VR telemetry, and autonomous robotaxi fleets, and Google Calendar's recurrence + free/busy engine. Plus 13 cross-cutting patterns including LAD, factless facts, streaming-first, storage modeling, and end-to-end data flow.
Trip lifecycle (with multi-driver convoys)
Journey → Trip → Event hierarchy + convoy bridge + 20K mass-event extension + real 148K dataset.
Surge pricing & supply/demand
State → Decision → Outcome causal chain. Frozen input_features_json + SCD2 model for replay.
Order & courier dispatch (stacked)
Customer + courier + restaurant grains, with brg_dispatch_orders bridge for batched deliveries.
Auction → Impression → Click → Conversion
4-fact chain joined by auction_decision_id. Re-runnable attribution via attribution_run_id.
Cross-device attribution & identity
Identity graph + SCD2 device bridge + match-at-read. SKAN aggregate carve-out for iOS.
Inventory, pacing & frequency capping
Unfilled opportunities as first-class rows. Pacing snapshot + make-good liability fact.
Orders, returns & multi-warehouse
Append-only returns + 1P/3P recognized_as carve-out + immutable inventory movements.
Engagement at scale (with ranker A/B)
ranker_model_id on every impression. Append-only engagements with is_undone for unlikes.
Listening history & pool-model royalties
Per-stream rate at period close. SCD2 bridge_track_rights for mid-quarter renegotiations.
Double-entry ledger
Append-only + SUM=0 per (txn × currency) invariant. UNIQUE(source_event_id) idempotency.
Bookings, calendar & reviews
Calendar-as-fact with daily snapshot_date in PK. Refund locked at cancel time.
Subscription + hourly usage metering
Daily subscription snapshot + hourly meter + 2-event proration on plan changes.
Royalty pool distribution
SCD2 bridge_track_rightsholder + frozen monthly country pool + 30-sec royalty boundary as denormalized flag.
For-You-Page recommendation + off-policy eval
5% exploration table for causal lift, predicted_score stored at impression-time, shadow-traffic model rollouts.
Double-entry ledger + idempotency
Append-only journal entries, SUM=0 per txn, refunds/chargebacks as new entries not updates, FX lock at event-time.
Account hierarchy + multi-tenant isolation
Hybrid parent_id + materialized path, 3-layer tenant isolation (partition + index + RLS), stage-changes as fact.
Series + bingewatching + late-arriving dimensions
LEFT JOIN + ROW_NUMBER + COALESCE defensive query, per-session vs per-day binge derivation, profile + account dual key.
Golden records + strangler + multi-org metrics
Ordered matching playbook (strict-then-fuzzy) + append-only crosswalk with un-merge support, dual-write CDC with 0.1% drift gate, share-and-bridge warehouse path, versioned dim_metric_definition. Adobe-Figma / Microsoft-Activision / Cisco-Splunk / Salesforce-Slack as live case studies (full deep-dive linked from the dedicated M&A Integration page below).
Identity graph, impossible travel, account sharing, model replay
Typed-strength identity graph (strong / medium / weak edges) feeding connected-component clusters; SCD2 on dim_ruleset + dim_risk_model + dim_feature_snapshot for 6-month decision replay; multi-geo / impossible-travel detection via session-event LEAD; account-sharing vs ATO vs multi-account-fraud separation in the schema; feedback loop where analyst labels (hours) + chargebacks (30-180d) reconcile into fact_outcome_label as training-set source of truth. Worked example: a Lagos-Berlin transaction blocked by cluster lookup expands to 17 linked mule accounts.
Growth accounting — DAU/WAU/MAU, the new/retained/resurrected/churned state machine, cohort LTV
The Social Capital / Reforge framework: a FULL OUTER self-join offset by one period decomposes every active-user base into new / retained / resurrected / churned (+ expansion / contraction for MRR). Two identities hold by construction — MAU(t) = retained + new + resurrected. One inc_amt column generalizes engagement and revenue. Frozen SCD0 cohorts feed the retention triangle and cumulative-LTV curve. Worked example: a "+100K MAU" headline that the quick ratio exposes as a 24%-churn treadmill. Netflix / Uber / Spotify lens.
Recurring events, time zones & free/busy
Recurrence-as-rule in an SCD2 series dim + occurrence-as-fact to a rolling horizon; exceptions append-only by recurrence_id; DST-correct UTC derived per occurrence; a 15-min free/busy slot fact for find-a-time. Full long-form treatment: The Recurrence Problem.
Senior DE Interview Prep — The Conversation
Eight deep sections worked through a Netflix case study but applicable to any senior loop: calibration (the four scoring axes), modeling (five-move sequence), SQL (seven patterns + dialect cheatsheet), Python (five reflexes + CodeSignal mechanics), streaming (Flink/Kafka vocabulary + 8-step play-event narrative), behavioral (STAR templates), system-design lite (4 architectures), day-of playbook + questions to ask back.
Snowflake — Architecture, Cost & CDC
The three-layer architecture, micro-partitions and pruning, the virtual-warehouse credit model, Time Travel and zero-copy cloning, Streams + Tasks + Dynamic Tables — plus an end-to-end project brief and a rapid-fire question bank.
▶ LIVESystem Design — Data Platforms
Lakehouse vs warehouse, batch vs streaming, dbt + Airflow patterns, schema registry, the Kappa architecture. Whiteboard-ready answers with diagrams.
▶ LIVEStreaming Architecture
Flink + Kafka deep-dive: event-time, watermarks, exactly-once, backpressure, checkpointing, state backends, schema evolution, the 8-step play-event walkthrough.
▶ LIVEData Quality & Reliability
Schema, volume, distribution and referential checks. Lineage, freshness SLAs, the on-call playbook. From "tests" to a real DQ framework.
▶ LIVEML Engineering Interview Prep
Feature stores, training/serving skew, model versioning, online vs offline metrics, A/B and incrementality. Where DE meets ML.
▶ LIVEM&A Integration — Data Platform Survival Guide
Entity resolution, schema-strangler dual-write, warehouse consolidation, multi-org metrics. Adobe-Figma, Microsoft-Activision, Cisco-Splunk, Salesforce-Slack as live case studies.
▶ LIVESkew & Distributions — The Senior DE Vocabulary
Whales / dolphins / minnows · decile / quartile / percentile · Pareto / long-tail / normal / skewed · mean vs median · LAD gap. The L5+ vocabulary interviewers test for, plus the one-sentence script that signals senior-IC bar in 90 seconds.
▶ LIVEHot Shards & Data Skew — Surviving Whales in Production
The operational deep-dive: Kafka partition skew, Flink keyed-state RocksDB hotspots, Spark salting / broadcast / AQE skew join, DB hot-row contention, DynamoDB / Cassandra hot partitions, BI dashboard query-time + visual lies. Plus the whale / dolphin / minnow handling playbook per layer.
▸ How Practice and Design fit together
Practice (Q&A) is for the rounds where you're given a schema and asked to write SQL or Python. Browse 1437 questions filtered by company, save your set, run answers in the embedded SQLite or Pyodide playground. Design is for the rounds where you're given an open-ended prompt — "design Uber's trip lifecycle data model" — and asked to architect a system end-to-end. Both pillars share the same workspace and use the same browser-only runtime; nothing is sent to a server.