Need a suggestion from Datawarehouse and ETL experts. I have a project which has bunch of fact and dimension table, and we wire the facts using surrogate keys to dimension (religiously following kimball). We have had some incidents of worng wiring.

So I want to write few scripts that will do the data validation by doing the cross lookup and checking if each surrogate keys refer to the right record from appliaction database. I just wanted to ask you guys if you had to deal with any data validation in reports? My solution is not very efficient and very tedious to implement, wanted to check if any easier suggestion is there. PS: I had this question on stackoverflow, but unfortunately got no response. So trying my luck here.


Usually surrogate keys are implemented based on dimension tables and external data sources in order to have coherent and valid data records; the ETL process, which in charge of extracting data from different sources, transforming and finally delivering data into the data warehouse, must ensure this condition (i.e., surrogate keys refer to the right record).
Personally, I did perform a data validation once by simply using a comparison between the OLAP(i.e., Data Warehouse) result and OLTP(i.e., DB sources) result for the same simple analysis query in order to see if there are any incoherent information.

  • That was very helpful. Thats is the approach I was thinking of taking. Guess there is no better way to do this validation, apart from doing a manual lookup(comparison), between OLAP and OLTP, like you mentioned.
    – imba22
    May 2 '16 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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