'dbt snapshot' fails when run 3rd time onwards

Help
,
1

The problem I’m having

The 3rd time onwards I try to run a ‘dbt snapshot’ command, it fails on an ‘Ambiguous column’ reference.

image
image1358×128 11.3 KB

First time ‘dbt snapshot’: timestamp 1 id 1 value 1
Result: the record gets inserted.

Second time ‘dbt snapshot’: timestamp 2 id 1 value 2
Result: the record of attempt 1 gets enddated and a new record is inserted.

So far so good.

Third time ‘dbt snapshot’: timestamp 3 id 1 value 1
Result: error thrown, referring to an ‘Ambiguous column’.

Upgrading the dbt version on the local development environment had no effect.

Sample code:

{% snapshot snapshot_check %}
 
    {{
        config(
            target_schema='snapshots',
            unique_key='id',
            strategy='check',
            check_cols=['value']
        )
    }}
   
    SELECT id
        , value
    FROM {{ ref('x')}}
   
{% endsnapshot %}

What I’ve already tried

The ‘timestamp’ stragegy with a ‘dts’ column for datetime when the change occured. Result exactly the same.