top of page

How to Build a Data Warehouse Migration Cutover Plan That Actually Works

Writer: Vexdata
Vexdata
10 minutes ago
12 min read


Data warehouse migrations fail in two places: in the months of work before cutover, and in the 48 hours of the cutover itself. Most teams plan the migration extensively and plan the cutover barely at all. They treat cutover as "point the BI tools at the new warehouse" and discover, usually at 2 AM on a Saturday, that they missed 14 things that need to be true before that switch can happen.


A data warehouse migration cutover plan is not a checklist — it's a sequenced protocol with go/no-go criteria, explicit rollback triggers, a communication tree, and a defined decision-making authority for each stage. The 48-hour window that most organizations use for cutover — a Friday night to Sunday morning window to minimize business disruption — is tight enough that every step must be planned in advance. There is no time during cutover to convene a meeting to decide what to do.


This guide walks through the full 48-hour protocol: what to validate and when, the 8 things that must be true before you flip the switch, how to structure the rollback decision, and specifically how automated data testing changes what's feasible in a compressed cutover window.


Who this is for: Data engineers and architects leading a migration from an on-prem warehouse or legacy cloud warehouse (Teradata, Netezza, Redshift, Synapse) to a modern platform (Snowflake, BigQuery, Databricks). The specifics use Snowflake and BigQuery as examples, but the framework applies to any platform migration.


T-minus 48 Hours: Pre-Cutover Validation

Starting 48 hours before your scheduled cutover window, the new warehouse should be fully loaded and idle — no more schema changes, no more data migrations. This is the validation phase.


What to Validate at T-48

Validation

How to Check

Pass Criteria

Row count parity by table

SELECT COUNT(*) on every table in both systems

< 0.01% difference

Schema parity

Compare INFORMATION_SCHEMA column lists

Zero column differences

Data type alignment

Compare column types across systems

No implicit type widening

Primary key uniqueness

COUNT(*) vs COUNT(DISTINCT pk) per table

Zero duplicates

NULL rate on key columns

NULL rate per column, both systems

Rate difference < 0.001%

Historical aggregate parity

Run 6 key business metrics on same date range

< 0.1% difference

Foreign key referential integrity

Check all FK relationships on new system

Zero violations

Index / clustering key setup

Confirm clustering columns on high-query tables

All clustering in place

-- Row count parity report across all tables
-- Run on old warehouse:
SELECT 'old_warehouse' AS system, table_schema, table_name, row_count
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY table_schema, table_name;

-- Run same query on new warehouse, then compare:
SELECT 
  o.table_name,
  o.row_count AS old_count,
  n.row_count AS new_count,
  n.row_count - o.row_count AS delta,
  ROUND(100.0 * ABS(n.row_count - o.row_count) / NULLIF(o.row_count, 0), 4) AS delta_pct,
  CASE WHEN ABS(n.row_count - o.row_count) / NULLIF(o.row_count, 0) < 0.0001 
       THEN 'PASS' ELSE 'FAIL' END AS status
FROM old_counts o
JOIN new_counts n USING (table_name)
ORDER BY delta_pct DESC NULLS LAST;

Decision rule: If any table fails row count parity at T-48, investigate and resolve before T-24. Do not proceed to T-24 with unresolved parity failures.


T-Minus 24 Hours: Freeze and Final Sync

At T-24 you enter a change freeze on the source system. No schema changes. No batch loads that don't have a mirror on the target. The only thing moving is data — and you need a plan for catching up any CDC (change data capture) backlog that accumulated during migration.


The Final Sync Protocol


1. Reference data lock

Freeze all lookup tables: country codes, product category mappings, status enums. These change infrequently but cause cascading join failures if they drift between source and target during the cutover window.

-- Snapshot reference tables at T-24 (run on source)
CREATE TABLE migration.ref_snapshot_t24 AS
SELECT 
  table_name,
  COUNT(*) AS row_count,
  MAX(updated_at) AS last_modified,
  MD5(STRING_AGG(CAST(id AS VARCHAR) || '::' || CAST(updated_at AS VARCHAR) ORDER BY id)) AS content_hash
FROM (
  SELECT 'product_categories' AS table_name, id, updated_at FROM ref.product_categories
  UNION ALL
  SELECT 'country_codes', id, updated_at FROM ref.country_codes
  UNION ALL  
  SELECT 'status_types', id, updated_at FROM ref.status_types
) ref_tables
GROUP BY table_name;

