SQL Server to Snowflake: CDC, Change Tracking & Replication Guide

SQL Server data reaches Snowflake through a one-time export and COPY INTO, scheduled queries on a modified-date column, or one of SQL Server's two built-in change features. Change data capture (CDC) copies every insert, update and delete from the transaction log into change tables; Change Tracking (CT) records which primary keys changed, and the reader fetches the current row. For a busy production database, one of these fits better: both see hard deletes without re-scanning tables.

Most constraints sit on SQL Server: CDC needs Enterprise or Standard edition and, outside Azure SQL Database, a running SQL Server Agent; its change data expires after three days by default; and a new column needs a new capture instance. In Snowflake, every merge resumes a warehouse. Estuary is one managed option here: its connectors poll SQL Server's change tables or Change Tracking versions into durable collections and load Snowflake on a schedule you set.

Choosing a SQL Server to Snowflake method

Queries and exports miss hard deletes and re-read SQL Server on every run, so most of this guide assumes CDC or Change Tracking.

How SQL Server CDC and Change Tracking feed Snowflake

Use CDC when you need every intermediate change, tables without primary keys, or reads from a readable secondary. Use Change Tracking when every table has a primary key, and you want lower source storage, computed columns, or an edition without CDC, such as Express.

• CDC is asynchronous. A capture job copies each change from the transaction log, with full column values and the update before-image, into a change table. Each table's capture instance fixes the column list, and a cleanup job deletes rows past retention. Both run as SQL Server Agent jobs, except on Azure SQL Database, which uses a built-in scheduler.
• Change Tracking is synchronous. Each DML transaction records the changed row's primary key in a side table. The reader joins those keys to the table for current values, so several changes between reads collapse into one.

A pipeline backfills existing rows, reads changes after a recorded log sequence number (LSN) or change version, then merges by key or appends.

SQL Server requirements

Change Tracking is available in every SQL Server edition, including Express, and in Azure SQL Database. CDC depends on the host:

Then create a read-only capture user and, as db_owner, enable each table:

sql
CREATE LOGIN cdc_reader WITH PASSWORD = '...';   -- Azure SQL Database: run this line in master
CREATE USER cdc_reader FOR LOGIN cdc_reader;
GRANT SELECT ON SCHEMA :: dbo TO cdc_reader;
GRANT SELECT ON SCHEMA :: cdc TO cdc_reader;
GRANT VIEW DATABASE STATE TO cdc_reader;
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'orders',
     @role_name = N'cdc_readers';   -- gating role, created if missing
ALTER ROLE cdc_readers ADD MEMBER cdc_reader;

For Change Tracking, Microsoft strongly recommends snapshot isolation:

sql
ALTER DATABASE sales SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE sales SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE dbo.orders ENABLE CHANGE_TRACKING;
GRANT SELECT ON SCHEMA :: dbo TO cdc_reader;
GRANT VIEW CHANGE TRACKING ON SCHEMA :: dbo TO cdc_reader;

Size retention for your worst outage: CDC keeps 4,320 minutes (three days) by default, and Change Tracking uses CHANGE_RETENTION. Give replicated tables a primary key, which Change Tracking requires and Snowflake MERGE matches on.

Snowflake requirements

Snowflake needs a database, schema, warehouse, and a dedicated user and role with grants on them. Use key-pair authentication: Snowflake is phasing out password-only sign-in for service users. Also set:

• AUTO_SUSPEND = 60 on the warehouse, since every resume bills a minute or more.
• A timestamp type that fits the source: TIMESTAMP_TZ keeps a datetimeoffset offset, TIMESTAMP_NTZ suits offset-free datetime2. An account-level TIMESTAMP_TYPE_MAPPING must agree with it.
• QUOTED_IDENTIFIERS_IGNORE_CASE off: when on, quoted mixed-case names like OrderDate are stored in uppercase.

Setting up SQL Server to Snowflake replication

1. Choose CDC or Change Tracking after checking edition, Azure tier and Agent; enable it, set retention and create the capture user.
2. Open network access: allowlisted IPs, an SSH tunnel through a bastion host, or private connectivity for a server with no public IP.
3. Prepare Snowflake as described above.
4. Pick a write mode per table:MERGE on the primary key, or append every change for audit tables. Keyless tables need CDC plus a declared key.
5. Load existing data. If the tool reads changes only after the backfill, the backfill must finish well inside retention.
6. Start change capture from the LSN or Change Tracking version recorded at backfill start, loading Snowflake as often as the tables are queried.
7. Validate. Compare row counts. UPDATE and then DELETE one key, checking Snowflake after each load. Add a column and confirm it arrives (under CDC, this tests the capture-instance switch). Pause the pipeline for less than retention and confirm it resumes from its last LSN without a gap.

