Data integrity testing is the practice of verifying that data stays accurate, complete, consistent, and valid as it moves or changes. During a data or database migration, it proves that every record, key, relationship, and value in the target matches the source and still obeys the rules the source enforced.
Data migrations often fail quietly: the load finishes, counts look right, and the first wrong number appears in a report weeks later. This guide gives data engineers and QA leads a short test plan and shows how ETL Validator automates it.
Key Takeaways
- Data integrity testing in a data migration verifies that the target holds every source record with the same values, keys, and relationships.
- A matching row count proves the load finished. Missing, duplicated, truncated, or rounded values can still sit behind a passing count.
- Test in three phases: baseline the source, reconcile each load, and compare every record after cutover.
- Datagaps ETL Validator automates these checks across 200+ data sources and keeps run evidence for audit
Why Does Data Integrity Break During Data Migration?
Integrity breaks because source and target databases handle types, NULLs, character sets, and constraints differently, and bulk loads can drop or repeat rows without raising an error.
Delivery records are mixed. McKinsey’s October 2021 survey of nearly 450 CIOs and IT leaders found 75% over budget on cloud migration and only 15% on time and on budget, and Bloor Research reports that more than 60% of data migration projects overrun on time, budget, or both. Gartner (February 2025) predicts organizations will abandon 60% of AI projects through 2026 when the data is not AI-ready, so damage done during migration travels downstream.
The checks below map to Datagaps ETL Validator, part of Datagaps DataOps Suite. ETL Validator tests data as it moves between databases, files, and cloud platforms, using five components: Metadata Compare checks structure, Data Profile captures column statistics, Metrics Compare reconciles aggregates, Data Compare matches records on a unique key, and Data Rules runs integrity and business rule checks. The table below lists the failure modes to check first and the component that catches each.
| Failure mode | What happens | ETL Validator component |
|---|---|---|
| Precision loss | A NUMBER(12,3) column lands in DECIMAL(12,2) and values round. | Data Compare, with threshold options for documented tolerances |
| Truncation | Text wider than the target column is cut. | Metadata Compare (column length mismatches) |
| NULL versus empty string | Oracle Database treats a zero-length string as null (Oracle SQL Language Reference). Most engines do not. | Data Profile (NULL counts); Data Compare |
| Duplicate or missing rows | A retried batch repeats some rows and drops others. | Data Compare on a unique key; Check Uniqueness |
| Lost constraints | Foreign keys or NOT NULL rules are not recreated. | Metadata Compare (foreign key and NOT NULL mismatches); Data Rules |
What Are the Four Types of Data Integrity to Test?
Test entity, referential, domain, and user-defined integrity. Each one fails differently during a migration.
- Entity: every row has a unique, non-null key. Duplicate loads break it.
- Referential: child rows point to parents that exist. Load order and dropped foreign keys break it.
- Domain: values fit the allowed type, range, and format. Type mapping and truncation break it.
- User-defined: business rules hold. Logic that lived in triggers or application code breaks it.
The Datagaps database testing guide covers the schema and constraint checks behind these types.
How Do You Test Data Integrity Before, During, and After a Data Migration?
Run data migration testing in three phases: baseline the source before migration, reconcile each load while it runs, and compare every record after cutover. Each phase produces evidence for the next.
Step 1, Before:
Profile every in-scope table (row counts, distinct keys, NULL counts, minimum and maximum values) and compare source and target metadata. Write the expected result and the rounding tolerance for each check before running it.
Step 2, During:
Compare counts and aggregates per batch, test keys in both directions, and review rejected rows. Re-run after incremental loads. The Datagaps blog on data reconciliation best practices that covers the techniques.
Step 3, After:
Compare every record on its business key, reconcile aggregates at reporting grain, run referential and business rule checks, and keep the reports as the sign-off record. The Datagaps complete guide to data migration testing covers the lifecycle in more detail.
Why Is a Matching Row Count Not Enough?
Row counts prove a load finished. They do not prove the data is right.
A passing count can hide a missing record and a duplicate that offset each other, a number that lost precision, text cut to fit a narrower column, or an empty string that became NULL. None of these changes the count.
ETL Validator checks each defect where it appears. Metadata Compare reports columns only in the source or target, column length mismatches, data type mismatches, foreign key mismatches, and NOT NULL mismatches. Figure 2 shows the Column Length Mismatches view for an Oracle to MySQL comparison, with a Download Report option for evidence.
Data Compare then matches records on a unique key. Check Uniqueness tests the selected key across the full dataset, and a result below 100% means duplicate keys exist. Auto Detect Unique samples the first 1,000 records to suggest a key. Figure 3 shows the warning for a key combination that is 97.67% unique.
How Does Datagaps ETL Validator Automate Data Migration Integrity Testing?
Datagaps ETL Validator, part of Datagaps DataOps Suite, automates the three phases with Metadata Compare, Data Profile, Metrics Compare, Data Compare, and Data Rules components. It connects to more than 200 data sources, and Datagaps positions it as full-record validation with no sampling. Reports and run logs serve as migration evidence.
- Connect source and target through JDBC. Oracle, SQL Server, PostgreSQL, Snowflake, Databricks, and Azure Synapse are also the supported platforms.
- Run Metadata Compare to flag column length, data type, foreign key, and NOT NULL mismatches before data is compared. Narrower target columns appear here before any data is compared.
- Profile the source with Data Profile and compare aggregates with Metrics Compare.
- Run Data Compare on the business key. Check Uniqueness flags keys that are not fully unique, and Custom Expressions with the AI Assistant normalize formats before comparison.
- Add Data Rules (seven rule types) for orphan, duplicate, and business checks. Schedule the run, download the comparison report, and open a Jira ticket from a failed batch.
One dataset stores a date as a timestamp and the other stores it as a string, so a direct comparison would flag every row. The user types a plain-language prompt, “convert to date”, and the AI Assistant drafts a Spark SQL expression for each column. The Test option shows values before and after casting, so a reviewer confirms the result before the comparison runs.
What Should a Data Migration Testing Checklist Include?
- Before: metadata compare, a source profile, and documented tolerances.
- During: per-batch counts and aggregates, keys in both directions, and explained rejected rows.
- After: zero duplicate keys, a full record compare, and referential and business rule checks.
- Downstream: reports that read the migrated tables match the source system.
- Sign-off: all results and approved exceptions stored as evidence.
Templates are in The ETL Testing Playbook and the data warehouse testing checklist. BI Validator covers report checks, and compliance solutions map evidence to audit needs.
Conclusion: How Do You Prove a Data Migration Kept Data Intact?
Data integrity testing gives a data migration a documented result instead of an assumption. Test entity, referential, domain, and user-defined integrity, then baseline the source, reconcile each load, and compare every record after cutover. A row count alone never closes the migration.
ETL Validator, part of Datagaps DataOps Suite, covers each step with Metadata Compare, Data Profile, Metrics Compare, Data Compare, and Data Rules, and keeps comparison reports and run logs as evidence. The data migration testing automation and data reconciliation pages describe the full workflow. Start with one source table and its migrated copy to see what a full comparison reports.
Frequently Asked Questions
What Is Data Integrity Testing in a Data Migration?
It verifies that the target database holds the same records, keys, relationships, and values as the source and still enforces the same rules. It combines metadata comparison, reconciliation, record-level comparison, and rule checks.
How Do You Validate Data After a Database Migration?
Database migration testing validates data in four moves: compare metadata, reconcile counts and aggregates, compare every record on its business key, and run referential and business rule checks. Set tolerances before the run and keep the reports.
Can Data Integrity Testing Be Automated?
Yes. Datagaps ETL Validator provides Metadata Compare, Data Compare, Metrics Compare, and Data Rules, with a scheduler, command line, and REST API for CI/CD. AI-drafted tests still need human review. See the Datagaps guide to data quality for AI readiness.
Should You Test a Sample or Every Record?
Samples find frequent defects quickly but can miss rare ones, such as a few truncated values in millions of rows. Datagaps positions ETL Validator as full-record validation with no sampling.
Book a personalized 30-minute demo with your own data scenario, or start a 14-day free trial. See pricing or the ETL Testing Complete Guide first if you prefer.
RajMohan Achanta
Associate Product Manager, Datagaps
Associate Product Manager at Datagaps. Shapes the product experience across ETL Validator, BI Validator, and DataOps Data Quality.
Avinash Keshri
Head, Product Marketing
Head of Product Marketing at Datagaps and IIM Bangalore alumnus. 13+ years of experience in commercializing AI and data platforms across global markets.
