# Design an incremental materialized view system: system design interview question

> Changes to joined tables can affect many results, which creates difficult trade-offs between correct totals, fresh results, and recovery cost.

Source: https://sysround.com/questions/design-incremental-materialized-view-system
Updated: 2026-11-05


Difficulty: hard. Levels: Senior, Staff+.

## 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."

## 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

## 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 probe

### 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:** 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+:** 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.

Practice this question with an AI interviewer: https://sysround.com/practice/new?problem=materialized-view-maintenance


---
SysRound (https://sysround.com) is an AI practice tool for system design interviews. It has a voice or typed AI interviewer and a shared whiteboard. At the end, you get feedback on each skill and a model answer. The AI feedback can be wrong.