What if we want to change the business key in a SCD2 process?

[Anne_A]

Dear Community,

I'm wondering what would be the best approach to change the business key in a SCD2 process. The current key (that was created years ago) includes sensitive information (of course it should not, but it does) which is why it should be modified. I assume the answer is not simple, but if one of you can point some useful resources (including books 🙂) I could use to further investigate the issue that would be very helpful.

Many thanks in advance

Cheers

Anne.

[SASKiwi]

Why would you need to change the business key in an SCD2 process itself? What would be wrong to leave that as is but simply create an encrypted copy of the business key in any output tables as a new column. Then in any published versions of these tables drop the unencrypted business key.

[LinusH · Accepted solution]

Hi, 

what is the actual requirement/constraint when it comes to this change?

Will the business key change in the source, or are you not allowed to store the exisating one in your persistant layers in the DW?

Generally speaking, if you know how to change the business key for the ongoing process, it's a matter of remapping, and change the target table structure.

Hopefully, the new business key has a 1-1 relationship with original one, and you are able to create mapping table. Then, at the time of deployment, use a script that updates the target table replacing the old business key with the new one.

[Anne_A]

Thank you VERY much for your insights @SASKiwi & @LinusH. So the idea is to replace the value everywhere, (kind of) simultaneously. That makes complete sense.

@LinusH you're right, the initial value (sensitive info) should not be propagated in the persistent layers of the DWH and yes, there will be a 1-to-1 relationship between the original and the new business keys.

Thanks again to both of you!

[SASKiwi]

@Anne_A - So I assume the business key then has no intrinsic value itself as it is constructed from other keys or columns, and the removal is just a security requirement? Sounds far enough to me.

[Anne_A]

@SASKiwi you're perfectly right 🙂!