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'.

{%- 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.