Accumulating snapshot with SCD?

[kskistad]

Is it common to update dimension keys within an accumulating snapshot fact table if it contains some slowly changing dimensions? Or is good practice to leave the keys alone and only use a more current key for new rows?

My inclination is to leave the keys alone to preserve history of that row, but what if the facts (measures) actually changed too? For example you have an accum. snapshot of loans, and you have a "Current balance" column that you update regularly. This row also has a sales region dimension, which is slowly changing because the region sales rep changes. If you updated the region key to the most recent, you are attaching that old loan to a new version of the region, which may or may not be what the business expects. But you definitely are updating the facts (the ones that change) day-to-day, so in some respect your loan record is a "current" snapshot, so shouldn't it also reflect the "current" region?

[ngalemmo]

But you definitely are updating the facts (the ones that change) day-to-day, so in some respect your loan record is a "current" snapshot, so shouldn't it also reflect the "current" region?

Maybe... there are valid business reasons to look at it historically as well as current state, which is why I recommend that you maintain both a type 1 and type 2 version of a dimension if you need to implement a type 2. The fact table would have two foreign keys for the same dimensional role, one pointing to current data in the type 1 dimension and the other pointing to the historical version in the type 2 table.

Getting back to your original question... you should never routinely re-key fact table foreign keys.