What happens after the initial load?

• Updates arrive as before- and after-images under CDC and as one changed key per read under Change Tracking, then merge by key or append.
• Deletes. CDC writes a delete row with the old values; Change Tracking returns the key. Pipelines then soft-delete the Snowflake row or remove it.
• New columns under CDC. A capture instance ignores columns added after its creation, so the column stays missing in Snowflake. You don't re-enable CDC: run sys.sp_cdc_enable_table with a new @capture_instance name, switch the reader once both instances cover the same log position, then drop the old one with sys.sp_cdc_disable_table. A table can have two capture instances at most.
• New columns under Change Tracking are read from the table once the reader refreshes its column list.
• Dropped columns arrive as NULL under CDC. Some type changes, such as bigint to int, make the capture scan fail.
• Partition switches move rows that CDC doesn't record. Change Tracking doesn't track TRUNCATE TABLE; readers must re-initialize.
• In Snowflake, Openflow, for one, leaves a new column NULL in existing rows until each row changes.

SQL Server to Snowflake data type mapping

Tell the pipeline which zone datetime and datetime2 values were written in; only datetimeoffset keeps a zone, in TIMESTAMP_TZ. For hierarchyid, spatial types and sql_variant, capture a computed column that converts the value to text, using Change Tracking (CDC records computed columns as NULL).

DECIMAL, NUMERIC and MONEY precision

decimal and numeric hold up to 38 digits, and money reaches 922,337,203,685,477.5807, 19 significant digits. Loaded as Snowflake FLOAT, a double with about 15 significant digits, large and high-scale values get rounded. Map each column to NUMBER(p,s) (Snowflake also allows 38 digits), or VARCHAR for the exact string, then re-load the table: values already rounded in Snowflake can't be recovered there.

Performance, latency and Snowflake cost

Under CDC, SQL Server adds its own hop before Snowflake's load interval starts: the capture job copies the log into change tables, waits 5 seconds between cycles by default (@pollinginterval), and handles at most @maxtrans × @maxscans transactions per cycle. Azure SQL Database's scheduler runs every 20 seconds, with no latency guarantee from Microsoft. In an availability group, capture waits until every secondary has hardened the log. Change Tracking records changes at commit, so only the reader's polling interval applies.

Reducing MERGE cost

• Load less often than SQL Server changes. A warehouse woken every minute never suspends, because each resume bills 60 seconds or more.
• Key in write order. A key that follows inserts, such as (order_date, order_id), lets MERGE prune micro-partitions; a random NEWID() key makes it read far more rows.

Production considerations

• Source impact. CDC writes full rows and before-images into change tables in the same database; Microsoft warns degradation "can be substantial" when scan and cleanup fall behind. Capture only needed columns (@captured_column_list), use a separate filegroup (@filegroup_name), or use Change Tracking for keyed tables.
• Monitoring. Alert on lag nearing retention, sys.dm_cdc_errors rows and the last Snowflake load time.
• Security. A read-only capture user, Snowflake key-pair auth, and an SSH tunnel or private link when there's no public IP.

Common problems and troubleshooting

Change data purged before the pipeline read it

If a pipeline stops for longer than retention, the cleanup job moves each capture instance's low endpoint past the pipeline's position and deletes the rows in between. CDC query functions fail for LSN ranges outside the valid window, so the gap can't be read:

sql
EXEC sys.sp_cdc_help_change_data_capture;   -- start_lsn: current low endpoint per instance
SELECT sys.fn_cdc_get_min_lsn(N'dbo_orders') AS min_lsn, sys.fn_cdc_get_max_lsn() AS max_lsn;

• Recovery, cheapest first: with a reliable modified-date column, re-read only rows changed since the outage began (rows deleted meanwhile stay missed); otherwise re-read the table and merge into Snowflake without truncating.
• Prevention: raise retention (it applies after the job restarts) and alert on lag; change tables hold every change for the full retention period, so budget disk:

sql
EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080;  -- minutes (7 days)
EXEC sys.sp_cdc_stop_job @job_type = N'cleanup';
EXEC sys.sp_cdc_start_job @job_type = N'cleanup';

Under Change Tracking, a reader whose last version is below CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.orders')) must re-initialize the table.

Changes stop arriving: SQL Server Agent and the capture job

On SQL Server and Azure SQL Managed Instance, CDC requires SQL Server Agent. You can enable CDC on a table while Agent is stopped, but nothing reaches the change tables until it runs, and the log can't truncate past unread changes, even in simple recovery. Azure SQL Database has no Agent; its scheduler runs capture.

