[Bug] unit test use source name for cte name and source identifier for table name

[zhoujia74]

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

I try to unit test a model that has a source table configured with both identifier and name. When we run the unit test, the test fails with error

TABLE_NOT_FOUND: line xxx:xx: Table 'xxx.__dbt__cte__test' does not exist.

source config:

  tables:
  - identifier: test
    name: test_table

model.sql:
select * from {{ source("test_table") }}

After checking the log, the generated sql uses source name for the generated cte name and source identifier to replace the source table name.

__dbt__cte__test_table as ( xxxx ) select * from __dbt_cte__test

If we rename the source identifier to match the name, then the error will be gone.

Expected Behavior

unit test should use same way to generate both the cte name and source table name

Steps To Reproduce

Follow the steps in the current behavior.

Relevant log output

No response

Environment

- OS: mac
- Python: 3.10.2
- dbt: both 1.8.4 and 1.8.6

Which database adapter are you using with dbt?

other (mention it in "Additional Context")

Additional Context

dbt-athena adapter

[dbeatty10]

Thanks for raising this issue @zhoujia74 !

What version of the dbt-adapters package do you have installed?

If you are using pip to install dbt, you can do a command like the following to get that version:

pip freeze | grep dbt-adapters

If I try a scenario like you described with dbt-core==1.8.6 dbt-adapters==1.4.0, then I get an error message similar to you.

But if I try it again with dbt-core==1.8.6 dbt-adapters==1.4.1, then it works.

[zhoujia74]

@dbeatty10 Thank you for looking into this. I just did some tests with different dbt versions. I can reproduce the error with dbt-core 1.8.4 and dbt-adapters 1.4.1. It works fine with dbt-core 1.8.6 and dbt-adapters 1.4.1. With what you finds on dbt-core 1.8.6 and dbt-adapters 1.4.0. Not sure which package is the real cause. Maybe something was fixed in both dbt-core and dbt-adapters. Anyway glad to know the latest versions work. Thanks you.

[dbeatty10]

Maybe something was fixed in both dbt-core and dbt-adapters.

It was!

There are two PRs in dbt-core and dbt-adapters that need to work in concert with each other:

• Use model alias for the CTE identifier generated during ephemeral materialization #10290 (dbt-core>=1.8.6)
• Use model alias for the CTE identifier generated during ephemeral materialization dbt-adapters#236 (dbt-adapters>=1.4.1)

So either of these combinations will have the desired behavior ✅ , but anything else will not ❌ :

• dbt-core>=1.8.6 and dbt-adapters>=1.4.1, or
• dbt-core<1.8.6 and dbt-adapters<1.4.1

Since this is working for you now with the latest versions, I'm going to close this as already covered. But please reach out if you run into anything else.