The blog outlines one method for handling Slowly Changing Dimensions – probably the most popular method referred to as type 2.
Could be applied to any data base product – but – this applies to Redshift and Snowflake and more…
A type 2 slowly changing dimension enables you to track the history of updates to your dimension records.
When a changed dimension record enters the warehouse, it creates a new record to store the changed data and leaves the old record intact. Type 2 is the most common type of slowly changing dimension because it enables you to track historically significant attributes.
The old records point to all history prior to the latest change, and the new record maintains the most current information.
Effective dating both the new and old record (the old record is assigned a non-active effective date and the new record is assigned an active effective date).
Assigning the new record a new (and unique) surrogate key.