The Staff Engineer Change Data Capture Interview: Snapshots, Log Positions, and Safe Consumers

What happens if a row changes while you are copying a table, then your consumer crashes after writing the change but before saving its checkpoint? A change data capture design has to answer both questions without silently losing the row or repeating its effects.

PostgreSQL's logical decoding documentation makes the recovery problem concrete: a replication slot can return to an earlier position after a crash and resend recent changes. Consumers are responsible for making that replay safe. Connecting a database to a stream is only the beginning of the explanation.

For senior and staff engineer interview preparation, follow one record through the whole pipeline. This guide uses an illustrative order-status system: PostgreSQL is authoritative, a connector publishes changes to a durable transport, and a consumer maintains a separate serving database. The exercises, workload assumptions, and review questions are practice tools, not a company's interview rubric.

1. Prove the snapshot-to-stream boundary

Start by asking what the destination needs. Does it need today's current state, every historical transition, or transactionally consistent views across several tables? For our exercise, assume it needs a complete current-state projection followed by ongoing changes. A snapshot alone cannot provide the later updates; a stream beginning today cannot recreate rows that already existed.

Make the cut explicit

PostgreSQL can export a snapshot when a logical replication slot is created through its streaming replication interface. That snapshot represents the database state after which changes are included in the slot's stream. The exported-snapshot contract lets a client copy that state and subsequently apply the stream without leaving a gap.

Explain the handoff as a consistency argument: establish the coordinated snapshot and log boundary, retain the needed log, copy the snapshot's rows, then apply changes from the matching boundary. “Copy the tables, then ask for the current log position” leaves an interval you have not accounted for. In an illustrative race, an order changes after its row is copied but before you select that later position. Neither the copied value nor the chosen stream contains the update.

The Debezium PostgreSQL connector documents a coordinated initial snapshot: it opens a transaction, reads a log position, scans rows, records completion, and streams from that position. An interrupted initial snapshot restarts. Choose the documented isolation and snapshot mode deliberately; do not assume an arbitrary table scan provides the same guarantee.

In your answer, distinguish a snapshot's completion from readiness to serve. For this exercise, load a new projection generation out of sight, drain its changes to a declared boundary, compare selected records against the source, and only then switch readers. Record the generation and checkpoint together so recovery cannot accidentally combine rows from one bootstrap with offsets from another.

2. Separate progress from log retention

A log sequence number, or LSN, is a location in PostgreSQL's write-ahead log. It is useful only when you say whose progress it describes. The pg_replication_slots reference distinguishes restart_lsn, the oldest WAL that might still be needed, from confirmed_flush_lsn, the position through which the slot's consumer has confirmed receipt.

Draw three separate markers in the exercise:

• Source retention: the earliest log data the slot still requires.
• Connector progress: the source position durably represented in the transport and connector recovery state.
• Sink progress: the transport records whose effects are durably applied to the serving database.

A healthy connector does not establish that the serving database is caught up. A broker consumer offset is not a PostgreSQL LSN. Trace the recovery store for each component and explain the acknowledgment that allows the preceding component to advance. In the proposed architecture, source acknowledgment depends on durable transport publication; downstream application has its own checkpoint.

Budget the outage, then budget recovery

PostgreSQL's replication settings document both sides of the retention tradeoff. The default max_slot_wal_keep_size=-1 allows unlimited retained WAL. A configured cap can allow needed WAL to be removed when a slot falls too far behind. An idle-slot timeout can also invalidate inactive slots at a checkpoint.

For arithmetic practice only, assume WAL generation of 20 MiB per second and a 30-minute connector outage. The database generates approximately 35.2 GiB during that interval: 20 × 1,800 ÷ 1,024. This is an illustrative workload calculation, not a benchmark or a complete disk budget. Add room for the existing backlog, bursts, and the time needed to catch up while new writes continue.

Monitor retained bytes, slot activity, and source-to-sink delay separately. PostgreSQL exposes wal_status and safe_wal_size; a lost slot is unusable, and safe headroom can be null. Those slot-state fields support an actionable recovery decision. If the required history is gone, explain a new coordinated bootstrap or another verified recovery source. Repeatedly restarting the connector cannot recreate missing history.

3. Preserve transactions and make replay harmless

Define the ordering guarantee before adding workers. PostgreSQL's output-plugin callback documentation describes concurrent transactions decoded in commit order, with transaction boundaries surrounding their changes. It also describes separate support for streaming large transactions while they are still in progress. Receiving a change and knowing that its transaction committed are different events.

The logical replication protocol distinguishes a commit LSN from the transaction's end LSN and includes stream-commit and stream-abort messages. Preserve the meaning of those fields through your connector. Avoid declaring “the LSN” a unique row-event identifier without examining the actual envelope.

