Open zeev-finaloop opened 2 months ago
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).
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
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
.
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.
Describe the bug
The
adapter.get_columns_in_relation
anddbt_utils.get_filtered_columns_in_relation
on Snowflake are basically running the querydescribe table YOUR_TABLE
. When running dbt build/run with the --empty flag, the generated query isdescribe 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) -%}
Rundbt 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?
The output of
dbt --version
:Additional context
Are you interested in contributing the fix?