How to Migrate from Snowflake to SQLServer

Galaxy Glossary

How do I migrate data from Snowflake to SQL Server?

Step-by-step process to export data from Snowflake and load it into Microsoft SQL Server with schema fidelity.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

Why migrate from Snowflake to SQL Server?

Teams often move to SQL Server when they need on-prem control, tighter integration with .NET apps, or reduced compute cost. SQL Server offers native replication, SSIS, and broad BI tool support while Snowflake excels at elastic scaling.

What preparation is required in Snowflake?

Create a read-only role, grant it SELECT on every target table, and stage sufficient warehouse credits. This limits accidental writes and guarantees consistent snapshots during export.

How do I export tables efficiently?

Use COPY INTO to unload each table into compressed CSV/Parquet on an external stage (S3, Azure Blob, GCS). Partition large tables by date columns to parallelize downloads.

How do I create matching schemas in SQL Server?

Generate DDL from Snowflake’s information_schema, then map datatypes: VARCHAR → NVARCHAR, NUMBER → DECIMAL, TIMESTAMP_NTZ → DATETIME2. Create tables in a dedicated schema to isolate the load.

How do I import the data files?

Use BULK INSERT, bcp, or OPENROWSET(BULK…) to load each CSV. Keep batch sizes ≤100 MB to avoid log bloat and use TABLOCK for faster inserts.

Can I validate row counts and checksums?

Compare COUNT(*) and CRC32 hashes between Snowflake and SQL Server. Store results in an audit table. Any mismatch triggers a reload of the affected partition.

What tools can automate the migration?

SQL Server Integration Services (SSIS), Azure Data Factory, and Flyway handle schema creation, file movement, and incremental loads. They also support retry logic and alerting.

Best practices for production cut-over?

Schedule a short read-only window in Snowflake, re-export delta data, replay CDC into SQL Server, then switch application connection strings. Keep Snowflake live as a fallback for 24 hours.

Why How to Migrate from Snowflake to SQLServer is important

How to Migrate from Snowflake to SQLServer Example Usage


-- Verify record counts after import
SELECT 'Customers'   AS table_name, COUNT(*) AS rows_in_sql
FROM   dbo.Customers
UNION ALL
SELECT 'Orders'      , COUNT(*) FROM dbo.Orders
UNION ALL
SELECT 'Products'    , COUNT(*) FROM dbo.Products
UNION ALL
SELECT 'OrderItems'  , COUNT(*) FROM dbo.OrderItems;

How to Migrate from Snowflake to SQLServer Syntax


-- 1. Unload from Snowflake
COPY INTO @my_stage/customers.csv.gz
  FROM Customers
  FILE_FORMAT=(TYPE=CSV FIELD_OPTIONALLY_ENCLOSED_BY='"' HEADER=TRUE)
  SINGLE=TRUE;

-- 2. Download file locally or to SQL Server host
GET @my_stage/customers.csv.gz file:///var/migration/

-- 3. Decompress and import with bcp
bcp ecommerce.dbo.Customers in "customers.csv" -S localhost -d ecommerce -U sa -P ****** -c -t"," -F 2 -E -b 10000

-- 4. Disable constraints during load
ALTER TABLE dbo.Customers NOCHECK CONSTRAINT ALL;

-- 5. Re-enable and validate
ALTER TABLE dbo.Customers WITH CHECK CHECK CONSTRAINT ALL;

Common Mistakes

Ignoring datatype mismatches – NUMBER(38,0) in Snowflake can overflow INT in SQL Server. Map to DECIMAL(38,0) or BIGINT before import to avoid truncation.
Importing with foreign keys enabled – BULK INSERT fails when child rows arrive before parents. Disable constraints during load, then re-enable and check.

Frequently Asked Questions (FAQs)

Is there a direct "COPY" from Snowflake to SQL Server?

No. You must unload to files or use a data pipeline tool. Direct cross-cloud copy is not supported.

Can I migrate incrementally?

Yes. Add UPDATED_AT columns, export only changed partitions, and use MERGE in SQL Server to upsert.

Will views and stored procedures transfer automatically?

Views can be scripted, but Snowflake JavaScript procedures need manual rewrites to T-SQL.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.