[ADAP-1054] [CT-3434] [Bug] DBT models with hyphens in filename fail in Snowflake

[jackwellsxyz]

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

dbt transpiles a model with a filename containing a hyphen directly into SQL without quoting illegal errors in Snowflake. Here's an example transpiled output that is sent to Snowflake:

        create or replace transient table MY_DATABASE.MY_SCHEMA.table-with-hyphens
         as
        (
... compiled SQL ...
       );

Expected Behavior

dbt should probably do two things:

• Warn the user that hyphens are not great practice in SQL
• Quote the table name in the CTAS statement such as create or replace transient table MY_DATABASE.MY_SCHEMA."table-with-hyphens"

Steps To Reproduce

1. Create model with a hyphen in the filename, such as table-with-hyphens.sql
2. dbt build --select +table-with-hyphens

Relevant log output

18:17:22    Database Error in model customer-emails (models/tables/customer-emails/customer-emails.sql)
  001003 (42000): SQL compilation error:
  syntax error line 1 at position 66 unexpected '-'.
  compiled Code at target/run/product/models/tables/table-with-hyphens.sql

^^^ Note that the above position (e.g. 66) is dependent on where the hyphens appear in the filename in the generated CTAS statement.

Environment

- OS: MacOS Sonoma
- Python: 3.11
- dbt: 1.7.1

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[dbeatty10]

Thanks for reaching out about this @jackwellsxyz !

What you observed actually expected behavior, and it is described in the documentation here. You can click between the "Default" and "Snowflake" tabs to see how the quoting behavior for dbt-snowflake differs from other databases.

Default

Snowflake

Explanation

The key insight why the default quoting differs for Snowflake is described here:

Whereas most databases will lowercase unquoted identifiers, Snowflake will uppercase unquoted identifiers. If a model name is lowercased and quoted, then it cannot be referred to without quotes!

Workaround

Although it's not our recommendation, users can do the behavior you are describing by adding this to their dbt_project.yml:

quoting:
  identifier: true

Summary

Since dbt-snowflake is behaving the way we expect, and we don't plan on adding additional warnings at this time, I'm going to close this as "not planned".