Macros

What Are Macros?

Macros are functions that take in arguments and return a string. These strings can then be used in node templates, transformations and join strings to produce strings consistently throughout your project. The strings returned ultimately represent Snowflake SQL commands (see Example SQL Generated by the Macro below).

This allows your team to build reusable code that is defined in a single location within the macro but reused repeatedly throughout your project.

They can be defined within a Node Template or in Build Settings Macros.

Macro Tutorial

For a tutorial on building Macros with Jinja see Use Jinja to Build a Macro.

How to use Macros

Macros are defined in Jinja 2 syntax and are populated with Snowflake SQL code. For information on writing in Jinja, refer to the Official Jinja Documentation.

Macros can be used nearly anywhere in the application; however, the metadata context given to the macro can vary from place to place.

Below you'll find a simple macro that receives a number as an argument and returns a string 'ODD' or 'EVEN'.

Simple macro

{%- macro even_odd(column) -%}
    CASE WHEN MOD({{ column }}, 2) = 0 THEN 'EVEN' ELSE 'ODD' END
{%- endmacro %}

If you wanted to use it as a transform in a node, you would call it using the syntax {{ function(‘“schema”.”table”’) }}. So to use the above one would enter {{even_odd('"CUSTOMER"."C_NATIONKEY"')}} or {{even_odd('{{SRC}}')}} as a shorthand.

Data Types

Make sure the data type is VARCHAR or another valid SQL string type, otherwise you may receive an error.

Below you'll find the SQL that was generated by the STG_CUSTOMER Node above, including the macro and its transformation.

EXPLAIN USING text
SELECT
  "CUSTOMER"."C_CUSTKEY" AS "C_CUSTKEY",
  "CUSTOMER"."C_NAME" AS "C_NAME",
  "CUSTOMER"."C_ADDRESS" AS "C_ADDRESS",
  CASE
    WHEN MOD("CUSTOMER"."C_NATIONKEY", 2) = 0 THEN 'EVEN'
    ELSE 'ODD'
  END AS "C_NATIONKEY",
  "CUSTOMER"."C_PHONE" AS "C_PHONE",
  "CUSTOMER"."C_ACCTBAL" AS "C_ACCTBAL",
  "CUSTOMER"."C_MKTSEGMENT" AS "C_MKTSEGMENT",
  "CUSTOMER"."C_COMMENT" AS "C_COMMENT"
FROM
  "COALESCE_SAMPLE_DATABASE"."TPCH_SF001"."CUSTOMER" "CUSTOMER"

To use a macro in a node template simply call it as you would a regular function, inside double curly braces, passing in any required parameters.

Macros Run Order

Depending on where macros are used in the app determines the precedence.

Macros are run in the following order:

1. Coalesce internal macros - Runs first
2. Workspace
3. Packages
4. Package Configuration
5. Macros in Node Templates - Runs last

This means that if there is a macro in a Package and one in a Node template that contradicts or edits the same data, the macro in the Package will run, then the macro in the Node Template will run.

• If there are naming collisions, the last macro that is run, is the one that will take effect.
• In a Node (including Package Node Types), they are run starting with the Node Definition, Create Template, then Run Template.
• The order macros appear in a file is the order they are run. They will be run from top to bottom.