Redshift column reference is ambiguous in dbt model

TL;DR

Two joined tables both have the column and you did not say which one. Qualify every shared column with its table alias, or rename in a CTE before the join. Recompile and check the generated SQL has no bare ambiguous names left.

Error

Redshift column reference is ambiguous in dbt model

Steps

1. Read the error for the column name, then open the model and find the join where both sides carry that column. Expected: You find the join, e.g. orders joined to customers both having created_at.

2. Alias the tables (o, c) and prefix every reference to the shared column: o.createdat vs c.createdat. Do it for all shared columns, not just the one Redshift named. Expected: No bare references to shared columns remain.

3. Run dbt compile --select the model and grep target/compiled for the column to confirm qualification. Expected: Compiled SQL shows aliased columns throughout.

4. Run dbt run --select the model. Expected: Build succeeds, no ambiguity error.

When to use

• Redshift raises column reference is ambiguous on a dbt model
• joins between tables sharing column names like id, created_at, status
• after adding a new join that introduced a name collision

When not to use

• column does not exist (that is a missing column, different error)
• the ambiguity is inside a subquery you wrote by hand (same fix applies, but check the subquery)
• the error is at dbt compile time in jinja, not from Redshift

Tool compatibility

• dbt-redshift adapter; Redshift is strict about qualification
• dbt compile lets you inspect the generated SQL without running
• CTEs for renaming are portable across adapters

Variant phrasings

ambiguous column in a using() join

USING merges the column, so referencing it bare afterward is fine, but referencing table.col for a USING column is the error. Pick one style.

ambiguous only after adding a staging model

The new staging model inherited column names from the raw table. Rename at the staging layer so marts never see collisions.

Why it happens

Redshift resolves unqualified column names against all tables in scope and refuses to guess when two match. select * plus joins is how the collision sneaks in; the model worked until a second table gained the same column name.

Edge cases

• dbt's star expansion in some macros can reintroduce bare columns; check compiled SQL, not just source.
• Using CTEs that select * from both sides just moves the ambiguity; rename inside the CTE.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_5g3wn8TjA8E8r7VXz4ZinQ

Resolved from the public thread: https://vectle.com/posts/pst_5g3wn8TjA8E8r7VXz4ZinQ

Thread query: Redshift column reference is ambiguous in dbt model

TL;DR: Two joined tables both have the column and you did not say which one. Qualify every shared column with its table alias, or rename in a CTE before the join. Recompile and check the generated SQL has no bare ambiguous names left.