Quote:true Column Configuration Bug

[T-Dunlap]

Describe the bug

We're encountering an issue with the quote:true column configuration of one of our external tables. It won't accept a column name [EXAMPLE'COLUMN'2] See the quote: true property example.

Steps to reproduce

In our yml file for our external tables, we're applying the quote:true property to our column. See example below

tables:
      - name: atxprism_allpositions
        external:
          location: "@{{ env_var('DBT_ENVIRONMENT') }}_BLAH.BLAH.{{ env_var('DBT_ENVIRONMENT') }}_BLAH"  # reference an existing external stage
          file_format: "( type = csv )"      # fully specified here, or reference an existing file format
          pattern: ".*[.]json"  # Optional object key pattern
          # Instead of an external tables, create an empty table, backfill it, and pipe new data
          snowpipe:
            auto_ingest:    true  # requires either `aws_sns_topic` or `integration`
            # aws_sns_topic:  # Amazon S3
            integration: "BLAH_{{ env_var('DBT_ENVIRONMENT') }}_BLAH"   # Google Cloud or Azure
            copy_options:   "on_error = continue, enforce_length = false" # e.g.
        columns:
         ...

        - name: "EXAMPLE`COLUMN`2"
          quote: true
          data_type: varchar(255)
          description: ""

Expected results

We expect that the package will correctly identify the [EXAMPLE'COLUMN'2] as a valid column name and create the external table accordingly.

Actual results

We received the following error

Screenshots and log output

_dbt_copied_at timestamp
16:36:27 Snowflake adapter: Snowflake query id: 01b41004-0202-4857-0001-9f36005278ee
16:36:27 Snowflake adapter: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 21 at position 27 unexpected '2'.
16:36:27 Snowflake adapter: Rolling back transaction.
16:36:27 Snowflake adapter: Rolling back transaction.
16:36:27 On macro_stage_external_sources: Close
16:36:27 Encountered an error while running operation: Database Error
001003 (42000): SQL compilation error:
syntax error line 21 at position 27 unexpected '2'.
16:36:27 Traceback (most recent call last):
File "/venv/dbt-1.7.0-latest/lib/python3.11/site-packages/dbt/adapters/snowflake/connections.py", line 291, in exception_handler
yield
File "/venv/dbt-1.7.0-latest/lib/python3.11/site-packages/dbt/adapters/sql/connections.py", line 80, in add_query
cursor.execute(sql, bindings)
File "/venv/dbt-1.7.0-latest/lib/python3.11/site-packages/snowflake/connector/cursor.py", line 1080, in execute
Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
File "/venv/dbt-1.7.0-latest/lib/python3.11/site-packages/snowflake/connector/errors.py", line 290, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/venv/dbt-1.7.0-latest/lib/python3.11/site-packages/snowflake/connector/errors.py", line 345, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/venv/dbt-1.7.0-latest/lib/python3.11/site-packages/snowflake/connector/errors.py", line 221, in default_errorhandler
raise error_class(
snowflake.connector.errors.ProgrammingError: SQL compilation error:
syntax error line 21 at position 27 unexpected '2'.

System information

The contents of your packages.yml file:

Which database are you using dbt with?

• [ ] redshift
• [x] snowflake
• [ ] other (specify: ____)

The output of dbt --version:

<output goes here>

The operating system you're using:

The output of python --version:

Additional context

[dataders]

yikes! this is special column. quick question is the column have single quotes (') or backticks (`)?

I wasn't able to reproduce, in fact it worked for me. I suspect there might be something else going on?

Here's what I did. I used the people_a.csv from this repo (link), but the file shouldn't matter since data isn't checked when the CREATE EXTERNAL TABLE statement is run, only when it's queried.

Anyway the below YAML generated the below SQL (can be found in logs/dbt.log). You can see the column name is properly enclosed within double quotes (") as per Snowflake guidance.

# sources.yml
version: 2

sources:
  - name: snowflake_external
    schema: "{{ target.schema }}"
    loader: S3

    tables:

      - name: people_csv_unpartitioned
        external:
          location: '@{{ target.schema }}.dbt_external_tables_testing/csv'
          file_format: '( type = csv skip_header = 1 )'
        columns:
          - name: "EXAMPLE`COLUMN`2"
            data_type: varchar(255)
            quote: true

create or replace external table DBT_TEST.dbt_external_tables_integration_tests_snowflake.people_csv_unpartitioned(
                "EXAMPLE`COLUMN`2" varchar(255) as ((case when is_null_value(value:c1) or lower(value:c1) = 'null' then null else value:c1 end)::varchar(255))
        )
    location = @dbt_external_tables_integration_tests_snowflake.dbt_external_tables_testing/csv