ETL Development Activities

The activities for ETL development do not need to be performed linearly. Figure 11.7 indicates which activities can be performed concurrently. The list below briefly describes the activities associated with Step 11, ETL Development.

1. Build and unit test the ETL process.

   Under the direction of the ETL lead developer, the ETL programs must be developed for the three sets of load processes: initial load, historical load, and incremental load. If you plan to use a database management system (DBMS) load utility to populate the BI target databases, then only the extract and transformation programs need to be written, including the programs that create the final load files. If you plan to use an ETL tool, the instructions (technical meta data) for the ETL tool must be created. All custom-written ETL programs and all ETL tool modules must be unit tested for compilation, functionality, and edits.

2. Integration or regression test the ETL process.

   Once you have unit tested all the individual ETL programs or program modules, the entire ETL process flow must be tested. This is accomplished with integration testing on the first release and with regression testing on subsequent releases. Both types of testing must be performed under a formal test plan with test cases, expected test results, actual test results, and a log of test runs.

3. Performance test the ETL process.

   Since many BI target databases are very large databases (VLDBs), it is important to stress test selected programs or ETL tool modules. Perform stress testing with full volume data on those programs or ETL tool modules that read or write to high-volume tables and that perform complicated operations, especially when running in parallel against high-volume tables. Performance tests can also be simulated with stress test simulation tools.

4. Quality assurance test the ETL process.

   Most organizations do not allow programs to be moved into production until they have passed through a QA test process. This test is usually run under the supervision of the operations staff in a separate QA environment.

5. Acceptance test the ETL process.

   If the business representative and the subject matter expert have been actively involved in integration or regression testing activities, then acceptance testing should be little more than a final, formal certification from the business representative. If they have not been involved, all functions of the ETL process must be validated to be complete and correct, especially the reconciliation process.

Figure 11.7. ETL Development Activities