Overview

A snapshot is how a CDC connector loads the current state of one or more source tables (or MongoDB collections) into the destination, so that subsequent change events can be applied on top of a complete baseline. Without a snapshot, the destination is missing every row that existed before the connector started; with a snapshot, the destination catches up to the source and then stays in sync via streaming.

Etlworks supports three snapshot types and two orthogonal modifiers that can be applied to one or more of the types. This article walks through each of them and explains when to use it, what changes when you do, and where the tradeoffs are.

Types vs. modifiers

The distinction matters because the two categories answer different questions:

• A snapshot type answers when the snapshot runs and what triggers it. The three types are Full (Initial) Snapshot, Ad-hoc Snapshot, and Incremental Snapshot. Every CDC flow runs at least one snapshot type at some point, and the choice determines the flow's lifecycle.
• A snapshot modifier answers how the snapshot is executed once it starts. The two modifiers are Parallel Snapshot and Partial Snapshot. Modifiers do not replace a type; they change how a Full or Ad-hoc snapshot copies data. They do not apply to Incremental Snapshot, which has its own built-in chunking mechanism.

A concrete example: a MySQL pipeline that bootstraps by copying the last 90 days of the orders table across four threads is using the Full (Initial) type with both the Partial modifier (only recent rows) and the Parallel modifier (four threads).

Choosing a snapshot approach

Match your goal to the entry in this table, then read the referenced section for tradeoffs.

The exact Snapshot Mode values that drive each type are covered in CDC settings reference → Snapshot mode.

Snapshot Types

Full (Initial) Snapshot

What it does. On the first startup of the CDC flow (and on any subsequent startup where the connector cannot find a usable offset), the connector opens a consistent read of every monitored table, streams every row into the destination, and then transitions to streaming mode — from that point forward it forwards change events from the source's log.

Where to configure. CDC connection, Snapshot Mode field. Values that produce a full snapshot include initial (snapshot then stream), ad-hoc initial (snapshot then stream, and enable ad-hoc snapshots for later), initial_only (snapshot and stop), and when_needed (snapshot only if no valid offset).

When to use. Every new pipeline where the destination needs a complete copy of the source's current state. This is the default and the right choice for the vast majority of setups.

Tradeoffs.

• Snapshot duration scales with data volume. On a large source, the initial snapshot can take hours or days. During that time the destination is being populated but has not yet seen recent source changes; the connector switches to streaming as soon as the snapshot completes.
• The snapshot is resumable by default (9.6.9+). Progress is persisted as each table completes, and if the flow is stopped or interrupted mid-snapshot, the next start skips the tables already snapshotted and continues with the rest — see Resumable full snapshot below. In releases before 9.6.9, or when the option is explicitly disabled, the full snapshot restarts from the beginning after any interruption.
• The source's log must retain enough history to cover the snapshot. While the snapshot is running, the source's binlog / WAL / archive log / oplog keeps growing. If retention is too short and log positions from before the snapshot completes get purged, the flow cannot transition to streaming cleanly and will report a "position not found" error on restart. Configure log retention with a margin that comfortably exceeds the expected snapshot duration.
• Locking and load. On most sources the snapshot opens a long-running consistent read, which can hold locks or delay VACUUM/log truncation. The database-specific configuration guides in Database-Specific CDC Cases cover the mitigations per engine.

Applicable modifiers. Parallel (except on MongoDB) and Partial can both be applied.

What can trigger full automatic re-snapshot of all tables

Resumable full snapshot (default, 9.6.9+)

Starting in Etlworks 9.6.9, the full (initial) snapshot is resumable at the table level: if the snapshot is interrupted before it completes, the next start skips the tables that were already snapshotted and continues with the ones that were not, then transitions to streaming from the source log position recorded at the very start of the original snapshot.

Resumability is enabled by default on every CDC connection. The related settings are:

