How to check data integrity after migration

Render each row as one ordered line and hash the stream: sqlite3 db "SELECT quote(id)||'|'||quote(status) FROM t ORDER BY id;" | sha256sum. Source and target agreed on 5576e6ba once the migration was fixed, and disagreed before that, on 50000 rows whose count matched exactly.

Why check this

This runs straight after the row count, on the staging copy, before the application is pointed at the new schema. A count proves the same number of rows arrived. It cannot prove they carry the same values, and a mapping defect keeps the count intact.

The failure it catches: a CASE with no branch for refunded put NULL into 2941 of 50000 rows. Counts matched. Those orders stopped appearing in the refunds report.

A checksum turns that into one comparison a pipeline can run. It also mismatches for reasons that are not data loss, so the second half of this page is about reading a mismatch.

Prerequisites

• The sqlite3 3.50.6 shell, the Android platform-tools build, and sha256sum 8.32. Both engines here render quote() identically, documented in the core functions reference.
• A pre-migration copy of the database, opened beside the live one with ATTACH.
• The fixture below: 50000 orders, and a migration whose CASE has no branch for refunded.

CREATE TABLE orders (
  id             INTEGER PRIMARY KEY,
  customer_email TEXT    NOT NULL,
  status         TEXT    NOT NULL,
  amount_cents   INTEGER NOT NULL,
  created_at     TEXT    NOT NULL
);
INSERT INTO orders (id, customer_email, status, amount_cents, created_at)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 50000)
SELECT i, 'user' || (i % 900) || '@example.com',
       CASE WHEN i % 17 = 0 THEN 'refunded'
            WHEN i % 7  = 0 THEN 'cancelled'
            WHEN i % 3  = 0 THEN 'shipped'
            WHEN i % 2  = 0 THEN 'pending'
            ELSE 'paid' END,
       (i * 37) % 90000 + 500,
       date('2026-01-01', '+' || (i % 240) || ' days')
FROM n;

-- the migration under test
CREATE TABLE orders_v2 (id INTEGER PRIMARY KEY, customer_email TEXT, state TEXT, amount REAL, created_at TEXT);
INSERT INTO orders_v2 SELECT id, customer_email,
  CASE status WHEN 'paid' THEN 'PAID' WHEN 'pending' THEN 'PENDING'
              WHEN 'shipped' THEN 'SHIPPED' WHEN 'cancelled' THEN 'CANCELLED' END,
  amount_cents / 100.0, created_at FROM orders;

-- the fixed version, inserted in a different physical order on purpose
CREATE TABLE orders_v3 (id INTEGER NOT NULL, customer_email TEXT, state TEXT, amount REAL, created_at TEXT);
INSERT INTO orders_v3 SELECT id, customer_email, upper(status), amount_cents / 100.0, created_at
FROM orders ORDER BY created_at, id;

-- the four-row pair used in step 7
CREATE TABLE src_t (id INTEGER PRIMARY KEY, name TEXT, note TEXT, amount);
CREATE TABLE dst_t (id INTEGER PRIMARY KEY, name TEXT, note TEXT, amount);
INSERT INTO src_t VALUES (1,'acme',NULL,12.30),(2,'globex','ok',7.00),(3,'initech','x',0.1),(4,'hooli','y',0.3);
INSERT INTO dst_t VALUES (1,'acme ','',12.30),(2,'globex','ok',7.0),(3,'initech','x','0.1'),(4,'hooli','y',0.1+0.2);

Steps

1. 1

   Step 1.

   Hash the source. quote() renders every value so its type and whitespace survive, ORDER BY fixes the sequence, sha256sum reduces the stream to one line.

   sqlite3 -noheader -list shop-before.db "SELECT quote(id)||'|'||quote(customer_email)||'|'||quote(status)||'|'||quote(amount_cents)||'|'||quote(created_at) FROM orders ORDER BY id;" | sha256sum
   

   5576e6ba7f6e5cb401c1f66dfe89c0a7b5aab51d4348aeecbbe92c29d5f996e3 *-

   Order by the business key, never by an implicit one. Step 6 shows what happens otherwise.

2. 2

   Step 2.

   Hash the target with the same line shape. The migration renamed status to state and moved cents to a REAL, so normalise both back, or you are comparing formats.

   sqlite3 -noheader -list shop.db "SELECT quote(id)||'|'||quote(customer_email)||'|'||quote(lower(state))||'|'||quote(CAST(round(amount*100) AS INTEGER))||'|'||quote(created_at) FROM orders_v2 ORDER BY id;" | sha256sum
   

   f56c16d76b65d4e23d580f436fb3809aa81f5ccaa787cfb150dd1c2b4fc4ca05 *-

   Different digest, same 50000 rows on both sides. The checksum found what the count could not.

3. 3

   Step 3.

   Name the rows behind the mismatch. Build the same line in each database, join on the key, keep the pairs that differ.

   sqlite3 shop.db ".mode line" "ATTACH 'shop-before.db' AS src; WITH s AS (SELECT id, quote(id)||'|'||quote(customer_email)||'|'||quote(status)||'|'||quote(amount_cents)||'|'||quote(created_at) AS line FROM src.orders), d AS (SELECT id, quote(id)||'|'||quote(customer_email)||'|'||quote(lower(state))||'|'||quote(CAST(round(amount*100) AS INTEGER))||'|'||quote(created_at) AS line FROM main.orders_v2) SELECT s.id AS id, s.line AS source, d.line AS target FROM s JOIN d USING (id) WHERE s.line IS NOT d.line ORDER BY s.id LIMIT 2;"
   

       id = 17
   source = 17|'user17@example.com'|'refunded'|1129|'2026-01-18'
   target = 17|'user17@example.com'|NULL|1129|'2026-01-18'
   
     id = 34
   source = 34|'user34@example.com'|'refunded'|1758|'2026-02-04'
   target = 34|'user34@example.com'|NULL|1758|'2026-02-04'

   Without the LIMIT the same query returned 2941 rows, each a refunded order that arrived with a NULL state. Use IS NOT, not <>: comparing a NULL with <> yields NULL, and the row drops out of the result you are relying on.