For our order-status exercise, suppose one source transaction changes an order and its line items. If readers require both changes together, propose buffering and applying the complete transaction atomically. Debezium can expose transaction boundaries, event counts, and each event's transaction order when transaction metadata is enabled. State how filtering and routing affect your completeness check. If the product tolerates independent updates, explain that narrower contract instead.

Walk through the crash window

In this illustrative sink design, write the projection changes and the consumed checkpoint in the same local database transaction. A crash before commit preserves the old checkpoint and old projection. A crash after commit preserves both. On restart, seek from that stored checkpoint and fence workers that no longer own the partition. If your destination cannot support that transaction, describe its deduplication or conditional-write mechanism explicitly.

Kafka's delivery-semantics documentation explains why this boundary matters: writing output before saving progress permits replay; saving progress first permits loss. Kafka transactions can coordinate Kafka output with consumed offsets. An external destination requires cooperation with that destination; a transactional broker alone does not make a separate database update atomic.

Also test repeated effects, not just repeated messages. In our exercise, replacing an order's status with the same value may be harmless; sending its shipment notification again is not. Give notifications a durable idempotency identity and delivery state. Separate transport-offset recovery from source-event deduplication, because a connector can republish the same source change as a new transport record.

4. Handle deletes, backfills, and schema changes

A useful follow-up is to delete the example order. Which key reaches the consumer? PostgreSQL's REPLICA IDENTITY settings determine the old-row information recorded for updates and deletes. DEFAULT uses primary-key columns; without a primary key it behaves as NOTHING. FULL records old values for all columns. Choose identity based on what your projection needs to remove or recompute.

Then examine the entire transform chain. Debezium's event-flattening transformation can drop delete records, retain tombstones, or rewrite a delete into a record marked __deleted. A tombstone is not an ordinary row with missing fields. Verify the configured representation against your sink's deletion contract before claiming deleted source rows disappear downstream.

The PostgreSQL wire format also distinguishes SQL null from an unchanged TOAST value whose contents are not transmitted, as the tuple-data reference shows. Treating an unavailable value as null can erase a field in your proposed projection. Decide whether to preserve an existing value, require a fuller source record, or rebuild that record through a coordinated path.

Do not let a backfill resurrect deleted data

In an illustrative failure, a backfill reads an order, the live stream deletes it, and a delayed backfill write restores it. Prevent that race with a coordinated snapshot merge, a separate rebuild generation, or a version contract that includes deletion state. Specify how long deletion metadata remains valid relative to your permitted replay window.

Debezium's engineering explanation of incremental snapshots describes one concrete merge algorithm: read primary-key chunks between window markers, buffer snapshot rows, and discard a buffered row when a matching live change arrives during the window. This is a historical explanation of the mechanism, not a recommendation to copy its old configuration defaults. A chunked snapshot should not be described as one database-wide point-in-time image.

Give schema changes their own rollout

PostgreSQL's built-in logical replication restrictions state that schemas and DDL are not replicated. A subscriber can error when incoming data no longer fits its schema; additive changes can often be applied there first. Debezium's PostgreSQL connector documentation also states that it does not emit DDL change events.

For the exercise, add a nullable field through a staged rollout: prepare consumers and destination storage, deploy the source change, backfill if required, and tighten constraints later. Define a separate plan for renames and type changes. Quarantine an incompatible record with its source coordinates and alert the owner; decide explicitly whether the affected projection must pause rather than silently skipping data.

5. Rehearse a recovery decision, not just a diagram

Use the following original practice sequence to make the design reviewable. Start with a snapshot boundary and one order. Update it during the copy. Crash the sink immediately before and immediately after its local transaction commits. Delete it while a backfill is running. Finally, exceed the source's permitted retention window and ask how the destination is rebuilt.

For each step, record four things: the authoritative value, the destination value, the durable checkpoint, and the next safe action. Describe what a reader can see during recovery. If the projection is stale, propose a visible freshness indicator or a bounded fallback to the source rather than assuming the recovered process is already current.

Use this self-review checklist as a practice aid:

• Can you identify the snapshot's matching stream boundary?
• Can you distinguish source WAL retention from sink application progress?
• Can you explain duplicates and every checkpoint crash window?
• Can you demonstrate that deletes survive transforms and backfills?
• Can you name the recovery path when log history is unavailable?

Turn any weak answer into a focused follow-up session. Draw the state transition, identify the durable evidence that justifies it, and propose a fault injection that would disprove your claim. For preparation with Interview Copilot, bring these original scenarios into your technical practice and refine the explanation you would give aloud.

The strongest CDC explanation follows ownership of a change from the snapshot to its final durable effect. As the design grows, return to that same record. Every new queue, worker, transformation, and rebuild needs a precise answer to one question: after a failure, what evidence tells you whether to replay, wait, or start again?