[Regression] Unit tests generate an sql with wrong CTE on models with alias

[jan-benisek]

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 subquery name on input models with alias.

Expected Behavior

I expect that the name of the CTE will be consistent with the from, regardless of alias.

Steps To Reproduce

• Maybe related ❗
  • this does not manifest with all the -t. It only happens under our CI target where we have slightly different rules to target schemas etc. Not sure what could help, but let me know if I can add more details.

If I have a model with alias:

# model name is slv_events.sql
{{
    config(
        alias='beautiful_events',
        schema='events',
        materialized='view'
    )
}}

select
    'foo' as foo

And a unit test on a table which has the aforementioned table as input:

unit_tests:
  - name: my_test
    description: "I am not working"
    model: usage
    overrides:
      macros:
        is_incremental: false
    given:
      - input: ref('slv_events')
        rows:
          - {foo: bar }
          - {foo: foo }
      - input: ref('second_dep')
        rows:
          - {tool: shovel}
    expect:
      rows:
        - {expect: 1, hope: hedgehog}

Then I am running the unit test dbt test --select "usage,test_type:unit" -t ci, I am getting an error:

11:29:48    Runtime Error in unit_test test_my_test (models/foo/bar/usage.yml)
  An error occurred during execution of unit test 'test_my_test'. There may be an error in the unit test definition: check the data types.
   Database Error
    relation "__dbt__cte__beautiful_events" does not exist

Which of course does not work, because the generated SQL has different name of the CTE and the from:

create temporary table
    "test_my_test__dbt_tmp112947432346"

  as (
    select * from (

with

 __dbt__cte__slv_events as ( -- <<<<<<<<<<< THIS DOES NOT MACH

-- Fixture for slv_events
select cast('bar' as character varying(32)) as foo, cast(null as boolean) as squirel
union all
select cast('foo' as haracter varying(32)) as foo, cast(null as boolean) as squirel
),  __dbt__cte__second_dep as (

-- Fixture for pdf_tools
select cast('shovel' as character varying(20)) as tool
), base_events as (

    select
        fe.active,
        fe.bar,

    from __dbt__cte__beautiful_events as fe -- <<<<<<<<<<<<< WITH THIS

    left join __dbt__cte__second_dep as pt on (
        fe.bar = pt.tool
    )

),

result as (

    select
        *
        -- bunch of other queries and transformations

    from magic_table
)

select
    *

from result
    ) as __dbt_sbq
    where false
    limit 0
  );

Relevant log output

No response

Environment

- OS: Mac
- Python: `Python 3.12.4`
- dbt: `1.8.3`

Which database adapter are you using with dbt?

redshift

Additional Context

No response

[dbeatty10]

Thanks for reporting this @jan-benisek !

This looks to me to have the same underlying root cause as dbt-labs/dbt-adapters#284.

More detail here: dbt-labs/dbt-adapters#284 (comment)

[hota911]

@dbeatty10 Thank you for looking into the issue. I have the same issue even with dbt-adapters = 1.4.0. The model is materialized="table" so workaround 3 does not work also.

[tsafacjo]

can I pick it ?

[chenxuanrong]

Can confirm the same issue happens on source with different identifier as well.

[ryan-pip]

I am also getting the same error where we use a custom generate_alias_name macro which insert a prefix on table names during development. Fails on any incremental models.

[jan-benisek]

I thought this would be resolved in 1.8.8 with #10754 but it seems like the error is still there.