Snowflake Change Tracking to implement SCD2

Question

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.

Link to documentation : https://docs.snowflake.com/en/sql-reference/constructs/changes

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.

Answer 1

You didn't share your model so based on what you provided, I am assuming the following : You need a measure that calculates the total number of unique sizes planned for each option (Style + Color) in the merial mater planned table.

 Total Sizes Planned = 
    CALCULATE(
        DISTINCTCOUNT('Material Master Planned'[Size]),
        ALLEXCEPT('Material Master Planned', 'Material Master Planned'[Option])
    )

Then , create a measure to count the distinct sizes available in the ientory table for each option.

Available Sizes = 
CALCULATE(
    DISTINCTCOUNT('Inventory'[Size]),
    ALLEXCEPT('Inventory', 'Inventory'[Option])
)

Then another measure that calculates the percentage of available sizes against the total sizes planned for each option.

Efficiency Percentage = 
DIVIDE(
    [Available Sizes], 
    [Total Sizes Planned]
)

Then finally the measure for the health status :

  Health Status = 
    IF(
        [Efficiency Percentage] >= 0.7, 
        "Healthy", 
        "Unhealthy"
    )