• UI label — Resume interrupted full snapshot on the CDC connection.
• Connector property — debezium.resumable.full.snapshot.enabled. Default value: true.

Behavior in detail. When the connector begins a full snapshot, it records the current source log position — the binlog file and position on MySQL, the LSN on PostgreSQL and SQL Server, the SCN on Oracle, the change position on DB2 and AS/400, the resume token or oplog timestamp on MongoDB. As each table finishes, its completion is persisted to the snapshot progress file. If the snapshot is interrupted for any reason — the flow stops, the integrator restarts, the connection is lost — the persisted progress survives.

On restart the connector begins a full snapshot again, checks the progress file, skips the tables already recorded as complete, and snapshots the remaining tables. After the resumed snapshot finishes, streaming picks up from the log position recorded at the start of the original snapshot — not the current head of the log — which preserves CDC correctness by ensuring no change events between the original snapshot start and the resumed streaming are lost.

Example. A CDC flow monitors five tables. The full snapshot completes tables 1–4 and fails on the fifth. On restart, Etlworks skips tables 1–4, snapshots table 5, and then resumes streaming from the log position recorded at the start of the original snapshot. In releases before 9.6.9, or with resumability disabled, all five tables would have been snapshotted from scratch.

Why this is useful. Large-table snapshots can take hours. Losing them to a transient failure — a network blip, a host restart, an out-of-memory condition — previously meant starting the entire snapshot over. Resumable full snapshots eliminate that cost, reduce recovery time, and reduce load on both the source database and the destination.

Correctness note: the original log position must still be available. If the source database has advanced its log retention past the position recorded when the original snapshot began, the connector cannot resume streaming from there without losing events. In that case Etlworks logs the condition clearly and, depending on the connector's configured failure behavior, either starts a fresh full snapshot or fails the flow. Configure source log retention with a margin that comfortably exceeds the expected total snapshot duration, including retries.

Edge case: very fast parallel snapshots. When parallel snapshot is enabled and individual tables finish very quickly, it is possible for the flow to fail before the completion of those fast tables has been persisted to the progress file. Restart then re-snapshots more tables than expected, because only persisted completions are honored. This is most visible with small tables and high Snapshot Max Threads values. If you observe it, either lower Snapshot Max Threads or accept the additional re-snapshot work.

Disabling resumable full snapshots. On rare occasions — for example, when reproducing a support case, or when the progress file itself is suspect — you may want the pre-9.6.9 behavior of always restarting the snapshot from scratch. Clear the Resume interrupted full snapshot checkbox on the connection, or set debezium.resumable.full.snapshot.enabled to false in Other Parameters. In that mode any interruption discards partial progress.

Ad-hoc Snapshot

What it does. Once the initial snapshot has completed and the connector is streaming, an ad-hoc snapshot re-runs the snapshot phase for one or more specific tables without resetting the whole pipeline. The connector pauses streaming briefly, snapshots the requested tables, then resumes streaming from where it left off.

Where to configure. CDC connection, Snapshot Mode field. Any value with the ad-hoc prefix (for example ad-hoc initial or ad-hoc schema_only) enables ad-hoc snapshots. Without an ad-hoc mode set, this feature is disabled.

When to use.

• To add a new table to the pipeline (see Add New Tables below).
• To reload an existing table without a full pipeline reset (see Reload Existing Tables below).
• To recover a specific table whose downstream state has drifted from the source.

Tradeoffs.

• Ad-hoc snapshots are resumable at the table level (9.6.8+). If a blocking ad-hoc snapshot fails after some of the requested tables have finished, restarting the flow retries only the remaining tables — see Resumable ad-hoc snapshot below. Within a single table, however, the snapshot restarts from the beginning of that table. For very large individual tables where mid-table resumability matters, prefer Incremental Snapshot.
• Streaming is paused during the ad-hoc snapshot. New change events on the tables under snapshot accumulate in the source's log and are replayed once the snapshot completes. On busy tables the pause introduces measurable latency.
• The FROM field must be an explicit list of tables. Ad-hoc snapshots operate on specific table names. When FROM is a wildcard, an empty value, or a regex, the mechanism cannot resolve which table to target — see Wildcard or regex FROM patterns disable ad-hoc snapshots below.

