Initial Snapshot Strategies in CDC Pipelines

The initial snapshot strategies used in CDC pipelines decide whether the destination starts with a baseline it can trust. The central requirement is a coordinated handover between a consistent point-in-time read and a recorded log position. Snapshot mode, parallel execution, log retention, and recovery must all preserve that relationship, because a fast stream cannot repair an incomplete starting state.

Why the initial snapshot is not a bulk copy problem

The technician assigned to a job changes twice between polls, but the next query returns only the latest assignment. The intermediate state has vanished from the record available to the polling process. If the row is deleted before the next poll, there is nothing left to select. Timestamp polling therefore misses both intermediate updates and deletes by design. Even if the rows are copied faster, both limitations still apply.

Log-based CDC captures changes, but it delivers them only from the position where the connector starts reading. Rows that were already there before that position still need a baseline. Unlike a bulk ETL copy, CDC initialization must connect that baseline to the exact point from which changes will be replayed. The snapshot and its log position must therefore be treated as a single atomic artifact.

If streaming starts too late, changes made while rows were being copied are lost without a warning. If it starts too early, rows written during the snapshot can be applied twice at the destination. The required boundary is not the time the copy finishes, but the preserved position that anchors its consistent read.

How PostgreSQL, MongoDB, and DynamoDB each expose the log position that anchors the snapshot

PostgreSQL requires wal_level=logical, appropriately sized max_replication_slots and max_wal_senders, and a dedicated replication user. The recommended plugin for PostgreSQL 10 and later is pgoutput. In the snapshot lifecycle described here, a coordinator acquires a session-level advisory lock through pg_try_advisory_lock(), captures the current LSN, opens a REPEATABLE READ transaction, and calls pg_export_snapshot(). The transaction stays open for the whole snapshot so that its consistent read horizon stays in place. The connector records both the start and end LSN, but streaming begins from the start LSN to include changes written during the copy.

A logical replication slot retains WAL until the consumer advances it. An unavailable connector can therefore leave enough retained WAL to exhaust the filesystem the database needs to operate. PostgreSQL 17 added sync_replication_slots for failover slot synchronization. On older versions, operators must recreate slots and re-anchor the snapshot when a standby is promoted.

MongoDB requires a replica set or sharded cluster because a standalone server has no oplog and cannot support CDC. A production replica set should have at least three members, and the connection string must describe its topology. The connector reads change streams and resumes from recorded resume tokens, which encode oplog positions. If the oplog has rotated past the recorded position, a fresh snapshot boundary is required.

DynamoDB Streams show changes to each item in time order. The connector must record the stream position at snapshot start and resume from there.

Five snapshot modes and what each one commits the pipeline to

A full snapshot with a log-position anchor assumes that the pipeline must establish the destination’s complete starting state. It captures all rows at a consistent LSN or oplog position, then streams from that position. This is the standard mode for a destination that must be complete and consistent from day one.

An incremental snapshot also supplies a baseline, but it reads bounded chunks interleaved with change events. Its ability to run without keeping a long transaction open on the source makes it appropriate for large, busy tables where a full scan would be too disruptive. The mode requires connector and source support, so it is not a generic substitute available to every pipeline.

A partial or filtered snapshot assumes that only a selected subset belongs in the initial baseline. It uses a SELECT override, such as a recent date window, and processes the result as a single chunk. Crucially, the filter applies only to the snapshot. Streaming captures every subsequent table change regardless of the WHERE clause, including updates and deletes for rows the destination never received.

Schema-only, or schema_only, records the table structure without copying rows and starts streaming from the current position. It relies on something else to supply the baseline, or every row that was already there will be missing. It should be used deliberately for an externally seeded baseline. By contrast, never performs no snapshot and sends only future changes from the current position. That choice is appropriate only when historical rows are genuinely unnecessary.

These modes encode different promises about destination state. Treating them as settings for speed hides those promises until a missing row or an unexpected change shows what went wrong.

How table structure determines whether a snapshot can be safely parallelized

Parallel chunking reduces snapshot duration only when the division of work preserves the baseline. A key-range partitioning key must be unique and stable even if another key is easier to query. A non-unique key can create overlapping or gapped ranges, causing boundary rows to appear twice or disappear. A key that changes, such as updated_at, can move rows between ranges during the snapshot. Throughput is therefore secondary to the correctness of the boundary.

The coordinator splits tables into key-range chunks when the REPEATABLE READ transaction opens and distributes those chunks to workers. Strategy selection then follows table structure. Integer Range suits sequential integer primary keys and produces even, predictable chunk sizes. CTID Block applies to string, UUID, hash, or sparse integer primary keys where integer ranges are unsuitable. Offset is the least efficient fallback when no suitable key structure exists.

