Data Engineering Interview: Design a Change Data Capture Pipeline

Problem and Scope

A company has 20 PostgreSQL OLTP databases, 200 tables to synchronize, and 8 TB of current data. The sources produce 20,000 committed row changes per second on average and 60,000 at peak. An encoded change is about 1 KB on average. Data must reach an analytical warehouse or lakehouse, with p99 latency from source transaction commit to queryable curated data below two minutes. The initial synchronization must finish within 72 hours without stopping application writes, and the snapshot plus CDC workload must cause less than 5% regression in source write p99.

The pipeline must handle inserts, updates, deletes, and primary-key value changes. It must preserve change order for a row within one source, use at-least-once delivery, support schema evolution and replay by table or primary-key range, recover from source and sink failures, and provide reconciliation that can demonstrate correctness. The durable change stream retains seven days of raw events, while a lower-cost object store keeps longer history. The database count, throughput, sizes, latency, and resource limits are interview assumptions, not performance promises for a product.

Although the design contains a broker and multiple components, the core skill is data engineering: joining a stored snapshot to an unbounded change stream without a gap, defining recoverable positions and idempotency boundaries, and proving that the destination is neither missing data nor silently wrong.

What Interviewers Evaluate

The first signal is defining correctness before drawing components. A weak answer starts with “Debezium plus Kafka.” A strong answer states invariants: committed changes cannot disappear, one row must converge in source order, replayed events cannot alter the final state, deletes must reach the target, the snapshot-to-stream boundary cannot have a hole, and an uncertain recovery position must fail closed instead of silently continuing.

The second signal is understanding that an initial snapshot is not “export all tables, then enable CDC.” Writes continue during an 8 TB export. If WAL capture begins only after the export, intermediate changes might already be gone. A reliable design establishes a recoverable log position and retention mechanism before or as it acquires a consistent snapshot, then streams committed changes from the matching position. A connector can encapsulate the procedure, but the candidate still has to explain why the handoff has no gap.

The third signal is carrying at-least-once delivery through the sink. A connector can crash after durably emitting an event but before recording its source offset. A warehouse batch can commit while its acknowledgement is lost. “Exactly once in the broker” does not automatically include an external warehouse. Strong answers attach source identity, source position, and transaction order to each event, then apply an event only when its version is newer than the version already recorded for that row.

The fourth signal is recognizing the two-sided risk of PostgreSQL replication slots. A slot lets the connector resume from an LSN, but it also retains WAL while the connector falls behind. Without retained-byte and disk-headroom alerts, a sink outage can eventually fill source storage. If a slot is dropped and recreated, the new slot cannot reproduce the old position. The pipeline must stop, reconcile, or resnapshot rather than continue “from now” and claim that nothing was lost.

Finally, the answer must cover real data semantics. A business delete event differs from a tombstone used for log compaction. A primary-key value update commonly appears as a delete for the old key and a create for the new key. LSNs from separate databases cannot be compared. If consumers need atomic visibility for every row in a multi-row transaction, the design must use transaction-boundary metadata and accept extra buffering, latency, and recovery complexity.

Questions to Clarify Before Answering

• Does the destination need current state, full change history, or both? Current state fits primary-key MERGE operations. Audit and replay also require an immutable raw change layer. Final state alone cannot reconstruct history.
• Where does the two-minute latency clock start and end? This problem measures from source commit to queryable curated data. A requirement that stops when the raw event enters the broker is much easier.
• Must a multi-table transaction become visible atomically? The default here is per-row ordering with brief partial visibility in the warehouse. A financial consumer that requires whole-transaction atomicity needs BEGIN/END assembly.
• Does every table have a stable primary key? Without one, locating and deduplicating UPDATE and DELETE events is harder. Add a business key or explicitly accept full-row identity, surrogate keys, and greater storage cost.
• Does source failover preserve logical replication slots? If not, recovery needs a controlled write pause, last-LSN verification, slot recreation, and reconciliation. A primary failover cannot be treated as an ordinary reconnect.
• Which schema changes may pass automatically? This design automatically accepts compatible changes such as an added nullable column. Drops, renames, narrowing type changes, and primary-key changes use controlled migrations.
• Which is harder, the 72-hour snapshot deadline or the 5% source budget? Reading 8 TB in 72 hours requires about 30.9 MB/s of aggregate effective throughput. If load tests show that this breaks the source budget, extend the deadline, use a semantically valid replica, or reduce the scope in phases.
• How long must the online stream absorb a destination outage? This design covers a 30-minute peak outage online. The seven-day durable stream and object archive handle longer replay.

