How do you backfill and SCD type 2 column?

Hi ,

We have just created a star schema where one of our dimension tables have multiple type 2 scd columns. We have tested this with new data and moving forward, the scd's are working fine (ie it creates new records and we correctly flag the latest record and have the correct start and end dates for previous and current records). The question is, how do we backfill historical records for these scd columns ? We have the data in the source system but is there a technique in backfilling historical records.

Related to this, if you have an established star schema and want to add an attribute in one of your dimensions, could you add it as a type 2 scd and backfill historical data for this attribute?

Thanks.

Regards,

Mike

Hi,
presumably you have historic facts you also want to load, otherwise there is no point in loading historic data into dimensions?
Assuming you do, then you would just normally create the historic Dim records in a staging area and then write the data to your Dim - this would be a one-off process.
You could use your standard SCD process to load this Dim but only if you start with your historic records and load them in order; as you have already loaded your "starting position" and moved forward you'll have to create a one-off process to load your historic data.

New attribute question: yes, you can. If you want to update old Dim records then you'll have to write a one-off data fix to do this. You wouldn't create new historical Dim records as obviously they wouldn't be connected to any facts so there is no point in creating them.

Hope this helps?

As far as the dimension itself goes, it's easy enough to figure out how to retro-fit old data…  but what about the facts?

Presuming you have existing facts that date back to the old data you are trying to load, the challenge will be re-keying the facts so they references the appropriate row.  This is not a minor task.  Overall, it requires about as much effort and testing as new development.

Personally, I hate having to do things twice.  If you have a source with history and you are implementing a Type 2, load it the first time.

As to your second question, sure you can… but again, you need to re-key facts that reference that table.

Thanks for your suggestions.

I will try them out.