generate_schema_name: Environment-Aware Schema Naming in dbt

dbt’s default generate_schema_name behavior concatenates target.schema with any custom schema specified in dbt_project.yml or model config. A model configured with schema: marts in a target called dbt_adrienne lands in dbt_adrienne_marts in every environment, including prod. Overriding this macro allows production to use clean schema names (marts) while development gets prefixed schemas (dbt_adrienne_marts).

How dbt’s Default Schema Naming Works

When you set a custom schema on a model:

models:
  my_project:
    marts:
      +schema: marts

dbt calls generate_schema_name internally to decide the final schema name. The default implementation always produces {target.schema}_{custom_schema}. There’s no built-in distinction between environments.

This means:

• In dev (target schema: dbt_adrienne): the model lands in dbt_adrienne_marts
• In prod (target schema: analytics): the model lands in analytics_marts

Neither of these is what most teams want. Prod should be marts. Dev should be dbt_adrienne_marts. The default makes both environments use the same concatenation logic, which forces an awkward choice: either accept messy prod schema names, or accept that dev models land in schemas identical to prod.

The Override

The fix is a generate_schema_name macro in your project’s macros/ directory. dbt automatically calls this macro instead of the built-in one when it exists — you don’t invoke it directly.

-- 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 }}
    {% else %}
        {{ target.schema }}{% if custom_schema_name is not none %}_{{ custom_schema_name | trim }}{% endif %}
    {% endif %}
{% endmacro %}

With this macro in place, a model configured with schema: marts lands in:

• Dev (target.name == 'dev', target.schema == 'dbt_adrienne'): dbt_adrienne_marts
• Prod (target.name == 'prod'): marts

The logic is simple: in prod, use the custom schema directly as-is. In every other environment (dev, CI, staging), fall back to the default concatenation behavior. Models without a custom schema just use target.schema in all environments, which is already correct.

Without the override

Two common outcomes without the override:

• Concatenated prod schemas (analytics_marts, analytics_staging, analytics_finance). Downstream consumers — BI tools, analysts, other pipelines — couple to the prefix. When the project name or target schema changes, connections break.
• Manual deployment steps: engineers hardcode schema names or comment/uncomment config before deploying. Manual steps get forgotten. One mis-deployed run puts models in the wrong schema.

The override determines schema names at compile time based on the deployment target — no manual intervention.

Handling CI and Staging Environments

The simple prod/not-prod split covers most projects, but you may want more granular control. A common extension handles CI environments explicitly:

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

This puts CI schemas in a ci_ prefixed space (ci_dbt_pr123_marts) so CI runs don’t pollute the dev namespace. On most teams the simple two-branch version is enough — but if CI schemas end up conflicting with dev schemas, adding the CI branch solves it.

Placement and the One-Macro-Per-File Rule

generate_schema_name is an override macro, not a utility macro. It belongs at the root of your macros/ directory alongside other dbt overrides (generate_alias_name, generate_node_name), not buried in a utils/ subfolder.

macros/
├── _macros.yml
├── generate_schema_name.sql     # override macros at root
├── generate_alias_name.sql      # if you have this
└── utils/
    ├── add_audit_columns.sql
    ├── cents_to_dollars.sql
    └── limit_data_in_dev.sql

This placement signals to anyone new to the project that these macros have special status — they’re called automatically by dbt, not explicitly by model SQL.

Since this macro is called automatically, it doesn’t need an arguments block in _macros.yml the same way utility macros do. But it’s worth documenting the environment-aware behavior in the description, since the conditional logic isn’t obvious to someone who’s never seen the override pattern before.

What Can Go Wrong

Wrong target.name in profiles. The macro keys off target.name. If your prod profile’s target isn’t actually named prod, the condition never fires. Verify with dbt debug that your target name matches what the macro expects. Using a var for the prod target name (rather than hardcoding 'prod') gives you flexibility:

{% if target.name == var('prod_target_name', 'prod') and custom_schema_name is not none %}

Models without custom schemas in prod. Models that don’t set a custom schema use target.schema in all environments, including prod. This is usually correct — your staging models might intentionally land in the default schema. But double-check that the prod target.schema is what you want for unschematized models.

Multiple databases. If your project writes to multiple databases, schema naming may need to account for that as well. The node argument gives you access to the model’s full configuration, including database — you can branch on node.database if needed.

The Broader Pattern

generate_schema_name is one of several dbt “hook” macros that override internal behavior. The pattern — override a macro that dbt calls automatically, branch on target.name to produce environment-aware behavior — appears in generate_alias_name (for custom model aliases) and generate_node_name (for custom node naming).

The same environment-aware logic that makes schema naming work also shows up in [[dbt Macros|the limit_data_in_dev pattern]]: use target.name to check the environment, and produce different behavior for prod vs. dev. The difference is that generate_schema_name is called by dbt, while limit_data_in_dev is called explicitly in your model SQL.

See dbt Project Structure and Naming for how schema organization fits into the broader project structure, and dbt Macros for the Jinja fundamentals behind how this macro produces its output.