When using `--empty`, `adapter.get_columns_in_relation()` returns an error

[zeev-finaloop]

Describe the bug

The adapter.get_columns_in_relation and dbt_utils.get_filtered_columns_in_relation on Snowflake are basically running the query describe table YOUR_TABLE. When running dbt build/run with the --empty flag, the generated query is describe table (select * from YOUR_TABLE where false limit 0). The latter is a broken syntax query and throws an error.

Steps to reproduce

Just build any macro with: {%- set relation_column_names = adapter.get_columns_in_relation(your_model) -%} Run dbt build --empty -d

Expected results

List of columns

Actual results

You will get something like: 001003 (42000): SQL compilation error: syntax error line 1 at position 15 unexpected '('.

Screenshots and log output

System information

The contents of your packages.yml file:

Which database are you using dbt with?

• [ ] postgres
• [ ] redshift
• [ ] bigquery
• [X] snowflake
• [ ] other (specify: ____________)

The output of dbt --version:

Core:
  - installed: 1.8.1
  - latest:    1.8.1 - Up to date!

Plugins:
  - snowflake: 1.8.2 - Up to date!

Additional context

Are you interested in contributing the fix?

[dataders]

thanks for flagging @zeev-finaloop! we're actually aware of the issue with adapter.get_columns_in_relation() and --empty -- we're tracking it in https://github.com/dbt-labs/dbt-snowflake/issues/1033 (somewhat related: https://github.com/dbt-labs/dbt-adapters/issues/213).

[dataders]

I'm tempted to close this as the problem and solution exists outside of the dbt-utils package, but I'll leave it open for now so that users encountering the same issue may discover it here

[kokorin]

I found this DBT Core issue: CT-1919 Create get_column_schema_from_query macro and it looks get_column_schema_from_query macro can help with --empty.

[kokorin]

Here is a workaround which makes union_releations to work with --empty:

{%- macro union_relations(relations, column_override=none, include=[], exclude=[], source_column_name='_dbt_source_relation', where=none) -%}
    {% if invocation_args_dict.get('empty', False) %}
        {% set actual_relations = [] %}
        {% for relation in relations %}
            {% set database, schema, identifier = relation.render().split('.') %}
            {%- set actual_relation = adapter.get_relation(database, schema, identifier) -%}
            {% do actual_relations.append(actual_relation) %}      
        {% endfor %}
        {% set relations = actual_relations %}
        {% set where = 'false limit 0'%}
    {% endif %}
    {{ return(adapter.dispatch('union_relations', 'dbt_utils')(relations, column_override, include, exclude, source_column_name, where)) }}
{% endmacro %}

The keys here are relation.render() which converts relation to full table name and set where = 'false limit 0' which applies exact same filter to relations.

[github-actions[bot]]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions[bot]]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.