Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Min_Yun_ChanMin_Yun_Chan 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 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.