30-Second Answer

“I would define correctness first: the snapshot and stream have no gap, a row converges by source position, replay is safe, deletes propagate, and a missing replication-slot history fails closed. Each PostgreSQL source uses logical decoding over WAL, takes a consistent snapshot, and resumes from the corresponding LSN. Events are partitioned by source, table, and primary key into a durable stream. The raw layer stays immutable, while the curated sink performs idempotent MERGE operations by primary key and source version. Schema compatibility is checked before application, and deletes and key changes are explicit. I would monitor segmented freshness, LSN lag, retained WAL, snapshot progress, and reconciliation differences, then inject connector crashes, slot loss, schema changes, and sink outages.”

Step-by-Step Deep Dive

Step 1: Size the buffer, snapshot, and recovery budget

The average load is:

20,000 events/s × 1 KB ≈ 20 MB/s
20,000 × 86,400 = 1,728,000,000 events/day
20 MB/s × 86,400 ≈ 1.728 TB/day

Peak ingress is about 60 MB/s. If the destination stops for 30 minutes at peak, the raw backlog is approximately:

60 MB/s × 1,800 seconds = 108 GB

Seven days at the average rate is about 12.096 TB of raw data before replication, indexes, and encoding overhead. Size the broker, object store, and network for peak traffic and catch-up, not just the 20 MB/s average. If the recovered consumer can only equal the live input rate, it will never remove the 108 GB backlog; the design needs spare consumption capacity or a temporary relaxation of curated-layer latency.

Finishing an 8 TB snapshot in 72 hours requires at least:

8 TB ÷ 72 hours ≈ 30.9 MB/s

That is a lower bound that excludes scan amplification, serialization, network retries, and target writes. Benchmark chunked snapshots on production-shaped data, then rate-limit by source I/O, cache behavior, replication lag, and p99. If the deadline conflicts with the 5% source budget, change the deadline or source path instead of overwhelming OLTP.

Step 2: Make every component answer a correctness or capacity need

The main data flow is:

PostgreSQL WAL / logical slot
  → source connector
  → durable change stream keyed by source + table + primary key
  → immutable raw archive
  → schema validation and light normalization
  → sink staging tables
  → idempotent MERGE / DELETE into curated tables
  → warehouse and lakehouse consumers

Give every source database its own source identifier, connector, and replication slot so one failure domain does not stall all sources. The durable stream absorbs bursts, decouples consumers, and supports short-term replay. Object storage retains longer history. Keep the CDC processing layer limited to schema validation, normalization, and routing; an unreplayable external lookup in the main path makes recovery harder. Write the warehouse through staging tables and small atomic MERGE batches to balance a two-minute SLA with efficient columnar writes.

Use a stable encoding of (sourceid, tableid, primary_key) as the partition key so changes for one row reach one ordered partition. This does not provide global order, and global order is unnecessary. The 20 source databases have separate LSN sequences whose numeric values have no cross-source meaning.

Step 3: Perform the write-online initial snapshot

The logical sequence is:

1. Establish a logical replication slot so required WAL cannot be reclaimed before the connector consumes it.
2. Acquire a consistent snapshot and its corresponding source-log position.
3. Read the snapshot in table and primary-key chunks, emitting current rows as READ events.
4. Stream committed INSERT, UPDATE, and DELETE records from the position associated with the snapshot.
5. Let the sink converge by source version, allowing boundary replay during recovery but never allowing a missing interval.

