How to change the target schema of a model in dbt (generate_schema_name)

You set +schema: finance on a folder and dbt creates a schema called dbt_nicola_finance, or analytics_finance in production. This is not a bug: by default dbt joins the target schema and the custom schema, so that developers never write into production schemas. The macro generate_schema_name decides the name, and you can override it.

Copy and paste

Set a custom schema, for a folder or a single model:

# dbt_project.yml
models:
  shop:
    marts:
      finance:
        +schema: finance

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

Use the custom schema as it is in every environment:

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- if custom_schema_name is none -%}
        {{ target.schema }}
    {%- else -%}
        {{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

Exact names in production, prefixed names in development:

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- if target.name == 'prod' and custom_schema_name is not none -%}
        {{ custom_schema_name | trim }}
    {%- elif custom_schema_name is not none -%}
        {{ target.schema }}_{{ custom_schema_name | trim }}
    {%- else -%}
        {{ target.schema }}
    {%- endif -%}
{%- endmacro %}

A file with this macro in your macros folder replaces dbt’s own version.

The resulting schemas

For a model with +schema: finance, a development target with schema dbt_nicola and a production target called prod with schema analytics:

The second version is the simplest, but every developer then writes into the same finance schema, production included. The third keeps developers apart. dbt also ships generate_schema_name_for_env, which puts everything in the developer’s schema outside production; to use it, return {{ generate_schema_name_for_env(custom_schema_name, node) }} from your macro.

Things to know

• The target name matters. The examples check target.name == 'prod', the name of the output in profiles.yml. Use the name your production target really has.
• Snapshots too. Since dbt 1.9, the schema of a snapshot defined in YAML also goes through generate_schema_name.
• Databases and table names. generate_database_name and +database work the same way for the database; alias changes the table name.
• dbt creates missing schemas. The role needs the right to create them; see how to fix “Insufficient privileges… CREATE SCHEMA”.

Tested with dbt Core 1.12.