quote
- Models
- Sources
- Seeds
- Snapshots
- Analyses
models/schema.yml
version: 2models:- name: model_namecolumns:- name: column_namequote: 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: 2sources:- name: stripetables:- name: paymentcolumns:- name: orderIDquote: truetests:- not_null
Without quote: true
, the following error will occur:
$ dbt test -m source:stripe.*Running with dbt=0.16.1Found 7 models, 22 tests, 0 snapshots, 0 analyses, 130 macros, 0 operations, 0 seed files, 4 sources13: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 6invalid 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.paymentwhere orderID is null
Instead of:
select count(*)from raw.stripe.paymentwhere "orderID" is null