Post reply

Incremental Load

  • April 7, 2015 at 2:47 pm

    #1789345

    RonKyle (4/7/2015)

    in the real world this typically ends up being a type 1, 2, or 3 slowly changing insert / update

    How does it end up being a type 3? If you mean an SCD 3, that's the alternate reality scenario. This usually requires special handling.

    So, regarding SSD... type 1 is update to original row only (no insert), type 2 is update to original row's timestamp followed by insert of new row, and type 3 is update to original row setting old attribute and new attribute (then maybe followed by an insert but not necessarily). In any event, SSD requires insert / update.

    But an accumulating insert load just inserts where primary key doesn't exist.

    For example:

    insert into TargetTable ( a, b, c)

    select a, b, c from SourceTable

    except

    select a, b, c from TargetTable;

    Or alternate TargetTable can have WITH (IGNORE_DUP_KEY = ON) option set on primary key, in which case you can simply insert into TargetTable from SourceTable and duplicates are ignored automagically.

    This would be for loading dimensions where attribute values don't change. Perhaps something like a DimCity ( CityKey, CityName ) table.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply