The generate_schema_name Macro
In the previous section, we discussed why we override the target schema to prevent developers from overwriting each other's work while ensuring Production databases remain clean.
In this section, we will do a deep dive into the actual Jinja code that powers this behavior: the generate_schema_name macro.
Topics Covered
- The default dbt logic
- Writing the custom macro
- Understanding the Jinja syntax
- Sibling Macros:
generate_database_nameandgenerate_alias_name
1. How dbt resolves names internally
Whenever dbt needs to build a physical table in Snowflake, it must determine exactly three things:
- The Database name
- The Schema name
- The Table name (Alias)
To figure out these three things, dbt calls three hidden, built-in macros.
If you want to change how dbt behaves, you simply create a file in your macros/ folder with the same name to override the default.
2. Deep Dive: generate_schema_name.sql
Let's look at the standard override macro that almost every enterprise dbt project uses.
Create this file exactly at: macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
{# Step 1: Grab the default schema from profiles.yml (e.g., DEV_ROHAN) #}
{%- set default_schema = target.schema -%}
{# Step 2: Did the user provide a custom schema in dbt_project.yml? #}
{%- if custom_schema_name is none -%}
{{ default_schema }}
{# Step 3: We have a custom schema. Are we in Production? #}
{%- elif target.name == 'prod' -%}
{{ custom_schema_name | trim }}
{# Step 4: We have a custom schema, but we are in Development. #}
{%- else -%}
{{ default_schema }}_{{ custom_schema_name | trim }}
{%- endif -%}
{%- endmacro %}
Understanding the Jinja
target.name: This variable refers to the specific target block in yourprofiles.ymlfile. If you rundbt run --target prod,target.namebecomes the string"prod".custom_schema_name: This is the value defined indbt_project.yml. For example, if you set+schema: marketing, the custom schema name is"marketing".| trim: This is a Jinja filter that removes any accidental leading or trailing whitespace.
3. Sibling Macros
Just as you can override the schema name, you can also override the Database and Table names.
generate_database_name.sql
By default, dbt builds everything in the database specified in your profiles.yml.
If you are at an Indian bank that strictly separates PII data into a BEEMS_HR_DB and transaction data into BEEMS_FINANCE_DB, you can override this macro so that dbt run builds models across entirely different databases simultaneously.
generate_alias_name.sql
By default, dbt names the physical table exactly the same as the .sql file name.
If your file is named stg_stripe_payments.sql, the table will be stg_stripe_payments.
You can override generate_alias_name if you want to dynamically prefix all tables built by a specific user (e.g., automatically renaming the table to rohan_stg_stripe_payments).
Summary
- dbt uses hidden internal macros to resolve the physical paths of your models.
- You can override these by creating
generate_schema_name,generate_database_name, andgenerate_alias_namemacros in your project. - The
target.namevariable is critical for separatingdevconcatenation logic fromprodclean logic.