How the interviewer opens it
“Design a service that keeps saved query results current as source tables change. It must support joins and aggregates, with clear freshness and recovery costs.”
The prompt is vague on purpose. The first minutes are for you. Reduce the prompt to the core features and agree on the numbers before you draw.
What to clarify first
- Supported queries
- Source guarantees
- Data size
- Traffic and read:write mix
- Join size and skew
- Latency and freshness
- Consistency and availability
- Durability and recovery
- Initial build cost
- Result access and exclusions
The interviewer has the answers. In a SysRound round, Alex gives these answers, as a real interviewer does.
Scope of a strong answer
Core requirements
- Register a view that joins two tables and optionally computes grouped sums and counts.
- Update results after source inserts, updates, and deletes.
- Serve complete result versions with their source position and freshness.
- Recover from worker failures without losing changes or applying changes twice.
Usually out of scope
- A general SQL parser or query optimizer.
- Joins across separate source databases.
- Outer joins, DISTINCT, MIN, MAX, and arbitrary user functions.
- Source schema changes and regional disaster recovery.
What interviewers ask about
- Changes to joined rows
- One source change can alter thousands of joined rows. Updates can move rows between join keys and groups.
- The first result
- The initial scan takes hours while source writes continue. An incorrect boundary can lose changes or apply changes twice.
- Freshness during heavy change
- Source write rates do not measure result work. Join fanout and popular groups can concentrate work on a few workers.
- Recovery after a failure
- State, source progress, and published results can disagree after a failure. Recovery must preserve correctness within a fixed time budget.
Common mistakes
- Treating a source update as an insert and ignoring its old contribution.
- Assuming all joins have one matching row on one side.
- Ignoring the boundary between the initial snapshot and later source changes.
- Equating an acknowledged source message with a durable, correct result.
- Publishing partial results while reporting a complete source position.
- Choosing a checkpoint interval without calculating restore time and replay work.
The bar by level
- Senior (L5/E5)
- Handles changes on both join inputs, including key changes. Defines complete result versions. Explains duplicate-safe recovery and calculates fanout and catch-up capacity.
- Staff (L6+/E6+)
- Defines a coherent protocol across source capture, operator state, and result publication. Bounds recovery cost and identifies hot-key limits. Explains resource isolation, history gaps, and safe reconstruction.