[Bug]: Not able to run the snapshot on the second run

[rgupta2398]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

I am integrating DBT with Dremio and want to implement the SCD2 logic. So on the very first run, it is working fine but on the subsequent runs, it is giving error - "product id" is ambiguous.

Below is my Snapshot model-
{% snapshot snapshot_products %}
{{
config(
target_schema='Bronze',
target_database='NessieCatalog',
unique_key='product_id',
strategy='timestamp',
updated_at='order_date'
)
}}

SELECT
   product_id,
    product_name,
  order_date,
    quantity,
    amount FROM {{ ref('raw_products') }} 

{% endsnapshot %}

dbt_project.yml --
models:
snapshottest:
Bronze:
+object_storage_source: NessieCatalog
+object_storage_path: Bronze
+materialized: table

snapshots:
snapshottest:
+object_storage_source: NessieCatalog
+object_storage_path: Bronze
+materialized: snapshot

Error --

13:28:06 1 of 1 START snapshot NessieCatalog.Bronze.snapshot_products ................... [RUN]
13:28:11 1 of 1 ERROR snapshotting NessieCatalog.Bronze.snapshot_products ............... [ERROR in 4.77s]
13:28:11
13:28:11 Finished running 1 snapshot in 0 hours 0 minutes and 5.97 seconds (5.97s).
13:28:11
13:28:11 Completed with 1 error and 0 warnings:
13:28:11
13:28:11 Runtime Error in snapshot snapshot_products (snapshots/snapshot_products.sql)
ERROR: Column 'product_id' is ambiguous

It is throwing error on the below query generated by dremio on backend---

merge into "NessieCatalog"."Bronze"."snapshot_products" as DBT_INTERNAL_DEST
using "NessieCatalog"."Bronze"."snapshot_products__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 ("product_id", "product_name", "order_date", "quantity", "amount", "dbt_updated_at", "dbt_valid_from", "dbt_valid_to", "dbt_scd_id")
    values ("product_id", "product_name", "order_date", "quantity", "amount", "dbt_updated_at", "dbt_valid_from", "dbt_valid_to", "dbt_scd_id")

Expected Behavior

No response

Steps To Reproduce

I am using dbt core 1.7 version and dremio software

Environment

- OS:
- dbt-dremio:
- Dremio Software:
- Dremio Cloud:

Relevant log output

[rgupta2398]

Hi Team, Can i get an update on this?