Updating old record in SCD2

[babbu9]

Hi
I have been working with SCD2 and have a difficulty in updating the old record and changing the delete flag on the deleted records.

We have 3 columns that track every insert/ update

for insert:
eff_date=sysdate
exp_date=null
delete_flag=No

for update:
insert new record
eff_date=sysdate
exp_date=null
delete_flag=No

old record
change exp_date=sysdate-1

for delete:
update old record
delete_flag=Yes

How will be implement this.

I am using Change capture stage but donot know
1. how to get the
keys for the old record that needs to be updated
2. Change the exp_date value to sysdate-1

For delete how to change the delete flag to yes to that particular record.

I would appreciate if you could help me by specifying what stages to use and how to implement the logic in them.

Thanks
Bob

[ray.wurlod]

Getting the key should have been done upstream of the Change Capture stage via a lookup against a "business key to surrogate key" data set.

Varying the delete flag or end date then becomes a straightforward update.