dbt snapshot ambiguous column reference

Help
1

The problem I’m having

dbt snapshot fails on subsequent runs as a result of an ambiguous column error in the source code

image
image1623×178 10.4 KB

FYI This is using dbt-databricks. My snapshot block looks like this:

{% snapshot raw_reviews_snapshot %}

{{
    config(
      target_schema='airbnb_poc',
      strategy='check',
      unique_key = 'ID',
      check_cols=['listing_id', 'date', 'comments', 'reviewer_name', 'reviewer_id']
    )
}}

SELECT 
    ID,
    listing_id,
    date,
    reviewer_id,
    reviewer_name,
    comments
FROM {{ source('airbnb_source','raw_reviews') }}
WHERE ID IS NOT NULL

{% endsnapshot %}

2

Hi Jeremy,

Did you get an answer on this? I am facing the same problem with snapshotting data in dbt

Thanks.

Nick

3

Hi, at least in my case , this is because I’ve schema evolution enabled. If I don’t this is not a problem.

TLDR:

dbt will insert a dbt_unique_key column at their 2nd run of the model. You can notice this in the dbt.log file.

You will see how at the first run of an snapshot, dbt doesn’t use the dbt_unique_key column. But in the following snapshot runs it does. And due to the schema evolution enabled this causes the issue.

One workaround solution to support schema evolution in snapshot models is to set the following query in the post_hook section “ALTER TABLE {{ this }} DROP COLUMN IF EXISTS dbt_unique_key”

4

Have the same issue with AWS Athena. When using “dbt snapshot --build” you will see the sql where it fails.

dbt creates as t temporary table “…_tmp”

Error is in this part. The unique key get aliased with the name dbt_unique_key_1, while this column is also created in the snapshot table. Result is “AMBIGUOUS_NAME: line 48:35: Column ‘snapshotted_data.dbt_unique_key_1’ is ambiguou” :

snapshotted_data as (

    select *, 

    
        model_name as dbt_unique_key_1
    


    from "snapshot_tables_stg_count"
    where
        
            dbt_valid_to is null
        

)

Workarounds like a post_hook to drop a column does not work as this sql is generated by dbt to create a temporay table