Data Conversion testing
I wonder if anybody has done any data conversion (ETL) testing here, and would like to share some idea or experience.
My current project is using SQL(only) to verify source - target mapping. We have hundreds of source/target tables, and most of tables have several million rows, also the transformation logic is very complicated.
Currently we extracted source data and store them in temp tables, then implement the logic in SQL, may need to create some temp tables in middle to finally get the desired data, then compare them with developer loaded target tables. In this way, we verify that all data has been correctly converted.
However, there is a final cut over date when the legacy system will stop running, and new system will kick off. Those huge amount of data have to be converted only at that moment(we were told it is maximum 4 days, which including loading data, verify data and verify new application). So we won't have time to prepare our temp tables and to do our complete testing.
Therefore, I wonder if any smart person here can share some idea how to do some quick check then to be confident to say data has been converted successfully.
If you have example or detail idea, that would be very appreciated.
Thanks in advance