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

Min_Yun_ChanMin_Yun_Chan Vertica Employee Employee
edited April 2020 in General Discussion

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

Answers

  • marcothesanemarcothesane - Select Field - Administrator

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file