Home / Guides
Tip

How to Validate Data After a Migration

By · October 27, 2026 · 12 min read

The short version

Data validation after a migration means proving the target holds the same rows and the same values as the source, with queries you run on both sides. Do it in four passes: exact row counts, per-column aggregates (sums, min/max, null counts, distinct counts, string lengths), an order-independent checksum of every row, and a sampled row-by-row diff. When a checksum disagrees, split the key range into buckets and checksum each bucket until you are down to single rows. Before trusting any of it, run the checks against a copy you know is right: the two databases format decimals, timestamps and NULLs differently, and the checksum has to agree on a good copy before a mismatch means anything.

The test: 10,000 orders from MySQL to PostgreSQL

I built an orders table in MySQL 8.4.11 with 10,000 rows: email, customer name, a DECIMAL(12,2) amount, a currency code, a status, a nullable note (549 of them end in trailing spaces), a DATETIME created_at and a nullable TIMESTAMP paid_at. Then I copied it to PostgreSQL 16.14 twice with a small Python loader:

The defects:

  1. Truncated strings. The target was customer_name varchar(40), filled from a staging table with customer_name::varchar(40). PostgreSQL truncates on an explicit cast: "if one explicitly casts a value to character varying(n) ... an over-length value will be truncated to n characters without raising an error" (PostgreSQL: Character Types). Two company names were longer than 40 characters.
  2. Timezone-shifted timestamps. The export session ran with SET time_zone = '+02:00'. MySQL converts TIMESTAMP values "back from UTC to the current time zone for retrieval", and does not do that for DATETIME (MySQL 8.4: DATE, DATETIME, and TIMESTAMP Types). So every paid_at came out two hours late and went into a timestamptz column as if it were UTC, while created_at was fine.
  3. A dropped row. Row 7431 had a NUL byte in its note. The loader retried failed batches row by row and skipped what still failed:
orders 9999 rows; skipped: [(7431, "ValueError('A string literal cannot contain NUL (0x00) characters.')")]

Nobody reads that log. The NUL byte itself is covered in invalid byte sequence for encoding "UTF8": 0x00.

Step 1: exact row counts, not the estimates

SELECT COUNT(*) on both sides. Do not use the catalog numbers. On the same source table:

$ mysql ... -e "SELECT TABLE_ROWS FROM information_schema.TABLES
                WHERE TABLE_SCHEMA = '...' AND TABLE_NAME = 'orders'"
TABLE_ROWS
10187
-- mysql: SELECT COUNT(*) FROM orders
COUNT(*)
10000
-- pg: SELECT COUNT(*) FROM orders
count
9999

The MySQL manual says TABLE_ROWS for InnoDB "is an approximation, and may vary from the actual value by as much as 40% to 50%" (MySQL 8.4: The INFORMATION_SCHEMA TABLES Table). PostgreSQL's pg_class.reltuples is likewise "only an estimate used by the planner" (PostgreSQL: pg_class). A one-row loss is far inside both error margins.

The count caught the dropped row and nothing else. A duplicated row would have hidden it.

Step 2: per-column aggregates

One query per side, one value per column, compared next to each other. The MySQL version:

SELECT COUNT(*) AS n,
       SUM(amount) AS sum_amount, MIN(amount) AS min_amount, MAX(amount) AS max_amount,
       COUNT(DISTINCT email) AS emails,
       MAX(CHAR_LENGTH(customer_name)) AS max_name_len,
       SUM(CHAR_LENGTH(customer_name)) AS sum_name_len,
       COUNT(*) - COUNT(note) AS null_notes,
       MIN(created_at) AS min_created, MAX(created_at) AS max_created,
       MIN(paid_at) AS min_paid, MAX(paid_at) AS max_paid,
       COUNT(*) - COUNT(paid_at) AS null_paid
FROM orders;

PostgreSQL runs the same query; I only lowercased the function names. Result against the broken copy:

check           mysql                  postgres
n               10000                  9999                          <--
sum_amount      12567426.81            12566460.53                   <--
min_amount      1.45                   1.45
max_amount      2499.40                2499.40
emails          9874                   9892                          <--
max_name_len    56                     40                            <--
sum_name_len    107378                 107336                        <--
null_notes      6033                   6033
min_created     2025-01-01 00:06:36    2025-01-01 00:06:36
max_created     2025-12-31 22:37:10    2025-12-31 22:37:10
min_paid        2025-01-01 15:57:50    2025-01-01 17:57:50+00:00     <--
max_paid        2026-01-03 07:19:10    2026-01-03 09:19:10+00:00     <--
null_paid       1629                   1629

Every planted defect shows. max_name_len stopping at exactly 40, the column width, is the signature of truncation. min_paid and max_paid are both off by exactly two hours while created_at matches, which points at time zone handling for one column type. The amount sum is short by 966.28, the amount of the missing row.

