dbt - Documentation

Using custom schemas

Specifying custom schemas

By default, dbt models are created in the schema specified in the active profile. This schema can be overridden on a per-model basis using the schema model configuration. Overriding a model's schema will concatenate the profile schema with the model schema as shown here:

Profile Schema
Model Schema
Model Name
Result

dbt_alice

<None>

orders

dbt_alice.orders

dbt_alice

ecom

orders

dbt_alice_ecom.orders

dbt_bob

ecom

orders

dbt_bob_ecom.orders

analytics

ecom

orders

analytics_ecom.orders

analytics

<None>

orders

analytics.orders

Specifying a schema

To specify a custom schema for a model, use the schema configuration:

{{ config(schema='ecom') }}

select ...

This configuration can also be set in the dbt_project.yml file -- it works just like the materialized configuration:

# models in `models/ecommerce/ will be rendered to the "*_ecom" schema
models:
  my_project:
    ecommerce:
      schema: ecom

Altering Schema Override Behavior (Advanced)

Compilation Context

The generate_schema_name macro has a limited compilation context. See the section on Compilation Context below for more information about the compilation context available in this macro.

Under the hood, dbt calls a macro called generate_schema_name() to render the appropriate schema name for a model. The implementation of this macro looks like:

{% macro generate_schema_name(custom_schema_name=none) -%}
    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}
        {{ default_schema }}
    {%- else -%}
        {{ default_schema }}_{{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

(Source On GitHub)

This behavior can be overridden by creating a macro in your own project called generate_schema_name which contains different logic. One desirable alternative is to ignore custom schema configurations in dev, and to just use the schema override for a model (if one is provided) in prod. That macro looks like:

{% macro generate_schema_name_for_env(custom_schema_name=none) -%}
    {%- set default_schema = target.schema -%}
    {%- if target.name == 'prod' and custom_schema_name is not none -%}
        {{ custom_schema_name | trim }}
    {%- else -%}
        {{ default_schema }}
    {%- endif -%}
{%- endmacro %}

(Source On GitHub)

This macro is automatically available in all dbt projects. To replace the default schema rendering logic with this environment-aware logic, create a file in the macros directory called get_custom_schema.sql, and paste in these contents:

-- put this in macros/get_custom_schema.sql

{% macro generate_schema_name(schema_name) -%}
    {{ generate_schema_name_for_env(schema_name) }}
{%- endmacro %}
Profile Schema
Model Schema
Model Name
Environment
Result

dbt_alice

<None>

orders

dev

dbt_alice

dbt_alice

ecom

orders

dev

dbt_alice

dbt_bob

ecom

orders

dev

dbt_bob

analytics

ecom

orders

prod

ecom

analytics

<None>

orders

prod

analytics

generate_schema_name Compilation Context

The generate_schema_name macro is compiled with a limited context. This means that not all variables and methods are available to you when defining a custom generate_schema_name macro. The following context variables are available in the generate_schema_name macro:

Macros

The generate_schema_name macro can call other macros defined in your project, or macros that are imported from a package.

Limited Var Context

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 (eg. with --vars) 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.