← Level 8 · Module Home · Level 9 (of 0–9)
Pilot set: 6 of ~30 target questions. No single query is the deliverable here — the candidate designs schema, indexes, and query strategy for an end-to-end need, then defends the tradeoffs.
Difficulty: ⚫ Staff/Production Category: System Design Type: I — Design
A company’s HR system (employes/departments/locations, this
handbook’s shared schema) currently serves both transactional writes
(HR staff editing records) AND analytical reporting (headcount
dashboards, compliance reports) directly against the same tables. As
company size grows, dashboard queries are starting to compete with and
slow down transactional writes.
Design an approach. Cover schema, indexing, and query strategy — not just “add a read replica” as a one-line answer.
fact_headcount_snapshot with
dimension_department, dimension_location) that’s populated via a
scheduled ETL/ELT job rather than queried live against the
transactional schema at all“Just add more indexes to the existing tables” — this doesn’t address the fundamental read/write workload conflict and can make transactional writes slower (more indexes to maintain per write, Module 16).
“How would you decide between a read replica alone vs. a full separate reporting schema? What signal would tell you which is sufficient?”
Difficulty: ⚫ Staff/Production Category: System Design, SaaS Type: I — Design
Design the schema for a subscription billing system that needs to support: prorated upgrades/downgrades mid-cycle, accurate MRR reporting (Q034), and historical “what did this customer’s plan look like on any given past date” queries for support/audit purposes.
Propose a schema and explain your key modeling decisions.
subscription_events table (customer_id,
event_type, effective_date, plan_id, mrr_delta) where every plan
change is a new row, never an update — this is what makes “what did
their plan look like on date X” answerable at all, and is exactly the
model Q034 assumedmrr_delta — a design
that only stores start-of-cycle plan values can’t represent this
accurately“How would you keep the derived ‘current state’ table consistent with the event log as changes happen — synchronously in the same transaction, or asynchronously?” (Tests awareness that this is itself a real consistency-vs-complexity tradeoff, connecting back to Module 17’s transaction design content.)
Difficulty: ⚫ Staff/Production Category: System Design, Fintech Type: I — Design
Design the data access pattern for a fraud detection system that needs to answer “has this account shown suspicious velocity in the last 5 minutes” on every transaction, in under 50ms, against a transaction table growing by millions of rows per day.
Propose an approach — a single query against the full transaction table is very unlikely to meet this latency target at this scale; explain why, and what you’d do instead.
transactions table for
“last 5 minutes of activity” per request, even with a good index, adds
unnecessary risk at this latency budget and scale — a hot, narrow,
purpose-built store (a small rolling window table, or a cache/
in-memory store keyed by account_id with a short TTL) is the
appropriate design, not a bigger index on the main tabletransactions table remains the system of record for
historical/audit queries and offline model training — this hot-path
velocity check is a deliberately separate, narrower data path,
not a replacement for itProposing to just add an index on (account_id, txn_timestamp) to the
main table and calling the design complete, without addressing whether
that alone can realistically hit a 50ms target as the table grows to
billions of rows.
“What happens to this design if the fraud check needs to also consider patterns across MULTIPLE accounts (e.g., many small transactions from different accounts to the same destination in a short window)? Does your hot-store design still work?”
Difficulty: ⚫ Staff/Production Category: System Design, Data Warehousing Type: I — Design
Design a schema and query strategy for tracking and analyzing user events (page views, clicks) at a volume of ~50 million events/day, with requirements to support both real-time-ish dashboards (data within a few minutes) and deep historical analysis (cohort retention over years).
Propose an approach covering ingestion, storage, and the two very different query patterns.
“Just put it all in one big table and add indexes” — doesn’t address the volume (50M/day compounds fast) or the fundamentally different access patterns between the two stated requirements.
“A stakeholder wants a NEW cohort analysis dimension added six months after this system launches. How well does your design accommodate that without a full re-architecture — what did you build in intentionally to allow for this?”
Difficulty: ⚫ Staff/Production Category: System Design, Compliance Type: I — Design
HR needs a tamper-evident audit trail of every change to employes
(who changed what, when, old and new value) for compliance review,
without slowing down normal transactional writes.
Propose a schema and capture mechanism, and explain the tradeoffs.
employes_audit table
(audit_id, emp_id, changed_by, changed_at, column_name, old_value,
new_value) — rows are only ever inserted, never updated or deleted,
which is itself part of what makes the trail trustworthyemployes
guarantees every write is captured regardless of which application
path made it (safer, but adds per-write overhead and couples audit
logic to the database layer), versus application-level logging
(faster, more flexible, but misses any write that bypasses the
application — a direct DB console edit, for instance)employes update can produce multiple
per-column audit rows — retention/archival policy needs its own plan“Just add an updated_at timestamp column to employes” — this shows
only the most recent change, not who changed what, and provides no
historical trail at all.
“A regulator asks for proof that a specific salary change on a specific date wasn’t altered after the fact. Does your design actually provide that proof, or just a claim of one?”
Difficulty: ⚫ Staff/Production Category: System Design, Multi-Tenancy Type: I — Design
This HR system is being sold to multiple companies as SaaS. Each
tenant’s employes/departments/locations data must never leak into
another tenant’s queries, even under an application bug.
Compare at least two isolation strategies and recommend one with justification.
tenant_id column on every table, enforced
at the database level via row-level security policies (not just
“the application always adds WHERE tenant_id = ?”) — cheapest to
operate at scale, but a missed tenant_id filter anywhere, without
RLS as a backstop, is a real cross-tenant leak riskRecommending shared-schema-with-tenant_id as strictly superior without
ever mentioning row-level security or acknowledging the leak risk of an
enforcement-only-in-application-code approach.
“A large enterprise tenant now requires their data physically isolated for a compliance certification. Does your original design accommodate that for one tenant without re-architecting for all of them?”