Two lines here are traps. null_notes matches although a row is missing, because the lost row had a note. And emails disagrees on the correct copy too: orders_ok gives 9893. Twenty source rows hold the same address typed with a capital first letter. The table collation is utf8mb4_0900_ai_ci, and _ci means case-insensitive (MySQL 8.4: Collation Naming Conventions), so MySQL counts Omar.lund499@example.com and omar.lund499@example.com as one. PostgreSQL's predefined collations are deterministic and treat strings as equal "only if they consist of the same byte sequence" (PostgreSQL: Collation Support):

-- mysql
COUNT(DISTINCT email)  COUNT(DISTINCT LOWER(email))  COUNT(DISTINCT BINARY email)
9874  9874  9893
-- pg (orders_ok): SELECT count(DISTINCT email), count(DISTINCT lower(email))
count  count
9893  9874

Compare COUNT(DISTINCT BINARY email) in MySQL with plain count(DISTINCT email) in PostgreSQL, or LOWER() on both.

Step 3: an order-independent checksum that matches across MySQL and PostgreSQL

Aggregates miss a value that changed inside the range, such as a shifted timestamp in the middle of the year. For that you hash every row. Row order differs between the two databases, so the per-row hashes have to be combined with something order-independent: I take the first 15 hex digits of each row's MD5 (60 bits, which fits a signed bigint) and sum them.

My first row string was the obvious one, CONCAT_WS('|', id, email, ..., created_at, paid_at) on both sides. Against the correct copy, 8,371 of 10,000 rows differed:

mysql: '1|priya.costa1@example.com|Priya Costa|750.66|EUR|paid|2025-11-17 02:38:09|2025-11-18 15:29:09'
pg:    '1|priya.costa1@example.com|Priya Costa|750.66|EUR|paid|2025-11-17 02:38:09|2025-11-18 15:29:09+00'

What it took to get to zero:

Problem What happened Fix
timestamptz text PostgreSQL appends +00 format both sides: DATE_FORMAT(..., '%Y-%m-%d %H:%i:%s') and to_char(paid_at AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS')
validator session time zone MySQL's TIMESTAMP output follows the session SET time_zone = '+00:00' before the MySQL query
NULL both CONCAT_WS functions skip NULLs, so ('a', NULL, 'b') and ('a', 'b', NULL) give the same string COALESCE(col, '<null>') per nullable column
deliberate transforms the good copy stripped the NUL in row 7431 apply the same REPLACE(note, CHAR(0), '') on the source side
decimals matched here, because both sides are fixed-scale never hash a float; see below

The NULL behaviour is documented for both: MySQL's CONCAT_WS() "does skip any NULL values after the separator argument" (MySQL 8.4: String Functions), and in PostgreSQL's concat_ws "Other NULL arguments are ignored" (PostgreSQL: String Functions). AT TIME ZONE on a timestamptz returns a plain timestamp "as the time would appear in that zone" (PostgreSQL: Date/Time Functions).

The session time zone is the dangerous one. I ran the per-row comparison with the MySQL validator session at UTC and at +02:00:

+00:00 orders_ok 0 rows differ
+00:00 orders 8371 rows differ
+02:00 orders_ok 8371 rows differ
+02:00 orders 3 rows differ

A validator that shares the export job's settings repeats the export job's mistake and approves the shifted copy. Pin the session settings in the validation script itself.

Decimals matched only because the column is numeric(12,2) on both sides. In PostgreSQL 750.60::float8::text gives 750.6, and 0.1::float8 + 0.2::float8 gives 0.30000000000000004. PostgreSQL calls double precision "inexact" and recommends numeric "for monetary amounts" (PostgreSQL: Numeric Types): a money column that landed as float needs fixing before it is worth hashing.

The 549 notes with trailing spaces hash the same on both sides, since nothing trims them. They bite in join-based diffs instead: 'gift wrap' = 'gift wrap ' returned 0 under utf8mb4_0900_ai_ci and 1 under utf8mb4_general_ci, a PAD SPACE collation where "trailing spaces are insignificant in comparisons" (MySQL 8.4: Trailing Space Handling).

The final checksum, MySQL side:

SET time_zone = '+00:00';
SELECT COUNT(*) AS n,
       SUM(CAST(CONV(SUBSTRING(MD5(CONCAT_WS('|', id, email, customer_name, amount, currency, status,
             COALESCE(REPLACE(note, CHAR(0), ''), '<null>'),
             DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s'),
             COALESCE(DATE_FORMAT(paid_at, '%Y-%m-%d %H:%i:%s'), '<null>'))), 1, 15), 16, 10)
           AS UNSIGNED)) AS checksum
FROM orders;

PostgreSQL side:

SELECT count(*) AS n,
       sum(('x' || substr(md5(concat_ws('|', id, email, customer_name, amount, currency, status,
             coalesce(note, '<null>'),
             to_char(created_at, 'YYYY-MM-DD HH24:MI:SS'),
             coalesce(to_char(paid_at AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS'), '<null>'))),
           1, 15))::bit(60)::bigint) AS checksum
FROM orders;

CONV() "works with 64-bit precision" and returns a string (MySQL 8.4: Mathematical Functions). Keep the CAST(... AS UNSIGNED). Without it SUM() gets strings, converts them, and returns a double; SUM() only returns an exact DECIMAL "for exact-value arguments" (MySQL 8.4: Aggregate Functions). I ran both forms:

n      naive_sum               checksum
10000  5.749917521253243e+21   5749917521253255122061

On the PostgreSQL side, 'x' || hex cast to bit(60) reads four bits per hex digit, the same notation as hexadecimal bit-string constants, and the cast from bit to an integer type is documented with the bit string functions. Results:

-- mysql orders
n  checksum
10000  5749917521253255122061
-- pg orders_ok
n  checksum
10000  5749917521253255122061
-- pg orders
n  checksum
9999  5808645550190618423058

The good copy matches to the last digit and the broken copy does not. I then reloaded orders with the export session at +00:00, which fixes paid_at, and ran it again:

-- pg orders
n  checksum
9999  5749683378206448034152

Still different, because two defects remain.

Step 4: sampled row-by-row diff

Pick random keys from the source, fetch those rows from both sides and compare column by column in Python, with timestamptz normalised to naive UTC. The first of three runs of 500 ids against the broken copy:

sampled 500 of 10000 ids
paid_at          417  e.g. (26, datetime.datetime(2025, 1, 31, 5, 14, 38), datetime.datetime(2025, 1, 31, 7, 14, 38, tzinfo=datetime.timezone.utc))

Sampling shows what a systemic defect looks like: the column and the two-hour gap. It found neither the truncation nor the dropped row in any of the three runs (410 and 433 paid_at hits in the other two), since those touch 3 rows in 10,000. Use samples to explain a mismatch; use checksums to prove there is none.

Step 5: localise a mismatch by bucketing the key

With the time zone fixed, the totals still disagree. Group the checksum by id DIV 1000 in MySQL and id / 1000 in PostgreSQL (integer division in both), compare bucket by bucket, then rerun only the bad buckets with smaller sizes:

buckets of 1000 ids in 1..10000: 11
ids 3000-3999: mysql n=1000, pg n=1000, checksum differs
ids 4000-4999: mysql n=1000, pg n=1000, checksum differs
ids 7000-7999: mysql n=1000, pg n=999, checksum differs
buckets of 100 ids in 3000..4999: 20
ids 3900-3999: mysql n=100, pg n=100, checksum differs
ids 4900-4999: mysql n=100, pg n=100, checksum differs
buckets of 100 ids in 7000..7999: 10
ids 7400-7499: mysql n=100, pg n=99, checksum differs
buckets of 10 ids in 3900..3999: 10
ids 3980-3989: mysql n=10, pg n=10, checksum differs
...
ids 7430-7439: mysql n=10, pg n=9, checksum differs

A bucket where the counts differ has missing or extra rows; a bucket with equal counts and a different checksum has changed values. Three rounds took 10,000 rows down to three buckets of ten, and each round reads only the key ranges that failed the round before. Then look at the rows:

-- mysql
id  customer_name   CHAR_LENGTH(customer_name)
3988    Instituto de Educacion Secundaria Profesor Tierno Galvan    56
4985    Wohnungsbaugenossenschaft Lichtenberg-Friedrichshain eG 55
ids
7430,7431,7432,7433,7434,7435,7436,7437,7438,7439
-- pg
id  customer_name  char_length
3988  Instituto de Educacion Secundaria Profes  40
4985  Wohnungsbaugenossenschaft Lichtenberg-Fr  40
string_agg
7430,7432,7433,7434,7435,7436,7437,7438,7439

Two truncated names and the missing 7431: all three defects, found without reading 10,000 rows.

Bucket on the primary key when it is dense. On a sparse or UUID key, bucket on a date column or on a prefix of the key's hash; what matters is that both sides compute the same bucket for the same row.

What goes in the migration runbook

Each step is a pass/fail line in the cutover plan, next to the rest of the database migration checklist. Keep the queries in the repository and run them from a script that sets its own session time zone. If the migration also replaces a reporting pipeline, the same idea scales up as dbt tests that compare old and new outputs: prove the new pipeline matches the old one with dbt tests.

Sources