How does Debezium's initial snapshot work, and how does it hand off to streaming the transaction log without missing or duplicating data?

seniormust knowfreq 60%

answer

snapshot emits op='r' rows
record binlog pos / LSN at snapshot start
MySQL: FLUSH TABLES WITH READ LOCK + REPEATABLE READ
Postgres: exported snapshot tied to slot LSN
stream resumes from recorded position; idempotent by key
snapshot.mode: initial/never/schema_only/initial_only

basics

~20 s

On first start, Debezium reads the current contents of the captured tables (the snapshot) and emits each row as an op='r' event, recording the log position (binlog offset / LSN) at snapshot start. It then begins streaming the transaction log from that recorded position, so every change after the snapshot is captured exactly once.

solid answer

~50 s

When a connector starts with no stored offset, it performs an initial snapshot: it records the current log position (MySQL binlog coordinates or Postgres LSN), reads the existing rows of each captured table via SELECT, and emits them as op='r' events. Consistency is achieved differently per DB — classic MySQL snapshots briefly use a global read lock (or FLUSH TABLES WITH READ LOCK) to fix a consistent binlog position, then read under a REPEATABLE READ transaction; Postgres uses an exported snapshot from a repeatable-read transaction tied to the replication slot's LSN. After the snapshot completes, Debezium switches to streaming from the captured position. Because the snapshot pins an exact log position and streaming resumes from it, no committed change is lost; rows changed during the snapshot are reconciled by streaming. The connector stores its progress as offsets in the Connect offsets topic so a restart resumes streaming, not re-snapshots. snapshot.mode controls behavior (initial, never, when_needed, schema_only, etc.).

When a connector starts with no stored offset, it performs an *initial snapshot*: it records the current log position (MySQL binlog coordinates or Postgres LSN), reads the existing rows of each captured table via SELECT, and emits them as `op='r'` events. Consistency is achieved differently per DB — classic MySQL snapshots briefly use a global read lock (or `FLUSH TABLES WITH READ LOCK`) to fix a consistent binlog position, then read under a REPEATABLE READ transaction; Postgres uses an exported snapshot from a repeatable-read transaction tied to the replication slot's LSN. After the snapshot completes, Debezium switches to *streaming* from the captured position. Because the snapshot pins an exact log position and streaming resumes from it, no committed change is lost; rows changed during the snapshot are reconciled by streaming. The connector stores its progress as offsets in the Connect offsets topic so a restart resumes streaming, not re-snapshots. `snapshot.mode` controls behavior (initial, never, when_needed, schema_only, etc.).

go deeper

for a junior

Know the snapshot reads existing rows (op='r') before streaming begins.

for a middle

Explain the snapshot-then-stream handoff and that a restart resumes from offsets, not a re-snapshot.

for a senior

Detail the consistency mechanism (binlog pos/LSN pin, REPEATABLE READ, brief MySQL lock) and how loss/dup is avoided via idempotency by key.

for a principal

Weigh blocking vs incremental snapshots, signal tables, watermarking, and operational impact of locking on a busy primary.

The problem. When a CDC connector starts for the first time, the transaction log only contains recent changes (logs are pruned). The current full state of the tables is not in the retained log. So Debezium must first capture a complete picture — the initial snapshot — and then seamlessly continue from the log without gaps or duplicates.

Snapshot steps (conceptually):

Determine tables to capture (from table.include.list / schema.include.list).
Record the current log position — MySQL binlog filename+position, or Postgres LSN — before/at reading rows. This is the pivot point streaming will resume from.
Read existing rows with SELECT * FROM table and emit each as a change event with op='r' ('read') and a populated after (no before, since these aren't changes — they're existing state). The source.snapshot field marks them as snapshot rows (true/last/false).
Switch to streaming the log from the recorded position.

Consistency mechanism — MySQL: Historically Debezium acquires a global read lock (FLUSH TABLES WITH READ LOCK) just long enough to read the consistent binlog coordinates and table schemas, then reads table data inside a REPEATABLE READ transaction with a consistent snapshot, releasing the lock early. snapshot.locking.mode (minimal/extended/none) tunes how long locks are held. The REPEATABLE READ MVCC view guarantees the SELECTs see a single consistent point in time matching the recorded binlog position.

