How to implement SCD Type 2 without using snapshot

Archive
1

Hi,
We are migrating data from sql server into Snowflake using dbt. We already have a table which capture history data using stored procedure in sql server.
How do I implement scd type 2 in dbt without losing existing data?

Thanks in advance.

2

I am thinking that if you do not want to use a snapshot, then you would instead:

use an incremental load in db
use “delete+insert” as the as the strategy. A merge will not work because you need to look back in the data.
use snowflake windowing functions - lead, lag, row_number() . This is because with windowing functions, you can “retire” the most recent older row on an update (will be inserted with a valid_to date being current_date). Normal Inserts will go in as inserts with the valid_to date being null.

This should work, but I would love to see other feedback as well.