Teradata gets the migration headlines, but the most common on-premises source we are asked to move is Microsoft SQL Server: a 2–20 TB data warehouse built over a decade in T-SQL, with SSIS packages feeding it, SSRS or Power BI reading it, and a hundred stored procedures nobody wants to rewrite by hand.
This tutorial is the playbook we use: how to assess the estate, what SnowConvert does and does not automate, how to map T-SQL constructs that have no Snowflake equivalent, how to keep the two systems in sync with CDC during a parallel run, and how to prove equivalence before you cut over.
Phase 0 — Inventory before you translate
You cannot scope a SQL Server migration from the schema alone. Pull four inventories first:
-- On SQL Server: object counts by type
SELECT type_desc, COUNT(*) AS objects
FROM sys.objects
WHERE is_ms_shipped = 0
GROUP BY type_desc
ORDER BY objects DESC;
-- Table sizes (the real migration volume)
SELECT s.name AS schema_name, t.name AS table_name,
SUM(p.rows) AS row_count,
SUM(a.total_pages) * 8 / 1024 AS size_mb
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1)
JOIN sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY s.name, t.name
ORDER BY size_mb DESC;
-- What is actually queried (Query Store, last 30 days)
SELECT TOP 200 qt.query_sql_text, SUM(rs.count_executions) AS execs
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
GROUP BY qt.query_sql_text
ORDER BY execs DESC;
The fourth inventory is human: who consumes each report, and which of those consumers can sign off on "the numbers match". Half of every migration schedule is sign-off, not SQL.
Expect the Query Store output to kill 30–60% of the estate. Tables that nothing has read in a year do not need to be migrated on day one; archive them to stage files and move on.
Phase 1 — What SnowConvert automates
SnowConvert (Snowflake's free migration tool, with a SQL Server/T-SQL source) parses DDL, views, functions, and stored procedures and emits Snowflake SQL plus a conversion report. Run it early, even on a partial extract, because the report is your estimate.
# Extract DDL and code, then convert
snowct sql-server \
--input ./extract/sqlserver \
--output ./converted/snowflake \
--report ./converted/report
What converts cleanly in our experience:
- Table and view DDL, including most data types
- Straightforward
SELECTlogic, CTEs, window functions,MERGE - Simple stored procedures, emitted as Snowflake Scripting (
EXECUTE AS CALLER) procedures IDENTITYcolumns, mapped to sequences orAUTOINCREMENT
What lands in the report as manual work, essentially every time:
| T-SQL construct | Snowflake approach |
|---|---|
#temp tables | Transient tables, CREATE TEMPORARY TABLE, or a CTE — the usual rewrite is a CTE |
| Table variables, cursors | Set-based rewrite; cursors in Snowflake Scripting work but perform badly on large sets |
MERGE with OUTPUT clause | MERGE plus a separate INSERT ... SELECT from a stream, or dynamic tables |
CLR functions, xp_cmdshell | Snowpark Python UDF/UDTF or an external access integration |
sp_send_dbmail, Agent jobs | Tasks, alerts, and external notifications |
| Linked servers | Shares, Openflow/connector ingestion, or external access integrations |
| Indexes, included columns, filtered indexes | Delete them. Clustering keys and the Search Optimization Service exist, but only reach for them after you measure |
NOLOCK hints, isolation levels | Delete them. Snowflake's snapshot isolation makes them meaningless |
datetime / datetime2 precision, SET DATEFIRST, collations | Explicit TIMESTAMP_NTZ(n), explicit DATE_TRUNC/DAYOFWEEKISO, and a collation decision per column |
Two mapping traps worth calling out:
- Case sensitivity and collation. SQL Server databases are frequently
CI(case-insensitive). Snowflake string comparison is case-sensitive by default. Either set a collation on the affected columns (VARCHAR(100) COLLATE 'en-ci') or normalise in the ELT layer. Skipping this produces joins that silently return fewer rows — the single most common cause of "the totals don't match" at validation time. NUMERICrounding and integer division. T-SQLINT / INTtruncates; so does Snowflake, but intermediateDECIMALscale rules differ. Cast explicitly in any financial calculation rather than trusting parity.
Phase 2 — Land the schema and the history
Target a three-layer landing: RAW (as-extracted, typed loosely), STAGING (cleaned/typed), MART (the models your reports read). Do not replicate the SQL Server schema verbatim into a single database — migrations that preserve the old layout preserve the old problems.
Bulk history load, in order of preference:
- Extract to Parquet into cloud storage,
COPY INTOfrom an external stage. Fastest and cheapest for multi-TB loads. Use a partitioned extract (by year, by key range) so failed chunks are re-runnable. - A managed connector (Openflow's database connectors, or Fivetran/Qlik/HVR if you already own one) for both snapshot and ongoing CDC.
- JDBC-based tools that stream rows — convenient, slow above a few hundred GB.
CREATE OR REPLACE FILE FORMAT ff_parquet TYPE = PARQUET;
CREATE OR REPLACE STAGE stg_sqlserver
URL = 's3://acme-migration/sqlserver/'
STORAGE_INTEGRATION = s3_migration_int
FILE_FORMAT = ff_parquet;
COPY INTO raw.sales_fact
FROM @stg_sqlserver/dbo/sales_fact/
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
ON_ERROR = ABORT_STATEMENT;
SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
TABLE_NAME => 'RAW.SALES_FACT', START_TIME => DATEADD(hour, -4, CURRENT_TIMESTAMP())));
Phase 3 — Keep both systems live with CDC
A cold cutover is only viable for small, low-churn warehouses. For everything else you run both systems in parallel for two to six weeks, which means change data capture from SQL Server into Snowflake.
Enable CDC on the source:
-- On SQL Server, per database and per table
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'sales_fact',
@role_name = NULL,
@supports_net_changes = 1;
Then pick a transport. Openflow's SQL Server connector, Debezium into Kafka plus Snowpipe Streaming, or a commercial connector all land the same shape: an append-only change feed in RAW. Apply it in Snowflake rather than letting the tool write to your marts directly — you want the apply logic versioned in your repo:
-- Append-only CDC landing table -> current-state table
CREATE OR REPLACE DYNAMIC TABLE staging.sales_fact
TARGET_LAG = '5 minutes'
WAREHOUSE = wh_elt
AS
SELECT * EXCLUDE (cdc_op, cdc_seq)
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY sale_id ORDER BY cdc_seq DESC) AS rn
FROM raw.sales_fact_cdc
)
WHERE rn = 1 AND cdc_op <> 'D';
Dynamic tables handle the dedupe-and-apply pattern with no task plumbing; see our dynamic tables tutorial for the trade-offs against streams and tasks.
Phase 4 — Prove equivalence
Validation is where migrations earn trust. Three levels, run nightly during the parallel period:
Level 1 — row counts and checksums per table.
SELECT 'sales_fact' AS table_name,
COUNT(*) AS row_count,
SUM(HASH(sale_id, sale_date, amount, status)) AS fingerprint
FROM mart.sales_fact
WHERE sale_date < CURRENT_DATE();
Run the equivalent CHECKSUM_AGG(BINARY_CHECKSUM(...)) on SQL Server. The hash functions differ between platforms, so compare column-by-column aggregates (SUM, MIN, MAX, COUNT(DISTINCT)) rather than trying to match hash values across engines.
Level 2 — report-level reconciliation. For each signed-off report, run the old query against SQL Server and the new model against Snowflake into a shared comparison table, and diff with a tolerance:
SELECT period, legacy_amount, snowflake_amount,
ABS(legacy_amount - snowflake_amount) AS diff
FROM validation.revenue_compare
WHERE ABS(legacy_amount - snowflake_amount) > 0.01
ORDER BY diff DESC;
Level 3 — data metric functions as a standing contract. Once a table passes, attach DMFs so drift is caught after cutover too:
ALTER TABLE mart.sales_fact
SET DATA_METRIC_SCHEDULE = 'USING CRON 0 6 * * * UTC';
ALTER TABLE mart.sales_fact
ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (customer_id);
Budget real calendar time here. Expect the first reconciliation run to show 5–15 mismatching reports, most of them traced to collation, rounding, time-zone handling, or an undocumented filter in a legacy view.
Phase 5 — Cutover and decommission
A cutover checklist that has survived several migrations:
- Freeze DDL on SQL Server (change control, not hope)
- Final CDC drain; confirm lag is zero via the connector's offset and a
MAX(updated_at)comparison - Repoint BI: Power BI datasets to the Snowflake connector, SSRS reports to replacement models or Streamlit apps
- Flip writers — SSIS packages retired, replaced by tasks/dynamic tables or your orchestrator
- Run both read paths for one business cycle with SQL Server read-only
- Revoke application access on SQL Server; keep a restorable backup for the audit retention period
- Decommission the hardware/licences — this is the line item that funds the project, so make sure finance sees the date
Sizing the effort
Rough field numbers for a 5 TB, 400-object SQL Server warehouse with a competent in-house team plus outside help:
- Assessment and SnowConvert report: 1–2 weeks
- Schema + history load: 2–3 weeks
- Code conversion and manual rewrites: 6–10 weeks (the long pole is always stored procedures and SSIS)
- Parallel run and validation: 4–6 weeks
- Cutover and decommission: 1–2 weeks
The estimates break when nobody owns report sign-off, when the source schema is replicated verbatim, or when CDC is deferred until late and the cutover window turns out to be four hours rather than four weeks.
PowderInsights runs SQL Server, Oracle, and Teradata migrations onto Snowflake — assessment, conversion, CDC parallel runs, and validation frameworks your auditors will accept. Tell us about your estate and we will scope it.