[Regression] Unit tests generate an sql with wrong CTE this referrence on incremental model with alias

[katsugeneration]

Is this a new bug in dbt-core?

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

Current Behavior

Unit tests fail due to wrong this cte referrence on incremental model with alias.

Expected Behavior

I expect that the name of this referrence will be consistent with the cte, regardless of alias.

Steps To Reproduce

1. I have a incremental model with alias

{{ 
    config(
        materialized='incremental',
        alias='aliased_example',
    )
}}

with source_data as (

    select 1 as id
    union all
    select null as id

)

select *
from source_data
{% if is_incremental() %}
where id > (select max(id) from {{ this }} )
{% endif %}

2. And a unit test on a table which overrides is_incremental macro is true

unit_tests:
  - name: case_incremental
    model: example
    overrides:
      macros:
        is_incremental: true
    given:
      - input: this
        rows:
          - {id: 0}
    expect:
      rows:
        - {id: 1}

3. Then I am running the unit test dbt test --s example, I am getting an error:

15:04:35  Finished running 1 unit test in 0 hours 0 minutes and 2.35 seconds (2.35s).
15:04:35  
15:04:35  Completed with 1 error and 0 warnings:
15:04:35  
15:04:35    Runtime Error in unit_test case_incremental (models/example/schema.yml)
  An error occurred during execution of unit test 'case_incremental'. There may be an error in the unit test definition: check the data types.
   Database Error
    Table "__dbt__cte__example" must be qualified with a dataset (e.g. dataset.table).

because the generated SQL has different name of the CTE and the from referrence:

select * from (
        

with  __dbt__cte__aliased_example as (

-- Fixture for example
select safe_cast(0 as INT64) as id
), source_data as (

    select 1 as id
    union all
    select null as id

)

select *
from source_data

where id > (select max(id) from __dbt__cte__example )

    ) as __dbt_sbq
    where false and current_timestamp() = current_timestamp()
    limit 0

    )

Relevant log output

No response

Environment

- OS: Ubuntu 24.04
- Python: 3.12.3
- dbt: 1.8.6

Which database adapter are you using with dbt?

bigquery

Additional Context

No response

[dbeatty10]

@katsugeneration Thanks for reporting this and opening #10755 to fix it 🏆 !

I was able to reproduce the issue you described with the following commands:

dbt run -s example --empty
dbt build --select example

[dbeatty10]

Labeling this as a regression since this worked with dbt-core==1.8.5 dbt-adapters==1.4.0 but stopped working with dbt-core==1.8.6 dbt-adapters==1.4.1.