[Bug] `--empty` flag generates invalid SQL when running `adapter.get_columns_in_relation`

jlucas91 commented 4 months ago

Is this a new bug?

[X] I believe this is a new bug
[X] I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When running the --empty flag introduced in DBT 1.8 adapter.get_columns_in_relation generates and runs an invalid DESCRIBE TABLE statement, halting the run. The statement looks roughly like:

describe table (select * from TABLE_NAME_HERE where false limit 0)

Which generates the error:

  001003 (42000): SQL compilation error:
  syntax error line 1 at position 15 unexpected '('.

Expected Behavior

adapter.get_columns_in_relation should fallback to some reasonable default when running with the --empty flag, or alternatively throws a more intelligible error message.

For our usages, I'm working around it by guarding calls to adapter.get_columns_in_relation behind a check on whether we're running in empty mode. However, this is cumbersome and a bit error prone.

Steps To Reproduce

In DBT 1.8 on Snowflake
Run ./dbt run --empty on a model that uses adapter.get_columns_in_relation
The error will be thrown

Relevant log output

No response

Environment

- OS: Linux
- Python: 3.11
- dbt-adapter: dbt-snowflake

Additional Context

No response

jlucas91 commented 4 months ago

jtcohen6 commented 4 months ago

@jlucas91 Thanks for opening - I think it's actually the same as:

https://github.com/dbt-labs/dbt-snowflake/issues/1033

This isn't Snowflake-specific, it would be relevant anywhere we're:

templating out {{ relation }}, as a string
as the resolved form of a ref/source call
in a metadata query (show/describe/etc) where we don't want to access the actual underlying data

I don't think that happens everywhere:

e.g. redshift__get_columns_in_relation doesn't template out {{ relation }} as a string, it decomposes it into database/schema/identifier
most metadata in BigQuery is via Python SDK
create / drop / alter statements don't tend to be used for the direct result of a ref/source call

So while on first read I was worried this was going to be an issue everywhere — I think we just need to do the work of combing through those call sites in our adapters. A reasonable lift, but it feels doable. In the meantime we can document this as a limitation.

colin-rogers-dbt commented 4 months ago

Scope of this fix should also include adding tests to cover some of these edge cases, resolving https://github.com/dbt-labs/dbt-snowflake/issues/1033 and updating maintainer docs

FishtownBuildBot commented 3 months ago

Opened a new issue in dbt-labs/docs.getdbt.com: https://github.com/dbt-labs/docs.getdbt.com/issues/5734

dbt-labs / dbt-adapters