SQL Server to Snowflake migration: replication, CDC, and data type mapping

How do you migrate from SQL Server to Snowflake?

You migrate SQL Server to Snowflake in four passes: convert the schema, backfill the data once, keep the warehouse current with incremental loads, then move the reports and turn the old feed off. Each pass is reversible on its own, which is what lets you cut over on a Tuesday afternoon instead of burning a weekend and hoping.

Start with the schema, because it decides everything downstream. Pull the table and view definitions out of the source catalog, translate each column to its Snowflake type, and review the handful of columns where the translation is a judgment call rather than a lookup. Snowflake ships SnowConvert to automate the bulk of the T-SQL data definition conversion, and it handles the mechanical parts well. It cannot tell you whether a DECIMAL(18,2) column named Amount is money that must never round or a quantity that can. That is your call, and it is worth an hour with whoever owns the reports.

The second pass is the backfill. Extract each table once and load it. For a few hundred million rows this is a bulk export to files, staged into Snowflake and loaded with COPY INTO. The practical advice is to run the extract against a secondary replica or an availability group readable secondary rather than the primary, because a full table scan of your largest fact table will compete with the transactions that pay the bills.

The third pass is where migrations actually live. Between the backfill and the cutover you need SQL Server and Snowflake to agree, which means loading only the rows that changed since the last run. That is the part teams underestimate, and it is what the SQL Server to Snowflake connector exists to own: the watermark bookkeeping, the staging table, the merge, and the retry behavior when a batch dies halfway.

The fourth pass is the boring one nobody plans for. Reports, extracts, stored procedure schedules, and that Access database in accounting all point at SQL Server. Before you decommission anything, work out what actually reads each table. Column-level lineage across your warehouse and BI layer turns that from a month of asking around into a query, and it is the difference between retiring a table and discovering in March that quarterly close depended on it.

How do you connect SQL Server to Snowflake?

You connect SQL Server to Snowflake by putting an extract and load process between them, because neither database reads the other natively. Snowflake has no driver that queries a SQL Server table directly. Something in the middle authenticates to SQL Server over TDS, pulls rows, stages them as files, and issues the Snowflake load. That something is a managed connector, a scheduled script, or a streaming CDC service.

On the SQL Server side, create a dedicated login with SELECT on the tables you replicate and nothing else. If you plan to use change data capture, that login also needs access to the cdc schema functions. Allowlist the reader's egress addresses or run it inside your network, and point it at a replica. On the Snowflake side, create a role scoped to one database and one schema, a dedicated warehouse so the load credits are visible on their own line, and either a key pair or an OAuth integration for the service user. Password authentication for a service account is a finding waiting to happen in your next SOC 2 review.

Then pick how you read changes. There are four realistic options and they trade freshness against operational surface.

Method	Freshness	Sees deletes	Load on SQL Server	Use it when
Full reload	As often as the table size allows	Yes, by replacement	Heavy, a full scan every run	Small dimension tables and lookups
Modified-date watermark	Minutes to hours	No	Light, an index seek per run	The default for most tables
Change Tracking	Minutes	Yes	Light, synchronous overhead on writes	You need deletes but not column history
Change data capture	Seconds	Yes, with before and after images	Moderate, an agent job scanning the log	Auditing, slowly changing dimensions, near real time

Most teams should start at the watermark row and only move down when a specific requirement forces it. A watermark load needs an indexed ModifiedDate or rowversion column, a stored high-water mark, and a small lookback window so rows committed during the previous run are not skipped. That is an afternoon of work and it covers reporting perfectly well.

What is the SQL Server to Snowflake data type mapping?

Most SQL Server types have an obvious Snowflake equivalent: integers become NUMBER, NVARCHAR becomes VARCHAR, and DATE stays DATE. The mappings worth deciding deliberately are MONEY, the datetime family, and UNIQUEIDENTIFIER. Get those three wrong and the data still loads, which is exactly why the errors survive to production.

