Learn how to create macros and reference them in your Y42 models.


dbt macros are reusable code snippets that abstract and encapsulate modeling logic. They can be used for common data transformation tasks such as data validation, type conversions or calculations. When used judiciously, macros reduce the amount of repetition and inconsistencies in your code.

Macros are written in the Jinja templating language, which provides a way to generate dynamic SQL queries by embedding control structures and expressions within user-defined templates.

Macros folder

By default, macros are stored in a .sql file located in the macros folder. While you can customize which folder to use by modifying macro-paths in the dbt_project.yml configuration file, we recommend preserving the default paths to avoid unintentional compilation errors. Note that you can define multiple macros in a single .sql file.

Create and use a macro

In this example, we'll create a macro that cleans up phone numbers by removing the parantheses surrounding the area code. After applying the macro, the model's output should look like this:

(495) 012-5803495-012-5803
(813) 031-0676813-031-0676

We can express the transformation logic in SQL:


REGEXP_REPLACE(REGEXP_REPLACE(telephone_number, r'\((\d+)\)', r'\1-'), ' ', '')

To convert this SQL transformation into a reusable macro, we'll need to perform the following steps:

Create the macro file

Create a .sql file in the macros folder, and write the transformation logic as a SQL query.

Wrap the SQL query with the Jinja expression for macros

Macros must be enclosed within Jinja expressions.

It should start with:
{% macro macro_name(arg1, arg2) %}

And end with:
{% endmacro %}

Arguments are optional. In this example, we'll use the column name we want to apply the transformation logic to.

Call the macro in a model

To use the macro in a model, reference it with:
{{ macro_name('arg1, arg2') }}


REGEXP_REPLACE(REGEXP_REPLACE(telephone_number, r'\((\d+)\)', r'\1-'), ' ', '')


Are all dbt macros compatible with Y42?