Applicable modifiers. Parallel (except on MongoDB) can be applied to speed up ad-hoc snapshots that cover multiple tables. Partial is not typically applied to ad-hoc snapshots.

Wildcard or regex FROM patterns disable ad-hoc snapshots

Ad-hoc snapshots operate on an explicit list of tables. The mechanism that lets you add a new table or reload an existing one requires the connector to know in advance which tables are in scope. When the CDC flow's FROM field is empty, set to the wildcard *, or contains a regular expression, the connector cannot resolve ad-hoc requests to specific tables and the feature is effectively disabled.

A wildcard or regex FROM also has a second, less obvious failure mode. The set of tables the pattern matches is recomputed every time the connector starts — on every flow restart, on every node failover, and during disaster recovery. If a new table that matches the pattern was created in the source database since the last start, the connector may decide to perform a full re-snapshot on the new members, even though, from the operator's point of view, nothing was changed. The same happens when a table is renamed in a way that changes whether it matches.

The fix is to keep FROM explicit. Configure FROM as a comma-separated list of fully qualified table names, for example schema.orders, schema.customers, schema.line_items. When you need to add or remove a table, edit the FROM list and use ad-hoc snapshot (below). Etlworks' Flow Findings inspector flags any non-explicit FROM on a CDC source as a Major structural issue. See also CDC settings reference → FROM — list of tables to capture.

Add New Tables

To start capturing changes from a new table using an ad-hoc snapshot at restart:

CDC connection:

• Ensure Snapshot Mode is set to one of the ad-hoc variants: ad-hoc initial, ad-hoc, or ad-hoc schema only.
• Enable Automatically trigger ad-hoc snapshot for new tables.

CDC flow:

1. Stop the flow.
2. Add the new tables to the FROM field. Use fully qualified table names, comma-separated. Regular expressions are not supported for this workflow.
3. Restart the flow.

On restart the connector runs an ad-hoc snapshot of the newly added tables, then resumes streaming with the expanded capture set.

Reload Existing Tables

To re-snapshot specific tables at runtime, without stopping or restarting the flow, use a signal collection. This is an ad-hoc snapshot triggered through a signal table or file rather than through a restart.

CDC connection:

• Set Snapshot Mode to one of the ad-hoc variants (ad-hoc initial, ad-hoc, or ad-hoc schema only).
• Set Signal Data Collection to either the fully qualified name of a signal table (when using a database signal source) or a file name with extension (when using a file-based signal source).

CDC flow:

• Include the signal connection (database or file) in the flow's Connections tab, named Signal.
• Signal entries must use fully qualified table names; regular expressions are not supported.

How it works. The signal collection holds table names to re-snapshot. When the connector detects an entry, it performs the snapshot for that table and then removes the signal (deleting the row or the file). The flow does not need to be restarted.

Signal format. One column, one or more fully qualified table names, comma-separated. Example:

test.inventory, test.payment, test.customer

When to use signal-based reload vs. incremental snapshot. Signal-based ad-hoc is simpler to set up and does not require write access to the source database. If the tables you are reloading are small enough that the pause in streaming is acceptable, this is the right choice. For very large tables where a long pause is unacceptable, use Incremental Snapshot instead.

Resumable ad-hoc snapshot (9.6.8+)

Starting in Etlworks 9.6.8, ad-hoc snapshots are resumable at the table level: if a blocking ad-hoc snapshot fails after finishing some of the requested tables, restarting the flow retries the ad-hoc snapshot for the tables that were not completed, rather than falling back to a full re-snapshot of every monitored table.

There is no configuration to enable this behavior; it applies automatically to any blocking ad-hoc snapshot triggered through either the restart path (Add New Tables) or the signal path (Reload Existing Tables).

