HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Failover technics - validation during the load phase
ETL PATTERNS
09-May-2010
There are several places within the ETL process to catch wrong and low quality data. Generally there are four basic moments:
  • Validation during the load phase
  • Validation during the transformation phase
  • Limited validation during transformation phase just defaulting invalid values
  • Ex-post validation - data quality management

Following article is about the first one.

Begin
  DBMS_ERRLOG.CREATE_ERROR_LOG(
 'IN_ACCOUNTS_VALID' -- interface table with valid data
,'IN_ACCOUNTS_ERR'   -- interface table with error data
);
End;
/

-- setup session parameters to secure details in character expression of fields in _ERR table
alter session set nls_date_format='YYYY-MM-DD-HH24-MI-SS';
alter session set nls_numeric_characters='. ';

-- insert statement with logging errors
INSERT INTO IN_ACCOUNT_VALID
...
LOG ERRORS INTO IN_ACCOUNTS_ERR ('load0') reject limit 1000000;

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky