none
ETL Testing

    Question

  • Hello Everyone:

    I need to know if there is any tool which can be used to for post migration ETL testing? The ETL has been written using sql scripts to move the data from Legacy database to a new database and both have different database structures We need to test the tranformed data for accuracy and check for data quality and quantity  after the ETL process is executed.

    I have questions about the following:
    1.what should be the best approach/ best practices to test the data in the new database after transformation?
    2. I would like to know if there is any tool which can be used to compare the data in old vs new database (both having different structures) ?

    Thank You all.

    Monday, October 22, 2012 9:29 PM

Answers

All replies

  • I have worked a lot with data migration and there is no such tool and do not expect one anytime  soon, because the transformations can be very complex.

    About the practices:

    First you should test all your scripts.

    Very that all the records has been migrated according to what is expected in all tables.

    Verify that each transformation is correct comparing source and target data.

    For numeric fields you can verify totals. Min and max values

    For character field you can verify total lengths, Counts by lengths.

    That is just to name a few. Other quality controls depend on business rules.

    Monday, October 22, 2012 11:30 PM
  • There's a tool from Redgate called SQL Data Compare, but I'm not sure how it will handle the different structures.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    Tuesday, October 23, 2012 6:56 AM
  • you can find some third party tools, or can do it manually with specific queries say count of records with where conditions in each databases.

    You can check for some customised tools like http://www.codeproject.com/Articles/205011/SQL-Server-Database-Comparison-Tool

    regards

    joon

    Tuesday, October 23, 2012 7:37 AM
  • Hi,

    I do use Redgate for databases with same data structure. But, I tried it to compare the two different databases (with different database structures) and it did not return any results.

    Tuesday, October 23, 2012 12:31 PM
  • Thank you for your replies. So, the best way to test the ETL migration is through manual testing. I mean by executing the sql queries (based on the test cases developed) to ensure about the data quality in the old database and new database and comparing the results. For data quantityalso  the sql queries can be used.

    I have also searched for the data validation tools but could not find what I am loooking for. There are some tools which do the data validation like having a phone number in certain format etc but not comparing the  data in two databases (legacy vs new db with different data structures).

    Tuesday, October 23, 2012 12:43 PM
  • Hi Tkk_3,

    The test approach for database migration testing consists of the following activities:
    1. Design the validation tests
    2. Set up the test environment
    3. Run your validation tests
    4. Report the bugs

    For more details about it, please see: http://inderpsingh.blogspot.com/2010/03/how-to-do-database-migration-testing.html

    Thanks,
    Eileen


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    Tuesday, October 30, 2012 8:06 AM
  • Thanks a lot. Does any one know about the automated testing tools which can speed up the ETL testing?
    • Edited by ss08 Thursday, November 01, 2012 3:15 PM
    Thursday, November 01, 2012 3:14 PM
  • I believe the new SSDT in conjunction with any build automation can be of help. E.g. CruiseControl .net.

    You can script SSIS package executions through bat files using the DTEXEC command line utility.


    Arthur My Blog

    • Marked as answer by Eileen Zhao Monday, November 05, 2012 1:21 AM
    Thursday, November 01, 2012 5:49 PM