What is the best way to validate migrated data?

In data migration, validating the integrity of the migrated data is pivotal. To pinpoint any pre-existing issues, it's essential to conduct validations at various phases, including pre-migration. A meticulous examination of fields and values and comparing data post-migration are integral steps to assure accuracy. Cleaning data before migration eliminates redundancy, and re-evaluating constraints and stored procedures ensures the maintenance of precise results in the new system. Tools like SQL Data Compare can assist in contrasting large datasets post-migration, providing business rules and mappings are adhered to.

I think when we migrate data, quality is one of the crucial things because proper quality check gives the confidence of completeness to all the people who are involved during migration. I believe those tasks that we do in the cleaning process like checking the completeness, missing values, the constraints mainly the primary and secondary keys. These things we can do using any tool that the user feels is considerable. The validation sometimes takes time and if you do some sampling after these cleanliness tasks you also increase the credibility of the overall system.

Validating migrated data is crucial to ensure the accuracy and reliability of data after the migration process. For instance, during the migration of customer information, validating the data can help identify and resolve any errors or discrepancies that may have occurred during the transfer. Without proper validation, the target system could suffer from corrupted or duplicated data, impacting its functionality and performance. Moreover, validating migrated data is essential for compliance with regulatory and security standards, ensuring that the data meets the expectations and requirements of stakeholders and users.

Assuming row counts in the millions and below, migrated data should be verified on every row/column after each migration, and continuously during parallel run. Tools like Alteryx are amazing for this, and with Gen AI likely to get even better.

When to validate migrated data depends on various factors, but it's generally recommended to do so at multiple stages of the migration lifecycle. For example, before migration, validating the quality, structure, and format of the source data can help identify potential challenges or gaps. During migration, validating the data during the transfer helps monitor progress and detect any errors. After migration, validating the accuracy, completeness, and usability of the data in the target system ensures a successful migration outcome. By validating at different stages, you mitigate the risk of data loss or corruption.

Migrated data should be validated at multiple stages. If there's a test phase then the main purpose of that phase should solely be to validate the data. Check for adata accuracy, completeness and usability in the target system.

From my perspective, validating migrated data can be approached in different ways depending on the specific requirements and characteristics of the data. Manual validation is suitable for smaller or simpler migrations, where data is visually inspected or compared using queries or formulas. However, it can be time-consuming and subjective. On the other hand, automated validation using specialized software or scripts can perform predefined checks or leverage advanced techniques like data profiling or reconciliation. This approach is efficient for larger or more complex migrations, ensuring consistency and accuracy.

To aporach this from one specific angle (namely SQL database/warehouse) there are a few very simple reconciliation checks that can be performed and don't require any specific software: - the quickest test is to compare table sizes (ideally in bytes rather than GB/TB). Whilst a match will not guarantee that the tables are identical, a mismatch means that there is certainly a difference between the two - if there are no restrictions for joining the two tables (i.e. datasets in two different regions in BigQuery won't allow it), then each row can be hashed and the two tables can be joined on the hashed values for examination - an easier approach is to generate a checksum for the each table and comparing the two values

Drawing from personal experience, I'd like to emphasize that an accessible first step in manual validation is contrasting aggregate numeric values between source and target systems. In a project related to sports betting data migration I was part of, we quickly checked the integrity of the transferred data by comparing key metrics such as the Number of Rows, Unique Customers, Total Wagers, and Gross Gaming Revenue. If these didn't align between the two systems, we'd known something was off. Similarly, with customer service data, looking at numbers like Total Calls Received, Answered, or Rejected can be super helpful. If these basic checks uncover problems, they often guide us to where we need further investigations.

Randomized manual validation can be a good way if data can be compared visually between different systems. Picking random instances to validate helps locate the discrapencies.

When it comes to validating migrated data, there are several best practices to follow. Firstly, defining a clear validation strategy and plan before starting the migration is crucial. This includes determining the criteria, metrics, methods, and tools for validation. Testing the validation methods and tools for reliability and compatibility is also important. Additionally, performing validation at multiple levels and stages of the migration, such as data level and system level, ensures comprehensive validation. By following these best practices, you can ensure the accuracy and integrity of migrated data.

Validating migrated data indeed comes with its own set of challenges and risks. The volume and variety of data, along with the complexity of transformations and mappings, can make the process time-consuming and difficult to manage. It's important to consider security and privacy concerns, ensuring compliance with regulatory and ethical standards. Furthermore, dependencies and relationships within the data can introduce potential inconsistencies or errors. Successfully addressing these challenges requires careful planning, robust validation methodologies, and proper data governance practices to ensure the accuracy and integrity of the migrated data.