Quoting for database catalogs, schemas, tables, and columns

[dbeatty10]

Contributions

• I have read the contribution docs, and understand what's expected of me.

TLDR

• Models: It is possible to configure quoting for database, schema, and identifier names on a dbt project-wide basis via quoting, but not a per-model basis. However, column names can be configured on a per-model basis via quote, but not project-wide.
• Sources: database, schema, and identifier, and column names can be configured on a per-source and per-table basis (via quoting and quote), but not project-wide.

There are many related pieces in the product documentation, but they aren't linked together (which is the aim of this documentation proposal).

The feature proposal in dbt-labs/dbt-core#2986 aims to unify these 4 levels of quoting into a quote config that can be applied hierarchically from project-wide down to the individual resource-level.

Link to the page on docs.getdbt.com requiring updates

There are a couple things that are mostly frequently quoted within SQL:

1. database identifier names
2. column names and values

For example, all of the following can be quoted within SQL:

• database / catalog names (project in BigQuery)
• schema names (dataset in BigQuery)
• identifier names (table / view / etc. names)
• column names
• column values
• other database object names (users, roles, indexes, etc.)

We have all of the following documentation, but it rarely refers to each other:

For database, schema, and identifier names

• quoting - Configuring quoting in projects (for database, schema, and identifier)
• quoting - Configuring quoting in sources (for database, schema, and identifier)
• quoted method in the Relation class - Applying quoting to relations (for database, schema, and identifier) (source)
• as_case_sensitive in the SnowflakeRelation class (see also: dbt-snowflake dbt-labs/dbt-adapters#714)

For column names

• quote - Configuring quoting in models for column names
  • also sources, seeds, snapshots, and analyses if you click the tabs
• quote_columns - Configuring quoting in seed files for column names
  • quote_seed_column - Quote or do not quote the column name of a seed according to the quote_columns configuration
• quoted method in the Column class via Column API - Applying quoting to relation columns for column names
• quote_identifiers in macros within dbt_utils (like star and pivot)

For column values

• quote within accepted_values - Configuring quoting in data tests for accepted column values
• dbt.string_literal - Applying quoting to column values in Jinja
• dbt.escape_single_quotes - Escaping single quotes within column values in Jinja

For multiple purposes

• adapter.quote - Applying quoting via the adapter-specific macro to a column name or individual components of a (database, schema, identifier) combo (or even other database objects like users or roles) (source).
  • quote_as_configured - Quote or do not quote the given database, schema, or identifier according to the quoting project configuration.

For users / groups / roles in grants

As hinted at in dbt-labs/dbt-postgres#55, we don't have any mechanism to specify that a grant should be quoted. i.e., we don't have a quote_grants config.

What part(s) of the page would you like to see updated?

It would be an improvement to merely cross-link these different pages so that the first result in a Google search is likely to eventually lead where you want to get.

Additional information

Instigating scenario: dbt-labs/dbt-core#7832

dbt-labs/dbt-core#2986 aims to unify all of these under a single quote config/property, but the earliest that might happen is v1.9+, and we'd want to clarify things for users earlier than that as well.