Macros
Learn how to create macros and reference them in your Y42 models.
Overview
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:
| telephone_number | cleaned_telephone_number |
|---|---|
| (495) 012-5803 | 495-012-5803 |
| 830-718-8803 | 830-718-8803 |
| (813) 031-0676 | 813-031-0676 |
We can express the transformation logic in SQL:
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.