How to query lowercase quoted columns from dbt in Snowflake

A staging model fails on a column that is plainly there:

SQL compilation error: error line 2 at position 4
invalid identifier 'ORDER_ID'

Snowflake stores unquoted names in uppercase, so order_id in your query means ORDER_ID. The table was created by a tool that quoted its column names, "order_id", and a quoted name keeps its exact case. The two are different columns as far as Snowflake is concerned.

Copy and paste

Quote the columns once, in the staging model, and give them unquoted names:

-- models/staging/stg_orders.sql
select
    "order_id"    as order_id,
    "customer_id" as customer_id,
    "orderDate"   as order_date,
    "amount"      as amount
from {{ source('raw', 'orders') }}

From here on, every model uses order_id without quotes.

Tests on the source columns need quote: true:

sources:
  - name: raw
    schema: shop
    tables:
      - name: orders
        columns:
          - name: order_id
            quote: true
            data_tests:
              - not_null

dbt then writes the test as select "order_id" from ... where "order_id" is null. Without quote: true the test fails with the same invalid identifier error.

A table whose name is lowercase and quoted:

sources:
  - name: raw
    schema: shop
    quoting:
      identifier: true
    tables:
      - name: orders

How Snowflake resolves names

Lowercase quoted columns usually come from loaders and scripts that quote every name, for example tables written from pandas.

Things to know

• Fix it in staging, not everywhere. Renaming in the staging model keeps the quotes in one file. Downstream models, tests and BI tools see ordinary uppercase columns.
• Seeds. A seed created with quote_columns: true has quoted column names too. Set +quote_columns: false for seeds unless you need the exact case.
• QUOTED_IDENTIFIERS_IGNORE_CASE. This Snowflake parameter makes quoted names case-insensitive. Snowflake warns that changing it can make existing mixed-case objects hard to find, and recommends deciding at account level, not in a dbt session.
• Reserved words. The same quoting, and quote: true in tests, applies to columns named like keywords, such as "GROUP" or "ORDER".

The dbt quoting was tested with dbt Core 1.12; Snowflake’s behaviour comes from its documentation.