SQL Server to Snowflake Guide: CDC, Openflow, and Batch Migration

SQL Server to Snowflake integration lets teams move operational database data into Snowflake for analytics, reporting, machine learning, customer intelligence, and AI workflows. You can do this with a CDC pipeline, Snowflake's native Openflow connector, or with file-based methods such as Snowpipe and SnowSQL.

For production replication, two CDC-based options are worth comparing: Estuary captures SQL Server changes using log-based CDC or Change Tracking, backfills historical rows, and materializes data into Snowflake with sub-minute latency. Snowflake Openflow is a native integration service built into Snowflake, powered by Apache NiFi, that uses Change Tracking to replicate SQL Server tables directly into Snowflake without a third-party tool.

Manual methods such as Snowpipe and SnowSQL work for one-time transfers or scheduled batch jobs, but they require separate export processes, staging files, orchestration, monitoring, and error handling. In this guide, we compare all four methods and explain when each one makes sense.

SQL Server to Snowflake: What Changes During Integration?

Moving data from SQL Server to Snowflake is not just a database copy. SQL Server is usually the operational system where transactional data changes constantly, while Snowflake is typically the analytical destination where teams query, model, and join data at scale.

Important integration decisions include:

For authoritative reference on each technology involved, see Microsoft's SQL Server Change Data Capture documentation and Snowflake's SQL Server migration guide.

SQL Server to Snowflake Methods Compared

If Snowflake needs to stay current as SQL Server changes, use a CDC-based pipeline or Openflow. If you only need a one-time transfer, SnowSQL or staged Snowpipe workflows may be enough.

Method 1: SQL Server to Snowflake CDC with Estuary

Estuary is a managed data pipeline platform that can capture SQL Server changes using CDC, backfill historical rows, and materialize the resulting collections into Snowflake tables.

This method is a strong fit when Snowflake needs to stay current as SQL Server changes, especially for analytics, operational reporting, machine learning, customer intelligence, and AI workflows. Compared with file-based methods, a CDC pipeline reduces the need for recurring exports, staging jobs, batch scripts, and manual reconciliation.

Not ideal for: Pure schema migration (Estuary handles data replication, not T-SQL to Snowflake SQL code conversion), databases where SQL Server CDC and Change Tracking cannot be enabled, or teams doing a one-time historical-only load with no need for ongoing sync.

Estuary SQL Server Connector Requirements

Before creating the SQL Server capture, confirm that:

• SQL Server is reachable from Estuary.
• The database and the individual tables you want to capture have CDC enabled.
• Each captured table has a primary key, or you manually specify a key in the Estuary collection definition.
• The capture user has the required database access.
• You know the SQL Server host, port, database name, username, and password.
• You have decided whether CDC, Change Tracking, or Batch capture is the right mechanism for your use case.

Estuary’s SQL Server CDC connector is best when you need real-time capture with full audit history. Change Tracking can be a better fit when you need lower storage overhead or need to capture computed columns. Batch capture is useful when SQL Server does not support CDC or Change Tracking, when you need views, or when you want custom queries. 

SQL Server Capture Options in Estuary

Estuary recommends CDC when you need complete audit logging with full row history, Change Tracking when you need lower storage overhead or computed columns, and Batch when CDC or Change Tracking is not available. 

Here’s how to set it up:

Step 1. Create an Estuary Account

• Start by signing up for a free Estuary account. You can register using Google or GitHub credentials. Once inside, you’ll be greeted by a dashboard with options to capture data and materialize it to your desired destination.

Step 2. Set Up Your SQL Server to Snowflake Environments

Before building the pipeline, make sure:

• SQL Server is network-accessible and allows CDC on the tables you want to sync
• You have login credentials with REPLICATION or equivalent privileges
• Your Snowflake account is ready to receive data. You’ll need the host, database, schema, warehouse, and a user configured with key-pair authentication.

[See full prerequisites for SQL Server and Snowflake →]

Step 3. Capture Data from SQL Server

• Click Captures and select New capture. This will connect you to your data source— the SQL Server.

