Defining a Custom dbt Macro

Lab Steps

Understanding What Are dbt Jinja Macros
Connecting to the dbt Web IDE
Defining a Custom dbt Macro
Creating a Model Leveraging dbt Macros
Executing the dbt Model
Need help? Contact our support team

Here you can find the instructions for this specific Lab Step.

If you are ready for a real environment experience please start the Lab. Keep in mind that you'll need to start from the first step.


dbt macros are very helpful when you need to mask some reusable logic in your dbt models.

In this lab step, you will define a custom macro that masks the handling of null field values with a case/when/then SQL construct.



1. Click on the Explore icon to reach the folder containing the dbt project files under the PROJECT drop-down:



2. Create a file named handle_null.sql in the macros folder by right-clicking on the folder and clicking New File:



3. Open the handle_null.sql file and insert the following snippet:

Copy code
{% macro handle_null(check_field, then_value, else_value) %}
        when {{ check_field }} is null
        then '{{ then_value }}'
        else '{{ else_value }}'
{% endmacro %}

As you can see you have declared the handle_null macro by using both the macro and endmacro Jinja statements. They are needed to let the compiler know where the macro starts and where it ends.

The macro will generate the CASE SQL statements checking for the check_value parameter: if it is null, it returns the then_value parameter; otherwise, it returns the else_value parameter.


4. Save the content of the file by clicking CMD + S (macOS users) or CTRL + S (Windows users).



In this lab step, you defined a custom macro that masks the handling of null field values with a case/when/then SQL construct.

Validation checks
Created the dbt Custom Macro

Check whether the dbt custom macro has been created.

Data build tool (dbt)