-- Verify target matches at T-24
SELECT 
  s.table_name,
  s.content_hash AS source_hash,
  t.content_hash AS target_hash,
  CASE WHEN s.content_hash = t.content_hash THEN 'MATCH' ELSE 'DRIFT_DETECTED' END AS status
FROM migration.ref_snapshot_t24 s
JOIN migration.ref_snapshot_target t USING (table_name);

2. CDC catch-up window

If you're using log-based CDC (Debezium, Fivetran, AWS DMS), confirm the replication lag at T-24. It must be under 15 minutes — otherwise the cutover window will be longer than planned while you wait for the target to catch up.

import boto3
from datetime import datetime, timezone

def check_dms_replication_lag(replication_instance_id: str, task_arn: str) -> dict:
    """Check AWS DMS replication task lag at T-24."""
    dms = boto3.client('dms')
    
    response = dms.describe_replication_tasks(
        Filters=[{'Name': 'replication-task-arn', 'Values': [task_arn]}]
    )
    
    task = response['ReplicationTasks'][0]
    stats = task.get('ReplicationTaskStats', {})
    
    lag_seconds = stats.get('ApplyLatency', 0)  # seconds behind source
    
    return {
        'task_status': task['Status'],
        'lag_seconds': lag_seconds,
        'lag_minutes': round(lag_seconds / 60, 1),
        'go_criteria_met': lag_seconds < 900,  # must be under 15 min
        'checked_at': datetime.now(timezone.utc).isoformat()
    }

3. Access control pre-staging

Do not create service accounts on cutover night. Create them now, test them, then disable them. On cutover you enable — you don't create.

Account Type

Action at T-24

Action at Cutover

ETL service accounts

Create + grant permissions

Enable + test connection

BI tool connections

Create read-only credentials

Swap connection string

Application DB users

Create with old data access

Grant new warehouse access

Audit/monitoring users

Create + validate log access

Verify alerts are firing


4. Notification staging

Draft and queue all cutover communications: stakeholder notification, BI downtime alert, upstream system hold request. Send none of them yet — but have them in drafts ready to send in 2 clicks.


T-Minus 4 Hours: Go/No-Go Criteria

Four hours before the cutover window opens, the migration team assembles for the go/no-go call. This is not a status meeting — it is a binary decision gate. Each criterion is either met or it isn't. If any single criterion fails, you delay.


The 8 Go/No-Go Gates

#

Criterion

How to Verify

Hard Threshold

1

Row count parity

Run parity script across all tables

< 0.01% variance on all tables

2

CDC replication lag

Check DMS/Fivetran lag metric

< 15 minutes at T-4

3

Performance baseline

Run top-20 query set on new system

< 120% of source system P95

4

Rollback tested

Full rollback drill completed

Rollback < 2 hours confirmed

5

All service accounts created

Test connections on all accounts

Zero connection failures

6

Monitoring live

Alerts firing to correct channels

Test alert received

7

Stakeholder sign-off

Written OK from data owners

Sign-off in writing

8

On-call coverage confirmed

All response engineers available

4-person team confirmed

# Automated go/no-go checker — run at T-4
from dataclasses import dataclass
from typing import List
import json

@dataclass
class GoNoGoCheck:
    name: str
    result: str  # 'PASS' or 'FAIL'
    value: str
    threshold: str
    blocking: bool = True

def run_gonogo_checks(source_conn, target_conn) -> dict:
    checks = []
    
    # Gate 1: Row count parity
    parity = check_row_count_parity(source_conn, target_conn)
    max_variance = max(abs(t['delta_pct']) for t in parity['tables'])
    checks.append(GoNoGoCheck(
        name='Row count parity',
        result='PASS' if max_variance < 0.01 else 'FAIL',
        value=f'{max_variance:.4f}% max variance',
        threshold='< 0.01%'
    ))
    
    # Gate 2: CDC lag
    lag = check_replication_lag()
    checks.append(GoNoGoCheck(
        name='CDC replication lag',
        result='PASS' if lag['lag_minutes'] < 15 else 'FAIL',
        value=f"{lag['lag_minutes']} minutes",
        threshold='< 15 minutes'
    ))
    
    # Gate 3: Query performance
    perf = run_query_benchmark(target_conn)
    checks.append(GoNoGoCheck(
        name='Query performance baseline',
        result='PASS' if perf['p95_ratio'] < 1.20 else 'FAIL',
        value=f"{perf['p95_ratio']:.2f}x source P95",
        threshold='< 1.20x source'
    ))
    
    all_pass = all(c.result == 'PASS' for c in checks if c.blocking)
    decision = 'GO' if all_pass else 'NO-GO'
    
    return {
        'decision': decision,
        'checks': [vars(c) for c in checks],
        'blocking_failures': [c.name for c in checks if c.result == 'FAIL' and c.blocking],
        'timestamp': datetime.now(timezone.utc).isoformat()
    }