sql
SELECT servicename, status_desc FROM sys.dm_server_services;  -- VIEW SERVER STATE (2022+: VIEW SERVER SECURITY STATE)
EXEC sys.sp_cdc_help_jobs;
SELECT * FROM sys.dm_cdc_errors;
SELECT TOP 5 start_time, end_time, tran_count, latency
FROM sys.dm_cdc_log_scan_sessions ORDER BY start_time DESC;
SELECT name, log_reuse_wait_desc FROM sys.databases;  -- what holds the log

Start Agent and set it to start automatically. On RDS and Cloud SQL, where the jobs are hidden, run EXEC sys.sp_cdc_start_job @job_type = N'capture';.

CDC after an Always On failover, Multi-AZ failover or restore

• Always On. Configure CDC on the primary and connect through the listener. Unless replication is also enabled, run sys.sp_cdc_add_job for the capture and cleanup jobs on the new primary after failover, and disable the old ones. Change Tracking must be read from the primary (error 22117 on a secondary).
• RDS Multi-AZ. RDS recreates the jobs on the new primary with recorded parameters; set custom ones with rdsadmin.dbo.rds_set_configuration.
• Restores. RDS snapshot and point-in-time restores remove CDC, as does restoring to another server without KEEP_CDC. Re-enable CDC and re-read the tables.

Using Estuary for SQL Server to Snowflake

Estuary pairs the SQL Server CDC connector (with Amazon RDS and Cloud SQL variants) or Change Tracking connector with the Snowflake materialization. Every captured SQL Server table gets its own collection. By default, the materialization stages each transaction in an internal stage, then runs COPY INTO when every key is new, or MERGE when some exist.

• Load interval: Snowflake loads follow the sync schedule, 30 minutes by default when caught up; backfills ignore it. Both captures poll every 500 ms by default, and the CDC one skips the poll when the database LSN hasn't moved, so freshness is set by the Snowflake schedule and, under CDC, SQL Server's capture job.
• CDC or CT connector: the CDC connector captures keyless tables and can read from an availability-group secondary. The Change Tracking connector needs primary keys and the primary replica, and fixes a table's column list when it starts capturing it, so a column added later flows after the capture restarts.
• Deletes: both connectors emit _meta/op = 'd' documents, which Snowflake keeps as soft deletes unless Hard Delete is on.
• Types:decimal, numeric and money are captured as exact strings but become Snowflake FLOAT by default. Set castToString: true on the field to keep the exact digits as TEXT (cast to NUMBER(38,4) in a view); a DDL override alone still converts through FLOAT. datetime values are read in the capture's Time Zone (UTC by default), binary, varbinary and image land as BINARY, the CDC connector omits computed columns, and values over 8 MiB are truncated.

Implementing SQL Server to Snowflake with Estuary

These steps adapt Estuary's general data flow guide to this route.

1. Complete the prerequisites in SQL Server requirements and Snowflake requirements. Estuary also needs:
   • No writes to SQL Server: new CDC captures use no watermarks table. Optionally, db_owner membership, so the connector can manage capture instances and clean up change tables.
   • The Snowflake objects from the connector's setup script, with a public key registered for key-pair authentication. Password auth is deprecated.
2. Create the SQL Server capture. On the Sources page, click New Capture, choose SQL Server via CDC (or SQL Server via Change Tracking), name the capture, and fill in the endpoint properties:
   • Server Address: host:port; the port defaults to 1433. Allowlist Estuary's IP addresses, or use Network Tunnel for an SSH tunnel. Private and BYOC deployments can use private networking.
   • User (cdc_reader above), Database, and Time Zone: the zone your datetime values were written in.
   • Authentication: Password, AWS IAM through an RDS Proxy, or Azure IAM with an Entra app registration (IAM setup). The Change Tracking connector takes only a password.
   • Advanced Options: with db_owner, turn on Automatic Capture Instance Management and Automatic Change Table Cleanup, and set CDC Instance Access Role to cdc_readers.

3. Select tables. Click Next. Output Collections lists one binding per table: every table the CDC connector can see (Discover Only CDC-Enabled Tables narrows the list), or only tracked tables for Change Tracking. With db_owner, publishing enables CDC on selected tables that lack it.
   • Remove tables you don't want. Tables with no primary key or usable unique index arrive with Backfill Mode set to Without Primary Key.
   • Raise Backfill Priority for key tables, and keep Automatically keep schemas up to date on (see capture instance switchover).

Click Save and publish. The backfill starts, with change capture running alongside it.

