Recently I've been testing on an ETL/Data warehouse project and the strategy I took was to work very closely with the business and leverage their knowledge to come up with all the weird and wonderful use cases with which they would have as well as testing your usual data integrity, transformations, lookups, etc.
On top of that I built an automation tool that takes in XML business rules to compare schemas, data, lookups and making sure the data flowed correctly end to end without truncations, calculation errors, etc. Admittedly it's a work in progress and I only spent 2 days on it but it does 90% of the job for me so that the rest of the time, my team and I could test scenarios that the business had and a few of our own and also within the business requirement limits.
However we found a host of new issues in UAT that points to new scenarios that does not conform to the business rules or business processes and hence breaks the code. The scenarios are not simple scenarios or logical scenarios. E.g. A piece of cargo currently resides in the UK and Australia AND New Zealand. Mainly it's a clash of conflicting source data because people somehow entered the same data from different sources so that data is valid on it's own but together does not make sense.
My experience being quite shallow in the world of ETL so I'd like to throw the question out there, so do you
- test everything to business requirements and contracts between the sources and deal with these "oddball" scenarios in UAT and BAU?
- like web/desktop applications and assume that people using their systems can do absolutely everything and anything and assume the worst and test everything? (Exhaustive testing, which is impossible cause permutations are almost infinite)
- Or is there a happy medium?