Bringing an old topic alive , this is what I am thinking .
We have a fact table that is , say 1000 columns wide and then lets say we have an additional column , an isactive column which , if 1 means that the fact record is active and if 0 means the record is inactive.
NOw if the same record comes in with the latest ETL , the older version of the record needs to be set to isactive = 0 and the latest one retains as isactive = 1 .
Lets say , we take the PK , the Natural Key and the isactive column to a separate table and instead of updating the 1000 column wide table on one flag column , we update this new table to switch the flags.
Downside is , the select queries have to join the 2 tables to get the active records.
Is there any obvious performance difference between updating 1 column in a 4 column wide table vs 1 columns in a 1000 column wide table ?