DBT Snapshots not handling Multiple Updates to the Same Unique Key

Help
,
1

The problem I’m having

I have a stage model with definition below. I make update to the Customer_Name_2 to One.

cortex_item_sk cortex_etl_sk Customer_Name_2 cortex_etl_operation cortex_etl_timestamp
3c518f3d73fcd99c40715c794a4f0738 57115a27c62562a3981483c57fb8beab NULL CDC_Full_Load 7/11/2024 15:37
3c518f3d73fcd99c40715c794a4f0738 8ff1350cc3d2bf14cb3eca99c078002b One Update 9/10/2024 14:50

Then I update to the Customer_Name_2 to Two.

cortex_item_sk cortex_etl_sk Customer_Name_2 cortex_etl_operation cortex_etl_timestamp
3c518f3d73fcd99c40715c794a4f0738 57115a27c62562a3981483c57fb8beab NULL CDC_Full_Load 7/11/2024 15:37
3c518f3d73fcd99c40715c794a4f0738 8ff1350cc3d2bf14cb3eca99c078002b One Update 9/10/2024 14:50
3c518f3d73fcd99c40715c794a4f0738 999164bfb8aeaad00b2966fe257d1c17 Two Update 9/10/2024 14:53

And finally i update to three

cortex_item_sk cortex_etl_sk Customer_Name_2 cortex_etl_operation cortex_etl_timestamp
3c518f3d73fcd99c40715c794a4f0738 57115a27c62562a3981483c57fb8beab NULL CDC_Full_Load 7/11/2024 15:37
3c518f3d73fcd99c40715c794a4f0738 8ff1350cc3d2bf14cb3eca99c078002b One Update 9/10/2024 14:50
3c518f3d73fcd99c40715c794a4f0738 999164bfb8aeaad00b2966fe257d1c17 Two Update 9/10/2024 14:53
3c518f3d73fcd99c40715c794a4f0738 3eadfcc49c543d66d716cbbce614f600 Three Update 9/10/2024 14:55

Issue

Now, when I run my snapshot here is the result of my snapshot

cortex_item_sk cortex_etl_sk cortex_etl_timestamp Customer_Name_2 dbt_valid_from dbt_valid_to
3c518f3d73fcd99c40715c794a4f0738 57115a27c62562a3981483c57fb8beab 7/11/2024 15:37 NULL 7/11/2024 15:37 9/10/2024 14:50
3c518f3d73fcd99c40715c794a4f0738 8ff1350cc3d2bf14cb3eca99c078002b 9/10/2024 14:50 One 9/10/2024 14:50 9/10/2024 14:53
3c518f3d73fcd99c40715c794a4f0738 999164bfb8aeaad00b2966fe257d1c17 9/10/2024 14:53 Two 9/10/2024 14:53 NULL
3c518f3d73fcd99c40715c794a4f0738 3eadfcc49c543d66d716cbbce614f600 9/10/2024 14:55 Three 9/10/2024 14:55 NULL

I am seeing multiple NULLs meaning 2 active records.

Expectation

The last record with latest cortex_etl_timestamp should be the only active record.

cortex_item_sk cortex_etl_sk cortex_etl_timestamp Customer_Name_2 dbt_valid_from dbt_valid_to
3c518f3d73fcd99c40715c794a4f0738 57115a27c62562a3981483c57fb8beab 7/11/2024 15:37 NULL 7/11/2024 15:37 9/10/2024 14:50
3c518f3d73fcd99c40715c794a4f0738 8ff1350cc3d2bf14cb3eca99c078002b 9/10/2024 14:50 One 9/10/2024 14:50 9/10/2024 14:53
3c518f3d73fcd99c40715c794a4f0738 999164bfb8aeaad00b2966fe257d1c17 9/10/2024 14:53 Two 9/10/2024 14:53 9/10/2024 14:55
3c518f3d73fcd99c40715c794a4f0738 3eadfcc49c543d66d716cbbce614f600 9/10/2024 14:55 Three 9/10/2024 14:55 NULL

Some example code


{{
    config(
        target_schema = var('snapshot_schema'),
        target_database = var('snapshot_database'),
        unique_key = 'cortex_item_sk',
        strategy = 'timestamp',
        updated_at = 'cortex_etl_timestamp'
        
    )
}}