English | EN 
Introduction
In the first chapter of this series, we explored the importance of planning, preparation, and establishing a structured approach to data migration testing. Once the migration strategy has been defined, the next priority is ensuring that the migrated data remains accurate, complete, and reliable. This is where Data Integrity Testing plays a critical role.
Regardless of the technology, platform, or business domain involved, the success of a data migration ultimately depends on the quality of the data delivered to the target environment. If data is lost, duplicated, corrupted, or incorrectly transferred, business processes may fail, reports may become unreliable, and user confidence in the new system can quickly diminish.
Data Integrity Testing focuses on confirming that information transferred from the source system to the target environment remains unchanged, complete, and consistent throughout the migration process. It provides assurance that records have been migrated correctly, relationships between datasets remain intact, and critical business information continues to support operational and reporting requirements. Think of it as moving a valuable collection of documents from one archive to another. It is not enough to move all the boxes successfully. Every document must arrive complete, organised, and accessible, with nothing missing or misplaced. Data Integrity Testing provides the same level of confidence for business data.
This chapter explores six key validation activities that help organisations establish trust in their migrated data and create a solid foundation for all subsequent testing activities.
1. Record Count Verification
One of the simplest and most effective ways to assess migration success is to compare the number of records in the source and target environments.
Record Count Verification ensures that all expected records have been migrated successfully by comparing table counts, file counts, or dataset totals across both systems. This activity is typically performed using SQL queries, reconciliation scripts, or automated validation tools.
For example, if the source system contains 500,000 customer records, the target system should contain the same number unless specific transformation or cleansing rules have been applied.
Although record count verification does not confirm the quality of individual records, it provides an important first indication that the migration has completed successfully and that no significant data loss or duplication has occurred.
2. Data Completeness Checks
Having the correct number of records is only part of the story. Each record must also contain all required business information. Data Completeness Testing verifies that mandatory fields have been populated correctly and that critical data elements have not been omitted during migration. This includes information such as customer identifiers, dates, product codes, financial values, classifications, and other business-critical attributes.
For example, a customer record may exist in the target environment, but if key fields such as address details or account status are missing, operational processes may fail despite the successful migration of the record itself.
By identifying missing or incomplete information early, organisations can prevent downstream system failures and improve confidence in the quality of the migrated data.
3. Duplicate Data Detection
Duplicate records can create significant business and operational problems. Data may become duplicated for various reasons, including repeated migration runs, mapping errors, integration issues, or inconsistencies across multiple source systems.
Duplicate Data Detection verifies that unique records remain unique following migration. Testing normally focuses on primary keys, customer IDs, account numbers, transaction references, and other unique business identifiers.
For example, duplicate customer records may result in inaccurate reporting, multiple communications being sent to the same customer, or incorrect financial calculations.
Detecting and resolving duplicate records helps maintain data quality and ensures the migrated environment remains accurate and reliable.
4. Foreign Key and Relationship
Many business applications depend on complex relationships between data entities. Customers have orders. Orders contain products. Employees belong to departments. Patients have medical histories. Foreign Key and Relationship Testing ensures that these relationships remain intact following migration.
Testing validates that foreign key references continue to point to valid records and that parent-child relationships have been preserved correctly. Any broken relationship can affect application functionality, reporting, and business processes.
For example, customer and order data may both be migrated successfully, but if the relationship between them is broken, users may no longer be able to access complete customer histories or generate accurate reports.
Maintaining referential integrity is therefore essential for preserving both system functionality and business value.
5. Checksum or Hash Verification
For migrations involving large volumes of data, organisations often require a higher level of assurance than record comparisons alone can provide. Checksum or Hash Verification uses cryptographic hash functions to confirm that datasets remain unchanged during migration.
A hash value acts as a digital fingerprint for a dataset. If even a single character changes, the resulting hash value will differ.
Common algorithms include:
- MD5
- SHA-256
By generating hash values for both source and target datasets and comparing the results, organisations can quickly identify corruption, truncation, or unintended modifications.
This approach is particularly useful when migrating sensitive, regulated, or business-critical information where absolute confidence in data accuracy is required.
Note: MD5 and SHA-256 are cryptographic hash functions that generate a fixed-length value from a file or dataset. They are widely used for data integrity validation, cybersecurity, and file verification.
6. Transactional Data Verification
Transactional information often represents the most important data within an organisation.
Examples include:
- Customer orders
- Payments
- Invoices
- Claims
- Bookings
- Financial transactions
Transactional Data Verification focuses on ensuring that these records have migrated accurately and remain consistent across both environments.
Testing typically involves capturing samples or snapshots of key transactions before migration and comparing them with corresponding records after migration.
For example, invoice totals, payment values, transaction dates, account balances, and reference numbers may be compared between source and target systems.
Even small discrepancies can have significant consequences, affecting financial reporting, customer trust, regulatory compliance, and day-to-day business operations. Validating transactional records helps ensure the integrity of core business processes following migration.
Conclusion
Data Integrity Testing is one of the most important stages of any migration programme because it establishes confidence in the accuracy and reliability of the migrated data. By validating record counts, checking data completeness, detecting duplicates, preserving relationships, verifying data through checksums, and reconciling critical transactions, organisations can significantly reduce migration risk and improve confidence in the target environment.
Without effective integrity testing, even a technically successful migration can create operational challenges, reporting inaccuracies, compliance concerns, and loss of user trust. Most importantly, Data Integrity Testing answers a simple but fundamental question: Can the business trust the data after migration? Only when that question can be answered with confidence should the migration proceed to the next stages of testing.
Next Chapter
Part 3 of 10: Data Transformation Testing explores how organisations can validate mapping rules, field conversions, calculations, business logic, and transformation processes to ensure that data remains meaningful and usable in the target environment.