Generating schema names per environment

dbt determines a model target schema using a built-in macro called generate_schema_name. By default, it concatenates the user default target schema from profiles.yml with any custom schema defined on the model, producing names like dev_alice_marketing. Teams override this macro so production environments receive clean, un-prefixed schemas like marketing, while development environments keep developer tables safely isolated.

Default schema resolution

When you set schema: marketing on a model, dbt calls generate_schema_name with that custom name. The default behavior works as follows:

• If no custom schema is configured, the model lands in the target.schema specified in your profile (for example analytics).
• If a custom schema is configured, dbt concatenates the profile schema with the custom schema, producing analytics_marketing.

This default concatenation prevents developers from accidentally overwriting production schemas during local testing, but it creates messy schema names in production where business users expect clean names like finance and marketing.

Why teams override generate_schema_name

In production environments, data teams want model schemas to match domain names exactly without ugly prefixes. At the same time, when developer Alice runs dbt run locally, her models must not write into the shared marketing schema and overwrite production data.

Overriding generate_schema_name solves both problems:

• In production (target.name == 'prod'), the macro ignores the profile schema prefix and uses the custom schema directly as marketing.
• In development (target.name != 'prod'), the macro prefixes the developer schema (dev_alice_marketing) or routes all models to dev_alice.

This setup guarantees clean production namespaces while maintaining complete isolation during development.

The standard override macro

To implement this logic, create a file at macros/generate_schema_name.sql:

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

With this macro in place, production builds write cleanly to marketing, while local runs stay sandboxed inside developer schemas.