Skip to main content

Configuring quoting in projects

dbt_project.yml
quoting:
  database: true | false
  schema: true | false
  identifier: true | false
  snowflake_ignore_case: true | false  # v2-only config. Aligns with Snowflake's session parameter QUOTED_IDENTIFIERS_IGNORE_CASE behavior. 
                                       # Ignored by dbt v1 and other adapters.

Definition​

You can optionally enable quoting in a dbt project to control whether dbt wraps database, schema, or identifier names in quotes when generating SQL. dbt uses this configuration when:

  • Creating relations (For example, tables or views)
  • Resolving a ref() function to a direct relation reference
BigQuery terminology

Note that for BigQuery quoting configuration, database and schema should be used here, though these configs will apply to project and dataset names respectively

Default​

The default values vary by database.

For most adapters, quoting is set to true by default.

Why? It's equally easy to select from relations with quoted or unquoted identifiers. Quoting allows you to use reserved words and special characters in those identifiers, though we recommend avoiding reserved words and special characters in identifiers whenever possible.

dbt_project.yml
quoting:
  database: true
  schema: true
  identifier: true

Examples​

Set quoting to false for a project:

dbt_project.yml
quoting:
  database: false
  schema: false
  identifier: false      
  snowflake_ignore_case: false  # v2-only config. Aligns with Snowflake's session parameter QUOTED_IDENTIFIERS_IGNORE_CASE behavior. 
                                # Ignored by dbt v1 and other adapters.

dbt will then create relations without quotes:

create table analytics.dbt_alice.dim_customers

Recommendations​

Snowflake​

If you're using Snowflake, we recommend:

  • Setting all quoting configs to False in your dbt_project.yml to avoid quoting model and column names unnecessarily and to help prevent case sensitivity issues.

    • Setting all quoting configs to False also means you cannot use reserved words as identifiers, such as model or table names. We recommend you avoid using these reserved words anyway.

  • If you're using dbt v2 and your Snowflake environment sets the session parameter QUOTED_IDENTIFIERS_IGNORE_CASE = true (for example, in an orchestrator or pre-hook), you should also enable quoting and snowflake_ignore_case in your dbt_project.yml to preserve the exact case of database, schema, and identifier:

    quoting:
      database: true
      schema: true
      identifier: true
      snowflake_ignore_case: true  # v2-only config. Aligns with Snowflake's session parameter QUOTED_IDENTIFIERS_IGNORE_CASE behavior. 
                                   # Ignored by dbt v1 and other adapters.

    Setting snowflake_ignore_case: true ensures that dbt compiles column and identifier names match Snowflake’s behavior at runtime, preserving parity between compile-time and runtime logic. Without this, you may encounter "column not found" errors.

Quoting a source

If a Snowflake source table uses a quoted database, schema, or table identifier, you can configure this in the source.yml file. Refer to configuring quoting for more information.

Explanation​

dbt skips quoting on Snowflake so lowercase model names work seamlessly in downstream queries and BI tools without worrying about case or quotes.

Unlike most databases (which lowercase unquoted identifiers), Snowflake uppercases them. When you quote identifiers, Snowflake will preserve their case and make them case-sensitive. This means when you create a table with quoted, lowercase identifiers, the table should always be referenced with quotes and use the exact same case, which can easily break downstream queries in BI tools or ad-hoc SQL.

Because dbt conventions use lowercase model and file names, quoting them in Snowflake risks breaking downstream queries in BI tools or ad-hoc SQL. If dbt instead used uppercase names by convention, the safe defaults for other databases would be at risk of breaking downstream queries.

snowflake_casing.sql
/*
  Run these queries to understand how Snowflake handles casing and quoting.
*/

-- This is the output of an example `orders.sql` model with quoting enabled
create table "analytics"."orders" as (

  select 1 as id

);

/*
    These queries WILL NOT work! Since the table above was created with quotes,
    Snowflake created the orders table with a lowercase schema and identifier.

    Since unquoted identifiers are automatically uppercased, both of the
    following queries are equivalent, and neither will work correctly.
*/

select * from analytics.orders;
select * from ANALYTICS.ORDERS;

/*
    To query this table, you'll need to quote the schema and table. This
    query should indeed complete without error.
*/

select * from "analytics"."orders";


/*
    To avoid this quoting madness, you can disable quoting for schemas
    and identifiers in your dbt_project.yml file. This means that you
    won't be able to use reserved words as model names, but you should avoid that anyway! Assuming schema and identifier quoting is
    disabled, the following query would indeed work:
*/

select * from analytics.orders;

Redshift​

Leave the default quoting values. You can do this in dbt_project.yml by either omitting the quoting config or setting database, schema, and identifier to true. The quoting config only controls whether dbt wraps names in double quotes, so it can't work around Redshift lowercasing mixed-case names. If you need mixed-case names, turn on the Redshift enable_case_sensitive_identifier setting and keep quoting on. If you set quoting to false, dbt sends unquoted names, and Redshift lowercases them even when enable_case_sensitive_identifier is true.

By default, Redshift lowercases database, schema, table, and column names, even when they're quoted. The enable_case_sensitive_identifier setting controls this:

  • false (default): Redshift lowercases all names, whether or not they're quoted.
  • true: Redshift keeps the case of quoted names. Unquoted names are still lowercased.

You can set this parameter to true for a cluster or workgroup. A SET command applies only to the current session. Refer to Turn on case sensitivity and the parameter reference in the Amazon Redshift documentation.

The following example shows the difference between setting the parameter to false or true when run in a Redshift session:

redshift_casing.sql
-- Default: quoted names are still lowercased
SET enable_case_sensitive_identifier TO false;

CREATE TABLE "MixedCasedTable" ("MixedCasedColumn" int);
INSERT INTO "MixedCasedTable" VALUES (1);

-- These work. Redshift stored the names in lowercase.
SELECT mixedcasedcolumn FROM mixedcasedtable;
SELECT "MixedCasedColumn" FROM "MixedCasedTable";

DROP TABLE mixedcasedtable;

-- With the setting on, quoted names keep their case.
SET enable_case_sensitive_identifier TO true;

CREATE TABLE "MixedCasedTable" ("MixedCasedColumn" int);
INSERT INTO "MixedCasedTable" ("MixedCasedColumn") VALUES (1);

-- This works. You must quote the names and match the case.
SELECT "MixedCasedColumn" FROM "MixedCasedTable";

-- This fails: relation "mixedcasedtable" does not exist
SELECT mixedcasedcolumn FROM mixedcasedtable;

DROP TABLE "MixedCasedTable";

Other warehouses​

Leave the default values for your warehouse.

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0
Loading