[Bug] Incorrect CTE name in unit tests using incremental mode for incremental models with a custom alias

[dbeatty10]

Is this a new bug?

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

Current Behavior

As originally reported by @jtstults-w in dbt-labs/dbt-core#10728 (comment):

The SQL for the unit test is not able to execute -- it fails with a "Table with name __dbt__cte__my_model does not exist!" error.

The erroneous SQL generally looks like this:

        create temporary table
          "dbt_core_TODO__dbt_tmp20240919065831552796"

        as (
          select * from (

    with __dbt__cte__my_model_alias as (

    -- Fixture for my_model
    select
    -- ...

    where event_time >= (select coalesce(max(event_time), 0) from __dbt__cte__my_model )

Note the mismatch between the CTE named __dbt__cte__my_model_alias and the reference in the where clause to CTE __dbt__cte__my_model 💥

Expected Behavior

The SQL for the unit test is able to execute.

Steps To Reproduce

Reprex

Create these files:

models/my_model.sql

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

select 2 as id, 1 as event_time

{% if is_incremental() %}

where event_time >= (select coalesce(max(event_time), 0) from {{ this }} )

{% endif %}

models/_properties.yml

models:
  - name: my_model

unit_tests:
  - name: dbt_core_TODO
    model: my_model
    overrides:
      macros:
        is_incremental: true
    given:
      - input: this
        rows:
          - {id: 2, event_time: 1}
    expect:
      rows:
          - {id: 2, event_time: 1}

Run these commands:

dbt run --select my_model --empty --full-refresh
dbt build --select my_model

Relevant log output

�[0m13:05:49  Running with dbt=1.8.6
�[0m13:05:52  Registered adapter: duckdb=1.8.3
�[0m13:05:53  Found 1 model, 410 macros, 1 unit test
�[0m13:05:53  
�[0m13:05:53  Concurrency: 1 threads (target='dev')
�[0m13:05:53  
�[0m13:05:53  1 of 2 START unit_test my_model::dbt_core_TODO ................................. [RUN]
�[0m13:05:53  1 of 2 ERROR my_model::dbt_core_TODO ........................................... [�[31mERROR�[0m in 0.14s]
�[0m13:05:53  2 of 2 SKIP relation feature_456.my_model ...................................... [�[33mSKIP�[0m]
�[0m13:05:53  
�[0m13:05:53  Finished running 1 unit test, 1 incremental model in 0 hours 0 minutes and 0.32 seconds (0.32s).
�[0m13:05:53  
�[0m13:05:53  �[31mCompleted with 1 error and 0 warnings:�[0m
�[0m13:05:53  
�[0m13:05:53    Runtime Error in unit_test dbt_core_TODO (models/_properties.yml)
  An error occurred during execution of unit test 'dbt_core_TODO'. There may be an error in the unit test definition: check the data types.
   Runtime Error
    Catalog Error: Table with name __dbt__cte__my_model does not exist!
    Did you mean "feature_456.my_model"?
    LINE 29: ...version": "1.8.6", "profile_name": "duckdb", "target_name": "dev", "node_id": "unit_test.my_project.my_model.dbt_core_TODO"} */
    
        
      
        
        
    
        create temporary table
          "dbt_core_TODO__dbt_tmp20240919070553469373"
      
        as (
          select * from (
            
    
    with __dbt__cte__my_model_alias as (
    
    -- Fixture for my_model
    select 
        
        cast(2 as INTEGER)
     as id, 
        
        cast(1 as INTEGER)
     as event_time
    ) select 2 as id, 1 as event_time
    
    
    
    where event_time >= (select coalesce(max(event_time), 0) from __dbt__cte__my_model )
                                                       ^
�[0m13:05:53  
�[0m13:05:53  Done. PASS=0 WARN=0 ERROR=1 SKIP=1 TOTAL=2

Environment

- OS:
- Python:
- dbt-adapters: 1.4.1
- dbt-core: 1.8.6

Additional Context

This scenario is really similar to dbt-labs/dbt-core#10763 but with a custom model alias rather than a model version. Might be the same underlying issue.

[dbeatty10]

Closing in favor of dbt-labs/dbt-core#10754.