What Happens on a NO-GO


A NO-GO at T-4 is not a failure — it's the system working. You have 4 hours to resolve the blocking issue and call a second go/no-go. If you can't resolve within 2 hours, declare a full delay and push the cutover window to the next scheduled date.


Common NO-GO causes and resolution times:

Cause

Typical Resolution Time

Action

CDC lag > 15 min

1–2 hours

Let lag drain, re-check

Row count mismatch on 1 table

2–4 hours

Reload that table, re-validate

Performance regression on key query

4–8 hours

Optimize or remodel, likely delay

Service account connection failure

30 min

Fix credentials, re-test

Stakeholder sign-off not received

Variable

Escalate to executive sponsor


The Cutover Window: Sequence of Operations

The cutover window is typically scheduled for a weekend night, starting at midnight. You have a defined end time — often Sunday 6 AM — after which business operations resume regardless. Every minute of the window is accounted for before you open it.


The Cutover Runbook (Hour by Hour)

Time

Action

Owner

Rollback Trigger

T+0:00

Stop all writes to source system; confirm write queue drained

Data Eng Lead

Any writes still flowing at T+0:15

T+0:15

Final CDC sync; wait for lag to reach zero

CDC Engineer

Lag not zero by T+0:45

T+0:45

Take final row count snapshot on source

Data Eng

N/A (informational)

T+1:00

Switch connection strings for ETL jobs to point to target

Platform Eng

Any ETL failure on first run

T+1:15

Run parallel validation: query source and target with same queries

QA Lead

> 0.01% result difference

T+2:00

Switch BI tool connections to target

BI Lead

Any broken dashboard at T+2:30

T+2:30

Enable application read connections to target

App Lead

Any application error in logs

T+3:00

Run full smoke test suite

QA Lead

Any smoke test failure

T+4:00

Monitoring confirmation: alerts, logs, anomaly detection

DevOps

Any alert gap identified

T+5:00

Decision point: GO LIVE or ROLLBACK

All Leads

Default rollback if no decision


Parallel-Run Validation


During the T+1:15 to T+2:00 window, you run parallel queries against both systems and compare results. This is your last chance to catch data correctness issues before switching BI and application traffic.

-- Parallel validation query template
-- Run on SOURCE:
SELECT
  'source' AS system,
  DATE_TRUNC('day', order_date) AS day,
  COUNT(*) AS order_count,
  SUM(order_total) AS revenue,
  AVG(order_total) AS avg_order_value,
  COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY 2
ORDER BY 2;

-- Run identical query on TARGET, then compare:
SELECT
  s.day,
  s.order_count AS source_orders,
  t.order_count AS target_orders,
  s.revenue AS source_revenue,
  t.revenue AS target_revenue,
  ROUND(100.0 * ABS(s.revenue - t.revenue) / NULLIF(s.revenue, 0), 4) AS revenue_variance_pct,
  CASE 
    WHEN ABS(s.revenue - t.revenue) / NULLIF(s.revenue, 0) < 0.0001 THEN 'PASS'
    ELSE 'FAIL'
  END AS validation_status
FROM source_results s
JOIN target_results t USING (day)
ORDER BY day;

Any FAIL in the parallel validation immediately triggers the rollback procedure. Do not continue to the BI switchover with unresolved validation failures.


Communication During the Window


Designate a single communications lead. Every 30 minutes, a status update goes to a shared channel: what was completed, what's next, any blockers. This prevents the cascade of individual status requests that consume engineering bandwidth during the most critical window of the migration.


The Rollback Decision Tree

Rollback is not a sign of failure — it's a sign of discipline. The teams that struggle with migrations are the ones that push through red flags hoping things will resolve. Define your rollback triggers before the window opens, and commit to following them.


Automatic Rollback Triggers (No Discussion Required)


