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)

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