A connector might implement a full consistent snapshot or incremental snapshot windows. The answer should not invent a “record an LSN, then run ordinary SELECT statements” protocol because isolation, long transactions, and concurrent writes make that deceptively complex. Rely on verified connector semantics and test one primary key that is repeatedly updated, deleted, and recreated while its snapshot chunk is running.

Chunk by primary-key range and persist progress. Smaller chunks reduce long transactions, cache disturbance, and failure rework; excessively small chunks add query and scheduling overhead. Rate-limit each source independently, establish end-to-end confidence with small tables first, then process large tables. The connector must keep draining WAL during the snapshot or retained WAL will grow. If the selected connector does not support schema changes during an incremental snapshot, freeze DDL for that table or pause its snapshot.

Step 4: Define the event, ordering, and idempotent application

A normalized event includes at least:

ChangeEvent {
  event_id
  source_id
  table_id
  primary_key
  operation          // READ | CREATE | UPDATE | DELETE
  before
  after
  source_lsn
  transaction_id
  transaction_order
  source_commit_time
  schema_version
  captured_at
}

Within one source, sourcelsn plus transaction order establishes event order. The version key must include sourceid because LSNs from different databases do not share a coordinate system. For each (sourceid, tableid, primary_key), the target stores the last applied source version. The sink acknowledges an equal or older version without changing the row. A newer version updates both the business row and applied version in one target transaction.

This makes the main at-least-once failure windows manageable:

• The event reaches the durable stream but its source offset is not recorded: recovery re-emits it, and the sink ignores the equal version.
• The target MERGE commits but the acknowledgement is lost: the batch replays and converges to the same state.
• The consumer crashes partway through a batch: committed rows replay safely and uncommitted rows are processed again.

If the destination requires multi-row transaction atomicity, enable transaction-boundary metadata and buffer by transaction_id until END, then commit the complete transaction together. Large transactions consume more memory and increase tail latency, and timeout or recovery logic must determine whether a transaction is complete. A normal analytical warehouse that needs row-level eventual consistency should not accept this complexity without a real requirement.

Step 5: Handle deletes, key changes, and schema evolution correctly

A DELETE event must carry enough key information for the curated layer to hard-delete the row, set is_deleted, or preserve history. A tombstone that follows a delete mainly supports log compaction. It cannot replace the business delete event, and middleware must not discard the delete before it reaches the target.

When a primary-key value changes, common CDC semantics emit a delete for the old key and a create for the new key. The sink must apply both or the old key remains as a ghost row. Changing the primary-key definition is riskier than changing a value. Use a read-only or write-pause window, drain the connector, update the schema, and then resume because the event-key shape can be inconsistent during the transition.

Use an explicit compatibility policy:

• Added nullable column: register a new version, let old consumers ignore the unknown field, expand the curated table before enabling writes.
• Dropped or renamed column: introduce and populate the new field, migrate every consumer, then remove the old field.
• Widened type: upgrade after target compatibility checks. Narrowed types or semantic changes quarantine incompatible events.
• Primary-key change: handle as a separate migration instead of silent automatic evolution.

An incompatible event enters quarantine and raises an alert. The main offset can advance only after the event is durably stored, replayable, and assigned a clear remediation owner. Silently skipping a bad schema creates an invisible data hole.

Step 6: Treat recovery, replay, and reconciliation as normal paths

Connector recovery requires both a persisted offset and a replication slot that still contains the corresponding history. Monitor restartlsn, confirmedflush_lsn, current WAL position, retained bytes, growth rate, and time to disk exhaustion. A destination outage should accumulate in the durable stream rather than stop connector acknowledgement long enough to retain unbounded WAL at the source.

If the slot is missing, the stored offset is behind the slot's available position, or required WAL is gone, fail closed. Stop curated publication for that source, record the last trusted position, resnapshot the affected tables, and reconcile by range. A newly created slot captures only changes after its creation and cannot prove that the earlier interval is complete.

