We have to implement a Slowly Changing Dimension Type 2 in Snowflake.

A Slowly Changing Dimension (SCD) Type 2 tracks historical changes by creating a new record for each change. In Snowflake you typically add surrogate key, effective_date, end_date, and current_flag columns. The implementation steps are: 1) Load the source data into a staging table. 2) Use a MERGE statement to compare staging with the dimension table on natural key. 3) For rows that exist but have changed values, update the existing record’s end_date to the current timestamp and set current_flag to FALSE. 4) Insert a new row with the new values, a new surrogate key, effective_date as current timestamp, end_date as NULL, and current_flag as TRUE. 5) For new rows that don’t exist, simply insert them with the same default values. The MERGE syntax in Snowflake handles both update and insert in one atomic operation, ensuring consistency. Complexity is O(n) for the number of source rows, and the approach scales well with Snowflake’s micro‑partitioning and clustering.