Ambiguous Column Error in Snapshot with check_cols strategy in dbt-Athena

[karthigamurugesan]

When running a dbt snapshot with the check_cols strategy enabled in dbt-Athena, the third run fails due to an ambiguous column error.

This issue occurs because dbt_unique_key_1 and dbt_unique_key_2 are added to the target table after the second run, but they are also explicitly defined in the SELECT list. As a result, the third run fails with an ambiguous column error.

Steps to Reproduce:
Run a dbt snapshot with check_cols enabled for a table in Athena.

The first run completes successfully, creating the snapshot table.
Run the snapshot again (second run):

This adds dbt_unique_key_1 and dbt_unique_key_2 to the target snapshot table.
Run the snapshot a third time:

The query now includes dbt_unique_key_1 and dbt_unique_key_2 in both:
The table schema (as part of snapshotted_data).
The SELECT list, where they are explicitly defined (e.g., year AS dbt_unique_key_1).
This results in an ambiguous column error, causing the job to fail.

dbt.log:

Expected Behavior:
The snapshot should execute without ambiguity in column names.

Observed Behavior (Error Message):

Runtime Error in snapshot athena_snapshot_src_parquet
AMBIGUOUS_NAME: Line 51:36: Column 'snapshotted_data.dbt_unique_key_1' is ambiguous.

Additional Information:
dbt Version: 1.9.2
dbt-Athena Adapter Version: 1.8.4