Data Warehouse Patterns - SQL Server Integration Services Design Patterns - page 328

Figure 11-11 . Use a triage table to store unmatched fact data
As an aside, this pattern could be modified to support manual intervention for cor-
recting failed lookups as well. If the business and technical requirements are such that
the unmatched fact data must be reviewed and corrected by hand (as opposed to a sys-
tematic cleanup), you could eliminate the triage source so that the triage data is not re-
introduced into the data flow.
Coding to Allow Errors
Although it may sound like an oxymoron, it's actually a common practice to code for
known errors. In my experience, the nature of most ETL errors dictates that the pack-
age execution can continue, and any errors or anomalies that cannot be addressed inline
are triaged, as shown in the previous example (with the appropriate notification to the
person responsible, if manual intervention is required), for later resolution. However,
there are situations where the data warehouse ETL process should be designed to fail if
error conditions arise.