Design a change data capture pipeline

The brief

Stream every insert, update and delete from a production database into the warehouse within minutes. Your design should meet these requirements:

1. Changes to the main tables of a production database reach warehouse tables within 5 minutes.
2. Updates and deletes are reflected correctly, not only new rows.
3. The pipeline never slows the production database and recovers from failures without losing or duplicating changes.

Scale to design for

A production Postgres (or MySQL) primary with about 200 tables, of which 20 matter most; around 1,500 writes a second on average and 5,000 at peak. The largest table has 500 million rows. Changes must reach the warehouse (Snowflake, BigQuery or a lakehouse) within 5 minutes. The database team forbids heavy queries on the primary. Schema migrations ship weekly, rows are hard-deleted, and some tables have no updated_at column.

State these assumptions out loud in the interview, then let them drive your choices: they decide what needs caching, sharding or asynchronous processing.

Before you start

You'll get more out of this round if you're comfortable with:

• Database write-ahead logs and binlogs
• Kafka topics and partitions
• Upserts with MERGE
• Schema evolution

Questions an interviewer may ask

Expect follow-ups like these once your high-level design is on the board. Have an answer, and a reason, for each:

• How do you load the existing 500 million rows and then switch to streaming without missing or duplicating a change?
• The connector is down for 3 hours: what happens to the production database, and to the warehouse when it comes back?
• Two updates to the same row arrive in one batch: which one wins, and how do you know?
• A developer renames a column in production: what does your pipeline do?
• How is a hard delete in the source reflected in the warehouse?

Common mistakes to avoid

• Polling the primary with SELECT on updated_at every few minutes
• Missing hard deletes
• Applying changes with parallel consumers in a way that reorders updates to the same row
• Inserting every change event into the target table so rows are duplicated
• Doing the initial load separately with no agreed start position
• Never monitoring the replication slot until the primary's disk fills
• A column rename that silently breaks downstream tables

Concepts this round covers

• Log-based capture
• Initial snapshot and ordering
• Schema changes and deletes
• Change data capture

What a complete answer covers

A log-based capture flow through a durable log, a snapshot-then-stream bootstrap, idempotent ordered merges into the warehouse, and handling of deletes, schema changes and replay.

There is no single correct design. A strong answer meets the requirements at the stated scale and explains the trade-offs behind each choice. New to the format? Read how to prepare for a system design interview.