[Bug] dbt build --empty results in a SQL compilation error when joining unaliased models

[amardatar]

Is this a new bug in dbt-snowflake?

• I believe this is a new bug in dbt-snowflake
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When running dbt build --empty (or run or possibly other variants) while using models with joins between ref-ed tables, Snowflake will return an error 002027 (42601): SQL compilation error: duplicate alias 'values'. This occurs while running without the --empty flag will succeed.

Expected Behavior

Running dbt with the --empty flag should have the same results as running without the flag.

Steps To Reproduce

This error can be reproduced using three models:

repro1.sql:

select 1 as a

repro2.sql:

select 1 as a

repro3.sql:

select *
from {{ ref('repro1') }}
join {{ ref('repro2') }} using (a)

Relevant log output

No response

Environment

- OS: macOS Sequoia 15.1
- Python: 3.12.1
- dbt-core: 1.8.8
- dbt-snowflake: 1.8.4

Additional Context

The issue exists only when performing a join on "ref"-ed models, without using an alias for any of the models.

The issue appears to be the result of both dbt using subqueries to template refs while using the --empty flag (in order to add a limit to the subquery), and Snowflake implicitly aliasing a subquery as values if that subquery does not have an explicit alias (see https://community.snowflake.com/s/article/A-query-joining-on-subquery-without-alias-fails-with-the-error).

The issue can be resolved by adding aliases, as suggested by the Snowflake article linked above, so this might be considered not worth fixing.

I had a quick look to see if this could be resolved by using require_alias, however this conflicts with any joins where an alias is already explicitly defined (which will likely be most cases in a typical project).

Another solution to this might be to use the ephemeral-model approach of templating via a CTE rather than a subquery (although this might be a change in dbt-adapters).