Design a change data capture pipeline
PremiumData pipelineHard
- Change data capture
- MySQL binlog
- Kafka
Quick answer
This data pipeline interview question asks you to design a change data capture pipeline. Aim for a design that moves data from sources to consumers reliably and on time, then explain ingestion, processing, storage and trade-offs. Interviewers want to see how you handle volume, late or bad data and failures along the way.
The problem
Design a change data capture (CDC) pipeline that reads every insert, update and delete from 32 sharded MySQL databases and publishes them to Kafka as ordered events within seconds. Search, cache and warehouse teams build on it. The hard parts are ordering, snapshots of huge tables, failover and schema changes.
Today each team polls the databases with its own queries, which loads the primaries and misses deletes. The new pipeline reads the database's own change log, so the work is done once.
What must a change data capture pipeline do?
- Change events: Publish one event per row change with operation, before and after images, primary key, table, shard, commit time and source position.
- Ordering: Consumers see changes to the same row in commit order.
How well must it do it?
- Freshness: Commit to Kafka in under 2 seconds at p99 in normal operation.
- No gaps: Every committed change is published at least once, across connector restarts and database failovers.
What scale should you design for?
Use these numbers in your estimates.
The scale numbers unlock with this questionUnlock
Key concepts
- Change data capture
- MySQL binlog
- Kafka
- GTID
- Incremental snapshots
- Schema registry
How you’ll practise
- Requirements
- Data volume
- Ingestion
- Storage and processing
- Serving and freshness
- Wrap-up
Lala, personalized to you, gives a hint when you ask and reviews your design when you are ready.
What do interviewers look for?
In any data pipeline design round.
- Clarifies sources, consumers and freshness needs
- Chooses batch, streaming or both with reasons
- Handles late, duplicate and malformed records
- Makes each stage safe to retry
- Explains storage formats and partitioning
- Plans monitoring, backfills and data quality checks
Questions people ask about this interview question
- Which design type is this question?
- It is a data pipeline question. You design a change data capture pipeline and explain your choices the way you would in an interview.
- Is this question asked at entry level?
- It is marked Hard. It suits engineers preparing for senior interviews, where you are expected to drive the discussion and go deep on trade-offs.
- What should I cover first?
- Start with the data sources, the consumers and how fresh the data must be. Agree on scope with the interviewer before you design anything.
- Can I practise it for free?
- It is a premium question. Sign up and unlock it once for 10 credits; it then stays unlocked. When you finish, Lala, your personalized AI mentor, can review your design for 30 credits.
Related questions
Updated · Written by the SevyDevy team