[Bug] Same temp table is used for different unit tests

[afillatre]

Is this a new bug in dbt-bigquery?

• I believe this is a new bug in dbt-bigquery
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When some unit tests in different models have the same name (like test_compute_one_line), some tests can fail depending on racing conditions (threads > 1).
The temp table name is <test_name>__dbt_tmp, so 1 test can override the content of another one.

Expected Behavior

Unit tests should run in parallel, without impacting other one, regardless their naming.
ATM I'm forced to run my tests with --threads 1 to prevent the issue

Steps To Reproduce

1. Create 2 models
2. Create a unit test within each model, both with the same name
3. Run the test with --threads > 1

Relevant log output

No response

Environment

- OS: OS X 14.4.1
- Python: 3.11
- dbt-core: 1.8.0
- dbt-bigquery: 1.8.1

Additional Context

No response

[jtcohen6]

Thanks @afillatre, I'm going to transfer this to dbt-adapters because I believe it's relevant to adapters beyond just BigQuery.

Here's where we generate the temp name:

dbt-adapters/dbt/include/global_project/macros/materializations/tests/unit.sql

Lines 9 to 10 in a2292c8

	{%- set target_relation = this.incorporate(type='table') -%}
	{%- set temp_relation = make_temp_relation(target_relation)-%}

I think the options are:

1. Create a temporary table name including both the unit test name and the model it's testing, to avoid this collision
2. Document that this is a known limitation of unit tests, and each unit test should have a globally unique name

[afillatre]

Thanks @jtcohen6. I had that first solution in mind as well. However I do not know if there's a limitation regarding the table's name length in any supported database.

[jtcohen6]

@afillatre On Postgres the max identifier length is 63 (which is very short!), so the postgres__make_relation_with_suffix macro (called by make_temp_relation) will hash names longer than 63.

If this is just a matter of passing both the model name + unit test name into make_temp_relation, a naïve version of that change might look like:

  {%- set model_test_identifier = model['unique_id'].split('.')[2:] | join('__') -%} -- returns model_name__unit_test_name
  {%- set target_relation = this.incorporate(type='table', path={"identifier": model_test_identifier}) -%}
  {%- set temp_relation = make_temp_relation(target_relation)-%}

[afillatre]

Wonderful, thanks for the tips.

I made it work like that (overriding a macro):

{% macro bigquery__make_temp_relation(base_relation, suffix) %}
    {% set model_test_identifier = model['unique_id'].split('.')[2:] | join('__') %} -- returns model_name__unit_test_name
    {% set target_relation = this.incorporate(type='table', path={"identifier": model_test_identifier}) %}
    {{ return(target_relation) }}
{% endmacro %}

Should I create a PR in the bigQuery connector ?