General Discussion > Automating data validation backend tests

Hi,
expecting a new data migration QA project to come soon.
and i would like to know if anyone has done any automation in backend data validation tests.
Well.. as per my understanding there are different layers of validations in data migration -
1-verify that data values after migration are valid as per syntax (data type,ranges etc)
2-verify that all mapping rules covered during migration
3-verify that all application using the data work well after migration.

I am talking about the automation of the #2. Yes, the ETL tool will take care of many things. But if we want to do independent verification how to go about it?

I think this kind of independent validation will generally include firing various SQLs to verify each of the mapping rules. Instead of duing this manually, how can we automate it?

I'm thinking of Perl test scripts that use DBI / DBD ODBC to invoke queries , then compare results.

Anyone done any automation like this?
All comments and inputs welcome!

Thanks!

Fri, June 5 | Registered CommenterDanu

Hi Danu,
At Kognitio, we have developed FastTest for both testing and auditing purposes. This combines a series of automatically generated and manually written tests to analyse the data pre and post-migration, producing reports on failed tests, data that will fail indexing or constraints, and audit failures. The automatically generated tests are produced by interrogation of the database indexes and constraints, and through replicating the ETL processes in Kognitio's DFTK migration software. An added feature is the ability to strip out any records that would cause constraint failures in order to provide a coherent data set for testing of the target application with migrated data. FastTest is supported by our own WX2 in-memory database that allows these processes to be run incredibly quickly, even with very large data volumes. A full suite of tests run on a recent migration project consisted of nearly 1,700 comparisons of source and target data, with very complex logic and 20m records and completed in just over an hour. This provides an excellent regression test for any changes made to a migration transformation process.
So, it is a great idea to do it, but it takes a lot of effort to set up in the first place.
Hope this is of help.
Philip

Wed, July 8 | Registered CommenterPhilip Magnall