Macros
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.
- dbt_project.yml
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.
FAQ
Are all dbt macros compatible with Y42?
While the majority of macros will run seamlessly in Y42, there are a few exceptions. Macros that attempt to pattern-match table names within the data warehouse will not work out-of-the-box. This is because Y42 executes Virtual Builds, which issues a unique table/view name identifier instead of using the dbt model name.