Skip to main content

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_name and generate_alias_name

1. How dbt resolves names internally​

Whenever dbt needs to build a physical table in Snowflake, it must determine exactly three things:

  1. The Database name
  2. The Schema name
  3. 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 your profiles.yml file. If you run dbt run --target prod, target.name becomes the string "prod".
  • custom_schema_name: This is the value defined in dbt_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, and generate_alias_name macros in your project.
  • The target.name variable is critical for separating dev concatenation logic from prod clean logic.