How to Use generate_schema_name Macro in dbt: Syntax and Examples

In dbt, the generate_schema_name macro lets you customize the schema name dynamically for your models. You use it by defining the macro in your project and calling it to return the schema name based on your logic, such as environment or user input.

Syntax

The generate_schema_name macro is defined as a Jinja macro that returns a string representing the schema name. It typically takes two parameters: custom_schema and node. You write your logic inside to decide the schema name dynamically.

Parameters:

• custom_schema: The schema name provided in the model config or profile.
• node: The model node object containing metadata like model name, resource type, etc.

jinja

{% macro generate_schema_name(custom_schema, node) %}
  {{ custom_schema }}
{% endmacro %}

Example

This example shows how to customize the schema name based on the target environment. If the environment is 'prod', it uses the default schema. Otherwise, it appends the environment name to the schema.

jinja

{% macro generate_schema_name(custom_schema, node) %}
  {% set env = target.name %}
  {% if env == 'prod' %}
    {{ custom_schema }}
  {% else %}
    {{ custom_schema }}_{{ env }}
  {% endif %}
{% endmacro %}

Output

If target.name is 'dev' and custom_schema is 'analytics', output will be 'analytics_dev'.

Common Pitfalls

Common mistakes when using generate_schema_name include:

• Not returning a string value from the macro, which causes errors.
• Ignoring the custom_schema parameter and hardcoding schema names, reducing flexibility.
• Not handling different environments properly, leading to schema conflicts.

Always ensure your macro returns a valid schema name string and uses parameters to keep it dynamic.

jinja

{% raw %}
-- Wrong: returns nothing or invalid type
{% macro generate_schema_name(custom_schema, node) %}
  {% do log('No return here') %}
{% endmacro %}

-- Right: returns a string
{% macro generate_schema_name(custom_schema, node) %}
  {{ custom_schema }}
{% endmacro %}
{% endraw %}

Quick Reference

Parameter	Description
custom_schema	Schema name from model config or profile
node	Model metadata object with details like name and resource type
Return value	A string representing the schema name to use

Key Takeaways

The generate_schema_name macro returns a dynamic schema name string for dbt models.

Use the custom_schema and node parameters to build flexible schema names.

Handle different environments inside the macro to avoid schema conflicts.

Always return a valid string from the macro to prevent errors.

Avoid hardcoding schema names to keep your project adaptable.