4. Create the Snowflake materialization. On the capture's details page, click Materialize, choose Snowflake, and fill in the endpoint properties:
   • Host (Account URL): orgname-accountname.snowflakecomputing.com, no protocol.
   • Database, Schema, Warehouse and Role from the setup script.
   • Snowflake Timestamp Type: the type you chose above.
   • Authentication: Private Key (JWT), with User and Private Key.
   • Hard Delete: on to delete rows; off keeps soft deletes.

5. Choose the sync schedule. Under Sync Schedule, Sync Frequency defaults to 30 minutes. Shorter intervals give fresher tables and more warehouse resumes. For business-hours freshness, set Timezone, Fast Sync Start Time, Fast Sync Stop Time and optionally Fast Sync Enabled Days; outside that window, syncs run every 4 hours. See sync schedules.

6. Map collections to tables. Click Next. When prompted for a naming convention, choose Match source structure to keep SQL Server schema names, or Set a default schema. Per binding, set Delta Updates for audit and event tables and Clustering Keys for large, selectively filtered ones (they bill credits). For decimal and money fields, add castToString in the binding's field configuration (custom column types).
7. Publish. Click Save and publish. Estuary creates the Snowflake tables and loads the backfill without waiting for the schedule.
8. Verify that data is landing.
   • On the capture details page, Connector Status shows backfill progress and the Shard Information card reads PRIMARY; open a collection from the Collections list to see its Data Preview.
   • The materialization details page charts Data Read.
   • In Snowflake, query a target table, then run the checks from Setting up SQL Server to Snowflake replication.

How Estuary handles capture instances, retention gaps and re-backfills

Backfill rows and change-table or Change Tracking events for a table share one collection: JSON files in your cloud storage bucket. Snowflake reads the collection, not SQL Server, so most repairs never touch the database.

Capture instance switchover without re-reading the table

On every poll, the CDC connector reads the newest capture instance whose start LSN it has passed. After you add a column and create a second instance, it keeps reading the old one until both cover its position, then switches with no backfill. With Automatic Capture Instance Management, it checks cdc.ddl_history every 30 seconds, creates a new instance for any altered table, and drops the superseded one once it has moved on. Auto-discovery then adds the column to the collection, and the materialization adds it to Snowflake as nullable.

A retention gap triggers re-backfills, not a failed capture

If the cleanup job deletes changes the capture hasn't read, the connector marks each affected table missing and backfills it again, while tables with valid change data keep streaming. Under CDC, once cleanup runs after an outage longer than retention, every table is affected: cleanup moves all capture instances' low endpoints past the capture's single LSN. The backfill merges into Snowflake without truncating, so rows deleted during the gap stay. A binding's Additional Backfill Filter, such as modified_at > '2026-10-01', limits a re-read to changed rows. Automatic Change Table Cleanup deletes change rows once they're in a collection, but size disk for full retention anyway.

Rebuilding Snowflake without re-reading SQL Server

A materialization backfill rebuilds a dropped or retyped Snowflake table from the collection, and a new materialization adds a second destination from the same history; neither touches SQL Server. A collection-key change does need a source re-read. The materialization commits its collection position with each Snowflake load, so a crash mid-load can't apply SQL Server changes twice or drop them. Replay depends on collection retention (trial storage keeps about 20 days), and delta-update tables get duplicates from incremental backfills.

When another approach may make more sense

• Snowflake-only, native tooling: two GA Openflow connectors cover SQL Server. The Change Tracking one supports 2016–2022, Azure SQL, RDS and Cloud SQL, for tables with primary keys; the CDC one adds 2025 and accepts a unique index or declared key. Both take only username and password, soft-delete with _SNOWFLAKE_DELETED, ignore TRUNCATE, and run in Snowflake or your AWS account.
• Debezium and Kafka already run well: Debezium reads the CDC change tables, but a database operator must refresh capture instances after schema changes, and the Snowflake Kafka connector only inserts, so you build the MERGE layer.
• One-time migration or nightly freshness:bcp with COPY INTO, or a scheduled query, avoids enabling CDC; every run re-reads the source, and deletes never arrive. Estuary's SQL Server Batch connector needs neither feature and re-runs its queries every 24 hours by default.

Related resources

• Estuary SQL Server CDC, Change Tracking, Amazon RDS, Cloud SQL and Batch connectors; Snowflake materialization and sync schedules
• Backfilling data, schema evolution, PrivateLink
• Microsoft: change data capture, CDC vs Change Tracking, CDC in Azure SQL Database, CDC with availability groups; Amazon RDS CDC; Cloud SQL CDC
• Snowflake: Openflow Connector for SQL Server, Openflow Connector for SQL Server (CDC), key-pair authentication

To try the route, create a SQL Server capture in the Estuary dashboard. Pricing: estuary.dev/pricing.