4. 4

   Step 4.

   Check what the obvious version of the checksum would have done. Concatenating with || and no quote() is the form most people write first.

   sqlite3 -noheader -list shop.db "SELECT id||'|'||customer_email||'|'||lower(state)||'|'||CAST(round(amount*100) AS INTEGER)||'|'||created_at FROM orders_v2 ORDER BY id;" | grep -c '^$'
   

   2941

   NULL concatenated with anything is NULL. 2941 rows collapsed to an empty line, taking their id, email and amount with them, and any two rows holding a NULL hash identically. quote() renders NULL as four characters and keeps the rest of the row.

5. 5

   Step 5.

   Add the missing branch, rerun the migration into orders_v3, hash it the same way.

   sqlite3 -noheader -list shop.db "SELECT quote(id)||'|'||quote(customer_email)||'|'||quote(lower(state))||'|'||quote(CAST(round(amount*100) AS INTEGER))||'|'||quote(created_at) FROM orders_v3 ORDER BY id;" | sha256sum
   

   5576e6ba7f6e5cb401c1f66dfe89c0a7b5aab51d4348aeecbbe92c29d5f996e3 *-

   Byte for byte the source digest from step 1. That is a passing data check.

6. 6

   Step 6.

   Now drop the ORDER BY from the query that just passed, and change nothing else.

   sqlite3 -noheader -list shop.db "SELECT quote(id)||'|'||quote(customer_email)||'|'||quote(lower(state))||'|'||quote(CAST(round(amount*100) AS INTEGER))||'|'||quote(created_at) FROM orders_v3;" | sha256sum
   

   6d6ecc953a68e08d9566dde3d1c8addadcb72c2bf70d0114c253e29b993f7195 *-

   The source hashed without ORDER BY still returns 5576e6ba. The rows are identical and the digests are not, because orders_v3 was written in created_at order and an unordered scan reports storage order. An unordered checksum compares physical layout.

7. 7

   Step 7.

   Read a mismatch that is not data loss. The four-row pair in Prerequisites differs in three ways no default listing shows.

   sqlite3 diffdemo.db ".mode line" "WITH s AS (SELECT id, quote(id)||'|'||quote(name)||'|'||quote(note)||'|'||quote(amount) AS line FROM src_t), d AS (SELECT id, quote(id)||'|'||quote(name)||'|'||quote(note)||'|'||quote(amount) AS line FROM dst_t) SELECT s.id AS id, s.line AS source, d.line AS target FROM s JOIN d USING (id) WHERE s.line IS NOT d.line ORDER BY s.id;"
   

       id = 1
   source = 1|'acme'|NULL|12.3
   target = 1|'acme '|''|12.3
   
     id = 3
   source = 3|'initech'|'x'|0.1
   target = 3|'initech'|'x'|'0.1'
   
     id = 4
   source = 4|'hooli'|'y'|0.3
   target = 4|'hooli'|'y'|3.000000000000000445e-01

   Row 1: a trailing space, and a NULL rewritten as an empty string. Row 3: the number 0.1 stored as the text '0.1'. Row 4: 0.1 + 0.2 is not the double 0.3, and quote() prints the precision the default display rounds away. Row 2, 7.00 against 7.0, did not differ: both are one double. None of these is a lost row, and all break the digest.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | Both digests equal | Every ordered row matches, value for value | Record the digest in the release notes with the row count. | | Digests differ, counts equal | Values changed or a mapping is incomplete | Run the line join in step 3 to name the rows before anything else. | | A NULL on one side only | A CASE or a join lost a branch | Fix the mapping and rerun; see How to verify row counts after migration. | | A quoted number against a bare one | The column changed affinity | Compare typeof() per column. A text '0.1' will not compare equal to 0.1. | | Full precision floats, such as 3.0e-01 | Arithmetic was done in the migration | Migrate the integer and divide on read, or compare rounded values on both sides. | | A trailing quote character out of place | Trailing or leading whitespace | quote() shows it; a plain listing never will. Check the collation too, in How to check database collation. | | Digests differ on identical data | The scan order differs | Add the ORDER BY. Storage order is not data. |

Thresholds

Common mistakes

What to check next

• How to verify row counts after migration: the cheaper check that runs first, and what it cannot see.
• How to check database integrity: whether the file itself is sound.
• How to test database migration: where in the up and down cycle these digests are taken.
• How to verify a database backup: the same comparison, used to prove a restore restored.

FAQ

How do I verify data after a migration without hashing every row?

Hash a stable sample: a fixed id window, or one digest per day of created_at. A per-group digest also tells you which group to open when it fails.

Does the checksum need every column?

Include every column the migration touched, plus the key. A column left out can change silently. Columns the migration was meant to change need normalising on one side, as in step 2, or the digest reports the rename.

Why does the digest change when the data did not?

Ordering, type affinity, whitespace and float formatting, in that frequency. Step 6 and step 7 show one of each. Run the line join before treating a mismatch as loss.

Can I compare a checksum across two different engines?

Only if both sides render values identically, and they do not. Dates, floats and NULL all print differently. Cast every column to a canonical text form on both sides, and test that on rows you know match. These commands ran against SQLite 3.50.6, the only engine on the machine that verified them.

Verified