These conditions trigger an immediate rollback decision. No vote, no extended debate. Any lead can call it.

  • Parallel validation shows > 0.01% revenue variance on any day in the comparison window

  • Any ETL job produces zero rows to the target (not reduced rows — zero)

  • Critical dashboard shows NULLs or zeros where data is expected

  • Smoke test failure rate > 5% of total tests

  • CDC lag exceeds 60 minutes during the cutover window (target is falling behind live source)

  • Any data loss detected (row count on target < row count on source by > 0.001%)


Conditional Rollback Triggers (Requires Team Decision)


These require a 15-minute team assessment. If the root cause cannot be identified and resolved in 15 minutes, default to rollback.

  • Query performance regression > 150% of source P95 on any business-critical query

  • Single application integration failure (not all integrations, just one)

  • Monitoring gap detected (some alerts not firing)

  • Stakeholder reports incorrect data in production report


The Rollback Execution Protocol

# Rollback execution checklist — run in this exact order

ROLLBACK_STEPS = [
    {
        'step': 1,
        'action': 'Stop all writes to target system',
        'command': 'aws dms stop-replication-task --replication-task-arn {task_arn}',
        'verification': 'Confirm DMS task status = stopped',
        'time_budget_minutes': 5
    },
    {
        'step': 2, 
        'action': 'Revert ETL connection strings to source system',
        'command': 'kubectl set env deployment/etl-workers DB_HOST=$SOURCE_HOST',
        'verification': 'Check first ETL run completes successfully on source',
        'time_budget_minutes': 10
    },
    {
        'step': 3,
        'action': 'Revert BI tool connections to source',
        'command': 'Run BI connection restore script: ./scripts/restore_bi_connections.sh',
        'verification': 'Open 5 key dashboards, confirm data is showing',
        'time_budget_minutes': 15
    },
    {
        'step': 4,
        'action': 'Revert application database connections to source',
        'command': 'Update app config: DB_WAREHOUSE_URL back to source endpoint',
        'verification': 'Application health check passes, no DB connection errors in logs',
        'time_budget_minutes': 10
    },
    {
        'step': 5,
        'action': 'Resume CDC replication from source for future retry',
        'command': 'Restart DMS task in full-load-and-cdc mode',
        'verification': 'DMS shows running status, lag decreasing',
        'time_budget_minutes': 5
    },
    {
        'step': 6,
        'action': 'Send rollback notification to all stakeholders',
        'command': 'Send pre-drafted rollback communication',
        'verification': 'Confirmation from comms lead',
        'time_budget_minutes': 5
    }
]

# Total rollback time target: under 60 minutes
TOTAL_ROLLBACK_BUDGET_MINUTES = sum(s['time_budget_minutes'] for s in ROLLBACK_STEPS)  # = 50 min

Post-Rollback: The 48-Hour Retrospective


A rollback buys you time to fix the root cause. Within 48 hours of a rollback, document:

  1. Which trigger fired and what data supported the decision

  2. Root cause of the failure (not the symptom)

  3. What pre-migration validation would have caught this

  4. Updated cutover plan with the fix and additional validation gates


Teams that run rigorous rollback retrospectives succeed on their second attempt. Teams that blame the technology repeat the same failures.


T-Plus 24 Hours: Post-Cutover Validation and Stabilization

Go-live is not the end of the migration — it's the beginning of the stabilization period. The first 24 hours after cutover are when silent failures surface: edge-case queries that weren't in your benchmark set, late-arriving data that exposed a CDC gap, or a report that runs weekly and just happened to run for the first time post-migration.


The T+24 Validation Checklist

Area

What to Check

Cadence

ETL job success rates

All scheduled jobs completing with expected row counts

Every job run

Dashboard accuracy

Side-by-side comparison of 10 key dashboards against archived screenshots

T+4h, T+12h, T+24h

Business metric parity

Revenue, order count, user count vs same period prior week

T+8h, T+24h

Query performance monitoring

P50/P95/P99 for all query categories vs baseline

Continuous

Error log volume

Application and ETL error rates vs pre-migration baseline

Continuous

Data freshness

Confirm each table is refreshing on expected schedule

Per table SLA

New data ingestion

First full batch of new data since cutover lands correctly

First batch

Anomaly Detection Queries for T+24

