[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?

• [X] I believe this is a new bug in dbt-core
• [X] 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