Should I updated the scd1 attribute of old records while updating the new one ?

Consider this table.

CUSTOMER_SK|CUSTOMER_ID|NAME|SURNAME|AGE|JOB|EFF_DATE|EXP_DATE|IS_CURRENT

NAME and SURNAME is SCD1 attribute. When this attributes changes I just updated.
AGE and JOB is SCD2 attribute. When this attributes changes I updated the IS_CURRENT column to 'N' for the existing record then insert new record.

As an example, customer1 has just one record in customer table. When name and surname changes just update the existing record. When the customer's age or job changes I adding new record. So there are 2 customer records for just one customer as it is supposed to be.

Then, customer1 name is changed. So should I update the just existing record ( is_current='Y' ) or all records for that customer should be updated ( no matter is_current = 'y' or 'n' )?

Thanks.

You update all rows for the customer.

I guess we call that SCD Type 6 these days.

Hi,

if you want to preserve the customer name (old and new) , then you can have a new column added to the diemnsion which will be like a customer ID which will be the natural key for the table.
This way you can capture if there is a frist name or last name change.



thanks

The problem with keeping history for things like "Name" is how do you differentiate between a name that has been changed and one that has been corrected?

Guys, the gist of the question was that the dimension is a type 2 (or I guess is it's now called a type 6… I can't count that high) but there is no desire to track changes for all the attributes (name being one of them). So you have a mix of 'type 1' and 'type 2' attributes in the same table. In such situations, type 1 attributes are handled by updating all rows based on the natural key.

If Name is SCD1, then just update the name for all the rows for that Natural key.

thanks