If an ETL tool is being used to move data from an OLTP database into a "business intelligence reporting" database, is there any standard way of automatically testing that the data in the reporting database is accurate and correct?

NOTE: I'm not interested in testing the functionality of the ETL tool itself only that the resulting DB matches what is expected.

  • 1
    Possible duplicate of Automating tests for ETL flow
    – Becuzz
    Dec 4, 2018 at 15:19
  • I guess my question might be slightly different in that I'm using a third party tool for the ETL so I don't want to test the ETL's functionality but only verify that the resulting DB is what is expected. Dec 4, 2018 at 15:19

2 Answers 2


"only that the resulting DB matches what is expected"

So you need to take some fixed sets of test data, run the ETL process and compare the content of the resulting DB with a set of reference data. That's it. The test data sets should be small enough to let you verify the result in a reasonable amount of time, and complex enough to cover all important steps of the ETL process.

How you do the comparison technically is up to you, it depends on the specific database system, the availability and size of the reference data, the overall size and complexity of your system, the tools you have at hand to make a meaningful comparison, your time constraints, the required level of detail of the comparison, your personal skill level, and so on. There is no "one-size-fits-all" solution to this, you need to find out what suits your needs best and taylor your environment to this.


If you are considering data migration (without transformations) you can implement a checksum (hash value) of some selected data from both source and destination, if they match then you can almost guarantee complete and honest migration

However, hash values might collide in some rare cases, so you might want to consider two or more checks to ensure integrity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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