Having presided over what can only be described as three separate data migration catastrophes earlier in my career, I have come to understand that the primary point of failure is rarely the technology itself. It is almost invariably a lack of a rigorous, data-quality-first playbook. The ad-hoc scripts and hopeful thinking that characterized those early attempts have been crystallized into a formalized methodology that I've now successfully executed across two major warehouse migrations and one BI platform transition. The core philosophy is that you are not moving tables; you are migrating trust in the data.
The playbook is structured around three interdependent pillars: the Validation Framework, the Incremental Cutover, and the Parallel Run Discipline. I will focus on the first, as it is the bedrock.
**1. The Validation Framework (Pre, During, Post-Migration)**
Every object migrated must pass through a battery of SQL-based validation checks. This goes far beyond simple row counts. We establish a contract for each table and dashboard.
```sql
-- Example validation script for a core `fact_orders` table
WITH source AS (SELECT * FROM legacy_schema.fact_orders),
target AS (SELECT * FROM new_schema.fact_orders)
SELECT
'row_count' as check_type,
(SELECT COUNT(*) FROM source) as source_value,
(SELECT COUNT(*) FROM target) as target_value,
ABS((SELECT COUNT(*) FROM source) - (SELECT COUNT(*) FROM target)) as variance
UNION ALL
SELECT
'distinct_key_check' as check_type,
(SELECT COUNT(DISTINCT order_id) FROM source) as source_value,
(SELECT COUNT(DISTINCT order_id) FROM target) as target_value,
ABS((SELECT COUNT(DISTINCT order_id) FROM source) - (SELECT COUNT(DISTINCT order_id) FROM target)) as variance
UNION ALL
SELECT
'aggregate_validation' as check_type,
(SELECT SUM(revenue_amount) FROM source WHERE order_date >= '2024-01-01') as source_value,
(SELECT SUM(revenue_amount) FROM target WHERE order_date >= '2024-01-01') as target_value,
ABS((SELECT SUM(revenue_amount) FROM source WHERE order_date >= '2024-01-01') - (SELECT SUM(revenue_amount) FROM target WHERE order_date >= '2024-01-01')) as variance
-- Tolerance thresholds are applied to the variance column in the reporting layer
```
This suite of checks is automated and run after each batch migration. The outputs are logged to a validation metadata table, which powers a dashboard showing check statuses, variances, and trends over time.
**2. Incremental Cutover by Business Subject Area**
Attempting a "Big Bang" cutover is the single greatest risk. The playbook enforces a domain-by-domain migration, ordered by data dependency.
* Phase 1: Static/Dimension data (Users, Products, Currencies)
* Phase 2: Core transactional facts (Orders, Sessions)
* Phase 3: Derived aggregates and mart tables
* Phase 4: BI Semantic Layer (Looker explores/PDTs, Tableau datasources)
Each phase must achieve a 100% validation pass for all critical checks (row counts, key integrity, financial totals within 0.1%) before the next phase is initiated. This creates natural, manageable rollback points.
**3. Parallel Run Discipline**
For a minimum of two full business cycles (e.g., month-end, week-end), both the legacy and new systems must be active. All downstream reporting is dual-written, and a sample of business users are tasked with comparing outputs. This is not about performance, but about outcome parity. The validation dashboard is made public to the stakeholder team to build confidence transparently.
**Timeline & Tools**
A typical migration for a mid-size warehouse (50-100 core tables) using this playbook takes 10-14 weeks from framework setup to full decommissioning. The most recent application, migrating from a traditional ETL setup to a dbt + Snowflake stack, took 11 weeks. The critical path is always the discovery and scoping of the validation rules, which must involve subject matter experts. The actual data movement, using tools like Airflow for orchestration and dbt for transformation, often constitutes less than 20% of the total effort.
The result is a transition that feels boringly predictable to the business—which is the highest compliment. The playbook transforms migration from a leap of faith into a verified, incremental engineering process.
- dan
Garbage in, garbage out.