SQL Server type	Snowflake type	Notes
BIGINT, INT, SMALLINT, TINYINT	NUMBER(38,0)	Snowflake has one integer family. NUMBER defaults to precision 38, scale 0.
DECIMAL(p,s), NUMERIC(p,s)	NUMBER(p,s)	Keep the declared precision. Snowflake caps precision at 38 digits, which covers SQL Server's own maximum of 38.
MONEY, SMALLMONEY	NUMBER(19,4)	Both store four decimal places. Mapping either to FLOAT introduces rounding you will not notice until a reconciliation fails.
FLOAT, REAL	FLOAT	Fine for measurements. Never for currency.
BIT	BOOLEAN	Nullable BIT is three-valued in both systems, so nulls survive the trip.
CHAR, VARCHAR, NVARCHAR, NCHAR	VARCHAR	Snowflake stores all strings as UTF-8, so the national character distinction disappears. Default VARCHAR length is 16,777,216 bytes.
TEXT, NTEXT, VARCHAR(MAX)	VARCHAR	Snowflake's maximum VARCHAR length is 134,217,728 bytes, so oversized columns are rarely the constraint.
DATE	DATE	Direct.
DATETIME, SMALLDATETIME, DATETIME2	TIMESTAMP_NTZ	None of these carry a time zone. DATETIME rounds to roughly 3.33 millisecond increments, so values will not match a DATETIME2 source exactly.
DATETIMEOFFSET	TIMESTAMP_TZ	The only SQL Server type that actually knows its offset. Mapping it to TIMESTAMP_NTZ silently discards the zone.
TIME	TIME	Direct.
UNIQUEIDENTIFIER	VARCHAR(36)	Snowflake has no native GUID type. Normalize the case of the hex string on the way in or joins will miss.
VARBINARY, BINARY, IMAGE	BINARY	Consider whether the blob belongs in the warehouse at all, or whether a storage URL is the better column.
XML	VARCHAR or VARIANT	Snowflake has no XML type. Store the raw document as text, or shred it to JSON and land it in VARIANT so you can query paths.
JSON in NVARCHAR(MAX)	VARIANT	This is the upgrade worth taking. VARIANT lets you query keys directly instead of parsing strings in every report.
ROWVERSION, TIMESTAMP	Do not migrate	It is a row change marker, not a time. Use it to drive the incremental read and leave it out of the target.
HIERARCHYID, GEOGRAPHY, GEOMETRY	VARCHAR or GEOGRAPHY	Snowflake has GEOGRAPHY and GEOMETRY but the representations differ. Convert to well-known text on export.

One more thing that is not a type mapping but behaves like one: collation. SQL Server databases are frequently case insensitive, so 'ACME' and 'Acme' compare as equal. Snowflake string comparison is case sensitive by default. A dedupe or a join on customer name that worked for a decade in SQL Server can start producing two rows in Snowflake. Either set a collation on the Snowflake column or normalize case explicitly in the load.

How do you replicate SQL Server to Snowflake in real time?

Real time means change data capture. CDC reads the SQL Server transaction log and writes every insert, update, and delete into change tables that mirror the source columns, so a reader can consume an ordered stream of changes instead of re-querying the table. It is the only method that gives you deletes and before-and-after images of updates, which is what auditing and slowly changing dimensions need.

The mechanics are worth understanding before you enable it. CDC runs as two SQL Server Agent jobs, so the Agent service has to be running for anything to be captured. The capture job runs continuously and processes up to 1,000 transactions per scan cycle with a five second wait between cycles. The cleanup job runs daily at 2 AM and retains change table entries for 4,320 minutes, which is three days, deleting at most 5,000 entries per statement. Each change row carries an __$operation code where 1 is a delete, 2 an insert, 3 the update before image, and 4 the update after image.

Two limits catch people. A single source table can have at most two capture instances at once, which matters because adding a column to a tracked table does not add it to the existing capture instance. The capture process ignores columns that were not identified when CDC was enabled, so a new column appears in SQL Server and never shows up in Snowflake until someone creates a second capture instance with the new shape. Drop a tracked column and the change table starts returning nulls for it rather than failing.

The other limit is the one that pages you at 3 AM. When a database is enabled for change data capture, the log truncation point does not advance until the capture process has gathered the marked changes, even if the recovery model is simple. Running CHECKPOINT will not truncate the log. If the capture job stops and nobody notices, the transaction log grows until the volume fills, and the database that stops is your production one. Monitor the capture job and the log size together from the day you turn CDC on.

Change Tracking is the lighter alternative. It records that a row changed and which columns changed, without keeping the values, so a reader learns which primary keys to re-read and then queries the current row from the base table. It costs less than CDC and gives you correct deletes, but it cannot reconstruct history. If your only requirement is that deleted rows disappear from Snowflake, Change Tracking is usually the better trade.

Can Snowflake replace SQL Server?

Snowflake can replace SQL Server for analytics and reporting, but not for transactional workloads. Snowflake is built for scanning and aggregating large volumes with separate compute, and it has no equivalent of the single-row seeks, enforced foreign keys, and low-latency writes an application depends on. The realistic end state for most teams is both: SQL Server keeps running the application, Snowflake owns the reporting.

That distinction changes what a successful migration looks like. If you are moving a reporting database, a data mart, or a warehouse that already exists in SQL Server, the move is straightforward and the payoff is real. If you are trying to move the database behind an order entry system, you are not migrating, you are re-architecting an application, and the type mapping is the least of your problems.

What is the difference between Snowflake and SQL Server?

