The problem I’m having with dbt snapshot on dbt_scd_id.
The context of why I’m trying to do this:
I need to create an SCD Type 2 table. While generating the snapshot, a hashed column dbt_scd_id is created by concatenating the unique key columns with a timestamp (e.g., using current_timestamp()).
However, during comparison, the hash column includes the timestamp, which differs with each execution.
When I check the query history, I see that three different statements were executed at different times. These merge statements check for matches on specific columns, but I don’t understand how the hash is matched on the merge statement. Especially since dbt_scd_id includes current_timestamp(), which changes every run.
Am I doing something wrong, or am I misunderstanding how the hashing and merge logic work?