CREEPING_DEATH algorithm ETL Transformation pattern

DATA ARCHITECTURE

by Ludek Bob Jankovsky, 02-Sep-2008 (ETL PATTERNS)

One of special patterns related to cool specific way of maintaining Slowly changing dimensions of type 2 (valid_from, valid_thru). Differently of "standard" way obsolete records are terminated just based on existence of newer record for the same instance. Module based on the pattern is independent on source of transformation (common for all mappings filling table) and much more efficient in bulk processing.

CREEPING_DEATH module usually follows SCD2_INSERT module. Load of module is usually done following way:

SCD2_INSERT module - inserts new and changed records
CREEPING_DEATH module - terminates validity based on analytical function of next VALID_FROM such as Lead(VALID_FROM)over(partition by PT_SID,PT_ID order by VALID_FROM) as NEXT$FROM

See: SCD2_INSERT pattern

Examples in the article have been built using Simple ETL module generator.

See: Simple ETL module generator

Metadata sample:

<?xml version="1.0"?>
<MODULE Pattern="CREEPING_DEATH" TargetOwner="A2" TargetTable="PARTY">
<COLMAP TargetColumn="PT_SEQ" MatchFlag="Y"/>
<COLMAP TargetColumn="PT_SID" PartFlag="Y"/>
<COLMAP TargetColumn="PT_ID" PartFlag="Y"/>
<COLMAP TargetColumn="VALID_FROM" ValidFromFlag="Y"/>
<COLMAP TargetColumn="VALID_THRU" ValidToFlag="Y"/>
<COLMAP TargetColumn="UPD_EFF_DATE" UpdateFlag="Y">p_effective_date</COLMAP>
<COLMAP TargetColumn="UPD_DT" UpdateFlag="Y">sysdate</COLMAP>
<COLMAP TargetColumn="UPD_PROCESS" UpdateFlag="Y">p_process_key</COLMAP>
</MODULE>

Results in a code with approximatelly following statement:

MERGE INTO A2.PARTY TRG$$
USING(
  SELECT
      PT_SEQ
     ,VALID_THRU
     ,Lead(VALID_FROM)over(partition by  PT_SID,PT_ID order by VALID_FROM) as NEXT$FROM
  From A2.PARTY
) SRC$$
ON(
  TRG$$.PT_SEQ=SRC$$.PT_SEQ
  AND SRC$$.NEXT$FROM is not null
)
WHEN MATCHED THEN UPDATE SET
   TRG$$.UPD_EFF_DATE=p_effective_date
  ,TRG$$.UPD_DT=sysdate
  ,TRG$$.UPD_PROCESS=p_process_key
  ,TRG$$.VALID_THRU=SRC$$.NEXT$FROM-1
;

Following additional attributes of column mappings are important for CREEPING_DEATH pattern:

Match flag - specifies matching key for unique identification of records.
Part flag - specifies matching key for identification of instances (serie of records describing changes the same element).
ValidFrom flag - specifies VALID_FROM column.
ValidTo flag - specifies VALID_THRU column.
Update flag - just for audit columns.

Ludek Bob Jankovsky