[Feature] Enable unit testing for models that use adapter.get_relation

[mithungame]

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

Currently we have an existing macro where we alter SQL behavior based on presence or absence of a model

  {%- set relation = adapter.get_relation(this.database, this.schema, this.table) -%}
  {%- set target_table = model.get('alias', model.get('name')) -%}
  {%- set target_relation_exists, target_relation = get_or_create_relation(database=this.database,schema=this.schema,identifier=target_table,type='table') -%}
  {%- if not target_relation_exists -%}
 < do what needs to be done for first creation >
  {%- else -%}
< do what needs to be done for subsequent creation >

The code works fine without any issue

When trying to unit test this model it fails with below error
'str object' has no attribute 'database'.

unit_tests:
  - name: test_dimension
    model: target_dim
    overrides:
      macros:
        is_incremental: true
    given:
      - input: ref('stage_table')
        rows:
          ... rows here
      - input: this
        rows:
          ... rows here
    expect:
      rows:
        ... rows here

narrowed it down to usage of the below line
{%- set relation = adapter.get_relation(this.database, this.schema, this.table) -%}

Expected Behavior

The "this" in adapter is treated as a string rather than a relation .
using {{ this }} in schema yml is not possible
the "this" inside adapter relation should be a relation

Steps To Reproduce

create below model and unit test

{{
    config(
        materialized='incremental'
    )
}}
{% if execute %}
{%- set relation = adapter.get_relation(this.database, this.schema, this.table) -%}
{% endif %}


select * from {{ ref('temp_table') }}
where event_time > (select max(event_time) from {{ this }})

Relevant log output

13:33:27  Running with dbt=1.8.3
13:33:28  Registered adapter: snowflake=1.8.3
13:33:29  Found 27 models, 15 data tests, 13 sources, 601 macros, 2 unit tests
13:33:29
13:33:32  Concurrency: 5 threads (target='dev')
13:33:32
13:33:32  1 of 1 START unit_test temp::my_incremental_model_incremental_mode .......... [RUN]
13:33:33  1 of 1 ERROR temp::my_incremental_model_incremental_mode .................... [ERROR in 0.09s]
13:33:33
13:33:33  Finished running 1 unit test in 0 hours 0 minutes and 3.81 seconds (3.81s).
13:33:33
13:33:33  Completed with 1 error and 0 warnings:
13:33:33
13:33:33    Compilation Error in unit_test my_incremental_model_incremental_mode (models\schema.yml)
  'str object' has no attribute 'database'. This can happen when calling a macro that does not exist. Check for typos and/or install package dependencies with "dbt deps".
13:33:33
13:33:33  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Environment

- OS: windows
- Python: 3.11
- dbt: 1.8.3

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[devmessias]

Maybe has the same cause as #10139.

This part

select * from {{ ref('temp_table') }}
where event_time > (select max(event_time) from {{ this }})

should be working because is covered by a functional test on dbt-core

dbt-core/tests/functional/unit_testing/fixtures.py

Lines 302 to 304 in a0674db

	{% if is_incremental() %}
	where event_time > (select max(event_time) from {{ this }})
	{% endif %}

[mithungame]

Yes, the {{this}} works as mentioned
But when the same "this" is invoked in adapter get relation function it is treated not as a relation object but rather as a plain string. The interpretation of "this" is not consistent

[devmessias]

I think this is a limitation of unit tests already pointed here in some reported bugs. We should have an option to override dynamic variables in jinja template like this

overrides:
    dynamic_vars:
         target_relation_exists = true

[devmessias]

For duckdb I got a different error msg

14:08:57  Finished running 1 incremental model, 3 seeds, 1 unit test, 3 view models in 0 hours 0 minutes and 0.54 seconds (0.54s).
14:08:57  
14:08:57  Completed with 1 error, 0 partial successs, and 0 warnings:
14:08:57  
14:08:57    Field "path" of type Path in DuckDBRelation has invalid value {'database': Undefined, 'schema': Undefined, 'identifier': ''}
14:08:57  

The model works fine without unit test. I think like I said before and discussed here #10908 maybe is a good idea to be able to easy override a dynamic var

[dbeatty10]

Thanks for reporting this @mithungame !

Overall this isn't 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 for further consideration. I believe this particular request to utilize adapter.get_relation would rely upon #8499 being implemented first.

Relation.add_ephemeral_prefix

There is a component that you are touching on that is unique:

• this within the unit test context being a str rather than a Relation.

This is because adapter.Relation.add_ephemeral_prefix currently returns a str.

There's a brief discussion in #8891 about it. But it wasn't clear to me if we definitely can't have it return a Relation or if we might be able to.

Currently, the relevant code that is making it a string is here, here, and here.

So maybe we can alter the code to be something like this (using Relation.create)? After some very brief testing, it looks to me like we could alter this method to return a Relation instead.

    @contextproperty()
    def this(self) -> Optional[Relation]:
        relation: Relation = None
        if self.model.this_input_node_unique_id:
            this_node = self.manifest.expect(self.model.this_input_node_unique_id)
            self.model.set_cte(this_node.unique_id, None)  # type: ignore
            identifier = self.adapter.Relation.add_ephemeral_prefix(this_node.identifier)  # type: ignore
            relation = self.adapter.Relation.create(identifier=identifier)
        return relation

Simplified reprex

We can reproduce the same error message with a table (or view) materialization rather than needing to use an incremental. So going with that for simplicity.

Create these files:

models/target_dim.sql

{{ config(
    materialized="table",
) }}

{{ log("{{ this }}: " ~ this, True) }}

{%- set relation = adapter.get_relation(this.database, this.schema, this.table) -%}

{% if execute %}
    {{ log("relation: " ~ relation, True) }}
{% endif %}

select 1 as id

models/_unit_tests.yml

unit_tests:
  - name: test_dimension
    model: target_dim
    given:
      - input: this
        rows:
          - {id: 1}
    expect:
      rows:
        - {id: 1}

Run this command:

dbt build

Get this output:

$ dbt build                   
15:20:34  Running with dbt=1.8.7
15:20:36  Registered adapter: postgres=1.8.2
15:20:37  Found 1 model, 417 macros, 1 unit test
15:20:37  
15:20:38  Concurrency: 5 threads (target='postgres')
15:20:38  
15:20:38  1 of 2 START unit_test target_dim::test_dimension .............................. [RUN]
15:20:38  {{ this }}: __dbt__cte__target_dim
15:20:38  1 of 2 ERROR target_dim::test_dimension ........................................ [ERROR in 0.04s]
15:20:38  2 of 2 SKIP relation dbt_dbeatty.target_dim .................................... [SKIP]
15:20:38  
15:20:38  Finished running 1 unit test, 1 table model in 0 hours 0 minutes and 1.55 seconds (1.55s).
15:20:38  
15:20:38  Completed with 1 error and 0 warnings:
15:20:38  
15:20:38    Compilation Error in unit_test test_dimension (models/_unit_tests.yml)
  'str object' has no attribute 'database'. This can happen when calling a macro that does not exist. Check for typos and/or install package dependencies with "dbt deps".
15:20:38  
15:20:38  Done. PASS=0 WARN=0 ERROR=1 SKIP=1 TOTAL=2