Snapshot Performance & Scaling

The snapshot copies every existing row of each table into Snowflake before incremental replication starts. It is usually the first place a pipeline slows down, and a stalled snapshot blocks the table from ever reaching the incremental phase. This section covers why large tables are the bottleneck, the levers you can tune, and the signals that tell you to scale.

Why large tables are the first bottleneck

During snapshot load the connector reads each source table in primary key order, in keyset-paged chunks, through the Fetch Table Rows [MultiDatabaseFetchTableSnapshot] processor. For a large table this is the slowest phase, and a stalled snapshot blocks the table from ever reaching incremental replication. Throughput comes down to how many tables you read in parallel, the batch sizes, and the runtime's memory and disk headroom.

Levers you can tune

Runtime disk and backpressure

During snapshot, the queue in front of Upload Rows via Snowpipe Streaming can fill with FlowFiles and trigger back pressure, which consumes runtime disk. For larger tables, use a Large runtime for the extra storage headroom. Because runtime size is fixed at creation, pick the size up front based on the largest tables you will snapshot.

How data reaches Snowflake

The connector loads snapshot rows into Snowflake with Snowpipe Streaming, through the Upload Rows via Snowpipe Streaming processor, grouped per destination table by a concurrency group. Snowpipe Streaming is serverless, so the snapshot upload itself does not run on your warehouse. Account-level Snowpipe Streaming limits still apply as the number of replicated tables grows.

Avoid source deadlocks: SNAPSHOT isolation

Snapshot reads run under SQL Server's default READ COMMITTED isolation and take shared locks. If other clients hold conflicting locks on the same tables, this can deadlock and SQL Server terminates one session. The mitigation is SNAPSHOT isolation, which reads from row versions instead of taking shared locks:

ALTER DATABASE <database> SET ALLOW_SNAPSHOT_ISOLATION ON;

Then enable SNAPSHOT isolation on the connector's source-read processors and restart them. Because the connector reads the source during both snapshot and incremental, this applies to both MultiDatabaseFetchTableSnapshot and MultiDatabaseCaptureChangeSqlServer.

When to scale

• A few large tables dominate snapshot wall-clock time. Raise Concurrent Select Queries For Snapshot on a Medium or Large runtime, and tune the fetch batch sizes.
• The upload queue grows and does not drain. A persistently growing queue in front of Upload Rows via Snowpipe Streaming signals backpressure and heavy runtime disk use. Move to a Large runtime for storage headroom.
• Instability after raising concurrency on a Small runtime. Higher concurrency assumes Medium or Large. Reduce concurrency on Small or move to a larger runtime.
• A table never transitions from SNAPSHOT_REPLICATION to INCREMENTAL_REPLICATION. Investigate a missing primary key, oversized values failing the table, or source deadlocks (enable SNAPSHOT isolation).

Runtime sizing bands and warehouse/merge tuning that span both phases are in the Incremental Performance tab.

Incremental Load

Ongoing capture of changes (INSERT, UPDATE, DELETE) after the initial snapshot.

What happens

Data flow

Key processors

Incremental Performance & Scaling

In steady state, changes are captured through Change Tracking, streamed into per-table journal tables, then applied to destination tables by a MERGE. Loading the journals is cheap and serverless; the MERGE runs on your warehouse and is where nearly all steady-state cost and latency live. This section covers the MERGE, the retention boundary, journal growth, and how to size the runtime and warehouse.

Where the cost is: journal, then MERGE

Because the load side is serverless and the Merge Journal to Destination step runs on a warehouse, the MERGE is the primary compute cost and the first place to look for both cost and latency problems. Change Tracking rolls multiple updates to the same row between polls into a single net change, which tends to make merges smaller than the CDC variant, which preserves every intermediate operation.

Control merge cost: Merge Task Schedule CRON

The Merge Task Schedule CRON ingestion parameter governs when the merge is allowed to run by throttling the FlowFiles that reach it. The connector evaluates it in UTC. The throttle is demand-driven: when no changes are pending, no merge runs and the warehouse can auto-suspend.

Warehouse sizing for merges

Snowflake recommends starting at XSMALL and adjusting from there. There are two distinct axes:

Rule of thumb: scale out for table count, scale up for memory-bound single merges.

Change Tracking retention: a hard failure boundary

The most important operational constraint in steady state is the source retention window, set with CHANGE_RETENTION. SQL Server purges tracked changes once they are older than the window. If replication lag ever exceeds retention, the changes the connector needs expire, the affected table permanently fails, and it requires a full reload (a fresh snapshot) to resync.

ALTER DATABASE <database>
  SET CHANGE_TRACKING = ON
  (CHANGE_RETENTION = 5 DAYS, AUTO_CLEANUP = ON);

Set retention comfortably above your worst-case lag, not equal to it, so a paused connector, a network outage, or a failed cycle does not force a reload. Enable Change Tracking with TRACK_COLUMNS_UPDATED = OFF (the default): the connector does not use column-level change info, and turning it on adds source storage and per-DML overhead for no benefit.

Journal tables grow forever

Journal tables are retained indefinitely and never automatically cleaned up (customer data is never auto-deleted). They live in the same schema as the destination and are named <TABLE>_JOURNAL_<epoch>_<generation>, with a new generation on each schema change. To reclaim storage you can truncate journals, drop journals for tables removed from replication, or drop all but the latest generation for active tables.

Scaling the runtime

Runtimes are single-node and fixed-size (Small, Medium, Large), chosen at creation; the connector scales vertically, and changing size means migrating to a new runtime. Size to sustained (not peak) load. These documented bands are starting points, not service guarantees:

Also use a dedicated runtime when you need sub-1-minute end-to-end latency or cannot tolerate noisy neighbors. Changes load into Snowflake through Snowpipe Streaming (serverless), grouped per destination table; account-level Snowpipe Streaming limits apply as the number of replicated tables grows, so factor table count into capacity planning and raise limits through Support before you approach them.

When to scale

• Merge queue depth persistently growing. FlowFiles piling up in the Merge Journal to Destination queue mean merges are not keeping up. Add clusters (concurrency) or increase merge frequency; scale up size if a single table's merge is the bottleneck.
• Journal storage growing in steady state. The merge side is lagging ingest. Increase merge cadence or add warehouse capacity.
• End-to-end lag rising while the source rate is flat. The connector or merge side is under-provisioned. Scale the warehouse and/or runtime.
• Spill or queueing on MERGE queries. Scale up size for spill (memory); scale out clusters for queueing (concurrency).
• Lag approaching the CHANGE_RETENTION window. You are close to permanent table failure and forced reloads. Reduce lag immediately and increase retention for headroom.
• Snowpipe Streaming account limits reached as table count grows. Plan capacity by table count and raise account limits through Support before adding more tables.

Stream Staleness Prevention

Prevents Snowflake streams from becoming stale during periods of inactivity.

How it works

Stream lifecycle

Processors