Handling Delete and Update in Insert only architecture

Hello All,

In our DV model, Hub has only business keys and Satellite holds non-business keys, valid from date and active flag ( no valid to date added)
CDC_FLG “I” or PK combination in source but does not exist in target
a. Insert the record in HUB and insert a record in the satellite and set the valid from date as current date and active flag as “Y”

CDC_FLG - “U” or PK combination in source exists in target
Check the hash diff in the satellite to check the changes to the Non-key columns
If there is a change identified in hash diff,
a. Insert a record in satellite with the old information and set the valid from date as the previous record and active flag as “N”
b. Insert a record in satellite with the new information and set the valid from date as the current date and active flag as “Y”

CDC_FLG “D” or PK combination in target does not exist in the latest feed from source
a. Insert a record in satellite and set the valid from date as the previous record and active flag as “N”

Kindly check and advise on the approach.

Thanks.
Swaminathan

Hi Swami

Since you have Insert-Only architecture, when
CDC_FLG = “U” you do not need to update the previous record with active-flag = “N”. All you do is Insert the new Sat rec if the Hashdiff has changed; otherwise there is no Insert.