-- Business metric parity check (run at T+8, T+24)
WITH this_week AS (
  SELECT
    COUNT(*) AS order_count,
    SUM(order_total) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers,
    AVG(order_total) AS avg_order_value
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '1 day'
    AND order_date < CURRENT_DATE
),
last_week AS (
  SELECT
    COUNT(*) AS order_count,
    SUM(order_total) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers,
    AVG(order_total) AS avg_order_value
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '8 days'
    AND order_date < CURRENT_DATE - INTERVAL '7 days'
)
SELECT
  ROUND(100.0 * (t.revenue - l.revenue) / l.revenue, 2) AS revenue_wow_pct,
  ROUND(100.0 * (t.order_count - l.order_count) / l.order_count, 2) AS orders_wow_pct,
  CASE 
    WHEN ABS(t.revenue - l.revenue) / l.revenue > 0.30 THEN 'INVESTIGATE'
    WHEN ABS(t.revenue - l.revenue) / l.revenue > 0.15 THEN 'WATCH'
    ELSE 'NORMAL'
  END AS anomaly_flag
FROM this_week t, last_week l;
-- ETL job health check — compare post-migration row counts to pre-migration baseline
SELECT
  job_name,
  run_date,
  rows_loaded,
  baseline_rows_loaded,
  ROUND(100.0 * (rows_loaded - baseline_rows_loaded) / baseline_rows_loaded, 2) AS variance_pct,
  CASE
    WHEN rows_loaded = 0 THEN 'CRITICAL'
    WHEN ABS(rows_loaded - baseline_rows_loaded) / baseline_rows_loaded > 0.05 THEN 'WARN'
    ELSE 'OK'
  END AS status
FROM etl_run_log l
JOIN etl_baseline_metrics b USING (job_name)
WHERE l.run_date >= CURRENT_DATE - INTERVAL '1 day'
ORDER BY status DESC, variance_pct DESC;

When to Declare Stabilization Complete


You are stable when:

  • 3 full business days of ETL runs have completed without intervention

  • All weekly reports have run at least once post-cutover and been confirmed by business owners

  • No open P1 or P2 incidents related to the migration

  • Query performance has been within SLA for 72 consecutive hours


Do not decommission the source system until you have declared stabilization complete. Keep it available in read-only mode for at least 30 days as an audit reference.


How Automated Testing Changes What's Feasible

Every protocol in this guide can be executed manually. Teams do it with spreadsheets and shared Google Docs and Slack threads. But manual cutover validation has a ceiling: you can check maybe 20 tables in a 30-minute window. You can't check 2,000 tables. You can't re-run 500 queries against two systems simultaneously. You certainly can't do it at midnight after 18 hours of migration work.


Automated testing changes the feasibility of the entire cutover plan.


What Automation Unlocks

Capability

Manual Approach

With Automation

Row count validation

20–50 tables in 30 min

All tables in 5 minutes

Parallel query validation

10–20 queries by hand

500+ queries in parallel

Go/no-go decision

Subjective judgment call

Binary pass/fail on defined criteria

Rollback detection

Human spots the problem

Alert fires within minutes

T+24 monitoring

Engineer manually checks dashboards

Continuous automated comparison

Documentation

Migration report written from memory

Audit trail generated automatically


The Real Risk: Migrations Without Automated Testing

Most migration failures we see don't fail at go-live. They fail silently in the weeks after: a dashboard that's been showing the wrong revenue number, an ETL job that's been quietly double-loading data, a customer segment that stopped updating because a join key changed type. These are the failures that hurt — because by the time they're discovered, weeks of downstream decisions have been made on corrupt data.


Automated, continuous data quality testing is what closes this gap. Not as a one-time validation, but as a permanent fixture in the post-migration stack.



Vexdata Makes Migration Validation Continuous


Vexdata is the data testing platform built for exactly this kind of work: high-stakes migration validation, parallel-run comparison, and post-cutover stabilization monitoring.

With Vexdata, you can:


  • Automate the T-48, T-24, and T-4 validation protocols from this guide — run them as scheduled test suites, not manual scripts

  • Parallel-run validation at scale — compare query results across source and target warehouses across hundreds of queries simultaneously

  • Set automated rollback alerts that fire to Slack or PagerDuty the moment a validation threshold is crossed during the cutover window

  • Continuous post-cutover monitoring — track row count trends, metric parity, and ETL health without manual dashboards


Teams using Vexdata for migration cutover reduce their manual validation effort by over 80% — and catch the silent failures that spreadsheet-based approaches miss.


Ready to build a migration cutover plan that actually works? See how Vexdata supports data warehouse migrations →

 
 
 

Comments

bottom of page