Compare data between 2 different schemas

Question

We refactored our old database. Some tables were dropped, some tables were split into 2 new tables etc. So now we have an absolutely new DB schema which has many differences from the old one. Someone has written an application which migrates data from the old database to the new one.

I'm looking for methods/techniques to compare correctness of data migration. Is there any standard or often-used ways to accomplish such compare?

After some research, I found one technique how to do it - create a VIEW of each table of the old database i.e. I will try to represent in VIEW data in format of old table (old schema), and then compare data in old table with data in VIEW.

I'm interested in other ways.

Answer 1

Although the representation of the data differs between the old and new schema the data itself should be the same. This means a series of reconciliation queries will provide certainty that the migration was without error.

Say you have an Orders table. The total number of Orders per year, month or day will be the same in the old and new databases. Similarly the total value of those Orders for each time period will match. You can expand this idea for orders-by-product, orders-by-customer etc.

If any data was changed in the migration the corresponding reconciliation queries will not return the same value from old and new DBs. You will know immediately that the problem was with an order from customer "X" on day "Y" for product "Z" and can focus your debugging on that area.

The same approach can be used for any other table. Take customers as and example. You can count customers by country, business area, first letter of name or any other differentiator you choose.