Adding a column to a Type 2 SCD

  • david_h_edmonds

    Ten Centuries

    Points: 1260

    Hello, I have a standard type 2 SCD that is performing well however the business have asked for additional info that needs to be added to the DIM.

    My issue is that if I add a column, it will set all existing rows to not current and insert a new row for all thus doubling row count on the DIM. Does anyone have any ideas on how to deal with this scenario?

    Obviously we won't be adding columns regularly so I am happy to consider a manual task if necessary. I was thinking something along the lines of adding the column, updating the new column values in the current records (manually with a 1 off SQLscript) and then using a SPROC to recreate the HASHBYTE value that we use to test if a record has changed. Obviously there are dangers with this approach so I am open to suggestions.



  • HenryKrinkle

    SSC Enthusiast

    Points: 192

    Hello Dave,

    It really depends on how the dimension is currently setup and what methods you are employing.  Does the additional column need to take part in change tracking?

    If so and you don't want to add a new row (which would seem to be correct if change tracking is necessary (E.g my dimension has column X, yesterday it was blank but today it has a value, therefore is a new row) then it appears the only option is a manual one.

    Adding a column that doesn't take part in change tracking is a common scenario and should be relatively easy.

    If you expect more columns that do take part in change tracking over time, I would be questioning the design of the dimension itself......

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply