We are migrating our production data (DB and filesystem) from a fairly complex data model to another fairly complex data model. The migration process is given by the specification as a set of mapping rules and transformation functions.

I'm planning a testing strategy for this migration process. To identify the riskiest areas here, I've read some lessons learned from migration testing and ETL tools testing in general and compared them with our migration process specification.

Where the things may go wrong?

  1. For backward compatibility reasons, the new data will be provided by the new application to the old modules through adapters. Old modules will do their job using migrated data as they used.
  2. The new data model will support some new application features.
  3. The new data model will no longer support some old (usually used infrequently) application features.
  4. Nullable fields in the old model are no longer nullable.
  5. Files in the filesystem are defined in a different format.
  6. Data that used to be decoupled in the old system will now be joined (e.g. customers with their projects) based on:
    • automatic rules
    • administrator rules
  7. New data formats will be used

What may go wrong?

  • Values for data used by new features may have incorrectly defaulted.
  • Adapter may translate new data to legacy format incorrectly.
  • Not all entities may be migrated, e.g., some old client configurations cannot be found in the new system.
  • Not all data (properties) may be migrated.
  • New data model may use data formats may not be able to store some old data structure (e.g., VARCHAR vs. BLOB)
  • Migrated files may not be readable for old modules
  • etc.

My very first idea is to combine two testing strategies here:

  • Low-level testing strategy that will be relatively thorough but will have little coverage. The idea is to isolate a most representative sample of original data (that makes sense for the business, e.g., single customer and its documents). Then, for each mapping rule define real expected output for the corresponding part of the sample data. Automate checks.

  • High-level testing strategy that will be less thorough but will cover the majority of application functionalities and try to identify integration problems with old modules. The idea is to migrate all data first. Then perform user test scenarios that involve all functionalities of the new application and of the old modules.

What other strategies would you suggest to discover bugs for the identified risks?

  • In production, will all customers be migrated at the same time, or will you migrate them gradually?
    – user246
    Feb 13, 2012 at 2:31
  • All at the same time. The reason is to avoid hosting both old and new version product at the same time. Why do you ask?
    – dzieciou
    Feb 13, 2012 at 12:02
  • 1
    An advantage of migrating gradually is that you can learn from your mistakes. Of course it requires hosting (and maintaining) both versions simultaneously. This may not be practical for you, and in any case it is tangential to your question.
    – user246
    Feb 14, 2012 at 1:52

7 Answers 7


Having been involved in a few data migrations myself, I'd say you have a pretty good start down the right track. Creating baselines of expected behaviour prior to the migration and comparing them after the migration will be useful, but as you mentioned there are a number of things that are expected to change so the results will not be exactly the same and there will be some manual effort involved to compare the results and ensure they meet expectations.

As you already outlined, you plan on running regression tests against the application to ensure that the functionality has not been broken. Hopefully, you already have automated tests or a well defined manual test suite for most of this.

