Data Warehouse Migration Planning That Won’t Stall

Your team needs a data warehouse migration plan that finishes. If you’re moving from Redshift or Postgres to Snowflake or BigQuery, here’s a program you can run in-house to migrate your data warehouse without leaving half your stack on life support. It covers inventory and dependency mapping, SQL translation, parallel-run validation and reconciliation, cutover sequencing, and decommissioning. It also calls out the reconciliation step most teams skip—the one that leads to double bills and confidence erosion. If you want a partner, Vertex Data Consulting (run by Eric Provencio) can lead or bolster your team. If you want to do it yourself, use this as your data warehouse migration strategy.

What a successful migration looks like (and why it stalls)

A successful data warehouse migration ends with one trusted warehouse, all jobs running on the destination, and the old platform shut down. No dual bills, no hidden Airflow DAGs, no BI workbooks pointed at legacy clusters. In plain English: warehouse migration is the process to copy data from one data warehouse environment to another, translate logic, prove equivalence, and re-point every producer and consumer. A data warehouse migration involves more than bulk loads—it demands inventory, SQL rewrite rules, validation, and a disciplined cutover.

Where teams stall: they skip reconciliation. They bulk load, translate the easy SQL, migrate a few dashboards, then hit differences—window functions behave differently, timestamps shift, or default casts change. Engineers keep the old system warm “just in case,” and you land in a half-migrated state that doubles spend and attention. The antidote is a program that treats parity as a deliverable with exit criteria and owners.

Two framing notes for stakeholders:

• Migration is the process of moving workloads, schemas, and schedules with controlled risk, not just data transfer.
• Pick a cloud data warehouse (Snowflake or BigQuery) for analytics engineering. Decide now what will not be migrated (dead tables, legacy reports).

Document your data migration strategy up front: scope (ingest, transforms, orchestration, BI), guardrails for sensitive data, and a rollback policy that prevents data loss. This is table stakes for a successful migration.

Program plan and phase gates (with exit criteria)

Treat the effort as a program with artifacts and hard exits. This is migration best practices applied as operations.

Phase 0 — Charter

• Target: Snowflake or BigQuery; environments; compliance needs; on-call policy.
• Define success metrics, a single cutover authority, and a calendar freeze window.
• Exit: signed scope, migration plan, rollback steps, and owner list.

Phase 1 — Inventory & Sequencing

• Map every object, schedule, and consumer. Measure data volume and change rate per table.
• Exit: sequenced backlog with risk tags and owners.

Phase 2 — Foundation

• Provision the target warehouse environment(s), roles, data governance, secrets, and network. This is core data infrastructure.
• Set up CI/CD, cost monitors, and a reconciliation schema.
• Exit: security review passed; test runs green.

Phase 3 — Bulk Load & Translation

• Load history; translate SQL/dbt; replace engine-specific features.
• Exit: 90% of tables built in the destination; unit tests green.

Phase 4 — Parallel Run & Reconciliation

• Run both stacks; compare counts, checksums, and KPIs; fix drift.
• Exit: drift SLA met for 14 days; signed KPI parity.

Phase 5 — Cutover

• Flip producers and consumers in sequence; keep the old read-only for a window.
• Exit: all jobs and BI connections pointed at the target.

Phase 6 — Decommission

• Archive snapshots; revoke access; drop clusters; stop billing.
• Exit: formal report and shutdown confirmation.

If orchestration or developer workflow also changes, see our dbt Cloud migration and dbt repo performance pages for patterns we won’t re-teach here. For adjacent warehousing topics, browse our warehousing articles.

Inventory and dependency mapping that holds under load

Don’t move data until you know who depends on it and how it behaves. Inventory must cover the data warehouse schema, transforms, schedules, downstream assets, and operational patterns. This is data engineering work—do it once, do it right.

• Catalog objects: tables, views, matviews, UDFs, stored procedures, external tables, file stages, and secrets.
• Lineage: dbt manifest.json, Airflow DAGs, ad hoc runners, notebooks, ML jobs, reverse ETL.
• Operational profile: data volume, update frequency, data types, SLA/SLO, incident history, and owners.

