State-oriented fact tables

[jtompkins]

Is there a reason that state-oriented fact tables get truncated and rebuilt each time a data mart process is run, regardless of whether any updates have taken place? For example if I run a DWH task that  updates one row in a hub table, and then go run the data mart, every state-oriented fact table will get completely rebuilt even though the last DWH update did not touch any of the associated hub/satellites. If I have one record that I want propagated to the data mart, it seems extremely wasteful to have millions of records truncated/inserted that haven't changed since the last run.

[Nanda_Ravindra · Accepted solution]

@jtompkins with state-oriented (from_date, to_date) fact tables, they get huge in volume. so the idea behind is that its best to insert them than scanning the table for updates. 

Also the State-Oriented Facts have to account not only  for changes in the fact records, but there could be potential back-dating scenarios with denormalization which completely changes the timeline of the data

Hope this helps!

Thanks,

Nanda