Example. A CDC flow monitors five tables. Two new tables are added to the FROM field and the flow is restarted, so Etlworks kicks off an ad-hoc snapshot for the two new tables. The snapshot completes the first new table and fails on the second. In releases before 9.6.8, the next start would have triggered a full snapshot of all five monitored tables. In 9.6.8 and later, the next start retries the ad-hoc snapshot for the remaining new table only, then resumes streaming.

Why this is useful. A single failed ad-hoc snapshot no longer wipes out the progress of the rest of the pipeline. Recovery time drops, source database load stays proportional to the actual work left to do, and the intended scope of the ad-hoc snapshot is preserved.

Fully clearing an incomplete ad-hoc snapshot. If you want to clear an incomplete ad-hoc snapshot instead of letting it resume — for example, if the reason it failed is no longer relevant — use Reset CDC State in the CDC offset editor. It removes the ad-hoc / resumable snapshot sidecar state files along with the offset and history files, after writing a backup zip you can restore from if needed. See Reset CDC State.

Operational note: signal-triggered ad-hoc snapshots. If the ad-hoc snapshot was originally triggered by a row in a database signal collection (see Reload Existing Tables), the recovery retry may be re-triggered internally using a file-based signal instead. This is expected behavior and does not require any action from the operator; the important outcome is that the incomplete blocking snapshot resumes without forcing a full snapshot.

Incremental Snapshot

What it does. An incremental snapshot runs alongside streaming rather than pausing it. The connector reads the source table in bounded chunks, and between chunks it continues to consume change events from the log. The result is a resumable snapshot that keeps streaming latency roughly constant regardless of the snapshot's total duration.

Where to configure. CDC connection, Signal Data Collection field (pointing at a signal table in the source database). Do not set Snapshot Mode to any of the ad-hoc variants when using incremental snapshot — incremental snapshot activates when Signal Data Collection is set on a non-ad-hoc mode.

When to use.

• Very large tables where a Full or Ad-hoc snapshot would take long enough that streaming latency during the pause becomes a problem.
• Adding a new table to a running pipeline without any pause in streaming.
• Pipelines where uninterrupted streaming is a hard requirement for downstream consumers.

Tradeoffs.

• Resumable. Unlike Full or Ad-hoc snapshots, an incremental snapshot survives interruption — the connector remembers which chunks it has completed and picks up from the next one on restart.
• Modifiers do not apply. Neither Parallel nor Partial can be used with an incremental snapshot — it has its own internal chunking, and the WHERE-clause mechanism used by Partial is not supported by the incremental path.
• See Production risks to understand before enabling below for the real cost of choosing this type.

Production risks to understand before enabling

Incremental snapshots are powerful, but they have two production-level costs that the convenience can obscure.

• Read/write access to the source database is required. The connector writes the signal markers to the source itself, in the signal table you create. On a regulated or audited source database, that may require a change request to grant write privileges to the CDC user.
• On PostgreSQL specifically, an incremental snapshot can cause WAL segments to accumulate for the duration of the snapshot. PostgreSQL retains every WAL segment that any replication slot still references, and the connector's replication slot holds back the WAL position throughout the incremental snapshot. On busy production sources we have seen the WAL volume grow by many gigabytes per hour and run out of disk before the snapshot completes. Plan for the increased disk usage, monitor WAL retention closely, and consider running the incremental snapshot in a maintenance window if the source is high-traffic.

If neither of these tradeoffs is acceptable, use ad-hoc snapshots with an explicit Signal connection instead — see the Reload Existing Tables subsection above. Ad-hoc snapshots do not require write access to the source and do not have the WAL retention effect.

Setting up an incremental snapshot

1. Verify permissions. The CDC connection must use a database user with write access to the source database (needed to write signal markers).

