Potential impact of frequent Vertica updates on SCD2 historization of dimensional tables?

Min_Yun_Chan

Would you advice against SCD2 historization of dimensional tables with frequent changes because of the potential multiple/frequent updates on Vertica?


Question from The shortest path to Vertica: Best practices for data warehouse migration & ETL
@marcothesane @Maurizio

marcothesane

As I already said on the call: Slowly Changing Dimensions are an intrinsic part of Ralph Kimball's The Data Warehouse Toolkit . And Vertica is just about the best RDBMS platform I've come across to support Kimball-ian star models with big volumes efficiently.
I actually wrote a blog on Slowly Changing Dimensions in Vertica, as I said elsewhere on this forum.
Mind you: Slowly Changing Dimensions change slowly, not quickly - that's not in near-real-time. They should not keep changing while an analyst is trying to make sense out of them in a reporting session, as such a behaviour would be disruptive to the analyst.

So if you run a batch oriented SCD update every few hours - and update/insert everything in the delta accumulated in the meantime in one single transaction - that's the stuff that Vertica loves.