[Feature] Introspective queries that refer to mocked given inputs

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

The macro (create_canonic_table in the exmaple below) is failing to correctly read the schema and name attributes from the sources.yml file when running the unit tests. This results in a null value for cookbook.schema and cookbook.name, causing a compilation error during the dbt test execution for test_pos_canonic_dlt_klarna. The sources.yml file is properly configured, but the issue persists.

Expected Behavior

we expect the unitests to be able read the sources.yml jinja values.

Steps To Reproduce

create a model:

{{- config(materialized='incremental',alias = 'klarna')-}}
{{- create_canonic_table(ref('pos_raw_dlt_klarna'),source( 'POS_CANONIC_DTD_DLT_COOKBOOK', 'DLT_KLARNA')) }}

create a macro:

{%- macro create_canonic_table(source_model, cookbook, add_metadata_cols = True) %}
{% set cookbook_db =  cookbook.database %}
{% set cookbook_schema =  cookbook.schema %}
{% set cookbook_name =  cookbook.name %}

{%- if execute %}
    {%- set _cookbook = run_query('select ORIGINAL_NAME,CANONICAL_NAME,CAST_METHOD from ' + cookbook_db + '.' + cookbook_schema + '.' + cookbook_name) %}
    ...

The sources.yml file, located in the same directory as the unittest and model files, is as follows:

version: 2
sources:

- name: POS_CANONIC_DTD_DLT_COOKBOOK
database: COOK_BOOKS
schema: POS
tables:
  - name: DLT_KLARNA

create the test:

unit_tests:

- name: test_pos_canonic_dlt_klarna
description: "test example"
model: pos_canonic_dlt_klarna

given:
  - input: ref('pos_raw_dlt_klarna')
    rows:
      - {'adjustment_id': '1'}
  - input: source( 'POS_CANONIC_DTD_DLT_COOKBOOK', 'DLT_KLARNA')
    rows:
      - { 'ORIGINAL_NAME': 'adjustment_id', 'CANONICAL_NAME': 'ADJUSTMENT_ID', 'CAST_METHOD': None }

expect:
  rows:
    - {'adjustment_id': '1'}

execute the tests

Relevant log output

the cookbook.schema and cookbook.name are returning null, which leads to an error when executing 'dbt test':

Compilation Error in unit_test test_pos_canonic_dlt_klarna (models/pos/canonic_dlt/pos_canonic_dlt_klarna.yml) can only concatenate str (not "NoneType") to str 14:44:14 1 of 1 ERROR

since when tryinh to execute the run_query it contact the values with null so you get query like that:

select ORIGINAL_NAME,CANONICAL_NAME,CAST_METHOD from ' + cookbook_db + '.' + null + '.' + null

Environment

- OS:
- Python:
- dbt: 1.8.6

Which database adapter are you using with dbt?

No response

Additional Context

No response

Thanks for reporting this @megetron3 !

This isn't actually a bug, but rather a known limitation. Namely, you can't run introspective queries on the given inputs to unit tests. So I'm going to convert this to a feature request.

See below for a full example of using an introspective query within dbt unit tests. Note: this doesn't work! But I believe it would work if https://github.com/dbt-labs/dbt-core/issues/8499 were implemented.

Reprex

Create these files:

seeds/my_source.csv

id
123

models/my_model.sql

{%- set cookbook = source("my_source", "my_source") -%}

{%- if execute %}
    {%- set results = run_query("select id from " ~ cookbook) %}
    {% set id = results.columns[0].values()[0] %}
{%- endif %}

select {{ id }} as id

models/_properties.yml

sources:
  - name: my_source
    database: "{{ target.database }}"
    schema: "{{ target.schema }}"
    tables:
      - name: my_source

unit_tests:
  - name: test_my_model
    model: my_model
    given:
      - input: source("my_source", "my_source")
        rows:
          - {id: 456}
    expect:
        rows:
          - {id: 456}

Run these commands:

dbt seed
dbt build -s my_model

This is the current output:

18:36:25    Database Error in unit_test test_my_model (models/_properties.yml)
  relation "__dbt__cte__my_source" does not exist
  LINE 3:     select id from __dbt__cte__my_source

dbt-labs / dbt-core