Snowflake Migration Strategy

Many organizations move data warehouses from older platforms to Snowflake. The move promises lower maintenance and flexible scaling, and it carries real risks when rushed. A clear strategy turns a stressful project into a series of small, verifiable steps.

Moving House as a Model

Moving to a new home works best with a plan. Owners list belongings, decide what to keep, pack by room, move in stages, and check every box on arrival. Data migration follows the same rhythm. Teams inventory objects, choose what to move, transfer in waves, and validate each wave.

MIGRATION PHASES
================

 +----------+   +----------+   +----------+   +----------+   +----------+
 | 1.ASSESS |-->| 2.DESIGN |-->| 3.MOVE   |-->| 4.VERIFY |-->| 5.CUTOVER|
 |          |   |          |   |          |   |          |   |          |
 | Inventory|   | Target   |   | Schema,  |   | Compare  |   | Switch   |
 | and scope|   | layout   |   | data,    |   | results  |   | users    |
 |          |   | and plan |   | code     |   |          |   | over     |
 +----------+   +----------+   +----------+   +----------+   +----------+

Phase 1: Assess

  • List databases, tables, views, stored code, and scheduled jobs.
  • Measure data volume and daily growth.
  • Identify unused tables that deserve retirement rather than migration.
  • Rank workloads by business importance and difficulty.

Phase 2: Design

Decide between lift-and-shift and redesign. Lift-and-shift moves objects with minimal change and finishes quickly. Redesign reshapes models to use Snowflake strengths and takes longer. Many teams lift-and-shift first and improve afterward.

APPROACH          SPEED     RISK     LONG-TERM VALUE
----------------  --------  -------  ---------------
Lift and shift    Fast      Low      Moderate
Redesign          Slow      Higher   High
Phased hybrid     Medium    Low      High

Plan warehouses, roles, databases, and naming rules before moving anything.

Phase 3: Move Schema, Code, and Data

Schema and Code

Source systems use their own SQL dialects. Conversion tools such as SnowConvert translate tables, views, and procedures, and engineers review the results by hand. Common differences include data types, date functions, and procedural syntax.

Historical Data

Export tables from the source system into compressed files in cloud storage, preferably Parquet or CSV, and load them with COPY INTO.

CREATE FILE FORMAT pq TYPE = PARQUET;

CREATE STAGE legacy_stage
  URL = 's3://my-migration-bucket/export/'
  STORAGE_INTEGRATION = s3_int
  FILE_FORMAT = pq;

COPY INTO sales_db.public.orders
FROM @legacy_stage/orders/
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Ongoing Changes

Source systems keep changing during migration. Change data capture tools, or Snowpipe with regular file drops, keep Snowflake current until cutover day.

Phase 4: Verify

Trust grows from evidence. Compare source and target at several levels.

-- Run the same query on both systems and compare
SELECT COUNT(*)            AS row_count,
       SUM(amount)         AS total_amount,
       MIN(order_date)     AS first_date,
       MAX(order_date)     AS last_date
FROM orders;

-- Snowflake fingerprint of the full table content
SELECT HASH_AGG(*) FROM orders;
CHECK LEVEL        WHAT IT PROVES
-----------------  ---------------------------------------
Row counts         No rows lost or duplicated
Column sums        Numbers survived conversion
Min and max dates  Ranges stayed intact
Sample rows        Text and special characters look right
Report outputs     Business results match the old system

Run old and new pipelines in parallel for a few weeks. Finance and operations teams compare final reports and sign off.

Phase 5: Cutover

  1. Freeze changes on the old system for a short window.
  2. Load the final change batch into Snowflake.
  3. Run last validation checks.
  4. Repoint dashboards, applications, and pipelines to Snowflake.
  5. Keep the old system read-only for a safety period.
  6. Retire the old system after a clean review.

Common Pitfalls

  • Migrating every table without checking whether anyone uses it.
  • Skipping security design and copying old permissions blindly.
  • Choosing oversized warehouses during testing and forgetting auto-suspend.
  • Ignoring time zone and data type differences.
  • Leaving no rollback plan for cutover day.

A Practical Scenario

A manufacturer runs a ten-year-old warehouse on its own servers. The team inventories four hundred tables and finds that one hundred fifty have not been queried in two years. They retire those tables, migrate the rest in three waves, and run old and new reports side by side for a month. Finance signs off on matching totals. Cutover happens on a quiet weekend, and the old system stays read-only for ninety days as a safety net.

Key Points

  • A migration moves through assess, design, move, verify, and cutover phases.
  • Lift-and-shift favors speed; redesign favors long-term value.
  • Cloud storage files and COPY INTO move historical data efficiently.
  • Row counts, sums, and HASH_AGG fingerprints verify data accuracy.
  • Parallel runs build confidence before cutover.
  • A written rollback plan protects the business on switch day.
Previous lesson
Back to course
Next lesson