Post-migration validation - is there an industry standard?

Question received from member:

"I'm interested to see if there are some 'standard' guidelines for the volume of data or other metrics that should be considered when establishing a data validation effort post migration.

I would like to see if there is a metric to corroborate or enhance the approach we're defining for our migration effort.

Is there an industry standard for testing coverage pertaining to a data migration/integration project?"

Comments

[Derek Munro]

This is a pretty big question and concerns your approach as much as anything else.

From a technical point of view, the simple answer is “full volumes”, because the data migration has to be a 100% auditable operation.
Every record and value has to be accounted for during the execution of the process – specifications and architecture diagrams aren’t enough.
A data warehouse can still provide valuable information if a few records are “missing”. Migrating operational systems has to be 100% accurate, otherwise you may have customer satisfaction or even legal problems.

From the very beginning, the migration programs must be built with “audit” in mind.
- Counts of records read and written at each stage (the result of a join may be more or less records than originally read)
- Counts for records “filtered out” (either explicitly by rules or quietly by relational joins) and records rejected.
- Sum monetary amounts
- etc.
If the target application happens to already contain data, this should all be “profiled” to help in the analysis and specification process . e.g. what values does the target application already handle for “status code”, or why do I never see more than one phone number per customer record...

As soon as the data has been moved you should be ready to analyse the content of the target application,
- counting exactly how many records have been loaded into each table
- summing monetary amounts
- evaluating the relationships between tables to ensure they meet requirements

The results of your analysis of the target system should match the information produced by the executing migration programs, so you can only achieve this with full data volumes.
Since data structures and levels of information will probably have changed, the counts and sums for source(s) and target are not directly comparable, so you will have determined beforehand exactly what information can be compared and in what way. E.g counts from a source may need to be grouped and summed to be comparable with target counts.

From a business point of view, the people qualified to write/approve the specifications should have established a representative set of test cases during the analysis phase of the migration to enable them to validate that the implementation corresponds to the specifications. The number of test cases depends on the complexity of the rules they wish to apply to the source data and the different scenarios catered for.
Of course the specifications may not be 100% right (even the best analysts miss stuff!), so they should also have prepared test cases and scenarios (data prototypes) based on what they expect to get from the migration programs, and loaded these into the target application to ensure that the target application behaves as they expect.
This data preparation required for validating the specifications is usually a laborious, manual process, but “data prototyping” solutions are emerging now to automate this.
The alternative to prototyping is to wait for the implementation team to load data into the target application, and then “try it out”. Since this usually happens towards the end of the project this is a bit like jumping out of a plane with a newly designed parachute – you know if it’s working just before you land.
This same data, plus some cases necessary to validate new functionality in the new application should be used for final acceptance testing by the business.

Good luck with your migration!

Derek Munro

[David Katzoff]

Derek provides some good points.

I have been involved in several migrations in the FDA regulated space where migration data validation is extremely important from a risk perspective. Another thing you may want to explore is automated approaches to testing the validation that can provide more granular testing. We have used these types of techniques to provide 100% verification where required and there are products on the market that can facilitate this type of testing. They become especially useful when incorporated into the migration configuration/development/test process to identify issues prior to the actual migration and have provided cleaner results than looking at counts and sample data sets.

Regards,

Dave

[Ram Kotamraju]

Hi Derek, can you point us to some products that facilitate these automated testing of the migration. We are using tool for migration that provide very mediocre audit capabilities and looking for some tool which provides a robust report on the Migrated data base in comparison with the source. BTW, we are migrating a DB2 database on mainframe Z-os to SQl Server 2008 on Windows 2008.

Thanks
Ram