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

length accordingly. Months later, you get a late-night phone call (most likely when
you're on vacation or when you're out at a karaoke bar with your fellow ETL profes-
sionals) informing you that the SSIS package has failed because of a truncation error.
Yep, the party's over.
We've all been bitten before by the truncation bug or one of its cousins—the inval-
id data type error, the unexpected NULL /blank value error, or the out-of-range error. In
many cases, however, these types of errors can be handled through defensive ETL
strategies. By using tasks and components that detect and subsequently correct or redir-
ect nonconforming rows, we can handle minor data errors such as this without bub-
bling up a failure that stops the rest of the ETL from processing.
Missing Data
With respect to data warehousing, a more common example of handling errors inline is
the case of late-arriving dimension data. As shown in Figure 11-8 , the typical pattern is
to load the dimensions first, followed by a load of the fact tables. This helps to ensure
that the fact records will find a valid dimension key when the former is loaded to the
data warehouse.
Figure 11-8 . Typical data warehouse methodology of loading dimensions, then facts
However, this pattern breaks down when you attempt to process fact records that
reference dimension data that does not yet exist in the data warehouse. Consider the
case of holiday retail sales: because things happen so quickly at the retail level during
the end-of-year holiday season, it's not uncommon for last-minute items to appear at a
store's dock with little or no advance notice. Large companies (retailers included) often
have multiple systems used for different purposes that may or may not be in sync, so a
last-minute item entered in the point-of-sale (POS) system may not yet be loaded in the
sales forecasting system. As a result, an attempt to load a data warehouse with both
POS and forecasting data may not fit this model because we would have fact data from