Trendyol’s open-source go-pq-cdc implements this auto-selection across Integer Range, CTID Block, and Offset. It anchors the consistent snapshot before it sends chunks to workers, so the chosen strategy has the boundary needed for zero data loss.

Keyless tables should be split into chunks only when custom message key columns form a unique key. Otherwise, they should be processed sequentially or loaded separately. If the source cannot absorb concurrent reads, keep snapshot threads at 1 and disable chunking. A maintenance window, a smaller JDBC fetch size that puts less pressure on the buffer-cache, or an incremental snapshot with bounded reads may better fit what the source can handle.

The externally seeded load: when the snapshot and the baseline come from different systems

A separate ETL process can load a very large baseline while CDC holds an older log position. Source partitioning can accelerate the load, but the separation introduces an explicit sequencing requirement. The CDC start position must be captured before, or at the very start of, baseline loading. Recording it after the load completes leaves no stream coverage for changes made during the copy.

First, capture and record the LSN, oplog timestamp, or stream shard position before the first baseline row is read. Then run the baseline load, which may take hours for very large tables. Keep source logs from the captured start position while loading and CDC catch-up are both in progress. Release that retention only after the baseline load and catch-up are confirmed complete. This sequence keeps the changes needed to connect the loaded rows to the live stream.

If the position is captured after loading, changes made during the load can be absent from both the baseline and the stream. That gap cannot be closed without re-running the baseline. WAL or oplog retained through hours of loading and catch-up can grow large enough to threaten database operation. The pattern is legitimate, but its safety depends on both the ordering of the steps and the capacity to retain the intervening log.

What happens to the destination when a snapshot assumption is violated mid-stream

A broken handover often appears at the destination as a data quality problem. A recent-date snapshot followed by unfiltered streaming, for example, produces UPDATE and DELETE events for rows that were excluded from the baseline. An INSERT-only destination can retain orphaned change records. A MERGE destination may silently insert a record that should have been an update. Those records can pass schema validation even though they get the business meaning of the change wrong.

Schema evolution creates another mismatch when columns are added, renamed, or retyped during the snapshot. Baseline rows and streamed rows may then carry different schemas. Downstream analytical models that assume a stable structure can drift from the source without an explicit error. A completed copy is therefore not sufficient evidence that its rows and the following stream describe data in the same way.

The destination’s history model is also a commitment made before the snapshot. Databricks’ CDC documentation describes SCD Type 1 as retaining current state by overwriting history, while SCD Type 2 creates versioned rows with validity periods. A Type 1 destination cannot later supply the history it discarded. Choosing SCD Type 2 after loading does not retroactively reconstruct those versions. The baseline must fit both what the destination rows are meant to mean and how they are stored.

Recovering from a failed or interrupted snapshot without re-running from scratch

Recovery depends on what the snapshot preserved before interruption. Incremental snapshots checkpoint progress per chunk while interleaving reads with change events. A connector restart can therefore resume from the last completed chunk boundary. The checkpoint makes completed work recoverable, rather than forcing every interruption to become a full source scan.

For an interrupted full snapshot, the log position captured at snapshot start must still be retained on the source. If WAL has been recycled or the oplog has expired past that position, the gap between the abandoned baseline and the available log cannot be reconstructed. The entire snapshot must restart from a new anchor. The connector must automatically trigger that fresh snapshot when the required log data has expired.

Consumers must also cope with change events that arrive again after a restart, rebalancing event, or replay. An upsert keyed on the source primary key or document identifier is the practical baseline for idempotency. Connector offsets do not guarantee exactly-once business outcomes at the consumer. Recovery that preserves connector progress but duplicates destination effects is not a correct resumption.

The point to rebuild from must be chosen before the snapshot runs. Kafka retention, an immutable sink such as S3 with preserved CDC files in AWS DMS + S3 architectures, or a controlled re-snapshot provide different recovery paths. That choice determines how far back the pipeline can recover without reading the source again. Recovery strategy is a first-class design input because, depending on the strategy, failure can lead to hours of recovery or days of rebuilding.

What a correctly engineered handover enables downstream

Reliable AI agents, accurate dashboards, and low-latency A/B testing depend on a baseline that agrees with the stream applied to it. When that baseline has gaps, downstream outputs can still look right even when they are wrong. Sub-minute latency delivers new changes sooner to a destination whose starting point remains unsound.

With the SCD choice made in advance, the destination can represent current state through SCD Type 1 or preserve versioned history through SCD Type 2 from day one. Handling schema evolution as part of the design also supports downstream continuity without manual intervention or destination table rebuilds.

The initial snapshot establishes what the destination knows, and its log anchor establishes where that knowledge continues. When those two agree, CDC can support both timely decisions and trustworthy results.