What are dbt Macros?

dbt macros are similar to functions in programming languages like Python or Java. dbt uses macros in a templating language called Jinja to avoid repeated code across multiple models. This allows developers to follow the DRY (Don’t repeat yourself) principle in software development.

In this blog, we will explore the concept of dbt macros and various methods to invoke them from both dbt models and other macros.

Syntax

In dbt, macros are defined using the Jinja templating language. The Jinja syntax allows you to create macros that can accept parameters, use control structures, and leverage variables and filters.

Below is the basic syntax for defining macros in dbt.

				
					{% macro macro_name(arg1, arg2, ..., argN) %}

    SQL logic here, using the parameters as needed.

{% endmacro %}

dbt macros are defined using the syntax {% macro macro_name(arg1, arg2) %} to start the macro block and we end it with {% endmacro %}. Arguments (arg1 and arg2) are optional and can be used to pass values to the macro.

Any code or SQL logic placed between the opening and closing tags ({% macro %} and {% endmacro %}) will be processed and rendered at the location where you call the macro.

Creating Macro

Macros are usually defined in .sql files within the macros directory of your project. We can define multiple macros in a single file. To create a macro, we have to create a SQL file in the macros directory of your project and create macros by following the above syntax. Here is an example of a macro that takes one parameter and returns the SQL logic, which is used across all models.

				
					-- macros/item_count.sql
{% macro item_count(item) %}

    SUM(CASE WHEN ITEM = '{{item}}' THEN 1 ELSE 0 END)

{% endmacro %}

In this example, the item_count macro takes the item name as a parameter and generates the logic to calculate the item count.

You can use this macro in your dbt models as follows.

				
					-- models/orders_count.sql

SELECT 	ORDER_ID, 
ORDER_DATE, 
{{ item_count('pizza') }} AS PIZZA_COUNT,
{{ item_count('doughnut') }} AS DOUGHNUT_COUNT

FROM ORDERS

GROUP BY ORDER_DATE

When this macro is applied, it will generate the following SQL query.

				
					SELECT 	ORDER_ID, 
ORDER_DATE, 
SUM(CASE WHEN ITEM = 'pizza' THEN 1 ELSE 0 END) AS PIZZA_COUNT,
SUM(CASE WHEN ITEM = 'doughnut' THEN 1 ELSE 0 END) AS DOUGHNUT_COUNT

FROM ORDERS

GROUP BY ORDER_DATE

Invoking a Macro

Macros can be invoked from multiple places in dbt. You can invoke a macro from a model, another macro, command line. There are three ways to invoke a macro.

Using Expressions blocks
Call blocks
Run-operation command

Invoke a Macro Using Expression Blocks

To invoke or use a dbt macro in your dbt project, you can call the macro by its name along with any required parameters in {{ }}.

Macro with No Parameters

If the macro doesn’t require any parameters, you can call it directly without passing any values:

				
					{{ macro_name() }}

Macro with Parameters

If the macro requires one or more parameters, you need to pass the appropriate values when calling the macro.

				
					{{ macro_name(arg1, arg2, ..., argN) }}

Invoke a Macro Using Call Blocks

Call blocks are also used to invoke a macro. It provides a way to dynamically extend the functionality of a macro by allowing you to pass one macro as an argument to another.

Syntax

				
					{% call called_macro( arg1, arg2, . . argN) %}

    Code to be accessed by the macro called_macro

{% endcal %}

It calls a macro called_macro, whatever code between the {% call %} {% endcal %} is accessible to called_macro using the caller() method.

Example

Create a macro:

				
					{% macro select_all_columns_macro(table_name) %}

    SELECT *
    FROM {{ table_name }} 
    WHERE {{ caller() }}

{% endmacro %}

Calling macro using call blocks.

				
					{% call select_all_columns_macro('EVENT_TABLE') %} 

CREATE_DATE >= '2020-02-18'::DATE

{%- endcall %}

When it is called, it would render

				
					    SELECT *
    FROM EVENT_TABLE 
    WHERE CREATE_DATE >= '2020-02-18'::DATE

Invoke a Macro from CLI

Using dbt run-operation command, we can invoke a macro from the command line.

Syntax

dbt run-operation {macro} --args '{args}'{macro}: Specify the macro to invoke. dbt will call this macro with the supplied arguments and then exit --args. Supply arguments to the macro. This dictionary will be mapped to the keyword arguments defined in the selected macro.

Example

				
					$ dbt run-operation select_all_columns --args '{table_name: my_table}'
$ dbt run-operation macro_two --args '{table_name: 'my_table', schema_name: 'dev'}'

Using a Macro From a Package

dbt provides several useful macros that have been organized into packages, with the most popular one being dbt-utils.

Once you install a package into your dbt project, you gain access to its macros, allowing you to utilize them in your own project. Remember to qualify the macros by prefixing them with the package name to ensure proper referencing and usage.

In the below example, we are calling group_by macro available in the dbtutils package.

				
					select column_1, column_2, column_3, max(column_4), avg(column_5)

from my_table

{{ dbt_utils.group_by(n=3) }}

This would compile to

				
					select column_1, column_2, column_3, max(column_4), avg(column_5)

from my_table

group by 1,2,3

Popular dbt Packages

The below popular dbt packages contain macros that can be (re)used across dbt projects.

dbt_utils: Brings a lot of utilities
dbt-audit-helper: Helps audit models
dbt_project_evaluator: Helps test best practices
dbt_expectations: Great Expectations like testing
dbt_snow_mask: Used to help provide a way to mask data
dbt_constraints: Used to help apply constraints to your snowflake tables

Conclusion

dbt uses macros to create modular and reusable SQL code, enhancing code organization and maintainability. We can call macros from models and other macros using expressions and call blocks.

If you’re interested in exploring more information about dbt macros and various methods to invoke them from both dbt models and other macros, reach out to our team of experts.