[Feature] Support contextual table aliasing

[colin-rogers-dbt]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-adapter functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Follow up from: #124

Currently in databases like postgres (at least pre v16) we attempt to inject subqueries with aliases. This can create a poor user experience as when they write something like:

select * from {{ source('my_table') }} as my_source where x=y 

and run with --empty, we produce something like:

select * from {{ source('my_table') }} as _dbt_limit_subq as my_source where x=y 

The duplicate aliases are invalid sql.

The challenge is that in order to know whether the ref already has an alias.

Describe alternatives you've considered

No response

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

No response

[igorvoltaic]

To anyone searching for a quick short term solution:
We used a dirty hack to run our pipelines with --empty. We parse our sql after it compiles here and remove double aliases where it breaks our code.

[gajanand-jm]

facing the same issue

any solution or progress here? we are planning to use --empty for CI

[michael-robbins-seek]

We ended up wrapping some of our references that had an alias with parenthesis, we're using the dbt Athena plugin and it seems to be ok (albeit hacky).

Eg

SELECT *
FROM ({{ source("my_schema", "my_table") }}) as foo

Which renders out as:

SELECT *
FROM ((select * from my_schema.my_table limit 0) as _dbt_injected_alias) as foo

[developmentalmadness]

I'm running into the issue w/ dbt-databricks 1.8.7 (dbt-core 1.8.8). What I don't understand is why this needs an alias at all? If platforms like postgres require an alias, then won't the model already be written with an alias? Otherwise, it seems the model wouldn't work under normal conditions.

UDPATE: I now realize this was for subqueries and that models written for a single table will often not use an alias. For some reason my brain filtered out "subqueries" and I inferred "all tables".

[amychen1776]

Related

• [Bug] Model reference and AS with empty run fails dbt-postgres#175
• [Bug] dbt build --empty results in a SQL compilation error when joining unaliased models dbt-snowflake#1251
• [Bug] --empty flag fails with dbt.ref in snowpark dbt-core#10517
• DBT External tables don't work with the --empty flag dbt-core#10464
• [CT-3310] Support --empty flag for dbt seed dbt-core#8981