Skip to main content

Overriding Internal Macros

dbt is built on a framework of hidden, internal macros. When you use the {{ ref() }} function, or configure a model as materialized='table', dbt executes complex internal Jinja logic to translate your request into Snowflake SQL.

As a Data Engineer, you are not restricted to just using these built-in macros; you can actively override them to force dbt to behave exactly the way your company requires.

The most famous example of this is the generate_schema_name macro.

Topics Covered​

  • Macro Precedence and Overrides
  • The generate_schema_name Macro
  • Using the Macro from a dbt Model
  • Customizing the Override

1. Macro Precedence and Overrides​

If dbt needs to build a physical table, it calls an internal macro named generate_schema_name to figure out which Snowflake schema to put it in.

When dbt looks for a macro, it follows a strict Order of Precedence:

  1. Does this macro exist in the local project's macros/ folder? (If Yes, use it!)
  2. Does this macro exist in an installed package (like dbt_utils)?
  3. Does this macro exist in the internal dbt-core source code?

Because of this order, if you create a file named generate_schema_name.sql in your own macros/ folder, dbt will instantly ignore its own internal logic and execute your code instead!

2. Re-Visiting generate_schema_name​

(Note: We covered the conceptual need for this in Module 3. Here, we analyze it from an automation/Jinja perspective).

By default, dbt concatenates schema names (e.g., DEV_ARJUN_MARTS). We want to override this so that the PROD environment uses clean schema names (e.g., just MARTS).

-- macros/generate_schema_name.sql

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

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

{%- if custom_schema_name is none -%}
{{ default_schema }}

{# We use the target.name variable to implement conditional logic! #}
{%- elif target.name == 'prod' -%}
{{ custom_schema_name | trim }}

{%- else -%}
{{ default_schema }}_{{ custom_schema_name | trim }}

{%- endif -%}

{%- endmacro %}

3. Use the macro through a model schema​

You do not call generate_schema_name() inside the model SQL. dbt calls it automatically when the model declares a custom schema. The model only declares the business-level schema name; the macro decides how that name is combined with the active target schema.

Step 1: Create the override macro​

Create this file in your dbt project:

macros/generate_schema_name.sql

{% macro generate_schema_name(custom_schema_name, node) -%}
{%- set default_schema = target.schema -%}

{%- if custom_schema_name is none -%}
{{ default_schema }}
{%- elif target.name == 'prod' -%}
{{ custom_schema_name | trim }}
{%- else -%}
{{ default_schema }}_{{ custom_schema_name | trim }}
{%- endif -%}
{%- endmacro %}

The target.name value comes from the active target in profiles.yml. The target.schema value is the default schema for that target.

Step 2: Create a model with a custom schema​

Create this model:

models/marts/fct_orders.sql

{{ config(
materialized='table',
schema='marts'
) }}

SELECT
order_id,
customer_id,
order_date,
order_amount
FROM {{ ref('stg_orders') }}

The important setting is schema='marts'. This becomes the custom_schema_name argument received by generate_schema_name.

The model does not need this call:

{{ generate_schema_name('marts', this) }}

dbt invokes the macro as part of model compilation and relation creation.

Step 3: Compare development and production​

Assume the dev target has target.schema = DEV_PRIYA:

DEV_PRIYA_MARTS.FCT_ORDERS

This keeps Priya's development objects isolated from other developers. Another developer might build the same model in DEV_ARJUN_MARTS.

Assume the prod target has target.schema = PROD:

MARTS.FCT_ORDERS

The model SQL does not change between environments. Only the target-specific schema behavior changes.

Step 4: Inspect the resolved relation​

Compile the model for each target:

dbt compile --select fct_orders --target dev
dbt compile --select fct_orders --target prod

To print the resolved database, schema, and identifier without running the model, use:

dbt ls --select fct_orders --target dev --output json
dbt ls --select fct_orders --target prod --output json

In each JSON record, inspect the relation_name field. It should contain the resolved database, schema, and model identifier.

Then build the model in the intended environment:

dbt run --select fct_orders --target dev
dbt run --select fct_orders --target prod

Only run the production command from an approved deployment environment. A local developer profile should normally use the dev target.

You can also configure the schema outside the model in dbt_project.yml:

models:
your_project_name:
marts:
+schema: marts

Use either model-level config(schema='marts') or project-level +schema: marts, according to your team's configuration convention. Do not add both for the same model unless you intentionally want the model-level setting to override the project setting.

4. Common mistakes​

  • Calling generate_schema_name() inside the model instead of configuring schema.
  • Creating generate_schema_name.sql outside the project macros/ directory.
  • Using a target name such as production while the macro checks for prod.
  • Expecting dbt compile to create a table. Compilation only renders SQL.
  • Running the prod target locally without confirming database permissions.

5. Customizing the Override​

Because this is your custom macro now, you can add whatever logic your company desires!

Imagine an Indian logistics firm where the Data Science team runs heavy ML models, and you want to automatically suffix _ML to any schema they create, but leave the core Data Engineering schemas alone.

You can modify the macro using Python-like if/else Jinja logic!

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

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

{%- if custom_schema_name is none -%}
{{ default_schema }}

{%- elif target.name == 'prod' -%}

{# NEW AUTOMATION LOGIC #}
{%- if 'data_science' in custom_schema_name -%}
{{ custom_schema_name | trim }}_ML
{%- else -%}
{{ custom_schema_name | trim }}
{%- endif -%}

{%- else -%}
{{ default_schema }}_{{ custom_schema_name | trim }}
{%- endif -%}

{%- endmacro %}

If the Data Science team tries to build a model in a custom schema called data_science_forecasts, dbt will automatically intervene and build it in DATA_SCIENCE_FORECASTS_ML.

Summary​

  • dbt allows you to override internal macros to change its core behavior.
  • dbt resolves macros by checking your local macros/ folder first.
  • generate_schema_name is the most commonly overridden macro, used to separate Development concatenation from Production schemas.
  • A model activates the macro by setting schema='marts' or +schema: marts.
  • Development and production can build the same model into different schemas.
  • Because it is just Jinja, you can inject highly custom automation logic (like automatically applying suffixes) into the override.