SQL Server is a general purpose relational database where storage and compute live on the same machine and you size that machine for the worst hour of the month. Snowflake separates storage from compute, so a heavy query runs on a warehouse you spin up for it and pay for by the second. That difference explains most of the others: no indexes to tune, no query hints, no maintenance windows, and a bill that tracks usage instead of licensed cores.

The differences that create migration work are the small ones. Snowflake speaks ANSI SQL, not T-SQL, so TOP n becomes LIMIT n, ISNULL becomes IFNULL or COALESCE, GETDATE() becomes CURRENT_TIMESTAMP(), + for string concatenation becomes ||, and DATEADD takes its arguments in a different order. Temp tables, table variables, and cursors all have analogues but they are not drop-in. Stored procedures do not port: Snowflake runs procedures in SQL Scripting, JavaScript, Python, Java, or Scala, and a large T-SQL procedure usually gets rewritten as a set of transformations rather than translated line by line.

How long does a SQL Server to Snowflake migration take?

A single reporting database with a few dozen tables and no stored procedure logic takes days. A departmental warehouse with a hundred tables and a few dozen reports takes roughly a quarter. The variable is almost never the data volume, because bulk loading is fast and parallel. It is the procedural code and the reports, because every T-SQL stored procedure, view, and SSIS package has to be read by a person who understands what it was for.

The fastest schedules come from cutting scope rather than working harder. Before you plan anything, count how many of the tables are actually queried, and how many of the reports were opened in the last ninety days. In most estates a large share of both are dead. Migrating them costs conversion effort, storage, and the meetings to validate output nobody reads.

Handling identifier case, the change that breaks the most reports

SQL Server stores identifiers with the case you typed and compares them according to the database collation, which is usually case insensitive. Snowflake resolves unquoted identifiers by folding them to uppercase, and treats double-quoted identifiers as exact. So a table created in Snowflake as CREATE TABLE Orders (OrderID INT) is physically named ORDERS with a column ORDERID, and a query that says SELECT "OrderID" FROM "Orders" fails with an object not found error even though it looks correct.

Pick one convention before the first table is created. The convention that causes the least pain is to create everything unquoted and let Snowflake fold it, then reference everything unquoted. Preserving the original mixed case by quoting every identifier is possible, but it means every query, every BI tool connection, and every future developer has to quote perfectly, forever. The teams that choose that path almost always regret it.

Making loads idempotent so a retry never duplicates a row

Any load that can run twice will run twice: a network blip, an agent restart, a manual backfill someone kicks off to fix a gap. The load has to be written so a replay produces the same table it would have produced once.

The pattern is three steps. Land the batch in a staging table. Deduplicate within the batch, keeping the latest version of each key, because a watermark window can easily pull two versions of the same row. Then MERGE from staging into the target matched on the primary key, updating when matched and inserting when not. Advance the stored watermark only after the merge commits, so a failure halfway through means the next run redoes the same window rather than skipping it.

Set the watermark back by a few minutes rather than to the exact maximum you read. SQL Server assigns a ModifiedDate when a statement runs, but the row does not become visible to your reader until the transaction commits. Without a lookback window, a long transaction can commit a row whose timestamp is already behind your watermark, and that row is never loaded. The overlap costs a handful of redundant rows per run, which the merge absorbs.

Cutting over without a reporting freeze

Run both systems in parallel. Once the backfill is loaded and incremental syncs are running, leave SQL Server as the system of record and rebuild the reports against Snowflake alongside the originals. For two weeks, compare them.

Compare the things a finance lead would ask about. Row counts per table per day. Sum of every money column by month, which is where a bad MONEY mapping shows up immediately. Distinct counts of the keys the reports group by, which surfaces the collation problem. Minimum and maximum timestamps per table, which surfaces a time zone error. Then a spot check of a handful of individual records read from both sides.

When the numbers agree for two consecutive closes, point the BI tool at Snowflake and leave the SQL Server feed running for another month. Rollback is then a connection string change rather than a restore. Only after that month do you disable the capture instances and drop the extract jobs. If you are consolidating other sources into the same warehouse afterwards, the same incremental pattern runs through the Postgres to Snowflake connector and the rest of the pair library, so the second source takes an afternoon rather than a quarter.

What this costs to run

Snowflake charges for storage and for the compute seconds your warehouses are running. A replication load is small compared to the queries analysts run against the result, so the load itself is rarely the line item. Give the load job its own extra-small warehouse with auto-suspend set to a minute so it is not paying for idle time, and keep it separate from the warehouse your BI tool uses. That way you can see what replication costs on its own and tune it.

The integration layer is where per-row pricing bites. Tools that bill by monthly active rows turn a large fact table into a bill that grows with your business rather than with the value you get from it. Adapters charges a flat monthly price and the same price at ten thousand rows and ten million, which is the whole point. Compare it against the alternatives in our breakdown of the best data integration tools, or read what drives the total in our guide to data integration cost.