[Bug] Unexpected unit test exception - column does not exist

[Auric-Manteo]

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

Hi,
I get an unexpected error in my unit test. We boiled it down to this "working" example.
If you see a possible workaround please respond in the Slack thread:
https://getdbt.slack.com/archives/C01UM2N7814/p1744267340681719

06:37:03  Finished running 1 unit test in 0 hours 0 minutes and 20.01 seconds (20.01s).
06:37:03  
06:37:03  Completed with 1 error, 0 partial successes, and 0 warnings:
06:37:03  
06:37:03    Runtime Error in unit_test test42 (models/core_api/compliance/test_core_api_agreement_party.yml)
  An error occurred during execution of unit test 'test42'. There may be an error in the unit test definition: check the data types.
   Database Error
    column "_airbyte_emitted_at" does not exist in agreement_party

models/core_api_agreement_party.sql

with agreement_party as (
    select
        agreement_party.id,
        _airbyte_emitted_at as added_to_staging_layer_at_utc
    from {{ source('core_api_source','core_api_agreement_party') }} as agreement_party
)
select *
from agreement_party

models/test_core_api_agreement_party.yml

unit_tests:
  - name: test42
    model: core_api_agreement_party
    given:
      - input: source('core_api_source','core_api_agreement_party')
        rows:
          - {id: 5, agreement_id: 102, name: 'roro', phone_number: 1234567, email: '[email protected]', _airbyte_emitted_at: 2024-02-01}
      - input: this
        rows:
          - { id: 1, agreement_id: 101, name: 'lulu', phone_number: 1234567, email: '[email protected]'}
    expect:
      rows:
        - {id: 5}

The generated SQL query is:

create temporary table
    "test42__dbt_tmp095509064132"
  as (
select
	*
from
	(
        with __dbt__cte__core_api_agreement_party as (
	-- Fixture for core_api_agreement_party
	select
		cast(1 as bigint)
 as id,
		cast('[email protected]' as character varying(256))
 as email,
		cast('lulu' as character varying(256))
 as name,
		cast(1234567 as character varying(256))
 as phone_number,
		cast(null as boolean) as is_agreement_hard_removed,
		cast(null as character varying(256)) as orgnr,
		cast(null as bigint) as state,
		cast(null as character varying(256)) as country,
		cast(null as bigint) as consumer,
		cast(101 as bigint)
 as agreement_id,
		cast(null as bigint) as agreement_removed,
		cast(null as bigint) as updated,
		cast(null as bigint) as created,
		cast(null as timestamp without time zone) as added_to_staging_layer_at_utc,
		cast(null as timestamp with time zone) as added_to_compliance_layer_at_utc
),
	agreement_party as (
	select
		agreement_party.id,
		_airbyte_emitted_at as added_to_staging_layer_at_utc
	from
		__dbt__cte__core_api_agreement_party as agreement_party
)
	select
		*
	from
		agreement_party
    ) as __dbt_sbq
where
	false
limit 0
  );

Expected Behavior

The unit test does not raise an unexpected error.

Steps To Reproduce

Add the files mentioned in the description and run the test via:

dbt test --debug --exclude "test_type:data" --select models/test_core_api_agreement_party.yml

Relevant log output

06:37:03  Finished running 1 unit test in 0 hours 0 minutes and 20.01 seconds (20.01s).
06:37:03  
06:37:03  Completed with 1 error, 0 partial successes, and 0 warnings:
06:37:03  
06:37:03    Runtime Error in unit_test test42 (models/core_api/compliance/test_core_api_agreement_party.yml)
  An error occurred during execution of unit test 'test42'. There may be an error in the unit test definition: check the data types.
   Database Error
    column "_airbyte_emitted_at" does not exist in agreement_party

Environment

- OS: MacOS
- Python: 3.11.10
- dbt: 1.9.4

Which database adapter are you using with dbt?

redshift

Additional Context

No response

[dbeatty10]

Thanks for reaching out @Auric-Manteo !

Do you get the error if you use a seed file instead of a source file? i.e. using ref() instead of source()?

See below for a full example with all necessary files. Could you try tweaking these to get the error that you are reporting?

Project files and commands

Create these files:

seeds/core_api_source__core_api_agreement_party.csv

id,agreement_id,name,phone_number,email,_airbyte_emitted_at
5,102,roro,1234567,[email protected],2024-02-01

models/core_api_agreement_party.sql

with agreement_party as (

    select
        agreement_party.id,
        _airbyte_emitted_at as added_to_staging_layer_at_utc
    from {{ ref("core_api_source__core_api_agreement_party") }} as agreement_party

)

select *
from agreement_party

models/core_api_agreement_party.yml

unit_tests:
  - name: test42
    model: core_api_agreement_party
    given:
      - input: ref("core_api_source__core_api_agreement_party")
        rows:
          - {id: 5, agreement_id: 102, name: 'roro', phone_number: 1234567, email: '[email protected]', _airbyte_emitted_at: 2024-02-01}
      - input: this
        rows:
          - {id: 1, added_to_staging_layer_at_utc: 2025-01-01}
    expect:
      rows:
        - {id: 5}

