[CT-3329] [Bug] Unit testing errors with long CTE name given reasonable test name length

[MichelleArk]

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

Long unit test names (in combination with long model name + input names) can lead to a "Relation name '{}' is longer than 63 characters" if the sum of lengths is > 63 (on postgres) characters.

Expected Behavior

No error

Steps To Reproduce

Changing the test_my_model_incremental_yml fixture from in test_unit_testing.py:

test_my_model_incremental_yml = """
unit_tests:
  - name: incremental_false
...

to:

test_my_model_incremental_yml = """
unit_tests:
  - name: test_incremental_false
...

Results in:

15:58:28  Completed with 1 error and 0 warnings:
15:58:28  
15:58:28    Runtime Error in unit_test my_incremental_model__test_incremental_false (models/test_my_incremental_model.yml)
  Relation name '__dbt__cte__my_incremental_model__test_incremental_false__events' is longer than 63 characters
15:58:28  
15:58:28  Done. PASS=1 WARN=0 ERROR=1 SKIP=0 TOTAL=2

Relevant log output

No response

Environment

- OS:
- Python:
- dbt:

Which database adapter are you using with dbt?

No response

Additional Context

This is happening because the ephemeral fixture nodes are named based on the concatenation of: the model being tested, the name of the unit test, and the name of the input itself. This ensures they have globally unique names in the unit test manifest but is overkill for the purpose of naming the CTEs, which are local to a unit test case.

Options:
Shorten the name of the ModelNode for input fixtures to only include the name of the original input node -- assuming that node names don't need to be unique within a DAG. - These aren't used in a DAG, so this shouldn't be a concern.
Consider hashing to add extra layer of caution to avoid name duplication.
These are ephemeral names only they are not executable.

Testing Considerations:

• Test having 2 input models with same names and different packages.

[jtcohen6]

We have some prior art for this. Postgres in particular has the 63-character limit; other DWHs have higher limits.

dbt-core/plugins/postgres/dbt/include/postgres/macros/adapters.sql

Lines 141 to 178 in bb35b3e

	{#
	Postgres tables have a maximum length of 63 characters, anything longer is silently truncated.
	Temp and backup relations add a lot of extra characters to the end of table names to ensure uniqueness.
	To prevent this going over the character limit, the base_relation name is truncated to ensure
	that name + suffix + uniquestring is < 63 characters.
	#}
	{% macro postgres__make_relation_with_suffix(base_relation, suffix, dstring) %}
	{% if dstring %}
	{% set dt = modules.datetime.datetime.now() %}
	{% set dtstring = dt.strftime("%H%M%S%f") %}
	{% set suffix = suffix ~ dtstring %}
	{% endif %}
	{% set suffix_length = suffix|length %}
	{% set relation_max_name_length = base_relation.relation_max_name_length() %}
	{% if suffix_length > relation_max_name_length %}
	{% do exceptions.raise_compiler_error('Relation suffix is too long (' ~ suffix_length ~ ' characters). Maximum length is ' ~ relation_max_name_length ~ ' characters.') %}
	{% endif %}
	{% set identifier = base_relation.identifier[:relation_max_name_length - suffix_length] ~ suffix %}
	{{ return(base_relation.incorporate(path={"identifier": identifier })) }}
	{% endmacro %}
	{% macro postgres__make_intermediate_relation(base_relation, suffix) %}
	{{ return(postgres__make_relation_with_suffix(base_relation, suffix, dstring=False)) }}
	{% endmacro %}
	{% macro postgres__make_temp_relation(base_relation, suffix) %}
	{% set temp_relation = postgres__make_relation_with_suffix(base_relation, suffix, dstring=True) %}
	{{ return(temp_relation.incorporate(path={"schema": none,
	"database": none})) }}
	{% endmacro %}
	{% macro postgres__make_backup_relation(base_relation, backup_relation_type, suffix) %}
	{% set backup_relation = postgres__make_relation_with_suffix(base_relation, suffix, dstring=False) %}
	{{ return(backup_relation.incorporate(type=backup_relation_type)) }}
	{% endmacro %}

[graciegoheen]

I ran into the same issue when testing this out, is there any way to make this CTE name shorter without requiring folks to have a shorter name for their unit test?

[dbeatty10]

@dave-connors-3 ran into this too.