# Design a change data capture system: system design interview question

> Coordinate a consistent snapshot with a change stream, preserve committed changes through failures, and enforce source storage limits.

Source: https://sysround.com/questions/design-change-data-capture-system
Updated: 2026-11-05


Difficulty: hard. Levels: Senior, Staff+.

## How the interviewer opens it

"Design a service that streams committed database changes and provides an initial snapshot. Support capture outages up to six hours and consumer outages up to 24 hours, within retained history. Keep 72 hours of stream history and a 2 TB source WAL limit; require a new snapshot when either limit prevents recovery."

## What to clarify first

- Source database and size
- Change types
- Traffic and read:write mix
- Record sizes
- Latency
- Consistency and availability
- Durability and recovery
- Source limits
- Stream retention
- Excluded features

## Scope of a strong answer

Core requirements:

- Create an initial snapshot of selected tables
- Stream committed inserts, updates, and deletes
- Resume within supported recovery limits without silent gaps
- Enforce source storage limits and require a new snapshot when continuity fails

Usually out of scope:

- Change the database engine
- Guarantee atomic delivery across tables
- Guarantee exactly-once effects in consumer systems
- Combine changes from multiple source databases

## What interviewers probe

### The snapshot boundary

The source changes during the scan. A mismatched snapshot and log position can omit changes or restore rows that the source deleted.

### Committed changes after a failure

Source progress, durable stream writes, and consumer effects occur in separate systems. A failure between them can cause loss or replay.

### Source storage and recovery limits

Capture can stop while the source continues to write. The service must protect source storage even when that action destroys capture continuity.

## Common mistakes

- Start capture only after the snapshot finishes.
- Treat an arbitrary table scan and WAL position as one consistent boundary.
- Acknowledge source progress before every required event has a durable copy.
- Let snapshot rows overwrite newer consumer state.
- Monitor retained WAL without a release threshold, headroom, and an explicit failure state.
- Promise recovery after required history expires or a source safety action removes it.

## The bar by level

- **Senior:** Defines a valid snapshot boundary and safe source acknowledgements. Handles transaction aborts, crash replay, row order, and independent consumer progress. Enforces retention limits with explicit failure states.
- **Staff+:** Proves continuity across snapshot, capture, and recovery states. Quantifies outage headroom and recovery capacity. Defines independent source protection, bounded WAL release, owner fencing, and the contract after forced history loss.

Practice this question with an AI interviewer: https://sysround.com/practice/new?problem=change-data-capture


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