Consistency mechanism — Postgres: The replication slot establishes a point in the WAL (the slot's confirmed_flush_lsn). Debezium reads existing data in a REPEATABLE READ transaction whose snapshot is aligned with the slot's LSN (using exported snapshots). Streaming then resumes from that LSN. No table locks are needed for the standard snapshot.

Why no loss and no duplication. The recorded position is the exact boundary. Any change committed before it is reflected in the snapshot rows; any change committed after it will be replayed during streaming. A row changed during the snapshot will appear in the snapshot at its then-current value and also appear again as a streamed u/d event — Debezium's design makes downstream processing idempotent by key: the latest event per primary key wins, so re-applying is harmless. This is at-least-once delivery reconciled by key, not true dedup of the snapshot row itself.

Offsets and restarts. Debezium stores its position (binlog coords / LSN) as connector offsets in the Kafka Connect offset.storage.topic. On restart it resumes streaming from the stored offset — it does not re-run the snapshot unless told to. MySQL schema history is also persisted to the schema history topic so the connector can correctly interpret older binlog entries.

snapshot.mode values (MySQL/Postgres):

initial (default): snapshot once if no offset exists, then stream.
never / no_data: skip snapshot, stream from current log position only (you lose pre-existing rows).
when_needed: snapshot if offsets are invalid/missing.
schema_only / no_data: capture schema but not row data (start streaming fresh).
initial_only: snapshot then stop.

Incremental snapshots (DDD-3 / signal-based). Modern Debezium supports incremental snapshots via a signal table — you can snapshot (or re-snapshot) specific tables while streaming continues, in chunks, using a watermarking technique (open/close window markers in the stream) to deduplicate rows that change mid-chunk. This avoids the all-or-nothing locking blocking snapshot and lets you add tables without restarting capture.

**The problem.** When a CDC connector starts for the first time, the transaction log only contains *recent* changes (logs are pruned). The current full state of the tables is not in the retained log. So Debezium must first capture a complete picture — the **initial snapshot** — and then seamlessly continue from the log without gaps or duplicates. **Snapshot steps (conceptually):** 1. **Determine tables** to capture (from `table.include.list` / `schema.include.list`). 2. **Record the current log position** — MySQL binlog filename+position, or Postgres LSN — *before/at* reading rows. This is the pivot point streaming will resume from. 3. **Read existing rows** with `SELECT * FROM table` and emit each as a change event with **`op='r'`** ('read') and a populated `after` (no `before`, since these aren't changes — they're existing state). The `source.snapshot` field marks them as snapshot rows (`true`/`last`/`false`). 4. **Switch to streaming** the log from the recorded position. **Consistency mechanism — MySQL:** Historically Debezium acquires a **global read lock** (`FLUSH TABLES WITH READ LOCK`) just long enough to read the consistent binlog coordinates and table schemas, then reads table data inside a **REPEATABLE READ** transaction with a consistent snapshot, releasing the lock early. `snapshot.locking.mode` (minimal/extended/none) tunes how long locks are held. The REPEATABLE READ MVCC view guarantees the SELECTs see a single consistent point in time matching the recorded binlog position. **Consistency mechanism — Postgres:** The **replication slot** establishes a point in the WAL (the slot's `confirmed_flush_lsn`). Debezium reads existing data in a **REPEATABLE READ** transaction whose snapshot is aligned with the slot's LSN (using exported snapshots). Streaming then resumes from that LSN. No table locks are needed for the standard snapshot. **Why no loss and no duplication.** The recorded position is the exact boundary. Any change committed *before* it is reflected in the snapshot rows; any change committed *after* it will be replayed during streaming. A row changed *during* the snapshot will appear in the snapshot at its then-current value and also appear again as a streamed `u`/`d` event — Debezium's design makes downstream processing **idempotent by key**: the latest event per primary key wins, so re-applying is harmless. This is at-least-once delivery reconciled by key, not true dedup of the snapshot row itself. **Offsets and restarts.** Debezium stores its position (binlog coords / LSN) as **connector offsets** in the Kafka Connect `offset.storage.topic`. On restart it resumes *streaming* from the stored offset — it does **not** re-run the snapshot unless told to. MySQL schema history is also persisted to the **schema history topic** so the connector can correctly interpret older binlog entries. **`snapshot.mode` values (MySQL/Postgres):** - `initial` (default): snapshot once if no offset exists, then stream. - `never` / `no_data`: skip snapshot, stream from current log position only (you lose pre-existing rows). - `when_needed`: snapshot if offsets are invalid/missing. - `schema_only` / `no_data`: capture schema but not row data (start streaming fresh). - `initial_only`: snapshot then stop. **Incremental snapshots (DDD-3 / signal-based).** Modern Debezium supports **incremental snapshots** via a *signal table* — you can snapshot (or re-snapshot) specific tables *while streaming continues*, in chunks, using a watermarking technique (open/close window markers in the stream) to deduplicate rows that change mid-chunk. This avoids the all-or-nothing locking blocking snapshot and lets you add tables without restarting capture.

How does Debezium avoid taking a long table lock during a MySQL snapshot?
It holds the global read lock only briefly to capture consistent binlog coordinates and schema, then reads row data inside a REPEATABLE READ transaction (consistent MVCC snapshot) and releases the lock early. snapshot.locking.mode=minimal does this; none skips locking entirely.
What are incremental snapshots and what problem do they solve?
Signal-table-driven snapshots that run in chunks while streaming continues, using watermark markers in the change stream to dedupe rows changed mid-chunk. They let you snapshot/re-snapshot specific tables without stopping capture or holding a long lock.
If a row is updated while the blocking snapshot is reading it, is data lost?
No. The snapshot captures the row at the consistent point; the update is also replayed during streaming from the recorded position. Because consumers are idempotent by primary key, the latest event wins — no loss, just a harmless re-application.

saying these in an interview costs you the question

Saying snapshot rows use op='c' (they use op='r').
Claiming Debezium re-snapshots on every restart (it resumes from stored offsets).
Asserting MySQL holds a full table lock for the entire snapshot duration.
Saying the snapshot and streaming can lose changes committed during the snapshot.
Believing classic initial snapshots can run concurrently with streaming (only incremental snapshots can).

How does Debezium's initial snapshot work, and how does it hand off to streaming the transaction log without missing or duplicating data?

§ answer

basics

solid answer

§ go deeper

▾level notes4 levels

▾deep dive~3 min

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics3 topics

answer

go deeper

context