Snapshot and Stream: the Bootstrap Problem
CDC starts from now. Everything that existed before now has to be read separately and stitched to the stream without a gap and without a duplicate that a later ordering guard cannot resolve.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
The connector starts today against a table with four years of rows in it. Where do the four years come from, and how do you join them to the live stream without losing a change or double-counting one?
The first consumer of any new CDC pipeline, who will ask on day one whether the table is complete — and the analyst six months later who asks why the trend has a discontinuity on the date the connector was enabled (Trusting Data).
Two grains meet here and that is the whole difficulty. A snapshot row is one entity as it was at the snapshot's read point. A stream event is one committed change. Stitching them means deciding how a state row and a change event coexist in the same table, and the answer has to be written down before either is loaded (Event vs Snapshot Modeling).
Take a SELECT * dump of the table, load it, then start the connector. It is the obvious sequence, it needs no coordination, and it is what almost everyone does first — reasonably, because the alternative requires understanding a boundary that has not bitten them yet.
The dump takes forty minutes. The connector is started afterwards, from the current log position, so every change committed during those forty minutes exists in neither the dump nor the stream. There is a gap, it is silent, and nothing downstream will ever reveal it (Missing Rows).
- The dump takes forty minutes. The connector is started afterwards, from the current log position, so every change committed during those forty minutes exists in neither the dump nor the stream. There is a gap, it is silent, and nothing downstream will ever reveal it (Missing Rows).
- The order is reversed to avoid the gap — connector first, then dump — and now every row changed during the dump appears both as a snapshot row and as a stream event, with no way to tell which is newer unless the snapshot rows carry a position (Duplicate Rows).
- The dump is read without a consistent view, so a long scan sees some rows before a transaction and others after it, producing a "snapshot" that corresponds to no point in time the database ever had (Isolation Levels).
- The snapshot is loaded with the load timestamp as its event time, so four years of history all appear to have happened on the bootstrap date and every time-series built on it is wrong (Event Time).
- The dump holds a long-running read transaction on a busy table for its whole duration, blocking maintenance and inflating the very log the connector needs (Workload Isolation).
- A re-snapshot after an incident republishes the entire table onto the live topic, rewinding every key that changed since and burying live traffic behind millions of bootstrap events (Backpressure).
What is actually happening
- The stream has a start position. Everything committed after it is delivered; everything before it is not, because the connector was not there. The bootstrap problem is exactly the problem of covering the interval before that position (Change Data Capture).
- A correct stitch requires the snapshot and the stream to meet at a known position. Either the snapshot is taken at a position and the stream starts from that same position, or the two overlap and the overlap is resolved deterministically. There is no third arrangement that is safe.
- The clean version is a consistent snapshot at a position: open a repeatable-read transaction, record the current log position, read the whole table inside that transaction, and start the stream from the recorded position. The snapshot is a coherent state of the database at that position and the stream continues from exactly there (MVCC: Multi-Version Concurrency Control, Isolation Levels).
- The pragmatic version is overlap and reconcile: start the stream first, then snapshot, and mark every snapshot row with the position at which it was read. The ordering guard then resolves the overlap automatically — a stream event with a higher position wins, a snapshot row with a higher position wins, and duplicates collapse (Upserts and Merges).
- The incremental version snapshots the table in chunks by primary key, interleaving chunks with live stream consumption so that no single long transaction is held. Each chunk is compared against changes seen during the same window, and rows superseded by a stream event are dropped from the chunk (Incremental Processing).
- Snapshot events must be marked as snapshot, because a consumer needs to distinguish "this row existed" from "this row changed" — a snapshot is not evidence that anything happened at that moment (What a CDC Event Contains). Deletes are the matching asymmetry: a snapshot can only see rows that exist, so a re-snapshot after an incident does not remove downstream rows for keys the source has since deleted unless the load is a full replace rather than a merge.
The boundary, and the three ways to get it wrong
Lay the two sources of data on one axis and the failure becomes obvious. The snapshot covers everything that existed at its read point; the stream covers everything committed after its start position. If those two points are not the same point, there is either an interval covered by neither or an interval covered by both.
Covered by both is a nuisance that a position guard solves automatically. Covered by neither is a permanent hole. The asymmetry is the whole reason the recommended order is stream-first: overlap is cheap and gaps are unrecoverable, so when in doubt, arrange to overlap (What a CDC Event Contains).
The table below labels each change by when it committed relative to the two boundaries, and says where it ends up. Read the landsIn column for the four rows in the middle: those are the events the design decision is actually about, and the difference between a correct pipeline and a quietly incomplete one is entirely in that column.
| Event | Happened | Arrived | Lands in |
|---|---|---|---|
| c1 order 88101 created | 08:15 | 10:22 | Snapshot only Committed long before the connector existed. The snapshot is the only path by which it enters the platform, and it arrives as current state, not as a creation event. |
| c2 order 88101 paid | 09:04 | 10:22 | Snapshot only, as final state The intermediate value is gone. A time-in-status metric cannot be computed for this order and no connector setting recovers it. |
| c3 order 88190 updated | 10:03 | 10:03 | Stream, and also the snapshot row read at 10:22 The overlap case. Two representations of one key; the position guard picks the higher position, which is the stream event. |
| c4 order 88191 updated | 10:11 | 10:11 | Stream, snapshot already past this key Chunked snapshotting had already read this key range at 10:08, so the stream event supersedes it. Correct under a guard, wrong under last-write-by-arrival. |
| c5 order 88192 deleted | 10:19 | 10:19 | Stream only The snapshot cannot represent a deletion. Without the stream event this key would sit downstream forever. |
| c6 order 88193 created | 10:52 | 10:52 | Stream only After the boundary. Ordinary live traffic from here on. |
| g1 order 88250 updated | 09:47 | never | Nothing — the gap, if the stream had started at 10:40 This is the failure the whole lesson exists to prevent: committed after the snapshot read its key range and before a stream that started too late. Silent, permanent, invisible to every count. |
Times are clock labels on a teaching timeline, not measurements. The row to study is the last one — it only exists in the arrangement where the snapshot runs first and the stream starts afterwards, which is the arrangement most teams choose by default.
Three stitches and what each one buys
There are three arrangements that are actually correct and one that is common. The common one — dump, then start the connector — is the one absent from this list, because it is not a strategy, it is a gap.
The choice between the three is decided by what the source will tolerate. If the database team will accept a long repeatable-read transaction, the consistent snapshot at a position is strictly the best answer: no gap, no overlap, nothing to resolve. On a busy production table they will not accept it, and the choice moves to the other two.
Note that the second and third options both depend on the position guard already being in place at the sink. That is not an extra cost attributable to bootstrapping — it is a property the pipeline needs anyway for restarts and rebalances, and bootstrapping simply makes it non-negotiable (CDC Ordering and Transaction Boundaries).
What will the source tolerate, and what does the sink already guarantee?
when The source supports snapshot isolation with a readable log position, the table reads within an acceptable transaction duration, and the database team accepts one long read.
cost A long-running read transaction against production, holding a consistent view and inflating log retention for its duration. Buys a stitch with neither gap nor overlap and no reconciliation to write (Isolation Levels).
when You cannot hold a long consistent read, and the sink is a keyed upsert guarded by log position.
cost Duplicate work for every key changed during the snapshot, and a hard dependency on the guard being correct in every consumer. Buys a gap-free bootstrap with ordinary tooling (Upserts and Merges).
when The table is too large for any single read, or the source cannot hold a long transaction, and the pipeline must stay current throughout.
cost Per-chunk consistency reasoning, a stable chunking key, and coordination between chunk reads and stream events. Buys a gentle load profile and a table that is live for hot keys while history fills in (Incremental Processing).
when Live consumers cannot absorb bootstrap volume, or a re-snapshot must not disturb a running pipeline.
cost A second path to build and keep in step, plus storage for two copies during the swap. Buys isolation of the rebuild from every consumer (Atomic Publish).
The stitch as a pipeline, stage by stage
Written as stages, the bootstrap stops being a script and becomes something with guarantees you can point at. Each stage below promises something specific, and the promise of the whole is exactly the weakest one in the list.
The stage people skip is the last. Validation before publish is what turns "we think the load worked" into evidence, and a bootstrap is the single highest-risk load a pipeline ever performs — one chance, against a source that will have moved on by the time anyone checks (Validating a Backfill Before You Publish).
The failsBy column is again the operationally useful one. Each stage has a characteristic failure and each failure has a distinct signature downstream: a gap looks like missing keys, an unresolved overlap looks like duplicate rows, a mis-stamped snapshot looks like one enormous day in a time series.
- 1Start the stream
Registers the connector position and begins buffering or publishing live changes before any snapshot read begins.
guarantees Every change committed from this position onward is captured at least once, ordered by log position.
fails by Being started after the snapshot instead of before it, which creates a silent gap exactly as wide as the snapshot took.
- 2Read the snapshot
Reads existing rows — in one consistent transaction, or in chunks by primary key — stamping each row with a snapshot marker and the position at which it was read.
guarantees Every row that exists at read time is emitted at least once, marked as snapshot rather than as change.
fails by Reading without a consistent view, producing a state the source never held; or omitting the position, leaving the overlap unresolvable.
- 3Publish to the same keyed topic
Sends snapshot rows and stream events to the same key-partitioned topic so both are subject to the same ordering.
guarantees Per-key ordering across snapshot and stream, which is what makes the guard sufficient.
fails by Publishing snapshot and stream to different destinations, so the guard sees two sequences and can order neither against the other (Event Keys and Partition Assignment).
- 4Apply with the position guard
Upserts on the source primary key, applying an event only when its position exceeds the position already recorded for that key; deletes act on the operation.
guarantees Effectively-once *state* at this sink — the assumption that buys it is that the applied position is stored with the row and compared numerically (Exactly-Once: Input Consumption, State Update, Output Write).
fails by An append-only sink, or a guard comparing positions as strings, which orders hex positions wrongly and drops real changes.
- 5Catch up
Drains stream events accumulated during the snapshot until the applied position approaches the source's current position.
guarantees That the table converges to a single coherent position across all keys.
fails by Being declared complete on aggregate lag while a single cold partition is still far behind (The Backlog Arithmetic: Four Levers and a Drain Time).
- 6Validate, then publish
Reconciles key sets and a summed measure against the source for a closed period spanning the boundary, then exposes the table to consumers.
guarantees Only what the reconciliation asserts. It is the only stage that produces evidence rather than a promise.
fails by Being skipped, or run against an open period, so the boundary interval — the one at risk — is exactly the one not checked (Reconciliation).
The guarantee of the whole bootstrap is the weakest line in the column. In practice that is almost always the last one, because validation is the stage under time pressure when a pipeline is being stood up.
How to build it
Most important first.
- Prefer a consistent snapshot at a recorded position where the source supports it. It is the only approach with neither a gap nor an overlap, and everything downstream is simpler for it.
- Where a long transaction is unacceptable, prefer incremental chunked snapshotting with the stream already running. Overlap is a solved problem given a position guard; a gap is not a problem at all, it is a permanent loss (CDC Ordering and Transaction Boundaries).
- Stamp every snapshot row with the position it was read at and with an explicit snapshot marker. Without the position the overlap cannot be resolved; without the marker consumers cannot tell bootstrap from change (What a CDC Event Contains).
- Preserve the source's own timestamps as event time and record the load time separately. The bootstrap date belongs in metadata, never in the business timeline (Event Time).
- Send the snapshot to the same keyed topic as the stream so that ordering and compaction apply uniformly — or to a separate bootstrap location if a re-snapshot must not disturb live consumers. Decide which, and write down which, before the first re-snapshot happens under pressure.
- Rehearse a re-snapshot before you need one — it is the recovery path for the module's worst failure and is invariably attempted for the first time during an incident (CDC Failure Modes and the Retention Deadline) — and validate every stitch before publishing, with a key-set and measure reconciliation against the source for a closed period spanning the boundary (Validating a Backfill Before You Publish).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- A snapshot taken at a recorded position, plus a stream from that position, guarantees completeness with no gap and no duplication — the strongest bootstrap guarantee available, and it requires the source to support both a consistent read and a position read in one transaction.
- Overlap-and-reconcile guarantees completeness with duplication, resolved to correct state only if every write is an idempotent upsert guarded by position. Without the guard it guarantees nothing.
- Snapshot-then-stream with no overlap guarantees nothing about the interval between them, and that interval is silent. This is the arrangement to recognise and refuse.
- A snapshot guarantees the existence of rows, never the absence of deleted ones. Re-snapshotting into a merge leaves downstream rows for keys the source deleted before the snapshot ran.
- After the stitch, the ordinary CDC guarantees resume: at-least-once delivery of every committed change, ordered by source log position, with no transactional atomicity and no deduplication downstream (CDC Ordering and Transaction Boundaries).
- Nothing guarantees the snapshot is internally consistent unless it was read in a single transaction at one isolation level. A chunked snapshot is consistent per chunk and reconciled across chunks by the stream, which is a different and weaker property (Isolation Levels).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- Reconcile key sets across the boundary: for a closed period spanning the stitch, every key present in the source must be present downstream, and vice versa. This is the check that finds a gap, and it is the only one that does (Reconciliation).
- Assert that every key has exactly one row in the current-state model and that its applied position is at or after the snapshot position. A key still sitting at the snapshot position long after bootstrap is either genuinely unchanged or evidence of a stream that never covered it.
- Assert a distribution check on the boundary date: a bootstrap that stamped snapshot rows with load time produces a single enormous day in any time series, which a volume check catches immediately (Volume Anomalies).
- The key-set check misses entities created and deleted entirely inside the gap, which leave no trace on either side. Nothing can find those after the fact, which is why the gap must be prevented rather than detected.
- During bootstrap the table has no meaningful freshness at all. It is neither complete nor current, and publishing it to consumers before the stitch is validated is how a platform loses trust on day one (The Freshness SLO).
- The honest freshness statement during bootstrap is positional: "snapshot complete to key K, streaming from position P". A wall-clock claim is not available and inventing one misleads.
- A chunked snapshot lets the live stream stay fresh while history loads, which is its main advantage over a single long read — the table becomes current for recently-changed keys long before it is complete.
- A re-snapshot on a live pipeline degrades freshness for every consumer of that topic, because bootstrap events compete with live traffic for the same partitions and consumers (The Backlog Arithmetic: Four Levers and a Drain Time).
- A schema change between the snapshot and the catch-up produces two shapes in one load. The snapshot columns are the ones that existed at read time and the stream may carry different ones (CDC and Schema Drift).
- Adding a table to an existing connector is a bootstrap in miniature and has exactly the same gap risk, usually with less care taken because the pipeline already exists (Schema Evolution).
- A re-snapshot after a connector upgrade may produce a differently-shaped payload than the original bootstrap, so raw contains two envelope versions for the same table and every model reading across the boundary must tolerate both (Backward Compatibility).
- Changing the primary key of a captured table invalidates the whole stitch: the guard is keyed on the old identity and the snapshot and stream no longer agree about what an entity is (Surrogate Keys).
- The re-snapshot is the recovery path for the module. When a connector's position falls outside the source's retained log, no replay exists and a fresh snapshot plus a stream from the current position is the only way back (CDC Failure Modes and the Retention Deadline).
- A re-snapshot into a keyed, position-guarded sink is safe by construction: old snapshot rows lose to newer stream events, and keys unchanged since are simply rewritten with the same values.
- A re-snapshot into an append-only sink duplicates the entire table. This is the difference between a routine recovery and a two-day incident, and it is decided by a design choice made months earlier (Idempotent Data Pipelines).
- Deleted keys survive a re-snapshot-into-merge. If the source may have deleted rows during the outage, the recovery must be a full replace of the key set, or a reconciliation that removes downstream keys the source no longer has (Deletion Requests).
- Publish the re-snapshot to a side location and swap atomically where consumers cannot tolerate a rebuild in place (Atomic Publish).
What can go wrong
- Snapshot then stream with no overlap: a silent gap exactly as wide as the snapshot took to run.
- A snapshot read without a consistent view, producing a state the database never held as a whole.
- Snapshot rows stamped with load time instead of source time, collapsing years of history onto one date.
- A re-snapshot on the live topic, burying current traffic and rewinding every key that changed since (CDC Ordering and Transaction Boundaries).
- A long snapshot transaction holding the log open, so bootstrapping the pipeline inflates the very retention the connector depends on (CDC Failure Modes and the Retention Deadline).
- The mitigation fails too: chunked snapshotting that chunks by an unstable key range, so rows that move between chunks during the run are read twice or not at all.
- "CDC gives us history." It gives changes from the moment it started. History before that is a snapshot, and a snapshot is current state, not history — the intermediate values are gone and no connector recovers them (Slowly Changing Dimensions).
- "We can snapshot afterwards if we need to." You can snapshot current state afterwards. Anything that happened during the gap is unrecoverable.
- "Duplicates from the overlap will sort themselves out." Only if the sink is keyed and position-guarded. In an append-only sink they are permanent and indistinguishable from real changes.
- "The snapshot is consistent because it was one query." One query without an explicit snapshot isolation level can still see different rows at different points of its scan (Isolation Levels).
- "Re-snapshotting is a safe reset." It is safe for keys that still exist. Keys deleted since the last good state stay downstream forever unless the load replaces the key set (Missing Rows).
- A snapshot exports the entire table at once, including every historical row and every column, which is a far larger single disclosure than the change stream that follows it (PII in Pipelines).
- A re-snapshot re-imports rows that were previously removed downstream in response to a deletion request, quietly undoing the deletion unless the request is reapplied after every bootstrap (Deletion Requests).
- Column filtering must be configured before the snapshot, not after. The bootstrap is the moment an excluded column would enter the platform, and it enters once and stays (Data Minimization).
Operating it
- Snapshot progress as chunks or key ranges completed against the total, published as a metric rather than read from a log line. It is the number every consumer asks for during bootstrap (Pipeline Metrics).
- Count of snapshot-flagged events versus streamed events per table, over time. A non-zero snapshot count outside a planned bootstrap is an unannounced re-snapshot and explains a duplicate incident before anyone opens a query (Volume Anomalies).
- Source-side log retention during the snapshot, because a long bootstrap and a held position are the two conditions that together fill a disk (Saturation: The Reading Utilization Cannot Give You).
- The lowest applied position across keys, which tells you whether the stitch has actually taken over from the snapshot everywhere or only in the hot part of the key space.
- At 10x table size a single-transaction snapshot stops being viable — the read outlives the source's tolerance for a long transaction — and chunking becomes mandatory rather than an optimisation.
- At 100x, bootstrap becomes a scheduled project with its own capacity plan, and "just re-snapshot" stops being an available incident response. That changes how seriously retention must be monitored, because the recovery path is no longer cheap (Capacity Planning: Traffic to Machines).
- Table count multiplies bootstrap coordination: fifty tables mean fifty stitches, each with its own boundary, and any join across two of them is inconsistent until both have completed (CDC Ordering and Transaction Boundaries).
- Consumer count makes a live re-snapshot more disruptive, because every consumer of the topic absorbs the bootstrap volume whether or not it needed the rebuild (Consumer Groups and the Parallelism Ceiling).
- A snapshot is a full read of the table — the one time CDC pays polling's cost shape, and it pays it against a production source (Scan Cost).
- Chunked snapshotting spreads that cost over time and adds coordination work per chunk. Same total bytes, gentler shape, more moving parts.
- A re-snapshot costs the full read again plus broker and consumer capacity for the entire table's worth of events, competing with live traffic. Budgeting for it is part of choosing CDC at all (Compute Waste).
- Retaining raw bootstrap events costs storage proportional to table size rather than change volume, which is the one place a CDC raw layer grows like a snapshot pipeline's (Storage Lifecycle).
- A consistent snapshot at a position buys a perfect stitch and costs a long-running read transaction against production — which on a busy source is exactly what the database team will refuse.
- Chunked incremental snapshotting buys a gentle load profile and a live stream throughout, and costs implementation complexity plus a per-chunk consistency argument you have to be able to make.
- Overlap-and-reconcile buys simplicity and costs a hard dependency on the position guard being correct everywhere. It is the right default precisely because that guard is needed anyway (What a CDC Event Contains).
- Snapshotting to a side location protects live consumers and costs a second path to build, test and keep in step with the primary one.
CDC bootstrap — snapshot, then stream
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALEvery change-capture mechanism starts from a position and therefore has a bootstrap problem. The stitch — snapshot at a position, stream from that position, or overlap and resolve — is the same argument whether the source is a database, a SaaS export or an event API.
- SOURCE-SPECIFICWhether a consistent snapshot at a recorded log position is even possible depends on the source: engines with multi-version snapshot isolation can hold a repeatable read while reporting the current position, engines relying on locking cannot do it without blocking writers, and a SaaS API generally offers neither a consistent full read nor a position to anchor it to.
- SCALE-SPECIFICA single-transaction snapshot is the simplest correct approach and stops being usable somewhere between a table that reads inside the source's tolerance for a long transaction and one that does not. Above that point chunked snapshotting is not an optimisation, it is the only option, and its per-chunk consistency argument becomes something the team has to own.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — Distributed Systems owns what a consistent cut across a running system means and why one cannot generally be taken without coordination. The snapshot-at-a-position trick works because a single database can give you a coherent view and a position atomically; that property disappears the moment there are two sources, and the reasoning belongs there.
- — DevOps / Production Engineering owns the operational rehearsal: a re-snapshot is a recovery procedure, and a recovery procedure that has never been run is a hypothesis. It should be exercised on a schedule the same way a restore is.