Some additional things that I would watch out for from my own personal experience:

  1. Make sure you take a look at the current edge cases and that they are covered in the migration. It is easy for a developer to handle all of the data that is normally expected, however, there are always edge cases where data for particular customers or particular configurations are stored differently and you need to ensure that those special cases are handled properly as well. (I found an issue that would have caused 200,000 people in a "special" state -out of millions - to be locked out of their accounts during one migration).
  2. If the system will be online during the migration, (I'm thinking of a migration that may take hours or even days or weeks) make sure that after the initial pass, the cleanup process to get all of the data since the start of the migration is just as robust as the original process. It's easy for developers to spend less time on this part, but it is just as critical. It is also harder to test if you are only testing with a small subset of the data.
  3. If at all possible, go through a full migration with the entire dataset as a dry-run prior to the migration. Even if you can't test every piece of data, this will give developers a lot of information and they may well find that some of their migration scripts errored out on certain data that they will need to analyze a bit more closely to ensure they handle correctly. Then go to town on your regression testing.

There is a set of tools from Red-Gate that allow you to do data comparisons between databases. Even if the data is stored differently, you can compare the results of specific queries, so if you know that a query in the old system should be equivalent to a query in the new system you can compare them that way.

  • @Sam_Woods, Thank you for sharing your experience. By cleanup process you mean bringing production data to the previous state?
    – dzieciou
    Feb 19, 2012 at 16:25
  • 1
    I mean that in very large data migrations the migration is often taking place while the system is still up and running and collecting new data. Often there will be an initial run of the migration process happening in the background, then you will flip the switch to the new data store and run a second process to migrate any additional data inserted into the old data store during the initial migration process. If you are simply taking down your service while the migration happens then this is not applicable.
    – Sam Woods
    Feb 21, 2012 at 22:03

Migration can be a messy process to test, and some testers might approach it half-heartedly. It is good to see that you are taking it seriously.

I might think about how migration could be impacted by configuration settings. If your product has per-customer configuration settings that could impact migration, you might consider how to test an appropriate sample of settings. Your definition of "appropriate" may depend on factors such as who your customers are, which settings are most important, risky, or complicated. Other questions in this forum discuss approaches to combinatorial testing.

  • Can you illustrate per-customer settings with an example? Do you mean that migrated DB and filesystem may not cover all client-related data in a production system?
    – dzieciou
    Feb 19, 2012 at 16:52
  • @dzieciou I added an example of per-customer settings. I meant that one way to test the migrated results is to consider how variations in per-customer settings may impact how the data is used. This is a general approach to testing rather than something specific to migration.
    – user246
    Feb 19, 2012 at 19:35

There's some really good advice on this thread. The only thing I can add is possibly a little off-topic but still pertinent: make sure you are fresh when the production environment gets migrated. I worked on a project a while ago that switched over at four in the morning, and the testers had all worked a 9-5 day then turned up onsite for the migration at midnight. When we came to do our sanity tests, our concentration was completely shot and we ended up missing some nasty issues!

  • Thank you. Can you comment on sanity tests in the context of migration? A critical subset of previously performed tests just to make sure everything works?
    – dzieciou
    Feb 19, 2012 at 17:02

I gained a lot of information from the question as well from all the answers. In the question, under the heading "Where the things may go wrong?" or "What may go wrong", I feel apart from a functional perspective, impact on the performance of the system should also be considered as you mentioned that the migration involves changes in the complex data model as well file system.

  • I tested destination system performance with artificial data from worst case scenario (that is worse in terms of entity numbers than existing production scenario). Why would I have to verify performance again of system with migrated data?
    – dzieciou
    Feb 18, 2012 at 9:22
  • Don't you think after migrating the data, the statistics of DB will change and hence might impact the performance of the system? Even in your case, schema of the db (not null, new foreign keys etc.) seems to be having changes, so performance bench marking from previous system to current system after migration makes sense.
    – rahul
    Feb 19, 2012 at 9:09
  • Maybe I was not clear here. 1) I've installed DB with new data schema (the same where I will migrate data to). 2) I've populated new schema with load of artificial data from worst case scenario I defined. Artificial data are of size bigger than data that I will migrate. 3) I evaluated query response time for those artificial data. Do you mean migrated data will have some statistics that artificial data does not have but still can impact performance negatively?
    – dzieciou
    Feb 19, 2012 at 9:50
  • 2
    I would like to provide a simple example to illustrate my point (it might not be true in yours case though) - Let's say before migrating the data, we are required to delete the indexes and once ALL data is migrated, required to repopulate the indexes. We somehow missed to repopulate the indexes. Here, the performance issue might occur due to missing indexes (db statistics) and not because there are some problems in the new schema or new design. But these kind of problems are not rare during data migration.
    – rahul
    Feb 19, 2012 at 11:16

Is there any business statistic functionality in the old and in the new system? If true you can make the same statistic in both versions and compare them.

Example: the number of customers should be the same, as well as the revenue of the best and the worst customer,..

These can act as a kind of cheksum that make shure that no critical value is lost.


Testing migration requires a good analysis of the current and the target data model and the mapping. Make sure all the data fields are being mapped. You also need to check if a data field has been broken down into multiple fields or two or more fields have been combined into one. Check for what has been eliminated as it may not be required anymore.

There are ETL tools available now which can help you in testing, but when I did similar project long time back I had written my own tool, which connected to both databases extracted structure and asked the user to map the data from old to new. It analyzed both the structure based on the user input and displayed the results(error cases).


Some great answers above and I think everyone is right. The more directions you come at this the better. Have you investigated some of the semantic profiling tools like Rever? (www.rever.eu). They have a case study of a similar problem of transformation but because their tools analyse the code as well as the schema they can show the metadata structures of the transformation logic for a logical view of the code for review before the expense of the physical testing recommended above. This in addition to a metadata view of source and target based on code and schema - as we all know many of the edge cases will only be visible in the code, not the schema.

  • Thank you. Can you point me to the mentioned case study? The only page I found has very limited information. A white paper on that, linked here, is not available.
    – dzieciou
    Feb 19, 2012 at 17:27
  • We may have no budget/time to try the tool, but I would like to understand the problem it addresses. We have implemented migration tool. What edge cases will be visible in its code and not in the schema? What edge cases the tool can help us discover here?
    – dzieciou
    Feb 19, 2012 at 17:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.