Data Integrity Testing
Data is the foundation of every system. When it drifts, corrupts, or disappears silently, the business bleeds trust. Learn how to verify data is accurate, consistent, and complete throughout its entire lifecycle.
1 The Hook — Why This Matters
In 2021, a South Island DHB migrated 50,000 patient records from a legacy system to a new clinical platform. The go-live appeared smooth: the UI loaded, logins worked, and dashboards sparkled. But three weeks later, a nurse noticed that 12 patients with known penicillin allergies showed "No known allergies" in the new system.
The root cause was a data transformation error. In the legacy system, unknown allergies were stored as NULL. The migration script mapped NULL to the string "none" in the target database. But the target system interpreted "none" literally — meaning "no allergies" — rather than "not recorded." Worse, the audit trail showed the migration completed successfully with no exceptions, because the script had verified row counts, not field-level accuracy.
Row counts matched. Business logic failed. Patients were at risk. Data integrity testing is not a checkbox. It is a lifesaver.
Senior engineer insight
The moment that changed how I think about data integrity testing was discovering that our migration reconciliation scripts were themselves wrong — we were comparing masked test data in staging, and the masking function silently dropped trailing zeros from patient IDs, so numeric IDs like 0012345 became 12345. All counts matched; every ID was corrupted. Now I always include an exact-string comparison on at least one ID column, not just numeric equality, and I verify the verification environment uses the same encoding as production.
Most common mistake: designing reconciliation checks after the migration script is written, so the checks inherit the same transformation assumptions as the script instead of independently challenging them.
From the field
On a HealthNZ project to consolidate three regional patient management systems into one national platform, the team ran row counts, sum checks, and a 200-record sample — all green. What they did not test was the Privacy Act accuracy obligation: that a patient's preferred name, as updated by the patient themselves in the old portal, had propagated to the new system. The legacy system stored preferred name in a separate preferences table with a nullable foreign key. The migration script joined on patient_id, but the preferences table had been populated by a different team using a different source ID. The join silently produced zero matches for 8,400 patients, and those records migrated with legal name only. Fourteen patients complained within a week that the new portal was using a name they had legally changed. The lesson: Privacy Act accuracy is not just about not losing data — it is about not losing the version of data the person chose to be identified by.
2 The Rule — The One-Sentence Version
Verify that data is accurate, consistent, and complete at every stage of its lifecycle — not just at rest.
Data integrity is not a single test. It is a discipline. You must validate data on creation, on read, on update, on delete, during migration, during transformation, and in every report that consumes it. A number that is correct in the database but wrong on the invoice is still a bug.
3 The Analogy — Think Of It Like...
A blood bank tracking donations from donor to recipient.
It is not enough to count the bags arriving at the depot. You must check the blood type on the label matches the donor record, that the fridge temperature log is unbroken, that the transfusion record links back to the correct bag, and that every movement is logged with who moved it and when. If any link breaks, the system is dangerous. Data integrity is the chain of custody for information.
4 Watch Me Do It — Step by Step
Here is a real NZ example: migrating 50,000 patient records from a legacy clinical system to a new platform. Follow these steps for any data migration or transformation.
- Verify CRUD operations at the database layer Create a patient record through the UI, then query the database directly. Confirm every field lands correctly. Update a field. Delete a record and verify whether it is a soft delete (status = inactive) or hard delete (row removed). Test both paths.
-
Test referential integrity
Every foreign key must point to a real record. Run:
SELECT * FROM appointments WHERE patient_id NOT IN (SELECT id FROM patients);Orphaned records break reports and compliance audits. -
Validate the migration with row counts and sampling
Compare source and target at the aggregate and detail level.
- Row count:
SELECT COUNT(*) FROM source.patientsvsSELECT COUNT(*) FROM target.patients - Sum check:
SELECT SUM(balance) FROM source.accountsvs target - Field-level sampling: randomly select 100 records and compare every field
- Row count:
- Run automated reconciliation scripts Build SQL or scripted checks that compare source and target. Automate these and run them in CI after every migration rehearsal. A script that takes ten minutes to write can save weeks of manual checking.
- Test data transformations explicitly Map every transformed field. Date formats (NZ DD/MM/YYYY vs ISO), null handling, currency rounding, and coded values. The penicillin allergy bug came from a transformation assumption, not a missing row.
-
Verify audit trails
Every create, update, and delete must log who did it and when. Query the audit table:
SELECT * FROM audit_log WHERE entity = 'patients' AND migrated = true;If the migration itself is not auditable, compliance fails.
| Check | Method | Pass criteria |
|---|---|---|
| Row count | COUNT(*) source vs target | Exact match |
| Sum check | SUM(balance) source vs target | Exact match to 2 decimal places |
| Null handling | Sample 100 records, check NULL mapping | NULL stays NULL; no silent conversions |
| Date formats | Spot-check date fields | All dates in DD/MM/YYYY |
| Referential integrity | Orphan check on all FKs | Zero orphaned records |
| Audit trail | Query audit_log table | Migration batch logged with timestamp and operator |
5 When to Use It / When NOT to Use It
✅ Use data integrity testing when...
- You are migrating data between systems
- You have ETL pipelines or data transformations
- The system handles financial, medical, or compliance data
- You support reporting, analytics, or reconciliations
- Auditors or regulators require data lineage proof
- Multiple systems share the same data source
❌ Don't rely on it alone when...
- The bug is in UI rendering, not data itself
- You need to test usability or accessibility
- Performance under load is the primary concern
- You have not verified the source data is clean
- You are testing a greenfield system with no data yet
Before you start data integrity testing, ask:
- Have you profiled the source data for quality issues, or are you assuming it is clean?
- Do you have SQL expertise available, or will you need a data analyst to write validation queries?
- Can you test the transformation logic in isolation (unit test the ETL), or only end-to-end?
- What is your rollback strategy if data corruption is discovered in production?
6 Common Mistakes — Don't Do This
🚫 Only testing through the UI
I used to think: If the screen shows the right data, the database is fine.
Actually: The UI may cache, transform, or mask errors. The DHB migration passed every UI test. The bug only appeared when a nurse cross-referenced the legacy printout. Always verify at the database layer.
🚫 Assuming data quality in the source
I used to think: The legacy system has been running for years, so the data must be clean.
Actually: Legacy systems accumulate decades of dirty data: duplicate records, orphaned rows, invalid dates like 31/02/1990. Profile source data before migration. Fix it in the source or handle it in the transform script.
🚫 Ignoring the rollback plan
I used to think: If the migration fails, we just run it again.
Actually: A failed migration can leave the target system in a partially written state. Test the rollback procedure in staging. Verify that rolling back leaves both source and target clean and consistent. A migration without a tested rollback is a gamble.
When this technique fails
Data integrity testing fails when it only happens at the UI layer—subtle database corruption can hide for months until a reporting query breaks. It also fails when source data is assumed clean: moving bad data to a new system is faster data corruption, not a fix. Finally, without a proven rollback strategy, a discovered integrity issue becomes a release-blocking crisis instead of a learning point.
7 Now You Try — Migration Detective
Scenario: You are testing a migration of 10,000 customer accounts from an old billing system to a new one. The migration report says "10,000 rows migrated successfully." You run these queries:
SELECT COUNT(*) FROM source.accounts; -- returns 10000 SELECT COUNT(*) FROM target.accounts; -- returns 10000 SELECT SUM(balance) FROM source.accounts; -- returns 4859320.55 SELECT SUM(balance) FROM target.accounts; -- returns 4859320.54
What is your assessment? Write it down before revealing the answer.
🚨 Migration failed — do not proceed to production.
Row counts match, but the sum check is off by $0.01. In a billing system, even a one-cent discrepancy means rounding errors or dropped precision somewhere in the transform. At 10,000 rows, a $0.01 error could be a single record with a $0.01 difference, or a systematic rounding bug affecting every record.
Next steps: Run a field-level diff to find the offending record(s). Check whether the source stored currency as DECIMAL(19,4) and the target as DECIMAL(19,2). If so, every record with fractions of a cent was rounded. That is a design issue, not a one-off.
Why teams fail here
- Reconciliation checks are designed by the same person who wrote the migration script, so both share the same flawed transformation assumption and the check never catches the bug.
- Teams test with anonymised or reduced-volume datasets that lack the edge cases present in production — orphaned records, null-heavy columns, and duplicate keys that only appear at scale.
- Audit trail testing is added as an afterthought, so nobody verifies that bulk migration operations are logged at the record level — only that the batch-level summary entry exists.
- NZ-specific date and timezone handling (NZST/NZDT transitions, DD/MM/YYYY vs ISO 8601) is not in the transformation spec, so the migration script inherits the source system's locale assumptions and no one tests the boundary at midnight during a DST changeover.
Key takeaway
A migration is not complete when the data arrives — it is complete when you can prove that every value means the same thing it meant before it left.
8 Self-Check — Can You Actually Do This?
Click each question to reveal the answer. If you got all three, you are ready to practice.
Q1. Why is a row-count match not enough to declare a migration successful?
Because every row could contain corrupted fields. Row count only proves the quantity of data moved, not the quality. You also need sum checks, field-level sampling, referential integrity validation, and transformation verification.
Q2. What is the difference between a soft delete and a hard delete, and why does it matter for data integrity?
A soft delete marks a record as inactive but keeps it in the database. A hard delete removes the row permanently. Soft deletes preserve referential integrity and audit history, but you must test that soft-deleted records do not appear in active reports or searches. Hard deletes risk creating orphans if foreign keys are not handled.
Q3. What should an audit trail capture for every data change?
At minimum: who made the change, what changed (before and after values), when it changed (timestamp), and why (transaction type or user action). For migrations, the audit trail should also capture the batch ID and the operator who triggered it.
9 Interview Prep — What They Will Ask
Q1. How do you verify a database migration was successful?
I use a layered approach: (1) row count comparison between source and target; (2) sum and aggregate checks on numeric columns; (3) field-level sampling of random records; (4) referential integrity checks for orphaned records; (5) transformation validation for dates, nulls, and coded values; (6) business rule verification on a subset of records; and (7) a rollback test to ensure we can undo safely.
Q2. A migration passes row count and sum checks, but users report missing data. Where do you look first?
I check the filter logic in the migration script. It is common for migration scripts to include a WHERE clause that silently excludes records — inactive accounts, soft-deleted rows, or records with a specific status. The excluded records do not affect row counts if they were never meant to migrate, but if the business expected them, the filter was wrong. I also check for duplicate key collisions that caused inserts to be skipped.
Q3. How do you test data integrity in a microservices architecture where multiple services write to the same data store?
I test concurrent writes: two services updating the same record simultaneously to check for lost updates or stale reads. I verify eventual consistency boundaries: how long until all services see the same value? I also check schema compatibility across services, and run contract tests to ensure each service writes data in the format others expect.
Q4. What is your approach to testing audit trails?
I treat the audit trail as a first-class requirement. I verify that every CRUD operation generates an audit record, that the audit record is tamper-resistant (separate schema or append-only), that timestamps are in the correct timezone (NZST/NZDT), and that audit queries perform acceptably over millions of rows. I also test that bulk operations — like migrations — generate audit entries, not just individual UI edits.