Replay has its own replayjobid, table and primary-key scope, and source-time boundary. It reads the immutable raw layer into staging. Live and replay data use the same source-version MERGE rule so an older backfill cannot overwrite newer state. When transformation logic changes, build a new curated version or shadow table first; comparing and rolling back is safer than overwriting production immediately.

Reconciliation covers at least four layers:

1. Continuity from source commit position to connector, durable stream, and target applied position.
2. Row counts, delete counts, and checksums by table, date, and primary-key bucket.
3. Sampled primary-key comparisons between source current state and destination latest state.
4. Periodic identifiable canary transactions that verify INSERT, UPDATE, and DELETE visibility within the SLA.

Break end-to-end latency into source commit to capture, capture to stream, stream to staging, and staging to curated. Also monitor event rate per source, LSN lag, retained slot WAL, snapshot chunk progress, duplicate or stale versions, schema quarantine, target MERGE failures, reconciliation deltas, and estimated catch-up time.

Step 7: Explain the boundaries of alternatives

Polling by updated_at is simple for low-write tables with minute-level freshness and acceptable rescans. It adds query load, easily misses hard deletes, and still has to handle equal timestamps and clock precision. Triggers can write deletes into an audit table, but they add work and operational coupling to the source write path. Log-based CDC is the better default for the busy OLTP workload in this problem.

The outbox pattern solves a different problem: a service writes business state and a domain event in one database transaction, avoiding a successful database commit followed by a failed message send. It is appropriate for selected business events. It does not automatically replace generic row-level synchronization for 200 tables. Both can coexist: service integration consumes the outbox, while analytics and audit consume CDC.

Strong Sample Answer

“I would begin with the guarantees. Every committed change that still exists in WAL or the durable stream must be recoverable. A row converges by source position, replay does not alter its final state, and deletes reach the target. If the replication slot and historical position are no longer intact, publication stops and the affected data is resnapshotted; the connector cannot silently restart at the newest position.

At average load, ingress is about 20 MB/s, 1.728 billion events and 1.728 TB per day. Peak is 60 MB/s, so a 30-minute target outage creates about 108 GB of backlog. Seven days of raw retention is about 12.096 TB. The 8 TB snapshot needs at least 30.9 MB/s of effective reading to finish within 72 hours, so I would benchmark it and rate-limit each source by p99, I/O, and retained WAL.

Each PostgreSQL source gets an independent logical slot and connector. The connector takes a consistent snapshot and then resumes committed changes from the corresponding LSN. Events are partitioned by source, table, and primary key into a durable stream and copied into an immutable raw layer. Light processing validates schema and normalizes the envelope. The warehouse loads staging tables, then performs idempotent MERGE or DELETE operations by primary key and source version. LSNs are never compared across databases; transaction order supplements LSN within a transaction.

The snapshot is chunked by primary-key range, progress is persisted, and source load is dynamically limited. WAL is drained while the snapshot runs, and the target's source-version rule absorbs recovery replay at the boundary. Events contain operation, before and after values, source LSN, transaction identity and order, and schema version. The sink applies only a newer version, so connector replay, a lost target acknowledgement, and consumer crashes all converge safely.

Delete events remain intact through the curated layer; tombstones are only for compaction. A primary-key value change applies as an old-key delete plus a new-key create. Added nullable columns can evolve compatibly, while drops, renames, narrowing types, and key changes use controlled migrations. Incompatible records are quarantined and replayable rather than silently skipped.

Operationally, I would monitor segmented latency, per-source LSN lag, retained slot WAL and disk headroom, snapshot progress, schema quarantine, target failures, and reconciliation deltas. Slot loss fails closed and triggers resnapshotting of the affected scope. Finally, I would inject concurrent writes during the snapshot, duplicate delivery, deletes, key changes, connector crashes, lost sink acknowledgements, a 30-minute sink outage, slot loss, and breaking schema changes. Row counts, key-bucket checksums, sampled rows, and canary transactions would prove that no change is missing and no stale event overwrites newer state.”

