[Bug] the run_query macro causes unit tests to fail with a SQL Compilation error

[kandji-stephen]

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'm trying unit tests with a model that has a couple run_query macro calls. I found that this macro causes the unit test to fail with a SQL compilation error. This model can be run without the unit test and it will compile correctly.

  002003 (42S02): SQL compilation error:
  Object '__DBT__CTE__BASE_TABLE' does not exist or not authorized.

Here's a simple model and unit test which will produce this error.

simple_test_model.sql:

{% set query %}
    SELECT max(created_at) 
    from {{ ref('base_table') }}
{% endset %}
{% set max_date = run_query(query) %}

select *
from {{ ref('base_table') }}

Unit test:

unit_tests:
  - name: simple_test_model_test
    model: simple_test_model
    given:
      - input: ref('base_table')
        rows:
          - {created_at: "2024-01-05T10:10:10Z"}
    expect:
      rows:
        - {created_at: "2024-01-05T10:10:10Z"}

Expected Behavior

Expect the unit test to succeed (or at least not crash)

Steps To Reproduce

1. Setup the model simple_test_model.sql using example model above.
2. Configure unit like the example above.
3. Execute the test with dbt test --select simple_test_model

Relevant log output

No response

Environment

- OS: MacOS 14.3
- Python: 3.10.10
- dbt: 1.8.1
- dbt-snowflake: 1.8.2

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[pinkham-eh]

We are also experiencing this issue on

MacOS 14.5
Python 3.9.16
dbt 1.8.1
dbt-snowflake 1.8.2
dbt-adapters 1.2.1

[rd-andreas-lay]

Same problem here with dbt & Snowflake.

dbt --version

Core:
  - installed: 1.8.3
  - latest:    1.8.3 - Up to date!

Plugins:
  - snowflake: 1.8.3 - Up to date!

[kangshung]

Bump. Problem still exists, and also on BigQuery.

Running with dbt=1.8.7
Registered adapter: bigquery=1.8.3