Surface dependencies and change rate in Postgres/Redshift:

-- Objects and dependencies
SELECT dep.nspname AS dependent_schema,
       dep.relname AS dependent_object,
       src.nspname AS source_schema,
       src.relname AS source_object
FROM pg_depend d
JOIN pg_class depc ON d.refobjid = depc.oid
JOIN pg_class srcc ON d.objid = srcc.oid
JOIN pg_namespace dep ON depc.relnamespace = dep.oid
JOIN pg_namespace src ON srcc.relnamespace = src.oid
WHERE dep.relnamespace > 0 AND src.relnamespace > 0;

-- Approximate change rate per table (7 days)
SELECT schemaname, relname,
       n_tup_ins + n_tup_upd + n_tup_del AS ops_total
FROM pg_stat_all_tables
ORDER BY ops_total DESC
LIMIT 50;

Capture owners and tests in dbt:

# models/schema.yml
version: 2
models:
  - name: fct_orders
    description: Fact powering revenue dashboards
    config:
      owner: revops
      tags: [critical, daily]
    tests:
      - unique: {column_name: order_id}
      - not_null: {column_name: order_id}

List every BI connection and service account; cutover surprises hide there. For each critical dataset (e.g., a 40M-row orders table on an X-Small warehouse), note average runtime, peak concurrency, and downstream dashboards. Flag legacy data you can retire. If you don’t measure run cost yet, start now. This inventory becomes your sequenced migration backlog and de-risks migrating a data warehouse under real load.

SQL translation and data warehouse schema deltas

Most pain comes from implicit differences between engines. Write down translation rules and centralize them in macros. Common deltas and how to handle them:

Feature	Redshift/Postgres	Snowflake	BigQuery	Translation note
Distribution/Sort	DISTKEY/SORTKEY	Micro-partitions; CLUSTER BY	Partition + CLUSTER BY	Drop DIST/SORT; consider CLUSTER BY on large tables
Sequences/Identity	SERIAL/IDENTITY	SEQUENCE or AUTOINCREMENT	GENERATE_UUID()	Prefer surrogate keys in dbt
JSON	json/jsonb operators	VARIANT + : path	JSON + JSON_VALUE/SAFE ops	Use explicit casts
Time zones	timestamptz semantics	UTC by default	UTC by default	Normalize to UTC before load
Strings	|| concat	|| or CONCAT	CONCAT	Prefer CONCAT for portability
Distinct on	SELECT DISTINCT ON (...)	Window + QUALIFY	Window + QUALIFY	Rewrite with ROW_NUMBER

Example rewrite for “latest per customer”:

-- Redshift/Postgres
SELECT DISTINCT ON (customer_id) customer_id, status, updated_at
FROM customer_status
ORDER BY customer_id, updated_at DESC;

-- Snowflake/BigQuery
SELECT customer_id, status, updated_at
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customer_status
)
QUALIFY rn = 1;

Macro centralization:

{% macro bool_to_int(col) %}
  {% if target.type == 'snowflake' %} IFF({{ col }}, 1, 0)
  {% elif target.type == 'bigquery' %} IF({{ col }}, 1, 0)
  {% else %} CASE WHEN {{ col }} THEN 1 ELSE 0 END
  {% endif %}
{% endmacro %}

Incremental model configuration:

-- models/fct_orders.sql
{{ config(materialized='incremental', unique_key='order_id', on_schema_change='sync') }}
SELECT ... FROM ...
{% if is_incremental() %}
  WHERE updated_at > (SELECT max(updated_at) FROM {{ this }})
{% endif %}

BigQuery: prefer SAFE. functions during backfills to prevent hard failures. Snowflake: avoid CREATE OR REPLACE on critical views during parallel-run to protect the legacy side. Keep a mapping doc from feature/function to replacement; that doc plus macros is your portable style guide. This is one of the quiet data warehouse migration best practices that saves a week later.