2. Create the signal table in the source database:

   CREATE TABLE db.schema.dbz_signal (
     id VARCHAR(64),
     type VARCHAR(32),
     data VARCHAR(2048)
   );

3. Configure the CDC connection:
   • Set Signal Data Collection to the fully qualified name of the signal table.
   • Do not set Snapshot Mode to any ad-hoc variant.
   • Add the signal table to the list of included tables (in the CDC flow or CDC connection).

4. Trigger a snapshot by inserting a row into the signal table:

   INSERT INTO db.schema.dbz_signal
   VALUES ('signal-1', 'execute-snapshot', '{"data-collections":
   ["inventory.dbo.orders", "inventory.dbo.item"]}');

   The snapshot runs immediately if the flow is active, or on the next restart if it is not.

Snapshot Modifiers

Modifiers change how a snapshot executes without changing when it runs or what triggers it. Both modifiers apply to Full and (in Parallel's case) Ad-hoc snapshots; neither applies to Incremental Snapshot.

Parallel Snapshot

What it does. Splits the snapshot workload across multiple threads so that several tables are snapshotted concurrently instead of one at a time. Each thread reads one table.

Where to configure. CDC connection, Snapshot Max Threads field. The default value is 1 (sequential); any value greater than 1 enables parallel snapshot for that connection.

Applies to. Full (Initial) Snapshot and Ad-hoc Snapshot. Does not apply to Incremental Snapshot.

When to use. Only when all of the following are true:

• The pipeline captures many tables at once (a two-table snapshot does not benefit from four threads).
• The source database has spare CPU, I/O, and connection capacity to absorb the added load.
• The integrator host has enough free heap to hold N threads' worth of buffered rows simultaneously.
• The source is not MongoDB (see the warning below).

Use with caution. Even when all four conditions above hold, parallel snapshot is not a free speed-up. The tradeoffs are real and add up quickly with high thread counts.

Tradeoffs

• Memory consumption on the integrator scales with thread count. Each thread independently holds a JDBC result-set buffer sized by the connection's JDBC Fetch Size field (default 10,000 rows). On wide tables (many columns, or columns holding large text or binary values) that buffer can be sizeable, and running four or eight threads at once can push the JVM into GC pressure or an OutOfMemoryError. This is the single most common reason parallel snapshots fail in production. See also CDC settings reference → JDBC Fetch Size.
• Source database load multiplies. Each thread opens its own connection and issues its own consistent read against the source. A busy production database may not have the connection headroom or the buffer-cache capacity to serve N concurrent snapshot readers without impacting the live workload.
• Log-retention pressure increases. Each thread holds its own consistent-read snapshot for the duration of its scan, and the source cannot truncate the binlog / WAL / archive log until the last one is done. On PostgreSQL this means the replication slot holds back the WAL that much longer; on MySQL it means the binlog files that much bigger; on Oracle it means the archive-log retention window that much wider.
• Diagnosability suffers. When a parallel snapshot fails partway through, the failure may involve interactions between threads (contention, connection-pool exhaustion, memory pressure) that are harder to reproduce and reason about than a single-threaded snapshot.
• The setting only affects the snapshot phase. Once the connector transitions to streaming, it uses a single capture thread regardless of this value. So parallelism only helps if the snapshot itself is the bottleneck.
• Interaction with resumable full snapshot. When Resumable full snapshot is enabled (the default in 9.6.9+) and Snapshot Max Threads is set high, individual tables can finish so quickly that a failure occurs before their completion is persisted to the progress file. Restart then re-snapshots those tables even though they finished before the failure. If you see this in practice, lower Snapshot Max Threads or accept the extra re-snapshot work.

Never use parallel snapshot with a MongoDB source

On MongoDB CDC sources, parallel snapshot is a beta feature and is not safe for production. The underlying Debezium MongoDB connector's parallel snapshot path is known to corrupt the snapshot or fail with hard-to-diagnose errors under load. This is true both for initial snapshots and for ad-hoc re-snapshots of existing collections.

Keep Snapshot Max Threads at 1 on every MongoDB CDC connection. Etlworks' Flow Findings inspector flags Snapshot Max Threads > 1 on a Mongo CDC source as a Major structural issue.

Recommended values (non-MongoDB sources)

When all preconditions are satisfied and MongoDB is not the source, start conservatively and confirm the source and integrator can absorb the load before raising further:

Watch integrator heap usage and source database load during the first parallel snapshot run and adjust downward if either is stressed. See also CDC settings reference → Snapshot Max Threads.

Partial Snapshot

What it does. Replaces the default SELECT * FROM table that the connector would run during the Full snapshot with a custom SELECT that includes a WHERE clause. Only rows matching the WHERE clause are copied to the destination as part of the snapshot; subsequent inserts, updates, and deletes are streamed in full via the normal change feed.

Where to configure. CDC connection, Other Parameters. Two related keys:

• snapshot.select.statement.overrides — comma-separated list of fully qualified tables that have a custom snapshot SELECT.
• snapshot.select.statement.overrides.<table> — the actual SELECT statement to use for each such table.

Applies to. Full (Initial) and Ad-hoc Snapshots. Does not apply to Incremental Snapshot.

When to use.

• The source table is large enough that a full snapshot is impractical, and only a recent subset (for example, the last 90 days) is needed at the destination.
• The pipeline is intentionally scoped to only include qualifying rows — for example, only orders in a specific status, or only records for a specific business unit.
• Testing or piloting a CDC pipeline against a small slice of production data before scaling to the full table.

Tradeoffs.

• Rows excluded from the snapshot are not present in the destination. This is the single most important thing to understand. If your WHERE clause excludes rows that later receive updates, the destination will see the update event without the original row — which typically resolves to either an inserted row from the update (for MERGE-based loads) or a lost update (for INSERT-only loads). The behavior depends on the destination flow configuration; test it before running against production.
• Deletes on excluded rows are still emitted. A delete event for a row that never existed in the destination is normally a no-op, but on strict destinations it can produce errors. Confirm the destination flow tolerates deletes for missing rows if this is a concern.
• The WHERE clause only affects the snapshot phase. Once streaming starts, the connector captures every change on the table regardless of the WHERE clause. If your intent is to filter out rows at both snapshot and stream time, use a source-side filter (a view or an additional transformation), not the partial snapshot mechanism.
• No easy way to "top up" excluded rows later. If you decide you need the older rows after all, the simplest recovery is to trigger an ad-hoc snapshot of the whole table (which will re-snapshot with the default SELECT unless the override is still active) or to reset the flow and re-run with the WHERE clause loosened or removed.

Setting up a partial snapshot

Set the two related keys in Other Parameters on the CDC connection. Example: snapshot only the last 90 days of the products and orders tables:

snapshot.select.statement.overrides=test.dbo.products,test.dbo.orders
snapshot.select.statement.overrides.test.dbo.products=SELECT * FROM test.dbo.products WHERE created_at > DATE_SUB(NOW(), INTERVAL 90 DAY)
snapshot.select.statement.overrides.test.dbo.orders=SELECT * FROM test.dbo.orders WHERE created_at > DATE_SUB(NOW(), INTERVAL 90 DAY)

The WHERE clauses use the source database's SQL dialect; adapt the syntax for PostgreSQL, SQL Server, Oracle, or DB2 as needed.

See also

• CDC settings reference: tradeoffs and dependencies — per-setting reference including Snapshot Mode, Snapshot Max Threads, Signal Data Collection, and JDBC Fetch Size.
• CDC Configuration and Monitoring — how to stop, reset, monitor, and recover a CDC flow.
• Database-Specific CDC Cases — source-specific snapshot behavior for MySQL, PostgreSQL, SQL Server, Oracle, DB2, AS/400, and MongoDB.
• Tips and Tricks for CDC Flows — field-level transformations, soft deletes, and event handling.