Designing ETL error trapping and detection routines

There are many ways in which you can detect and trap errors. Tools such as Oracle Warehouse Builder and Oracle Data Integrator provide some capabilities to detect data quality errors and provide mechanisms to correct the issues. The Oracle database allows you to add constraints to your data model (primary, foreign, unique, and check constraints) to enforce data integrity. They are useful and help to trap some errors; the problem with them is in detecting an error. For example, if you get a duplicate key error and are using a primary key constraint with bulk transformation within Oracle, a few things will happen. The first is the bulk transformation will encounter an error and potentially stop. If you have a failover to row processing, the process will start again, processing a single row at a time. The first duplicate record will be inserted into the target table, and the rest will return an error and can be configured to be placed into...