Microbatch generates invalid SQL when using aliases

[M4Al]

I have a model similar like this
select A.id, B.id from {{ref('sometable'}} A inner join {{ref'someOtherTable')}} B on A.id = B.id

If this is configured as a microbatch model,, it gets compiled to

select A.id, B.id from (select * from someTable where lastModifiedAt >= ''2025-01-21 00:00:00+00:00'' and lastModifiedAt < ''2025-01-22 00:00:00+00:00'') _dbt_et_filter_subq_sometable A inner join someOtherTable B on A.id = B.id

as you can see there is a extra unexpected alias _dbt_et_filter_subq_sometable inserted that causes the sql to break.

I know that these kind of subqueries need an alias to be accepted as valid SQL, so that's probably the reason they are added automatically.

The workaround in my case is to wrap the table in an extra select statement like this

select A.id, B.id from ( select * from {{ref('sometable'}} ) A inner join {{ref'someOtherTable')}} B on A.id = B.id

Running dbt-core 1.9.1 and dbt-fabric 1.9.0

[prdpsvs]

@M4Al , Could you please share a repro, of your model and config?

It looks like dbt is adding alias by default using _render_subquery_alias(). https://github.com/dbt-labs/dbt-adapters/blob/999dd7fd8238259538e271938cb5d1fc7e65c335/dbt-adapters/src/dbt/adapters/base/relation.py#L252

I am trying to find out if alias is optional or not
dbt-labs/dbt-adapters#807