Ab Initio ETL Tool || Change Data Capture (CDC Process) In Ab Initio Graph || Data Warehouse ....!

Change Data Capture (CDC Process):

Change data capture can be done in Ab initio by reading the history feed and current feed and passing them through a JOIN. Join the history feed and current feed using the primary key. In join both the output dml and transform will be PDL driven.

In the output dml, for each non-key fields create two fields viz old_field1 and new_field1. In the transformation function assign the values from the history feed to old_field and current feed to new_field. The output of the join will be a single record for each key values containing the old value and new value in two different column.

Then write transformation logic to compare the old value with the new value. If old_field1 == new_field1 then it is a unchanged record and assigned the unchanged_flag as "N" otherwise "Y". then apply the filter logic to segregate the unchanged and changed records based on unchanged_flag. Unchanged records from join output will be discarded because its already exist in table and changed records will be updated in table based in unchanged_flag indicator. Unused records from current feed will be considered as new records and inserted into target table and unused records from history feed will be discarded because this records already exist in Table.