• Navigate to and select SQL Server as your data source connector.

• Fill in the capture details with your SQL server address, database username, and password. Click Next.

• Estuary will connect to your database and auto-detect all tables that support CDC
• Choose which tables you want to capture (all are selected by default)
• Click Save and Publish

Estuary will now backfill historical rows and begin listening for real-time changes via SQL Server CDC. Each selected table becomes a versioned collection within Estuary.

Snowflake Connector Requirements

Before creating the Snowflake materialization, make sure you have:

• Snowflake host URL.
• Target database and schema.
• Virtual warehouse.
• Snowflake role with required access.
• Snowflake user configured for key-pair/JWT authentication.
• Private key for the configured Snowflake user.
• Timestamp type selected for materialized timestamp columns.
• QUOTED_IDENTIFIERS_IGNORE_CASE set to FALSE for the Estuary user.
• Username/password authentication should not be used for new Snowflake materializations.

Snowflake username/password authentication was deprecated in April 2025, and Estuary’s Snowflake connector now uses key-pair/JWT authentication.

Step 4: Configure Snowflake as the Destination

• Click the Materialize collections button to transfer your captured data.
• From the connector tile, select Snowflake Data Cloud as your destination.

• On the Snowflake connector page, provide the following details:
  • Host URL (your Snowflake account URL, e.g., orgname-accountname.snowflakecomputing.com)
  • Database and Schema where the data will be materialized
  • Warehouse (name of your Snowflake warehouse)
  • User (Snowflake username)
  • Private Key (for authentication)

• After entering the configuration, click Next.

Estuary maps each collection (from SQL Server) to a new or existing table in Snowflake. You can optionally:

• Rename the target tables
• Filter fields
• Apply in-flight transformations

By default, Estuary uses standard merges, but you can enable delta updates on a per-table basis. Delta updates reduce latency and costs for large datasets, and when enabled, Estuary writes data into Snowflake using Snowpipe Streaming for near real-time ingestion.

• Finally, click Save and Publish to activate the pipeline.

Snowflake Cost and Performance Tips

When materializing SQL Server data into Snowflake, consider both data freshness and warehouse cost. Snowflake compute is billed per second with a 60-second minimum, and inactive warehouses do not incur compute charges. Estuary recommends configuring the Snowflake warehouse to auto-suspend after 60 seconds and setting the materialization sync schedule based on freshness requirements.

For large datasets, collection keys can affect Snowflake update performance. Standard updates can perform better when keys help Snowflake prune micro-partitions efficiently. For example, chronological keys such as /date, /user_id may perform better than using only /user_id for large update-heavy tables.

Standard Updates vs Delta Updates in Snowflake

Estuary’s Snowflake materialization connector supports both standard updates and delta updates. Standard updates are the default and are better when the Snowflake table should reflect the latest fully reduced state of SQL Server records.

Delta updates can reduce latency and cost for large datasets because Estuary does not need to query existing rows in the Snowflake table. However, delta updates are not suitable for every workflow because the resulting table in Snowflake is not fully reduced. Snowpipe Streaming is used by default for delta update bindings and is the lowest-latency method to load data into Snowflake through Estuary.

Snowpipe is a Snowflake-native file ingestion option. To use it with SQL Server, you still need a separate process to export SQL Server data into files, upload those files to a cloud stage, and then let Snowpipe load them into Snowflake. This can work for scheduled batch pipelines, but it adds orchestration and monitoring work.

Method 2: SQL Server to Snowflake Using Snowflake Openflow

Snowflake Openflow is a managed integration service built directly into Snowflake, based on Apache NiFi technology that Snowflake acquired via Datavolo. It connects SQL Server to Snowflake using Change Tracking, without requiring a third-party pipeline tool or external orchestration.

This method is a strong fit when your team already operates inside the Snowflake ecosystem and wants to manage data replication from the Snowflake UI rather than from a separate platform.

Not ideal for: Teams that need log-based CDC with full row history and audit logging (Openflow uses Change Tracking, not transaction log capture), tables without primary keys, or SQL Server instances where Change Tracking cannot be enabled. Estuary's CDC connector is a better fit for those requirements.

Openflow requirements

Before setting up Openflow, confirm that:

• You have a Snowflake account with the Openflow feature available.
• SQL Server is reachable from Snowflake's network (or you have a private link configured).
• Change Tracking is enabled at the SQL Server database level and on each table you want to replicate. Unlike SQL Server native CDC, Openflow relies on Change Tracking, not the transaction log.
• Each replicated table has a primary key. Openflow uses Change Tracking to detect changed primary keys; tables without primary keys cannot be tracked this way.
• You have a dedicated SQL Server user with SELECT and VIEW CHANGE TRACKING permissions on the tables you want to replicate.
• Your Snowflake user has the necessary roles to create databases, schemas, warehouses, and Openflow pipelines.

To enable Change Tracking on a database and table:

```sql
-- Enable at the database level
ALTER DATABASE [YourDatabase]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);

-- Enable on each table you want to replicate
ALTER TABLE dbo.YourTable
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);

```

Step 1. Open Openflow in Snowflake

Log in to your Snowflake account and navigate to Data > Openflow in the left sidebar. If Openflow is not visible, confirm that your Snowflake edition and region support it.

Step 2. Create a new pipeline

Click New pipeline and select SQL Server as the source connector. Openflow will prompt you for:

• SQL Server hostname and port
• Database name
• Username and password for the replication user
• Whether to use SSL for the connection

Snowflake will test the connection before proceeding.

Step 3. Select tables to replicate

After a successful connection test, Openflow scans the source database and presents the tables that have Change Tracking enabled. Select the tables you want to replicate.

If a table is not listed, Change Tracking has not been enabled on it. You will need to run the ALTER TABLE command shown above before it appears.

Step 4. Configure the destination

Specify the target Snowflake database, schema, and warehouse for the replicated tables. Openflow creates the target tables automatically based on the source schema.

Openflow writes change data into journal tables alongside a current-state view of each replicated table. The journal tables give you an append-style log of changes; the current-state table reflects the latest version of each row.

Step 5. Start replication

Click Start pipeline. Openflow performs an initial full snapshot of each selected table, then begins listening for changes via Change Tracking.

Step 6. Validate the replication

After the initial snapshot completes, run a row count comparison between SQL Server and the target Snowflake tables. Then validate that subsequent inserts, updates, and deletes in SQL Server appear in Snowflake within the expected latency window.

sql
-- On SQL Server
SELECT COUNT(*) FROM dbo.YourTable;

-- On Snowflake
SELECT COUNT(*) FROM YOUR_SCHEMA.YOURTABLE;

Openflow vs Estuary: when to use which

Both Openflow and Estuary handle ongoing SQL Server to Snowflake replication, but they suit different requirements.

If your tables all have primary keys, your team prefers to stay within the Snowflake UI, and you do not need a full transaction log audit trail, Openflow is a reasonable choice. If you need log-based CDC, tables without primary keys, multi-destination replication, or sub-second latency, Estuary is the better fit.

For more on Snowflake's native Openflow connector, see the Openflow for SQL Server documentation.

Method 3: SQL Server to Snowflake Using Snowpipe

Snowpipe is Snowflake's file-based ingestion service. It monitors a cloud stage (S3, Azure Blob, or GCS) and automatically loads new files into Snowflake as they arrive. To use it with SQL Server, you need a separate process to export SQL Server data into CSV or Parquet files and place them in the stage.

Not ideal for: Teams that need Snowflake to reflect SQL Server updates and deletes in real time. Snowpipe is append-only. It loads files into Snowflake but does not process SQL Server change events. Each file must be staged separately, and deleted rows in SQL Server will not appear as deletes in Snowflake unless you handle that logic yourself.

Step 1. Set up your Snowflake account. Create a database and schema, ensuring you have the required privileges.  

Step 2. Create an external stage using this SQL statement:

