# dbt snapshot ambiguous column reference

**URL:** <https://discourse.getdbt.com/t/dbt-snapshot-ambiguous-column-reference/8889>\
**Category:** Help\
**Created:** [June 27, 2023, 4:46pm UTC](https://discourse.getdbt.com/t/dbt-snapshot-ambiguous-column-reference/8889 "2023-06-27T16:46:41Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jeremym9](https://avatars.discourse-cdn.com/v4/letter/j/a3d4f5/32.png) [@jeremym9](https://discourse.getdbt.com/u/jeremym9)\
**Post date:** [June 27, 2023, 4:46pm UTC](https://discourse.getdbt.com/t/dbt-snapshot-ambiguous-column-reference/8889/1 "2023-06-27T16:46:41Z")

</div>

## The problem I’m having

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

 ![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/8/84e2e7147495f3cb99a1b2b549e49e537f3c91a8.png)

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 %}

---

<div class="post-metadata">

**Author:** ![nick.van.dal](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@nick.van.dal](https://discourse.getdbt.com/u/nick.van.dal)\
**Post date:** [March 8, 2024, 3:08pm UTC](https://discourse.getdbt.com/t/dbt-snapshot-ambiguous-column-reference/8889/2 "2024-03-08T15:08:06Z")

</div>

Hi Jeremy,

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

Thanks.

Nick

---

<div class="post-metadata">

**Author:** ![nbuzz](https://avatars.discourse-cdn.com/v4/letter/n/f9ae1b/32.png) [@nbuzz](https://discourse.getdbt.com/u/nbuzz)\
**Post date:** [April 30, 2024, 2:57pm UTC](https://discourse.getdbt.com/t/dbt-snapshot-ambiguous-column-reference/8889/3 "2024-04-30T14:57:53Z")

</div>

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”

---

<div class="post-metadata">

**Author:** ![Tazalytics](https://avatars.discourse-cdn.com/v4/letter/t/2bfe46/32.png) [@Tazalytics](https://discourse.getdbt.com/u/Tazalytics)\
**Post date:** [August 24, 2026, 2:10pm UTC](https://discourse.getdbt.com/t/dbt-snapshot-ambiguous-column-reference/8889/4 "2026-08-24T14:10:44Z")

</div>

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” :

```auto
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

---

<div class="post-metadata">

**Author:** ![luckyraven60782](https://avatars.discourse-cdn.com/v4/letter/l/bbce88/32.png) [@luckyraven60782](https://discourse.getdbt.com/u/luckyraven60782)\
**Post date:** [October 27, 2026, 6:29pm UTC](https://discourse.getdbt.com/t/dbt-snapshot-ambiguous-column-reference/8889/5 "2026-10-27T18:29:26Z")

</div>

This post was flagged by the community and is temporarily hidden.