[X] 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)
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).
Is this a new bug in dbt-snowflake?
Current Behavior
When running
dbt build --empty(orrunor possibly other variants) while using models with joins between ref-ed tables, Snowflake will return an error002027 (42601): SQL compilation error: duplicate alias 'values'. This occurs while running without the--emptyflag will succeed.Expected Behavior
Running dbt with the
--emptyflag should have the same results as running without the flag.Steps To Reproduce
This error can be reproduced using three models:
repro1.sql:
repro2.sql:
repro3.sql:
Relevant log output
No response
Environment
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
--emptyflag (in order to add alimitto the subquery), and Snowflake implicitly aliasing a subquery asvaluesif 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).