sql language-plaintext
CREATE STAGE <stage_name>
  URL = 's3://<your_s3_bucket>'
  CREDENTIALS = (AWS_KEY_ID='<your_aws_access_key>', AWS_SECRET_KEY='<your_aws_secret_key>');

Security note: For production environments, consider using a Snowflake storage integration instead of embedding cloud access keys directly in SQL. Storage integrations are generally easier to govern and rotate safely.

Replace <stage_name> with the name of your stage, <your_s3_bucket>, <your_aws_access_key>, and <your_aws_secret_key&gt; with your S3 bucket name and AWS credentials.

Step 3. Create a pipe using this SQL statement:

bash language-plaintext
CREATE PIPE <pipe_name>
  AUTO_INGEST = TRUE
  AS COPY INTO <your_table_name>
  FROM '@<stage_name>/<your_file_prefix>'
  FILE_FORMAT = (TYPE=CSV FIELD_DELIMITER=',' SKIP_HEADER=1);

Replace <pipe_name> with the name of your pipe, <your_table_name> with the name of the table in your snowflake database and schema where you want to load the data, <stage_name> with the name of your external stage, and <your_file_prefix> with the prefix for your files in the external stage.

Step 4. Run this command to enable Snowpipe in your Snowflake database: 

bash language-plaintext
ALTER ACCOUNT SET PIPE_EXECUTION_PAUSED = FALSE;

This allows Snowpipe to ingest data when it arrives in the external stage automatically.

Step 5. Export SQL Server data to files and upload them to the stage.

Snowpipe loads files from a cloud stage into Snowflake. It does not directly extract rows from SQL Server. You need a separate process, such as BCP, SSIS, a scheduled script, or another export job, to write SQL Server data into CSV or another supported file format and place those files in the staged location.

To create a batch file, open a text file, and run this command:

bash language-plaintext
@echo off  
title [title of your batch script]
echo [first line]
pause

Save the text file with the extension .bat, double-click to run the batch file, right-click and select edit so you can paste the SQL statement in the file. 

Step 6. Use a scheduling tool like Windows Task Scheduler or Cron to schedule the batch file to run regularly. If you implement the preceding steps correctly, your SQL data should be automatically loaded into Snowflake through Snowpipe.

For teams that want to avoid repeated file exports, staging, and scheduling, a CDC-based pipeline may be a better fit.

Method 4: SQL Server to Snowflake Using SnowSQL

SnowSQL is Snowflake's command-line client. It lets you run SQL commands against Snowflake directly from a terminal, including COPY INTO commands that load staged files into Snowflake tables. It is best suited for one-time loads or ad hoc migrations where you want direct control over each step.

Not ideal for: Ongoing replication. SnowSQL executes commands and loads files, but it has no built-in mechanism to capture SQL Server changes. Using it for regular sync requires building your own export, scheduling, and delta-detection logic.

Step 1. Download SnowSQL from the Snowflake website and install it on your local machine.

Step 2. Create a Snowflake account to create a new database and table in Snowflake.

Step 3. Run this command to connect to Snowflake using SnowSQL:

plaintext
snowsql -a <account_name> -u <username> -d <database_name> -s <schema_name> -w <warehouse_name>

Replace <account_name>, <username>, <database_name>, <schema_name>, and <warehouse_name> with your Snowflake account details.

Step 4. Run this SQL command to create a Snowflake table to host the data in transit:

plaintext
CREATE TABLE <table_name> (
    <column1_name> <column1_data_type>,
    <column2_name> <column2_data_type>,
    ...
)

Replace <table_name>, <column1_name>, <column1_data_type>, <column2_name>, <column2_data_type>, and other blank values with your table and column names and data types.

Step 5. Run this command to export data from the SQL Server table using the BCP utility.

plaintext
bcp <table_name> out <file_name> -c -t',' -S <server_name> -U <username> -P <password>

Replace <table_name> with the name of the table you want to export, <file_name> with the name of the file you want to export to, <server_name> with the name of the SQL Server instance, <username> with your SQL Server username, and <password> with your SQL Server password

Step 6. Run this command to upload the data to Snowflake:

plaintext
PUT file://<file_path> @<stage_name>/<file_name>

