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.