dbt - Documentation

Using custom schemas

Getting started

What is a custom schema?

By default, all dbt models are built in the schema specified in your target - target - A set of connection details for a data warehouse (e.g. username and password), and a default schema for dbt to build relations (e.g. tables, views) in. . In dbt projects with lots of models, it may be useful to instead build some models in schemas other than your target schema – this can help logically group models together.

For example, you may wish to:

  • Group models based on the business unit using the model, creating schemas such as core, marketing, finance and support; or,
  • Hide intermediate models in a staging schema, and only present models that should be queried by an end user in an analytics schema.

You can use custom schemas in dbt to build models in a schema other than your target schema. It's important to note that by default, dbt will generate the schema name for a model by concatenating the custom schema to the target schema, as in: <target_schema>_<custom_schema>.

Target schema
Custom schema
Resulting schema

<target_schema>

None

<target_schema>

analytics

None

analytics

dbt_alice

None

dbt_alice

<target_schema>

<custom_schema>

<target_schema>_<custom_schema>

analytics

marketing

analytics_marketing

dbt_alice

marketing

dbt_alice_marketing

How do I use custom schemas?

Use the schema configuration key to specify a custom schema for a model. As with any configuration, you can either:

  • apply this configuration to a specific model by using a config block within a model, or
  • apply it to a subdirectory of models by specifying it in your dbt_project.yml file
{{ config(schema='marketing') }}

select ...
# models in `models/marketing/ will be rendered to the "*_marketing" schema
models:
  my_project:
    marketing:
      schema: marketing

Understanding custom schemas

Why does dbt concatenate the custom schema to the target schema?

When first using custom schemas, it's common to assume that a model will be built in schema that matches the schema configuration exactly, for example, a model that has the configuration schema: marketing, would be built in the marketing schema. However, dbt instead creates it in a schema like <target_schema>_marketing by default – there's good reason for this!

In a typical setup of dbt, each dbt user will use a separate target schema (see Managing Environments). If dbt created models in a schema that matches a model's custom schema exactly, every dbt user would create models in the same schema.

Further, the schema that your development models are built in would be the same schema that your production models are built in! Instead, concatenating the custom schema to the target schema helps create distinct schema names, reducing naming conflicts.

If you prefer to use different logic for generating a schema name, you can change the way dbt generates a schema name (see below).

How does dbt generate a model's schema name?

Under the hood, dbt uses a macro called generate_schema_name to determine the name of the schema that a model should be built in. The code for the macro that expresses the current logic follows:

{% macro generate_schema_name(custom_schema_name, node) -%}

    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ default_schema }}_{{ custom_schema_name | trim }}

    {%- endif -%}

{%- endmacro %}

Advanced custom schema configuration

Changing the way dbt generates a schema name

If your dbt project includes a macro that is also named generate_schema_name, dbt will always use the macro in your dbt project instead of the default macro.

Therefore, to change the way dbt generates a schema name, you should add a macro named generate_schema_name to your project, where you can then define your own logic.

Note: dbt ignores any custom generate_schema_name macros that are part of a package - package - A dbt project that is installed within another dbt project. Packages are either focused on performing common analytics on top of common datasets (especially from SASS products, such as Stripe, Snowplow, and Segment), or to group together macros that are useful (for example, macros for Redshift and Snowplow). See Package Management for more details. installed in your project.

An alternative pattern for generating schema names

A frequently used pattern for generating schema names is to change the behavior based on dbt's environment, such that:

  • In prod:

    • If a custom schema is provided, a model's schema name should match the custom schema, rather than being concatenated to the target schema.
    • If no custom schema is provided, a model's schema name should match the target schema.
  • In other environments (e.g. dev or qa):

    • Build all models in the target schema, as in, ignore custom schema configurations.

dbt ships with a global macro that contains this logic – generate_schema_name_for_env.

If you want to use this pattern, you'll need a generate_schema_name macro in your project that points to this logic. You can do this by creating a file in your macros directory (we normally call it get_custom_schema.sql), and pasting in the following:

-- put this in macros/get_custom_schema.sql

{% macro generate_schema_name(custom_schema_name, node) -%}
    {{ generate_schema_name_for_env(custom_schema_name, node) }}
{%- endmacro %}

generate_schema_name arguments

Argument
Description
Example

custom_schema_name

The configured value of schema in the specified node, or none if a value is not supplied

marketing

node

The node that is currently being processed by dbt

{
  "name": "my_model",
  "resource_type": "model",
  ...
}

Jinja context available in generate_schema_name

If you choose to write custom logic to generate a schema name, it's worth noting that not all variables and methods are available to you when defining this logic. In other words: the generate_schema_name macro is compiled with a limited Jinja context.

The following context methods are available in the generate_schema_name macro:

Jinja context
Type
Available

Variable

Variable

Variable

Limited, see below

Macro

Macro

Other macros in your project

Macro

Other macros in your packages

Macro

Which vars are available in generate_schema_name?

dbt scopes variables differently depending on where they are defined in the dbt_project.yml file. Because the generate_schema_name macro is compiled only once for all of the models in your project, vars defined in a dbt_project.yml scope will not work with the generate_schema_name macro.

Variables defined on the command line (as in, with the --vars option) are however globally scoped in nature, so they are provided in the generate_schema_name context. While this functionality is supported, other mechanisms like env_vars may be more suitable for typical use cases.

Using custom schemas


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.