Why does a log-based CDC pipeline take an initial snapshot before it starts streaming the log?
answer
- the log holds deltas, not the table
- old rows were never written recently
- log segments get recycled after hours or days
- you need a starting state to apply deltas to
- snapshot rows are emitted as reads, not inserts
basics
~10 sA transaction log records only changes, and only recent ones. Rows untouched since the log's retention window began appear nowhere in it, so the pipeline must read the table once to establish a baseline.
solid answer
~50 sLog-based CDC reads the database's own transaction log, which is a record of **changes**, not of state. A customer row inserted two years ago and never updated since is mentioned in no log segment still on disk — the log is trimmed after hours or days so it does not fill the volume. A consumer that simply started from the current log position would build a table containing only the rows that happened to change after it connected. The initial snapshot closes that hole: the connector reads the current contents of each captured table and emits one event per existing row, giving the sink a complete starting state. Streaming then applies deltas on top of it. The snapshot is not optional convenience — it is the only source for rows older than the retention window.
code
text · 12 lines-- table orders (4 rows, one written today)
id=1 status=SHIPPED updated 2019-04-02
id=2 status=SHIPPED updated 2020-11-19
id=3 status=SHIPPED updated 2021-08-01
id=4 status=PENDING updated today
-- log segments still on disk (retention: 3 days)
UPDATE orders id=4 status=NEW -> PENDING
-- a consumer starting from "now" reconstructs:
id=4 status=PENDING
-- rows 1..3 exist in the source and nowhere in the streamgo deeper
Be ready to state plainly that a transaction log records changes, not the current contents of a table, and that it is deleted after a short window. That one sentence is what the screening question is testing.
Explain the retention window as the mechanism: rows unmodified since the oldest surviving log segment cannot be recovered from the stream at all, so a direct read of the table is the only baseline available.
Show that you know the baseline must be pinned to a specific log position, and that snapshot events are semantically reads rather than inserts — a distinction that breaks downstream consumers with insert-triggered side effects.
Frame it as where the baseline comes from, not whether one is needed: a source scan, an existing warehouse extract reconciled by key, or a deliberate future-only capture. Each moves cost between the source database and the reconciliation step.
## The log is a change record, not a copy of the table Log-based change data capture works by reading the durable write-ahead record a relational database already keeps for crash recovery and replication. Every committed insert, update and delete is appended to it in commit order. A CDC connector attaches to that stream, decodes each entry into a change event, and forwards it to a sink. The crucial property is what the log does *not* contain: rows that have not been written recently. A table of 40 million rows of which 10,000 changed today contributes roughly 10,000 entries to today's log. The other 39,990,000 rows are invisible to a log reader. They live only in the table's data pages. ## Retention is the hard limit Even in principle you could not replay a table's whole history from the log, because no production database keeps its log forever. Logs are recycled once the space they occupy is no longer needed for recovery and no replica still needs them — commonly hours to a few days. The retention window is a disk-capacity decision, not a data-modelling one. So the log gives you an unbounded-in-the-future but strictly bounded-in-the-past view. Anything before the window's start is unreachable through the log at any price. ## What the snapshot produces The initial snapshot (also called a bootstrap or seed load) issues ordinary reads against the captured tables and converts each existing row into a change event — typically marked with a distinct operation code such as `r` for *read*, so a consumer can tell a snapshot row from a real insert. Downstream, the effect is the same: upsert the row by primary key. When the snapshot completes, the sink holds a full replica of the source as of some instant, and every subsequent log event moves it forward. A snapshot event has a genuine semantic difference from a live insert. It carries no *before* image, no transaction identifier, and no meaningful commit timestamp — it says "this row exists", not "this row was just created". Consumers that trigger business logic on inserts must not treat the initial load as 40 million new customers signing up. ## The snapshot alone is not enough either A snapshot without a log position is as useless as a log without a snapshot. The connector must know exactly which log position corresponds to the state it just read, so that streaming can resume from there. Read the snapshot first and *then* look up the current log position and you lose every change committed while the scan ran. That handover is the part interviews probe hardest. ## When you deliberately skip the snapshot Three cases justify starting with no row-level snapshot at all: - **The sink only cares about the future.** An audit stream, a cache invalidator, or a search-index updater fed from a nightly full reload does not need history; capturing schema only and starting at the current log position is correct and much cheaper. - **History already exists somewhere else.** If a warehouse already holds a full extract of the table taken at a known point, you can start streaming from a log position at or before that extract and reconcile by primary key, avoiding a re-read of the source. - **The source cannot take the load right now.** Postponing the snapshot and backfilling later is a valid staging plan, provided the log position is pinned in the meantime. All three trade a scan of the source for a reconciliation problem somewhere else. They are choices about *where* the baseline comes from, not evidence that a baseline is unnecessary. ## The mental model to keep Think of the sink's table as an accumulator. The log supplies increments. The snapshot supplies the constant of integration. Without it you get a table whose contents are correct only for the small, arbitrary subset of keys that happened to be written since you connected — which is far more dangerous than an obviously empty table, because it looks plausible and passes row-level spot checks on hot keys.
- Why do CDC connectors mark snapshot rows with a different operation code from real inserts?A snapshot row means "this row exists as of the baseline", not "this row was just created". It carries no before image and no meaningful commit timestamp. Sinks that upsert by key treat both identically, but any consumer with insert-triggered side effects — welcome emails, downstream fan-out, counters — must ignore the snapshot pass or it will fire once per pre-existing row.
- If the source table is tiny, could you just re-snapshot periodically instead of streaming at all?You could, and for small, slowly-changing reference tables a periodic full reload is often simpler and perfectly adequate. What you lose is latency, the ability to see deletes as events rather than as absences, and any intermediate states a row passed through between reloads. The tradeoff turns on table size and whether anyone downstream needs the intermediate versions.
- Does the snapshot have to read every captured table before streaming can begin?Not necessarily. Connectors commonly snapshot table by table and can be configured to capture a subset, and incremental snapshot designs let streaming run concurrently with the backfill. What must hold is that for each table, the sink eventually has both a baseline and every log event from that baseline's position onward, with no gap.
The transaction log is a bank statement, not a balance. You can add up every transaction on the statement and still not know the account balance unless someone tells you what it was when the statement started.
saying these in an interview costs you the question
- Claims the transaction log contains the table's full history forever
- Thinks a snapshot is optional because the log will catch up
- Treats snapshot events as new inserts and fires downstream side effects
- Says retention length is a modelling choice rather than a disk-space one
- Believes starting from the current log position eventually converges to the full table