Type 2 SCD Key metadata change

[seanc217]

I asked something similar, but I'm not sure I clarified this well so let me try again...

I have some data that originally has the following business key:

bank varchar2(4)
prod_type varchar2(2)
prod_sub_type varchar2(2)

I would use these fields in a change capture to capture any changes to my dimension rows.

Now, I have a change to the business key where

prod_sub_type changes from varchar(2) to varchar(4)

so where I have prod_sub type = "02", it now becomes "0002"

My question is how do I handle such a change so I can update my records. Currently the change capture will recognize them as new records and insert them without expiring the old records.

One idea I have is to make the column a number field and convert it because it really should be a number anyway. That way to DataStage there is no change to the record.

Any ideas?

[roy]

Hi,

The Change Capture stage can output the deleted records as well as updated and new ones, so take the deleted records and update the end date.

IHTH,

[ray.wurlod]

Using a numeric data type is the correct approach.