How to test deployment of ETL jobs (dev-test-production)?

We have been developing a Data Warehouse and bunch ETL jobs for loading data from source systems into the DW. Now we are on the egde of deploying these ETL jobs into production environment. ETL jobs should be heavily tested and I would like to know if you guys could give some hints and guidence how to do this testing? What should be tested (data validation, job function, database performance, indexing)? What kind of reports should be written etc.?

Did you use any documentation to build the ETL logic? Source to target mappings, design documents, etc? These documents are the source of the rules to test your ETL.

Well, yes of course, but I think that there is much more than just testing ETL jobs (logic, function). I would be delighted if someone could give some advice what kind of testing should be done at minimum.

Here are some additional ideas for testing. The list is really endless. Just depends on how much budget/time you are able to spend on setting up the tests and framework.

- Failure / Restart testing (at each logical break point - by landing zone - raw, cleansed, transformed, integrated, staged, final load)
- Unit Testing (isolated logic and function tests - ideally, these would be created in a way that they can be automated and used for regression on later builds)
- Performance testing (hardware)
- Load testing (ensure load process will scale to handle peak loads within processing window)
- Security testing (ensure users don't have access to the kitchen)
- Integration testing (over multiple units and/or entire process - test functions as well as data integrity from source to target)

A good resource in this space is Ralph Hughes' book Agile Date Warehousing. He also teaches classes specifically in Agile DW Testing that are quite informative, and go into great detail on a testing framework he built for a massive DW at a telecom client recently.

Hope that helps.
Brent Greenwood

So... is it the dropping and reloading that is causing me the problem? or should i generate a surrogate key a different way ensuring it gets the same key after any necessary rebuild?



________
adil