[Bug] SQL compilation error when running --empty flag on on model that utilizes dbt_utils.union_relations() macro

[datadisciple]

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

When running the simple model below with the --empty flag I get the following error:

-- models/unioned_model_a.sql
with unioned as (
    {{ dbt_utils.union_relations(
        relations=[ref('source_y__model_a'), ref('source_z__model_a')]
    ) }}
)

select * from unioned

Database Error in model unioned_model_a (models/unioned_model_a.sql)
  001003 (42000): SQL compilation error:
  syntax error line 1 at position 15 unexpected '('.

Expected Behavior

The --empty model should be built successfully like what happens with an explicit union that does not leverage dbt_utils.union_relations()

-- models/unioned_model_a.sql
with unioned as (
    select *
    from {{ ref('source_y__model_a') }}
    union all
    select *
    from {{ ref('source_z__model_b') }}
)

select * from unioned

Steps To Reproduce

1. Setup a dbt project with Snowflake
2. Create the model described above that uses dbt_utils.union_relations()
3. Run dbt run --select unioned_model_a --empty

Relevant log output

23:22:18  Completed with 1 error and 0 warnings:
23:22:18  
23:22:18    Database Error in model silver_truck_visits (models/unioned_model_a.sql)
  001003 (42000): SQL compilation error:
  syntax error line 1 at position 15 unexpected '('.
23:22:18  
23:22:18  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Environment

- OS: macOS 14.7
- Python: 3.10.11
- dbt: dbt-core=1.8.6, dbt-snowflake=1.8.3

Which database adapter are you using with dbt?

snowflake

Additional Context

A very similar bug was brought up in #10673 and solved by tacking .render() onto the end of the relation reference in the pre-hook causing trouble.

If I try and translate that solution to the dbt_utils.union_relations() macro here I get a failure because the union_relations macro is expecting a relation:

-- models/unioned_model_a.sql
with unioned as (
    {{ dbt_utils.union_relations(
        relations=[ref('source_y__model_a').render(), ref('source_z__model_a').render()]
    ) }}
)

Compilation Error in model unioned_model_a (models/unioned_model_a.sql)
  Macro union_relations expected a Relation but received the value: my_db.my_schema.source_y__model_a
  
  > in macro _is_relation (macros/jinja_helpers/_is_relation.sql)
  > called by macro default__union_relations (macros/sql/union.sql)
  > called by macro union_relations (macros/sql/union.sql)
  > called by model unioned_model_a (models/unioned_model_a.sql)

Even if the above solution had worked, I would still feel like needing to tack on the .render() after every relation would be more of a workaround than a solution for this example.

[dbeatty10]

Thanks for reporting this @datadisciple !

I was able to reproduce what you described using both dbt-snowflake and dbt-databricks. But it doesn't affect dbt-postgres, dbt-redshift, or dbt-bigquery. See "reprex" below for details.

Digging deeper

Looking at the logs in logs/dbt.log, here's the SQL that triggers the error on Snowflake:

describe table (select * from analytics_dev.dbt_dbeatty.my_model where false limit 0)

Here's the SQL that triggers the error on Databricks:

    describe table (select * from `hive_metastore`.`dbt_dbeatty`.`my_model` where false limit 0) _dbt_limit_subq_my_model

Looking at the source code for dbt_utils.union_relations more closely, it looks like this is the line that is ultimately triggering the error in dbt-snowflake and dbt-databricks when it reaches here and here (+ here), respectively.

It looks to me like this is solved in dbt-snowflake in dbt-labs/dbt-snowflake#1100 (which hasn't been released yet), but would still need a similar code update in dbt-databricks here:

    describe table {{ relation.render()|lower }}

Workarounds in the meantime

The following overrides worked for me until these are released in dbt-snowflake and dbt-databricks:

macros/overrides/snowflake/get_columns_in_relation.sql

{% macro snowflake__get_columns_in_relation(relation) -%}
  {%- set sql -%}
    describe table {{ relation.render() }}
  {%- endset -%}
  {%- set result = run_query(sql) -%}

  {% set maximum = 10000 %}
  {% if (result | length) >= maximum %}
    {% set msg %}
      Too many columns in relation {{ relation.render() }}! dbt can only get
      information about relations with fewer than {{ maximum }} columns.
    {% endset %}
    {% do exceptions.raise_compiler_error(msg) %}
  {% endif %}

  {% set columns = [] %}
  {% for row in result %}
    {% do columns.append(api.Column.from_description(row['name'], row['type'])) %}
  {% endfor %}
  {% do return(columns) %}
{% endmacro %}

macros/overrides/databricks/get_columns_in_relation.sql

{% macro get_columns_comments(relation) -%}
  {% call statement('get_columns_comments', fetch_result=True) -%}
    describe table {{ relation.render()|lower }}
  {% endcall %}

  {% do return(load_result('get_columns_comments').table) %}
{% endmacro %}

Reprex

Create these files:

models/my_model.sql

select 1 as id

models/my_model_unioned.sql

with unioned as (
    {{ dbt_utils.union_relations(
        relations=[ref("my_model")]
    ) }}
)

select * from unioned

Run these commands:

dbt run -s +my_model_unioned --empty

Summary

Closing in favor of dbt-labs/dbt-snowflake#1033 and databricks/dbt-databricks#807.

[datadisciple]

super insightful, thanks @dbeatty10!