Why can matching totals conceal incorrect imported records?

Prepare the diagnostic input

A migration can preserve row count and total quantity while assigning the wrong values to individual records. Compare identity-linked quantities with a NULL-safe difference and include the target-only branch. SQLite IS NOT is used deliberately for the tested NULL semantics.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE source_rows(id TEXT PRIMARY KEY,qty INTEGER);
CREATE TABLE target_rows(id TEXT PRIMARY KEY,qty INTEGER);
INSERT INTO source_rows VALUES ('A',3),('B',7);
INSERT INTO target_rows VALUES ('A',4),('B',6);

Reproduce the misleading result and correct it

SELECT (SELECT COUNT(*) FROM source_rows)=(SELECT COUNT(*) FROM target_rows)
AND (SELECT SUM(qty) FROM source_rows)=(SELECT SUM(qty) FROM target_rows) AS agrees;

SELECT s.id,s.qty AS source_qty,t.qty AS target_qty FROM source_rows s LEFT JOIN target_rows t ON t.id=s.id
WHERE t.id IS NULL OR s.qty IS NOT t.qty
UNION ALL SELECT t.id,NULL,t.qty FROM target_rows t WHERE NOT EXISTS(SELECT 1 FROM source_rows s WHERE s.id=t.id) ORDER BY id;

Source A/B contain three/seven units; target A/B contain four/six. Both totals are ten across two records, so the misleading summary says agrees=1. The correction lists both mismatches. Do not auto-overwrite the target until the source and correction authority are confirmed.

Check the boundary before applying the fix

Repair A/B in the synthetic target, then add C with zero quantity. Only extra C remains. A zero-valued extra record matters even when the quantity total still agrees.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

UPDATE target_rows SET qty=3 WHERE id='A'; UPDATE target_rows SET qty=7 WHERE id='B'; INSERT INTO target_rows VALUES ('C',0);

Each side has unique stable IDs and one quantity field. Field transformations, duplicate identities and intentional target-only records need an agreed comparison contract.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.