Bulk load, data extraction, and change capture

Choose methods based on constraints, not fashion. For history, use high-throughput data extraction and bulk loads; for churn, add CDC. Your goal is to transfer data predictably, not to re-architect mid-flight.

• Historical bulk: UNLOAD from Redshift/Postgres to S3 or GCS; stage; COPY/LOAD into Snowflake/BigQuery. Partition by date/hour; compress (GZIP/SNAPPY); use Parquet when possible to reduce data transfer.
• CDC: native logs (AWS DMS as a migration service), Debezium/Kafka, Snowpipe + auto-ingest, or BigQuery Datastream/Data Transfer. Pick the least stateful option that meets your latency needs.
• Safety: keep raw files in object storage for rollback. This reduces risk of data loss and helps if you later adopt a data lake.

Segmented unload/load in parallel for throughput:

import concurrent.futures
import subprocess

PARTS = [f"2026-06-{d:02d}" for d in range(1, 31)]

def unload(date):
    sql = f"""
        UNLOAD ('SELECT * FROM public.orders WHERE order_date = DATE '{date}'' )
        TO 's3://my-bucket/migrate/orders/dt={date}/part_'
        CREDENTIALS 'aws_access_key_id=...;aws_secret_access_key=...'
        PARQUET;
    """
    return subprocess.run(["psql", "-c", sql], check=True)

def load_to_snowflake(date):
    sql = f"""
        COPY INTO analytics.orders
        FROM @migrate_stage/orders/dt={date}
        FILE_FORMAT=(TYPE=PARQUET) MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE;
    """
    return subprocess.run(["snowsql", "-q", sql], check=True)

with concurrent.futures.ThreadPoolExecutor(max_workers=8) as ex:
    list(ex.map(unload, PARTS))
    list(ex.map(load_to_snowflake, PARTS))

On BigQuery, use batch LOAD DATA from GCS with URIs per partition. Validate row counts per partition as you go. Don’t launch a tooling bake-off mid-flight; finish the move, then optimize. If you’re doing a warehouse to the cloud move from an on-premises data warehouse, test network throughput and egress before bulk jobs. The same applies to on-premises data sources feeding the destination.

Which data warehouse migration tools? Use what fits your constraints: AWS DMS, Datastream, native copy utilities, or a simple Python process. Pick the simplest migration tool that meets SLAs. This is classic cloud migration work—pragmatism wins.

Parallel-run validation and reconciliation (the step teams skip)

This is where most outcomes are decided. Treat reconciliation as a product: a ledger of comparisons, clear variance thresholds, owners, and sign-offs. The goal is to prove migrated data behaves the same where it matters.

Compare what matters

• Row counts per table and partition.
• Deterministic hashes of primary keys or full rows.
• Top KPIs at report grain.
• Edge cases (NULL-heavy, sparse, high-cardinality).

Example counts and hashes:

-- Snowflake
SELECT COUNT(*) AS cnt,
       MD5(TO_VARCHAR(MAX(order_id))) AS sample_hash
FROM analytics.orders
WHERE order_date BETWEEN '2026-06-01' AND '2026-06-30';

-- BigQuery
SELECT COUNT(*) AS cnt,
       TO_HEX(MD5(STRING(ANY_VALUE(order_id)))) AS sample_hash
FROM `analytics.orders`
WHERE order_date BETWEEN '2026-06-01' AND '2026-06-30';

Reconciliation ledger in the destination:

CREATE TABLE IF NOT EXISTS analytics_audit.recon_results (
  dataset STRING,
  partition_key STRING,
  src_count INTEGER,
  tgt_count INTEGER,
  diff INTEGER,
  src_checksum STRING,
  tgt_checksum STRING,
  status STRING,
  checked_at TIMESTAMP
);

Automate with dbt tests so failures block cutover:

# tests/reconciliation.yml
version: 2
models:
  - name: fct_orders
    tests:
      - dbt_utils.equality:
          compare_model: ref('fct_orders_legacy')
      - not_null:
          column_name: order_id