Replace <file_path> with the path to the file you exported from SQL Server, <stage_name> with the name of the Snowflake stage you want to upload the file to, and <file_name> with the name of the file you want to upload.

Step 7. Execute this command to load the data from the file to the Snowflake table you created earlier:

plaintext
COPY INTO <table_name>
FROM @<stage_name>/<file_name>
FILE_FORMAT = (TYPE = CSV, FIELD_DELIMITER = ',', SKIP_HEADER = 1);

Replace <table_name> with the name of the Snowflake table you created earlier, <stage_name> with the name of the Snowflake stage you uploaded the file to, and <file_name> with the name of the file you uploaded.

This completes the one-time process of transferring data from the SQL Server to Snowflake using SnowSQL. To automate the transfer of bulk data, you can either run a task for each of your SQL statements and build dependencies between them or wrap the entire SnowSQL script into a stored procedure triggered with your tasks. 

SQL Server to Snowflake Validation Checklist

Before using the pipeline in production, validate:

• SQL Server CDC is enabled on the database and selected tables.
• Each captured table has a primary key or a manually specified Estuary collection key.
• Historical backfill completed successfully.
• Inserts, updates, and deletes from SQL Server appear correctly in Snowflake.
• Snowflake tables are created in the expected database and schema.
• Timestamp columns use the expected Snowflake timestamp type.
• Standard or delta update mode matches your analytics requirements.
• Row counts match between SQL Server and Snowflake.
• Sample records match between the source and the destination.
• Duplicate records are not created during retries or backfills.
• Downstream dashboards, dbt models, notebooks, and applications still work.
• Snowflake warehouse auto-suspend and sync schedule are configured for cost control.
• Monitoring and alerts are configured for capture or materialization failures.

Common SQL Server to Snowflake Migration Mistakes

Treating File-Based Loads as Real-Time Replication

Snowpipe and SnowSQL can move data into Snowflake, but they require exported files, schedules, staging, and monitoring. They are not the same as continuously capturing SQL Server inserts, updates, and deletes through CDC.

Forgetting to Enable CDC on SQL Server Tables

For CDC-based replication, CDC must be enabled on both the database and the individual tables you want to capture.

Ignoring Primary Keys

Primary keys are important for deduplication, updates, and delete handling. If a SQL Server table does not have a primary key, you must define an appropriate key in the Estuary collection.

Choosing the Wrong Snowflake Update Mode

Standard updates and delta updates have different tradeoffs. Use standard updates when Snowflake should represent the latest state of each row. Use delta updates only when append-style event data is appropriate.

Skipping Snowflake Cost Controls

Configure warehouse auto-suspend and materialization sync schedules to avoid unnecessary compute costs.

Not Testing Downstream Queries

A successful data load does not guarantee that dashboards, transformations, or applications behave correctly. Validate downstream queries before cutover.

Conclusion

SQL Server to Snowflake integration can be handled with a managed CDC pipeline, Snowflake Openflow, or with file-based methods such as Snowpipe and SnowSQL. The right method depends on your latency requirements, change volume, schema complexity, cost controls, and how much operational work your team wants to manage.

Manual methods can work for one-time transfers or scheduled batch loads, but they require separate export jobs, staging files, orchestration, monitoring, and validation. They are not the same as continuously capturing SQL Server inserts, updates, and deletes.

Estuary is a strong fit when Snowflake needs to stay current as SQL Server changes. It can capture historical rows and ongoing changes from SQL Server, then materialize them into Snowflake using standard updates or delta updates with Snowpipe Streaming. For production workloads, validate CDC enablement, keys, update mode, Snowflake authentication, warehouse cost settings, and downstream queries before cutover.

For teams evaluating production replication, the safest next step is to test the pipeline with a representative table, validate backfill and CDC behavior, and confirm Snowflake cost and query performance before cutover.

Further Reading:

• How to Migrate from SQL Server to Snowflake – An in-depth, practitioner-written guide from Seattle Data Guy covering real-world migration planning, architecture differences, and tooling considerations