Migrating a SQL Server data warehouse to Snowflake: full methodology

Why Snowflake and not Fabric?

Before discussing methodology, let’s clarify the target choice. Snowflake and Fabric are both excellent platforms, but they address different needs. Snowflake is the preferred choice when the organisation wants independence from a single cloud vendor, fine-grained cost control (pay-per-query), or when the data engineering team prefers SQL + DBT.

Fabric is the natural choice for organisations already deeply integrated into the Microsoft ecosystem (Azure AD, Power BI, Teams). The capacity-based licensing model is also simpler to budget for some IT departments.

If you’re still undecided, the audit of your existing estate will give you the objective data to make the call.

Phase 1: Comprehensive inventory

Every migration starts with an inventory of what exists. And this is precisely where most projects derail. Manual inventory (“we ask each team to list what they use”) is always incomplete, always biased, and always takes longer than planned.

The reverse-engineering approach is fundamentally different. We analyse the source code — DTSX files from SSIS packages, T-SQL scripts from stored procedures, SQL Server Agent job definitions — to reconstruct the real map of the estate.

What it produces: the exact number of packages, dependencies between them, source and destination tables, applied transformations, environment variables, and a technical debt score for each component.

Phase 2: Strategic triage

The inventory almost always reveals surprises. In our experience, 15-25% of the SSIS estate is obsolete — packages that still run but whose results are consumed by nobody. Decommissioning them before migration reduces scope, cost, and risk.

The remaining components are classified into three categories: automatically convertible (standard cases — data flows with classic sources, transformations and destinations), convertible with manual rework (complex cases — dynamic T-SQL, cursors, cross-database references), and requiring full rewrite (custom Script Tasks, third-party components).

Phase 3: The conversion

Automated conversion is the lever that changes the project’s economics. Rather than having each package manually rewritten by a consultant, the converter parses the DTSX, extracts business logic, and generates equivalent code in DBT models and Snowflake SQL.

The automatic conversion rate varies by estate complexity. On projects we’ve delivered, it ranges from 60% to 80%. The remaining 20-40% requires manual intervention — but the converter provides a skeleton that accelerates manual work by 2-3x.

Phase 4: Parallel validation

This is the most underestimated yet most critical phase. The principle: running the old and new systems in parallel, comparing outputs row by row, and only cutting over once the results match perfectly.

Each discrepancy is classified into three categories: conversion bug (the generated code has an issue), expected behavioural difference (Snowflake handles certain operations differently from SQL Server — rounding, NULL handling, collation), or pre-existing legacy bug.

This last case is more common than you’d think. On a recent project, we discovered 2 calculation errors that had been present in the legacy for years. The migration revealed them because DBT’s automated tests checked invariants that had never been verified before.

Phase 5: The switchover and hyper-care

The cutover happens in waves — never big bang. Each batch (typically 20-50 components) goes live after validation. The legacy system stays active in read-only mode for 2 weeks (hyper-care) to allow immediate rollback if needed.

Knowledge transfer to internal teams happens during this phase. The advantage of DBT is that the code is standard SQL version-controlled under Git — any SQL analyst can read and modify it, unlike SSIS packages which require specialised tooling.

Orders of magnitude

To calibrate your expectations, here are the benchmarks we observe in practice. An estate of 100-200 SSIS packages of average complexity migrates in 6-10 weeks. An estate of 200-500 packages in 10-16 weeks. Beyond that, phased migration with parallel execution is essential.

In terms of budget, automated conversion reduces cost by 2-3x compared to manual rewriting. The upfront audit reduces scope by 15-25%. Combined, these two approaches make fixed-price engagements possible — the client knows the total budget before signing.