Skip to main content

Use Jinja and SQL to Build a Macro

You’ll learn how to build a simple macro and a macro that uses another macro. If you want to follow along, get a copy of the Snowflake test data.

What is a Macro?​

A macro is a reusable block of code that you can define once and use multiple times. Macros can help keep your code DRY(Don’t Repeat Yourself).

Macro Syntax​

Macros are defined by using the keyword macro. Notice in the example, {{ flower_shop(type = 'white') }}, only type was supplied, not flower. It used the default flower = ‘rose’ in the macro definition.


{%- macro flower_shop(type, flower = 'rose') -%}

Today I want a {{ type }} {{ flower }}.

{%- endmacro -%}

{{ flower_shop(type = 'white') }} //Today I want a white rose. 

{{ flower_shop('purple', 'orchid') }}// Today I want a purple orchid.

Macro Uppercase Example​

You can use this macro to change all the column data to UPPERCASE. If you’re using the Snowflake sample data, this example changes all the CUSTOMER.C_COMMENTS to UPPERCASE.

  1. To run this macro, go to Build Settings > Macros.
  2. Copy and paste the macro there.
  3. In your Node Editor, either double click on the Transforms field or select the column and open the Column Editor.
  4. Enter {{uppercase_col_data('{{SRC}}')}} or enter {{uppercase_col_data('"CUSTOMER"."C_COMMENTS"')}}.
    1. SRC is a token that refers to the fully qualified source column name.
  5. Validate the function. Then Create and Run to see the data as UPPERCASE.
{%- macro uppercase_col_data(column) -%}
   UPPER({{ column }})
{%- endmacro %}

Using a Macro with Another Macro​

To keep your code from repeating itself, you can refer to another macro with another. In this example, we create a macro to remove dashes. Then another macro that uses remove_dashes and formats a phone number to standard American number, 123-456-7890.

This means the remove_dashes macro is available to be used and the format_phone_number can be used separately.

It uses the CUSTOMER.C_PHONE column.

  1. To run this macro, go to Build Settings > Macros.
  2. Copy and paste the macro there.
  3. In your Node Editor, either double click on the Transforms field or select the column and open the Column Editor.
  4. Enter {{format_phone_number('{{SRC}}')}} or enter {{format_phone_number('"CUSTOMER"."C_PHONE"')}}.
    1. SRC is a token that refers to the fully qualified source column name.
  5. Validate the function. Then Create and Run to see the phone number formatted.
{% macro remove_dashes(column_name) %}
    REPLACE({{ column_name }}, '-', '')
{% endmacro %}

{% macro format_phone_number(column_name) %}
    CONCAT(
        LEFT({{ remove_dashes(column_name) }}, 3), '-',
        SUBSTRING({{ remove_dashes(column_name) }}, 4, 2), '-',
        RIGHT({{ remove_dashes(column_name) }}, 4)
    )
{% endmacro %}

Macros and Tokens​

Macros can also use helper tokens. Helper tokens are shorthand in Coalesce that lets you refer to a column or node without needing to type the whole name.

For example, you have a macro named hello_goodbye.

  • Longhand - {{hello_goodbye('"CUSTOMER"."C_PHONE"')}}
  • Shorthand - {{hello_goodbye('{{SRC}}')}}. This uses SRC to refer to fully qualified source column name.

Check out Helper Tokens.