I have a question which may sound unusual or naive. Suppose I have a dimension table of five attributes (columns) with a surrogate key acting as a primary key and present in Fact tables as foreign key. The dimension and fact tables are populated. At this point if I want to add a new attribute (column) to the dimension should not I have to reload the entire dimension, which will create new surrogate key for each row? In that case don't I have to reload all the related fact tables too?
I'm not sure if there is a better way to add new columns to dimension tables. Need your help to understand any alternatives?