Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
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.
"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.
