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_nameMacro - 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:
- If no custom schema is defined, put it in the default target schema (
DEV_PRIYA). - 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, andDEV_PRIYA_GOLD - Arjun builds in
DEV_ARJUN_BRONZE,DEV_ARJUN_SILVER, andDEV_ARJUN_GOLD - Rohan builds in
DEV_ROHAN_BRONZE,DEV_ROHAN_SILVER, andDEV_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
elifblock 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, orDEV_PRIYA_GOLD). - This protects developers from overwriting each other's work.
- In production, you want clean medallion schemas (
BRONZE,SILVER, andGOLD). - You achieve this by creating a
generate_schema_name.sqlfile in yourmacros/folder to override dbt's default behavior based on thetarget.name.