System design interview

CDC Pipeline with <5-Minute Latency

MediumLocked55 min read

Sync a 500GB PostgreSQL OLTP database to a warehouse with under 5-minute latency, including deletes, schema changes, and reconciliation.

system-designcdcstreamingwarehouseschema-evolution

Start here

The problem in plain words

A shop keeps its orders in a live database. The analytics team keeps a copy in a warehouse so they can run big reports without slowing the shop. The old way to keep the copy fresh is to re-copy everything every night. That means the warehouse always shows yesterday.

Product now wants new orders in the warehouse within 5 minutes. Re-copying 500 GB every 5 minutes is out of the question. Asking the database "what changed since last time?" sounds clever, but it misses rows that were deleted, and it keeps hammering the database with queries.

There is a better source of truth. Every serious database already keeps a diary of every change it makes, written so it can recover after a crash. Change data capture, or CDC, reads that diary and sends each change onward as it happens.

Words you will meet

Primary key
The column that uniquely identifies each row, such as order_id. It lets a copy find and update the right row.
Transaction log (WAL)
The database's write-ahead log: a diary of every change, kept for crash recovery. In Postgres it is called the WAL.
CDC (change data capture)
Reading the transaction log and turning each insert, update and delete into an event you can send elsewhere.
Logical decoding
The Postgres feature that turns the raw log into readable row changes.
Replication slot
A bookmark in the log that Postgres holds for a reader. The database keeps all log data after the bookmark until the reader catches up.
Snapshot
A consistent copy of a table at one moment, used to load the starting state before changes stream in.
Debezium
A popular open-source tool that does CDC from databases like Postgres and publishes changes to Kafka.
Upsert / merge
Insert the row if it is new, update it if it exists. Applying the same change twice leaves the same result.
Reconciliation
Comparing source and copy, for example counts or checksums, to prove they match.
Why re-copying and polling both fall short
dumploadstaleOrders database500 GB, alwayschangingNightly copyor poll updated_atWarehousestale or missing rowsAnalystwants 5-minutefreshness
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.The old way copies the whole database once a night. The copy is a day old the moment it finishes.
  2. 2.Polling "rows changed since last time" is quicker, but a deleted row leaves nothing behind to find, so the warehouse keeps rows that no longer exist.
  3. 3.The analyst sees old or wrong numbers, while the product team wanted fresh ones in under 5 minutes.

The numbers, as things you can picture

  • 500 GB databaseThis is the size of the data, not how fast it changes. Most databases of this size change only a little each second, which suits CDC.
  • Under 5 minutesFrom a change committed in the shop to that change visible in the warehouse.
  • Initial copy takes hoursThe first load of 500 GB is slow, so the design must keep up with changes while it runs.

What the interviewer wants

Design copying a Postgres database to a warehouse within 5 minutes, handling deletes and schema changes correctly, and prove the two sides match.

Interview framing

You are designing change data capture (CDC) from Postgres to a warehouse. About 50 minutes. The interviewer wants correctness under deletes and schema change, not only Debezium in the middle.

The prompt:

Sync a 500 GB PostgreSQL OLTP database to a data warehouse with under 5 minutes latency. Handle schema changes, deletes, and row-count reconciliation.

Background from first principles

Nightly dumps give you yesterday. Product wants orders in the warehouse within minutes of checkout.

Polling updated_at every minute misses deletes, races with in-flight updates, and hammers indexes. CDC reads the database transaction log (logical decoding in Postgres). The log already knows what changed: insert, update, delete, in commit order.

Two phases matter:

1. Initial snapshot: copy existing 500 GB consistently enough to start. 2. Ongoing streaming: apply change events so the target stays within the lag SLA.

Also separate landing from modeling. Getting mirrored tables fresh in under 5 minutes is a different problem from refreshing every star-schema mart in under 5 minutes. Strong candidates split those and sequence them.

Expanded problem and constraints

  • Source: Postgres about 500 GB.
  • Target: warehouse or lakehouse (Snowflake, BigQuery, Redshift, or Iceberg).
  • Freshness: under 5 minutes under normal load. Alert when lag exceeds that.
  • Ops: inserts, updates, deletes must apply correctly (tombstones or hard deletes). Pick a contract.
  • Schema: evolvable without silent truncation or crash loops.
  • Trust: periodic reconcile of counts (and optional checksums).
  • Protect primary: prefer replica or logical decoding with care for slot growth.

What good looks like

You explain why not poll. You describe snapshot plus catch-up. You draw Debezium or DMS to Kafka to an idempotent merge sink. You define delete semantics. You plan expand/contract schema changes. You include reconcile and lag alerts as part of the design.

You call out tables without primary keys as blockers. You mention TRUNCATE as a special case.

Clarifying questions

  • Tables in scope: all 500 GB or a subset of domains?
  • Soft deletes vs hard deletes in source?
  • Who owns schema migrations and at what cadence?
  • Target model: mirror tables vs transformed marts?
  • Acceptable lag for huge bulk updates?
  • Primary keys on all replicated tables?
  • Is a replica available for snapshot and decoding?

Scale and estimation prompts

  • 500 GB is size, not change rate. Ask writes/sec or MB/sec of WAL.
  • Initial snapshot may take hours. Plan online catch-up so you do not dual-write forever.
  • Merge cost in the warehouse can dominate if you micro-batch too chatty. Batch applies carefully while staying under 5 minutes.
  • Estimate: if peak change volume is 20 MB/sec, Kafka and sinks are easy; if nightly batch jobs rewrite huge tables, CDC spikes and needs headroom.

Out of scope for v1

  • Transforming every mart in under 5 minutes (start with mirrored landing, then transform).
  • Multi-primary multi-region conflict resolution.
  • Tables without primary keys (call them out as blockers).
  • Perfect history for every column change unless SCD is requested.
  • Using CDC as a drop-in replacement for backups.

Why this problem shows up in interviews

Many companies already have nightly ETL. Product then asks for warehouse freshness measured in minutes. Candidates who only know dumps struggle. Candidates who only draw streaming logos without deletes and reconcile also struggle. This prompt sits in the middle: log-shaped capture, warehouse-shaped sinks, operational discipline.

Extra constraints to confirm

Confirm whether analysts query landing tables directly or only curated marts. Confirm whether soft deletes already exist in OLTP as columns (is_deleted) versus hard deletes that only appear in the WAL. Confirm maintenance windows where primary failover testing happens, because CDC must survive those drills.

What interviewers listen for

They listen for: slot growth awareness, idempotent merges, delete contracts, schema expand/contract, lag alerts tied to the 5-minute SLA, and honesty about snapshot duration. Fancy connector names without those themes score poorly.