Thank you for sharing your experience about ODS with us Cliff.

With regards to the SCD beyond the 3 types Margy Ross and Ralph Kimball describe several alternatives and combination (hybrid) types in this article. The one I was thinking of last year is storing the historical dimensional data in another table(s), using the technique similar to the one used for 'auditing', i.e. the structure of the history table is the same as the dimension table, with some additional columns such as change timestamps, flag columns (such as change type and latest), version columns and user information columns. The alternative of this technique is storing only the necessary columns of the dimension table, rather than all columns. In the case of a 'snow-flaked' or 'outtrigger' dimension, we have the options of combining the historical dimensional data in 1 history table, or mimic-ing the structure of the 'snow-flaked' dimension tables.

I apologise I've just seen your post today.

Kind regards,

Vincent