[Bug] Duplication of CTEs during unit tests when two referenced models have the same custom alias

[dbeatty10]

Originally posted by @yonatan-cohen8186 in #10728 (comment):

One more in the same context (more or less) -
In case of two refs that happen to hold the same alias (but differ by the database and/or schema) - How can we avoid a duplication of CTEs?

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

When a model being unit tested has two upstream models that both have the same database identifier, then there is a SQL error like the following when running the unit test:

17:18:06    Runtime Error in unit_test dbt_core_10740 (models/_properties.yml)
  An error occurred during execution of unit test 'dbt_core_10740'. There may be an error in the unit test definition: check the data types.
   Runtime Error
    Parser Error: Duplicate CTE name "__dbt__cte__my_upstream_model"

There are a couple ways the same database identifier can come about:

1. both models use a custom alias, and both have the same value (full example provided below)
2. one of the models uses a custom alias, and it is the same as the model name of the other (full example not provided)

I didn't check one way or the other for cross-project references, but that could be another case this might come about.

Expected Behavior

Each given model should have its own CTE, and those CTE should have unique names from each other.

A different way to consider this:

• If the given models have unique fully qualified database identifiers, then they should have unique CTE when they are unit tested.
• If the given models have the same fully qualified database identifier, then they should either share the same CTE, or raise an error that two different model references point at the same database object.

Steps To Reproduce

Create these files:

models/my_upstream_model_a.sql

{{ config(schema="upstream_a", alias="my_upstream_model") }}

select 1 as id_a

models/my_upstream_model_b.sql

{{ config(schema="upstream_b", alias="my_upstream_model") }}

select 1 as id_b

models/my_model.sql

select id_a as id from {{ ref("my_upstream_model_a") }} union all
select id_b as id from {{ ref("my_upstream_model_b") }}

models/_unit_tests.sql

unit_tests:
  - name: dbt_core_10740
    model: my_model
    given:
      - input: ref('my_upstream_model_a')
        rows:
          - {id_a: 2}
      - input: ref('my_upstream_model_b')
        rows:
          - {id_b: 3}
    expect:
      rows:
          - {id: 1}

Run these commands:

dbt run -s +my_model --empty
dbt build --select my_model

Relevant log output

$ dbt build --select my_model  
17:41:37  Running with dbt=1.8.6
17:41:38  Registered adapter: duckdb=1.8.3
17:41:39  Found 3 models, 410 macros, 1 unit test
17:41:39  
17:41:39  Concurrency: 1 threads (target='dev')
17:41:39  
17:41:39  1 of 2 START unit_test my_model::dbt_core_10740 ................................ [RUN]
17:41:39  1 of 2 ERROR my_model::dbt_core_10740 .......................................... [ERROR in 0.25s]
17:41:39  2 of 2 SKIP relation feature_456.my_model ...................................... [SKIP]
17:41:39  
17:41:39  Finished running 1 unit test, 1 view model in 0 hours 0 minutes and 0.68 seconds (0.68s).
17:41:39  
17:41:39  Completed with 1 error and 0 warnings:
17:41:39  
17:41:39    Runtime Error in unit_test dbt_core_10740 (models/_properties.yml)
  An error occurred during execution of unit test 'dbt_core_10740'. There may be an error in the unit test definition: check the data types.
   Runtime Error
    Parser Error: Duplicate CTE name "__dbt__cte__my_upstream_model"
17:41:39  
17:41:39  Done. PASS=0 WARN=0 ERROR=1 SKIP=1 TOTAL=2

Environment

- OS: 
- Python: 
- dbt: 

Which database adapter are you using with dbt?

No response

Additional Context

No response