Defining a Custom dbt Macro

Introduction

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.

Instructions

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) %}
    case
        when {{ check_field }} is null
        then '{{ then_value }}'
        else '{{ else_value }}'
    end
{% 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).

Summary

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