Run these commands:

dbt seed
dbt run
dbt test --debug --exclude "test_type:data" --select models/test_core_api_agreement_party.yml

Get this output:

...
16:23:29  1 of 1 PASS core_api_agreement_party::test42 ................................... [PASS in 4.32s]
16:23:29  Finished running node unit_test.dbt_project.core_api_agreement_party.test42
16:23:29  Using redshift connection "master"
...
16:23:30  Completed successfully
16:23:30  
16:23:30  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
16:23:30  Resource report: {"command_name": "test", "command_success": true, "command_wall_clock_time": 11.722821, "process_in_blocks": "0", "process_kernel_time": 0.229852, "process_mem_max_rss": "134643712", "process_out_blocks": "0", "process_user_time": 1.164278}
16:23:30  Command `dbt test` succeeded at 10:23:30.216973 after 11.72 seconds
16:23:30  Flushing usage events

[Auric-Manteo]

Hi dbeatty10,
Thanks for looking into this.
Your code works fine. It runs without error.

[dbeatty10]

@Auric-Manteo We'll need to reproduce the issue reported first before we can choose next steps.

Could you try tweaking the code I provided to get the error that you are reporting?

[Auric-Manteo]

Could you try tweaking the code I provided to get the error that you are reporting?

@dbeatty10 yes, of course
Let me know what I should do and I can execute the code locally.

@Auric-Manteo We'll need to reproduce the issue reported first before we can choose next steps.

You mean the example I posted does not produce the error for you?

[dbeatty10]

@Auric-Manteo After taking another look, the issue you are running into is the same as open issue #10433, so I'm going to close this one as a duplicate.

Short explanation

The name of your source table and the name of your dbt model are identical. In these cases, dbt is only creating one CTE when it needs two CTE for the unit test fixtures, leading to the error you are seeing 💥

Could you try the 1st workaround listed below and let me know if it works for you?

Workarounds

Some different workarounds in the meantime:

1. Use a source name that differs from the table name in the database (easiest, recommended) -- see below for details
2. Rename the dbt model so that it is distinct from the source name
3. Use an intermediate ephemeral model (example)

Use a source name that differs from the table name in the database

Change this:

sources:
  - name: core_api_source
    tables:
      - name: core_api_agreement_party  # actual name in the database

to this:

sources:
  - name: core_api_source
    tables:
      - name: SOURCE_WITH_UNIQUE_NAME_HERE  # what I want to call it in dbt
        identifier: core_api_source__core_api_agreement_party  # actual name in the database

[Auric-Manteo]

Ok, cool. I'll wait for the bugfix and try again then.
I tried with your suggestion but must be missing something since I got a different error now.

  

create temporary table
  "test42__dbt_tmp081056744853"
  
  
  
as (
  select * from (
      with  __dbt__cte__core_api_agreement_party as (

-- Fixture for core_api_agreement_party
select 
  
  cast(1 as bigint)
as id, 
  
  cast('[email protected]' as character varying(256))
as email, 
  
  cast('lulu' as character varying(256))
as name, 
  
  cast(1234567 as character varying(256))
as phone_number, cast(null as boolean) as is_agreement_hard_removed, cast(null as character varying(256)) as orgnr, cast(null as bigint) as state, cast(null as character varying(256)) as country, cast(null as bigint) as consumer, 
  
  cast(101 as bigint)
as agreement_id, cast(null as bigint) as agreement_removed, cast(null as bigint) as updated, cast(null as bigint) as created, cast(null as timestamp without time zone) as added_to_staging_layer_at_utc, cast(null as timestamp with time zone) as added_to_compliance_layer_at_utc
),  __dbt__cte__core_api_agreement_party as (

-- Fixture for SOURCE_WITH_UNIQUE_NAME_HERE
select 
  
  cast(5 as bigint)
as id, cast(null as character varying(256)) as country, 
  
  cast('roro' as character varying(256))
as name, 
  
  cast(1234567 as character varying(256))
as phone_number, 
  
  cast('[email protected]' as character varying(256))
as email, 
  
  cast(102 as bigint)
as agreement_id, cast(null as bigint) as created, cast(null as character varying(256)) as orgnr, cast(null as bigint) as state, cast(null as bigint) as updated, cast(null as bigint) as consumer, 
  
  cast('2024-02-01' as timestamp)
as _airbyte_emitted_at
), agreement_party as (
  select
      agreement_party.id,
      _airbyte_emitted_at as added_to_staging_layer_at_utc
  from __dbt__cte__core_api_agreement_party as agreement_party
)
select *
from agreement_party
  ) as __dbt_sbq
  where false
  limit 0

);
06:10:56  Redshift adapter: Redshift error: WITH query name "__dbt__cte__core_api_agreement_party" specified more than once

[dbeatty10]

Thanks for trying that out @Auric-Manteo ! You're right -- the error message you ran into is a related issue to what you originally reported.

So the workarounds are just these two:

1. Rename the dbt model so that it is distinct from the source name
2. Use an intermediate ephemeral model (example)