Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. System Design

System design interview

CDC Pipeline with <5-Minute Latency

MediumPro55 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

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 five minutes is a different problem from refreshing every star-schema mart in under five 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 five-minute SLA, and honesty about snapshot duration. Fancy connector names without those themes score poorly.