Common Mistakes

• Exporting every table and enabling CDC only afterward → WAL from the export interval can disappear, leaving a snapshot-to-stream hole → Establish log retention and a consistent snapshot position before streaming from the matching point.
• Saying only “use Debezium and Kafka” → Product names do not define latency, ordering, recovery, or sink idempotency → State correctness invariants and capacity first, then map each component to them.
• Comparing LSNs from different databases as one global version → Every source has an independent log coordinate → Include source_id in the version and compare positions only inside one source sequence.
• Assuming broker exactly-once makes the warehouse exactly-once → An external MERGE can sit outside the broker transaction and replay after a lost acknowledgement → Apply changes idempotently by primary key and source version.
• Waiting indefinitely for a stalled connector → Its slot can retain WAL until source storage fills → Monitor retained bytes and time to exhaustion, isolate sink outages with the durable stream, and set stop-loss thresholds.
• Recreating a lost slot and continuing at the newest position → The new slot has no earlier history and can hide missing data → Fail closed, resnapshot, and reconcile the affected scope.
• Treating the tombstone as the only delete signal → A compaction marker cannot replace a business DELETE carrying the row key → Preserve the delete event and choose hard delete, soft delete, or history at the destination.
• Automatically accepting every schema change → Drops, narrowing types, and key changes can break consumers or event keys → Define a compatibility matrix, quarantine incompatible records, and stage breaking migrations.
• Optimizing only for the 72-hour snapshot deadline → A large scan can damage source p99, cache behavior, and replication → Benchmark the 30.9 MB/s lower bound, rate-limit dynamically, and protect the source budget.
• Comparing only total row counts → Misapplied updates, missed deletes, and offsetting errors can remain hidden → Compare counts and checksums by key bucket, sample rows, and inject canaries.

Follow-Up Questions

Follow-up 1: How do you prove that an update and delete during the snapshot cannot let an old snapshot value overwrite new state?

Use the connector's verified consistent-snapshot and log-handoff semantics instead of guessing at application timing. The sink stores a source version per row and accepts only newer versions, so a replayed READ cannot overwrite a newer UPDATE or DELETE. In testing, repeatedly update, delete, and recreate one key around its snapshot chunk, then assert final source-to-target equality and source-position continuity.

Follow-up 2: How do you estimate recovery time after a 30-minute warehouse outage?

Peak backlog is about 108 GB. Recovery time depends on recovered throughput minus continuing live ingress. If live traffic returns to the 20 MB/s average and consumers sustain 80 MB/s, the net catch-up rate is about 60 MB/s, making the theoretical drain time about 30 minutes before MERGE amplification and safety margin. If processing only equals live ingress, the backlog never shrinks.

Follow-up 3: What changes if all rows in one source transaction must become visible together?

Enable transaction-boundary metadata, assemble events by transaction ID, and commit the transaction to staging and publication only after a complete END boundary. Persist large transactions to disk, define timeout and remediation paths, and resume from durable assembly state. This raises tail latency and state cost, so first confirm that brief cross-table inconsistency is truly unacceptable for the consumer.

Follow-up 4: If the replication slot is lost but the durable stream still has seven days of data, must you run a full snapshot?

First locate the possible gap. If the durable stream provably contains every change after the last trusted LSN, replay and reconciliation can restore continuity without a full snapshot. If the slot disappeared before events reached the stream, or the gap boundary cannot be proven, resnapshot the affected tables or primary-key ranges. The decision follows demonstrable continuity, not the cost of rework.

Follow-up 5: Why not replace the entire design with an outbox?

An outbox is excellent when a service explicitly publishes domain events such as order-created or payment-completed and writes the event in the same transaction as business state. It requires application participation and contains only selected facts. This problem synchronizes inserts, updates, and deletes across 200 tables for analytics, so generic row-level CDC remains necessary. The two patterns can coexist for different consumers.