Hi, I created a dbt snapshot and it’throwing the ambiguous column error on 2nd run:
ERROR: Column ‘division_id’ is ambiguous
I’ve checked the logs and found that the below merge statement is causing the issue, since all the columns are present in both _DEST & _SOURCE tables, it should give the table_name.column_name while inserting the data, but it’s only giving the column name which is causing the issue:
merge into “NessieCatalog”.“Test_Curated”.“stg_Divisions_sens” as dbt_INTERNAL_DEST
using “NessieCatalog”.“Test_Curated”.“stg_Divisions_sens__dbt_tmp” as dbt_INTERNAL_SOURCE
on dbt_INTERNAL_SOURCE.dbt_scd_id = dbt_INTERNAL_DEST.dbt_scd_id
when matched
then update
set dbt_valid_to = dbt_INTERNAL_SOURCE.dbt_valid_to
when not matched
then insert (“division_id”, “division_name”, “location”, “airbyte_run_jobtime”, “dbt_updated_at”, “dbt_valid_from”, “dbt_valid_to”, “dbt_scd_id”)
values (“division_id”, “division_name”, “location”, “airbyte_run_jobtime”, “dbt_updated_at”, “dbt_valid_from”, “dbt_valid_to”, “dbt_scd_id”)
Python version - 3.8.17
dbt version - 1.7.13
I tested the dbt snapshots about 4-5 months back with the same versions and it was working fine but now it’s throwing this error.
I’ve tried running full load a few times but it always fails on 2nd run.
My snapshot:
{% snapshot stg_Divisions_sens %}
{{ config(
target_database=‘NessieCatalog’,
target_schema=‘Test_Curated’,
unique_key=‘division_id’,
strategy=‘timestamp’,
updated_at=‘airbyte_run_jobtime’,
) }}
select * from {{ source(‘Test_Bronze’, ‘Divisions’) }}
{% endsnapshot %}