Building snapshots on an append only table.

Help
1

My problem

On snowflake I have a RAW, STAGING and MARTS database. In my RAW database I ingest data in an append only way. If a row in production gets updated it copies it to the RAW.SCHEMA.TABLE.

This means that for one ‘primary key’ I can have multiple rows, but the cursor value, let’s say ‘updated_at’ is different. Essentially logging the changes.

I initially had written some custom logic to build SCD Type 2 tables. But, now I am trying to use Snapshots for this. However, this does not seem to work.

For example, let’s say I have 3 entries for the same ‘primary key’; so 3 updates. When I run:

dbt snapshot

The column dbt_VALID_TO is empty for all 3 rows, while I would expect it only to be empty for the most recent row.

Can someone explain what I am doing wrong? Or am I misunderstanding what snapshots are for?

My code

{% snapshot table_history %}

{{
    config(
      target_database='RAW',
      target_schema='SCHEMA',
      unique_key='ID',

      strategy='timestamp',
      updated_at='updated_at',
    )
}}

select * from {{ source('SCHEMA', 'TABLE') }}

{% endsnapshot %}