Schedule KPI parity checks:

WITH old AS (
  SELECT SUM(revenue) rev FROM legacy.fct_revenue WHERE ds > CURRENT_DATE - 7
), new AS (
  SELECT SUM(revenue) rev FROM analytics.fct_revenue WHERE ds > CURRENT_DATE - 7
)
SELECT old.rev, new.rev, ABS(old.rev - new.rev) AS diff;

Write down acceptable variance (floating-point wobble, late-arriving rows) and require product-owner sign-off. This is how you catch real data quality issues before users do. For a deeper testing playbook, see our data quality testing strategy.

Cutover sequencing, rollback, and decommissioning

Cutover is not a big red switch. It’s a sequence with recovery built in.

Sequence

1. Freeze schema changes on the legacy side.
2. Switch CDC to land in the destination while keeping the legacy updated until validation clears.
3. Flip internal consumers (dbt/jobs), then BI tools, then any external APIs.
4. Make the legacy read-only for 24–72 hours.

Feature flag the read path during the window:

# app/config.yml
warehouse_read_source: new  # values: old | new

# usage in a service
if cfg['warehouse_read_source'] == 'new':
    query = NEW_SQL
else:
    query = OLD_SQL

Rollback: keep raw files, preserve CDC bookmarks, and maintain dual-target DAG templates until the sign-off window closes. Rotate secrets at cutover to meet data security requirements. When done, decommission quickly: archive snapshots to durable data storage, revoke credentials, drop clusters, and stop billing. Keep a small, documented archive for compliance and truly necessary legacy data.

To avoid lock-in on the destination, keep raw/stage files in Parquet, write portable SQL in dbt, and isolate external system interfaces behind services. That leaves an option to migrate your data again later or adopt a data lake for heavy workloads.

FAQ: migration strategies, tools, and avoiding lock-in

What is data warehouse migration?

It’s the end-to-end effort to move a data warehouse environment to another platform, prove equivalence, and shut the old off. A successful data warehouse migration ends with one source of truth.

What are the types of data warehouse migration?

Common types of data warehouse migration in practice: rehost (lift-and-shift tables/SQL), replatform (adopt equivalent features), refactor (redesign models/process), and replace (retire and rebuild). Mix strategies by domain.

Is ETL the same as data migration?

No. ETL/ELT are ongoing pipelines; migration is a time-bound move. You’ll use ETL/ELT to transfer data, but cutover and decommission make it a migration project.

Which tool is best for data migration?

There’s no single winner. Evaluate AWS DMS (log-based CDC), Snowpipe/Auto-ingest, BigQuery Datastream/Data Transfer, or a lean Python process. Choose the simplest data warehouse migration tools that satisfy latency and reliability. If timelines are tight, bring in a trusted migration service.

Are you enabling ML/AI by moving?

Often yes. BigQuery ML and Snowflake external functions become practical once you migrate. If you plan Slack-native agents or an internal AI platform, the destination can act as a feature store after stabilization.

What problems should be addressed first?

Clean dirty or redundant datasets before you migrate your data. Quarantine existing data with unclear lineage, document owners, and add tests to prevent reintroducing data quality regressions.

Batch vs real-time?

Do bulk history first, then add CDC where change rate and SLAs warrant it. Real-time everywhere is not a default; let business need drive it.

Why migrate your warehouse to the cloud, and how to avoid lock-in?

Elastic compute and managed services suit modern data workloads. To avoid lock-in, keep raw layers in open formats, prefer portable SQL, and don’t bury core business rules in proprietary UDFs.

Career angle: transitioning into data engineering?

Owning a migration end-to-end (inventory, SQL rewrite, validation, cutover) is excellent experience. Ship one successfully and your credibility in data engineering jumps.

---

If you want help pressure-testing your plan or need a hands-on lead to migrate your data without stalls, we can partner alongside your team. We also have playbooks for dbt + Airflow integration—see running dbt from Airflow—and can stand up leadership-ready metrics fast via Business Health Reporting. Ready to move? Start a project with Vertex.