I have developed the following ETL strategy for all my data warehouse loads.
I tend to follow, as closely as possible, the methodology developed by Ralph
Kimball. First, I distinguish between the 2 possible load types: full
refresh or incremental. The former truncates all existing data in the
destination table (dimension or fact) whereas the latter compares the
incoming data to the existing data, record by record, field by field.
Needless to say, a full refresh load takes much less time to process.
Terminology: a surrogate key is a meaningless integer that will be used as
the primary key in the dimension/fact table. All incoming production keys
will be replaced by a surrogate key. Type 1 and 2 slowly changing
dimensions are part of Ralph Kimball's methodology. Transformation and
cleansing routines will be discussed later. All comments are welcome.



Full Refresh Pseudocode



For each incoming record

transform the record

cleanse the fields

output to file

get next record

end



load output file to database





Incremental Load Pseudocode



For each incoming record

get lookup table

get maximum surrogate key from database

transform the record

cleanse the fields

determine if incoming record exists (using production key and
lookup table)

if incoming record exists

get existing matching record from database

determine fields that changed

if fields have changed

replace existing record in database with new changes (type 1 slowly changing
dimension) or insert new record in database (type 2 slowly changing
dimension)

end if

else

insert new record in database

end if

get next record

end

Hi Domenico,


This is wrong. Usually you decide which data to load by comparing a field in
the production table. This can be a time stamp or a flag (already loaded =
1, not loaded or changed = 0). So the amount being loaded is minimal.
Imagine a Telco company trying to fully import its data where the delta
alone is sometimes several hundred million of rows a day!

Joerg

"Joerg Narr" <n_o_spa_mjoerg_narr (AT) hotmail (DOT) com> wrote

the "incremental" load functionality. The initial load into a data
warehouse (using the initial export of data) is handled much quicker using
this full refresh method specifically because you do not have to compare
fields to see if they have changed.