Data migration testing project - what are the key things to consider?

Hi, currently I am working on a data migration testing project and I would like to know the key important things of data migration from a testing perspective.

Things like identifying the key testing areas, how to go about developing test case / scripts, what Testing techniques to be used in the process etc.

Also, it would be very helpful if I can get access to some sample test cases and test scripts..

Looking forward for your help.

Thanks and Regards.

Comments

[Paul Johnson]

The exact type of testing and how the testing is performed will depend on your particular project and environment, but generally you need to make sure the following is done.

1. Data Quality Testing/Checking

This is where the records on the target system are checked for accuracy against the legacy data. There are various methods of doing this. On a recent project we created checklists for the testers to follow. These were in the form of spreadsheets which had sample data inserted from the old system on the main worksheet. On additional worksheets there were mapping tables so that the tester could identify any old values that had been mapped to new values. The testers simply ran through each record using the target system and updated the spreadsheets if and when differences were seen. Any differences were then forwarded to the migration consultants and once confirmed were entered onto an issue logging system so that these could be tracked until resolved.

Don't forget that not all fields will have been migrated from the legacy system, some will have been defaulted by the migration routines.

Theres a couple of things to bear in mind at this stage.
Don't leave it entirely up to the testers to choose which records to check. This must be driven at least in some part by the migration consultants as they will know which subsets of records are likely to have potential issues.
Also you'll have to decide how many records you're going to check in this way. This will probably vary within the same project. For example you may have 20000 customer records and it may be feasible to check between 5 and 10 percent of these. The same migration may have 300000 claim or order records attached to customers, you'll probably stuggle to check 10 percent of these, so you'll probably need to go to more like 1% (which is still 3000).

So don't underestimate the time this is going to take.

2) Testing the Target systems functionality with migrated data.

Does the target system perform as it should with migrated data. Your data may have migrated well and look accurate, but usually its the data you can't see on the screen (such as flags, control records and counts etc) which cause problems here and cause the target system to either error or do unexpected things. This is where you will need to write testscripts for the testers to perform day to day tasks on the target system. The testscripts should be written by using business knowledge, but again you may need some input from the migration team as to which records to check for the same reasons as in step one.

Each part of the system should be checked to ensure that it is performing as it should. For instance can you add a new customer, can you add a order record to a migrated customer, what about an accounts record, do the amend routines work correctly, do the month end accounts routines all work as expected, do any extracts to other systems take the records as expected. These are just some of the areas you'll need to cover.

Again theres a lot to consider and care should be taken to ensure that nothing is last to chance.

Regards,
Paul Johnson

www.insurancedatamigrations.co.uk

[David Katzoff]

Paul's response is right on the money especially the second part about testing the data in the target application. I have been involved in numerous migration projects, and while there is typically a heavy focus on testing the data, there have been many cases where the target system users were surprised by migrated data that didn't entirely behave or appear as expected. This is a lesson learned that I mention during the planning stages of the migration projects in which I am involved.

One thing I would add is that there are automated approaches available for testing that the data was moved and transformed as required. Using an automated process enables you to increase the level of sampling and potentially test 100% of the data depending on the size of the migration and criticality of the data. My experience has been that using such approaches where appropriate increases the accuracy and precision of the migration.

Regards,

Dave

[Danu]

I have a question for David : Could you please suggest some names for the automated tools for testing data migration?

[David Katzoff]

The tool that we use for automated migration testing is TRUcompare which performs an end-to-end comparison of the source and target data. TRUcompare has allowed us to provide our customers with 100% verification that the data has been migrated correctly and has been instrumental in enabling us to complete successful production migrations with few surprises as we can test all of the data and address all of the issues prior to production. There are also some pre-migration testing features that help identify mappings that result in invalid data and source data that needs to be cleansed. Here is the URL to get more information.

http://www.valiancepartners.com/products_trucompare.htm

If you have any other questions, feel free to email me at dkatzoff@valiancepartners.com

[Krish kumar]

Hi David,
TRUcompare is an open source tool? if not can you suggest me one open source tool which performs end to end comparision of source and target data?

Thanks
krish

[Dylan Jones (Founder)]

Krish - there are no open source tools that do this out of the box I believe, TruCompare is not open source.

You may able to build something via an ETL type tool and there are several which are open source, just search on google.