infer_schema fails for Snowflake when columns have . character

[shaug]

Describe the bug

Using infer_schema against parquet files where column names can have the . character (e.g., data frames created from deeply nested objects with pandas json_normalize) will fail during actual table creation because the columns can contain the . character but aren't being quoted (required for snowflake identifiers with extended characters).

Steps to reproduce

1. Create one or more parquet files where at least one column has the . character (or any other character snowflake requires being quoted).
2. Define a model source file whose table schema uses infer_schema: true to infer the table schema for those parquet file(s).
3. Run dbt run-operation stage_external_sources against the configured source files to generate the table scheme in snowflake

Expected results

The snowflake schema is generated successfully

Actual results

The snowflake schema fails to generate successfully, as snowflake complains about the . characters in the unquoted column name(s)

Screenshots and log output

00:16:20  Snowflake adapter: Snowflake query id: 01b93bb0-020b-c793-0009-ecff002e102a
00:16:20  Snowflake adapter: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 8 at position 24 unexpected '.'.
00:16:20  Snowflake adapter: Error running SQL: macro stage_external_sources
00:16:20  Snowflake adapter: Rolling back transaction.
00:16:20  Encountered an error while running operation: Database Error
  001003 (42000): SQL compilation error:
  syntax error line 8 at position 24 unexpected '.'.
00:16:20  Traceback (most recent call last):
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/snowflake/connections.py", line 317, in exception_handler
    yield
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/sql/connections.py", line 147, in add_query
    _execute_query_with_retry(
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/sql/connections.py", line 97, in _execute_query_with_retry
    cursor.execute(sql, bindings)
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/snowflake/connector/cursor.py", line 1097, in execute
    Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/snowflake/connector/errors.py", line 284, in errorhandler_wrapper
    handed_over = Error.hand_to_other_handler(
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/snowflake/connector/errors.py", line 339, in hand_to_other_handler
    cursor.errorhandler(connection, cursor, error_class, error_value)
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/snowflake/connector/errors.py", line 215, in default_errorhandler
    raise error_class(
snowflake.connector.errors.ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 8 at position 24 unexpected '.'.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/task/run_operation.py", line 67, in run
    self._run_unsafe(package_name, macro_name)
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/task/run_operation.py", line 48, in _run_unsafe
    res = adapter.execute_macro(
          ^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/base/impl.py", line 1193, in execute_macro
    result = macro_function(**kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt_common/clients/jinja.py", line 355, in __call__
    return self.call_macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt_common/clients/jinja.py", line 323, in call_macro
    return macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 768, in __call__
    return self._invoke(arguments, autoescape)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 782, in _invoke
    rv = self._func(*arguments)
         ^^^^^^^^^^^^^^^^^^^^^^
  File "<template>", line 220, in macro
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/jinja2/sandbox.py", line 394, in call
    return __context.call(__obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 303, in call
    return __obj(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/clients/jinja.py", line 82, in __call__
    return self.call_macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt_common/clients/jinja.py", line 323, in call_macro
    return macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 768, in __call__
    return self._invoke(arguments, autoescape)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 782, in _invoke
    rv = self._func(*arguments)
         ^^^^^^^^^^^^^^^^^^^^^^
  File "<template>", line 52, in macro
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/jinja2/sandbox.py", line 394, in call
    return __context.call(__obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/jinja2/runtime.py", line 303, in call
    return __obj(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/base/impl.py", line 399, in execute
    return self.connections.execute(sql=sql, auto_begin=auto_begin, fetch=fetch, limit=limit)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/snowflake/connections.py", line 514, in execute
    _, cursor = self.add_query(sql, auto_begin)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/snowflake/connections.py", line 549, in add_query
    connection, cursor = self._add_standard_queries(
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/snowflake/connections.py", line 605, in _add_standard_queries
    connection, cursor = self.add_standard_query(query, **kwargs)
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/snowflake/connections.py", line 525, in add_standard_query
    return super().add_query(self._add_query_comment(sql), **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/sql/connections.py", line 130, in add_query
    with self.exception_handler(sql):
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/homebrew/Cellar/[email protected]/3.12.6/Frameworks/Python.framework/Versions/3.12/lib/python3.12/contextlib.py", line 158, in __exit__
    self.gen.throw(value)
  File "/code/dbt-project/.venv/lib/python3.12/site-packages/dbt/adapters/snowflake/connections.py", line 343, in exception_handler
    raise DbtDatabaseError(msg)
dbt_common.exceptions.base.DbtDatabaseError: Database Error
  001003 (42000): SQL compilation error:
  syntax error line 8 at position 24 unexpected '.'.

System information

The contents of your packages.yml file:

packages:
  - package: dbt-labs/dbt_external_tables
    version: 0.11.1

Which database are you using dbt with?

• redshift
• snowflake
• other (specify: ____________)

The output of dbt --version:

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

Plugins:
  - snowflake: 1.9.0 - Up to date!

The operating system you're using:

macOS Sequoia 15.1.1

The output of python --version:

Python 3.12.6

Additional context