Skip to main content

quote

models/schema.yml
version: 2

models:
  - name: model_name
    columns:
      - name: column_name
        quote: true | false

Definition​

The quote field can be used to enable or disable quoting for column names.

▶Changelog

Default​

The default quoting value is false

Explanation​

This is particularly relevant to those using Snowflake, where quoting can be particularly fickle.

This property is useful when:

  • A source table has a column that needs to be quoted to be selected, for example, to preserve column casing
  • A seed was created with quote_columns: true (docs) on Snowflake
  • A model uses quotes in the SQL, potentially to work around the use of reserved words
select user_group as "group"

Without setting quote: true:

  • Schema tests applied to this column may fail due to invalid SQL
  • Documentation may not render correctly, e.g. group and "group" may not be matched as the same column name.

Example​

Add tests to a quoted column in a source table​

This is especially relevant if using Snowflake:

version: 2

sources:
  - name: stripe
    tables:
      - name: payment
        columns:
          - name: orderID
            quote: true
            tests:
              - not_null

Without quote: true, the following error will occur:

$ dbt test -s source:stripe.*
Running with dbt=0.16.1
Found 7 models, 22 tests, 0 snapshots, 0 analyses, 130 macros, 0 operations, 0 seed files, 4 sources

13:33:37 | Concurrency: 4 threads (target='learn')
13:33:37 |
13:33:37 | 1 of 1 START test source_not_null_stripe_payment_order_id............ [RUN]
13:33:39 | 1 of 1 ERROR source_not_null_stripe_payment_order_id................. [ERROR in 1.89s]
13:33:39 |
13:33:39 | Finished running 1 tests in 6.43s.

Completed with 1 error and 0 warnings:

Database Error in test source_not_null_stripe_payment_order_id (models/staging/stripe/src_stripe.yml)
  000904 (42000): SQL compilation error: error line 3 at position 6
  invalid identifier 'ORDERID'
  compiled SQL at target/compiled/jaffle_shop/schema_test/source_not_null_stripe_payment_orderID.sql

This is because dbt is trying to run:

select count(*)
from raw.stripe.payment
where orderID is null

Instead of:

select count(*)
from raw.stripe.payment
where "orderID" is null