ETL Water Quality Data from the Legacy STORETW System

This ETL is unique in that:

• The transformation SQL in encapsulated in a PL/SQL package.

• Primary key values are manipulated to avoid conflicts with the STORET WQX data.

• The output is fed into another ETL for actual inclusion in the WQP.

These scripts are run by the OWI Jenkins Job Runners. The job name is WQP_STORETW_ETL. They follow the general OWI ETL pattern using ant to control the execution of PL/SQL scripts.

The basic flow is:

• Rebuild the ETL PL/SQL Package. (create_storet_objects_stage.sql)

• Copy the data download and cleanup scripts to the database (nolog) server.

• Make sure they have the correct end-of-line characters.

• Download data files as needed. (storet_dump.sh)

• Import the data into the storetw schema of the nolog database using impdp.

• Grant select on the storetw tables to wqp_core. (storetw_grants.sql)

• Transform the data into the station_no_source, activity_no_source, and result_no_source tables. (runWeeklyPackageStage.sql)

• Mark the current version of data as processed. (storet_finish.sh)