Has anyone used change tracking feature in Snowflake to track DML changes on a table and create SCD2 tables in a Snowflake data warehouse? Not talking about using streams and tasks but specifically turning on change tracking on the table and using a stored procedure/ SQL to create SCD2 tables.
It looks like a possible way to implement scd2 but want to know if anybody has implemented it already. Also, when the feature was introduced and if it is stable.
Hi – it was implemented months/years ago and is stable. However, I’m wondering why you’d want to use it to build an SCD2 solution when using streams would normally be a better solution? What’s your use case that makes this type of change tracking a better solution than streams?
I was wondering if using change tracking is easier than streams because you don’t need to create another object.
I doubt it. Streams automatically tracks the last record that you wrote to your Dim table so you can do incremental updates of only the changes made since the last update. If you used CHANGES then you’d have to implement this sort of mechanism yourself
Can’t I insert/update my dim table and then query the changed records using ‘changes’ clause and write to a history table that maintain versions?
That doesn’t seem to make much sense (to me). The changed records are the source for what you need to write to your Dim – which contains the history, that’s what and SCD2 Dim table is, it holds history
Show 2 more comments