Skip to main content

Target Schema Override

When you define a custom schema for a model in your dbt_project.yml file, dbt does something that often confuses new Data Engineers: it concatenates the schema name.

Let's say Priya's default target schema in her profiles.yml is DEV_PRIYA. She configures her bronze/ folder to build into a schema called BRONZE.

She expects a Bronze table to be built at: BEEMS_DB.BRONZE.stg_orders Instead, dbt builds it at: BEEMS_DB.DEV_PRIYA_BRONZE.stg_orders

Why does dbt do this, and how can we override it?

Topics Covered​

  • The Default Schema Behavior
  • Why dbt concatenates schemas
  • The generate_schema_name Macro
  • Overriding the Macro for Production

1. The Default Schema Behavior​

By default, dbt uses a macro called generate_schema_name to determine exactly where a model should be built.

The default logic of this macro is:

  1. If no custom schema is defined, put it in the default target schema (DEV_PRIYA).
  2. If a custom schema is defined, concatenate the target schema and the custom schema with an underscore (DEV_PRIYA_BRONZE).

2. Why does dbt do this?​

dbt concatenates schemas to protect your environments.

Imagine you have three Data Engineers working on the project simultaneously: Priya, Arjun, and Rohan. If dbt simply built the models in BEEMS_DB.BRONZE, BEEMS_DB.SILVER, and BEEMS_DB.GOLD, all three engineers would constantly overwrite each other's tables every time they ran dbt run on their laptops!

By concatenating the target schema, dbt ensures that everyone has their own isolated sandbox:

  • Priya builds in DEV_PRIYA_BRONZE, DEV_PRIYA_SILVER, and DEV_PRIYA_GOLD
  • Arjun builds in DEV_ARJUN_BRONZE, DEV_ARJUN_SILVER, and DEV_ARJUN_GOLD
  • Rohan builds in DEV_ROHAN_BRONZE, DEV_ROHAN_SILVER, and DEV_ROHAN_GOLD

3. The Problem in Production​

While concatenation is fantastic for local development, it is terrible for Production.

In production, your orchestration tool (like Airflow or dbt Cloud) uses a specific user with a target schema of PROD. You want your medallion layers to be in BRONZE, SILVER, and GOLD, not PROD_BRONZE, PROD_SILVER, and PROD_GOLD.

4. The Solution: Overriding generate_schema_name​

To fix this, we must tell dbt to use concatenation in Development, but use strict, clean schema names in Production.

We do this by overriding dbt's default macro.

Step 1: Create the Macro​

In your dbt project, navigate to the macros/ folder and create a file called generate_schema_name.sql.

Step 2: Paste the Override Logic​

dbt Labs provides the standard code for this override in their official documentation. Paste this code exactly as is:

{% macro generate_schema_name(custom_schema_name, node) -%}

{%- set default_schema = target.schema -%}

{# If no custom schema is provided, use the default target schema #}
{%- if custom_schema_name is none -%}

{{ default_schema }}

{# If we are in the PROD environment, ignore the default schema entirely #}
{%- elif target.name == 'prod' -%}

{{ custom_schema_name | trim }}

{# If we are in DEV, concatenate them to keep sandboxes isolated #}
{%- else -%}

{{ default_schema }}_{{ custom_schema_name | trim }}

{%- endif -%}

{%- endmacro %}

Step 3: Test the Output​

Now, let's see what happens to Priya's fct_orders model (configured to the GOLD schema).

Scenario 1: Priya runs it locally (target = dev)

  • dbt detects the target is not 'prod'.
  • It concatenates the names.
  • Output: DEV_PRIYA_GOLD.fct_orders
  • Result: Priya's sandbox is safe.

Scenario 2: GitHub Actions runs it (target = prod)

  • dbt detects the target is 'prod'.
  • It executes the elif block and ignores the default schema completely.
  • Output: GOLD.fct_orders
  • Result: The production database is perfectly clean!

Summary​

  • By default, dbt concatenates custom schemas with your target schema (e.g., DEV_PRIYA_BRONZE, DEV_PRIYA_SILVER, or DEV_PRIYA_GOLD).
  • This protects developers from overwriting each other's work.
  • In production, you want clean medallion schemas (BRONZE, SILVER, and GOLD).
  • You achieve this by creating a generate_schema_name.sql file in your macros/ folder to override dbt's default behavior based on the target.name.