Why Data Validation Is the Most Underrated Migration Step

The Validation Gap

Most migration projects validate one thing: row counts. Source has 5,000,000 rows. Target has 5,000,000 rows. Ship it. This is a dangerously incomplete check. Row counts match even when every value in a column is wrong.

We have seen migrations pass row-count validation only to fail silently on numeric precision loss (Oracle NUMBER silently cast to PostgreSQL INTEGER), timezone shifts (Oracle DATE has no timezone, PostgreSQL TIMESTAMP does), and NULL handling differences. The only way to catch these is multi-layer validation.

The Four Layers of Migration Validation

Layer 1 — Row Count Comparison

Fast and cheap. Run a SELECT COUNT(*) on every table on both sides. Any mismatch is an immediate blocker. DBMigrateAIPro runs this automatically after bulk migration.

Layer 2 — Checksum Sampling

Compute a hash (MD5 or SHA-256) of every column value for a sample of rows — typically 1% or 10,000 rows per table, whichever is smaller. Compare hashes between source and target. This catches value corruption that row counts miss.

-- PostgreSQL checksum approach
SELECT
  id,
  MD5(
    COALESCE(first_name,'') || COALESCE(last_name,'') ||
    COALESCE(salary::TEXT,'') || COALESCE(hire_date::TEXT,'')
  ) AS row_hash
FROM employees
ORDER BY id
LIMIT 10000;

Layer 3 — Statistical Distribution Check

For numeric columns, compare MIN, MAX, AVG, and STDDEV between source and target. A correct migration preserves these statistics exactly. Deviations reveal type conversion issues — for example, FLOAT columns being silently truncated.

Layer 4 — Constraint Verification

Verify that all NOT NULL constraints, unique constraints, and foreign key constraints are satisfied on the target. A row that passes checksum validation can still violate a FK constraint added post-migration.

-- Check FK constraint validity in PostgreSQL
SELECT conname, conrelid::regclass AS table_name
FROM pg_constraint
WHERE contype = 'f'
  AND NOT convalidated;
-- Should return 0 rows after a clean migration

Automating Validation

DBMigrateAIPro runs Layers 1 and 2 automatically after every migration. The comparison report (HTML, saved to reports/) shows per-table row counts, checksum match rates, and any discrepancies with row-level detail. Layers 3 and 4 are available as optional post-migration checks in the Validation tab.

When to Run Each Layer

• After bulk migration completes — run Layers 1 and 2 immediately
• After CDC lag drops below 5 seconds — run Layer 1 again to confirm ongoing sync
• Before cutover — run all four layers on the full dataset
• One week post-cutover — run Layer 3 statistical checks to catch any application-level data corruption introduced by the new system

The Single Biggest Mistake

Skipping validation entirely on "low-risk" tables. Every table has risk. A lookup table with wrong enum values can cause application logic errors that take weeks to trace back to the migration. Validate everything — the automated checks take seconds per table.