Skip to main content

MATE Macros

This document primarily describes how to develop MATE macros and use MATE (and third-party) packages in your MATE models and transformations.

Developing MATE Macros

Macros are fundamentally a Jinja feature. Therefore, let's look at the Jinja documentation for a definition:

Macros are comparable with functions in regular programming languages. They are useful to put often-used idioms [or code] into reusable functions to not repeat yourself ("DRY" - Do Not Repeat).

Macro Fundamentals

The basic structure of a macro, according to the Jinja documentation, is as follows:

{% macro input(name, value='', type='text', size=20) -%}
<input type="{{ type }}" name="{{ name }}" value="{{ value }}" size="{{ size }}">
{%- endmacro %}

Broken down into components, a macro comprises:

  • {% macro %}
  • macro name
  • arguments (in parentheses after the macro name)
  • encapsulated logic
  • {% endmacro %}
tip

We can use any MATE/Jinja features and constructs inside the macro's encapsulated logic.

Basic MATE Macro

Let's look at a very simple scenario to describe how to build a macro.

  • The first step is to create a new directory, macros under the /dataops/modelling/ directory
  • Then create a new file called calc_exchange_rate.sql and save it in the macros directory
  • Thirdly, create your macro inside this SQL file and save it
tip

Add the empty parentheses after the macro name even if it has no arguments.

For instance:

calc_exchange_rate.sql
{% macro calc_exchange_rate() %}
currency1/currency2
{% endmacro %}

This macro calculates an exchange rate between a currency pair (currency1/currency2) such as the USD/CAD (US Dollar/Canadian Dollar) currency pair. The first currency is always the single (or one) unit of a currency pair. And the second currency is the exchange rate. Therefore, assuming that currency1/currency2 represents the USD/CAD currency pair, this macro calculates how many CAD (currency2) are required to purchase 1 USD.

As a result, you can call this macro every time you need to calculate an exchange rate, instead of retyping the calculation.

For instance, let's assume that the rows in a source table raw.currency_pairs include different currency pairs' exchange rates and that the column names are currency1 and currency2.

macros/stg_currency_pairs.sql
stg_currency_pairs as (
select
id as currency_pair_id,
currency_pair,
{{ calc_exchange_rate() }} as exchange_rate
from
raw.currency_pairs
group by 1,2
)

This SQL then compiles as follows:

compiled/stg_currency_pairs.sql
stg_currency_pairs as (
select
id as currency_pair_id,
currency_pair,
currency1/currency2 as exchange_rate
from
raw.currency_pairs
group by 1,2
)

For now, let's assume that the raw.currency_pairs table only has six rows:

idcurrency_paircurrency1currency2
cp_001USD/JPY1130.03
cp_002GBP/USD11.25
cp_003USD/CHF10.98
cp_004AUD/USD10.71
cp_005USD/CAD11.28
cp_006EUR/USD11.05

When run, this SQL statement builds the following model from this source table:

currency_pair_idcurrency_pairexchange_rate
cp_001USD/JPY0.0077
cp_002GBP/USD0.80
cp_003USD/CHF1.052
cp_004AUD/USD1.408
cp_005USD/CAD0.78
cp_006EUR/USD0.95

This staging model can now be used in any downstream models, including fact and dimension models.

MATE Macro with Arguments

Currently, the calc_exchange_rate macro includes the table column names in between its opening ({% macro %}) and closing ({% endmacro %}) tags.

But what if the column names change, or you'd like to use this functionality every time you need to calculate an exchange rate, irrespective of the table, model, or column names?

The solution is solved by adding arguments inside the parentheses after the macro name to this macro.

For example:

Let's convert the columns currency1 and currency2 into arguments. Therefore, currency1 becomes the argument first_currency and currency2 becomes the argument exchange_rate_currency.

macros/calc_exchange_rate.sql
{% macro calc_exchange_rate(first_currency, exchange_rate_currency) %}
{{ first_currency }} / {{ exchange_rate_currency }}
{% endmacro %}

To use this macro in a model, we must pass the argument values to the macro. For this example, the column names remain the same, so they are passed in as follows:

macros/stg_currency_pairs.sql
stg_currency_pairs as (
select
id as currency_pair_id,
currency_pair,
{{ calc_exchange_rate('currency1', 'currency2') }} as exchange_rate
from
raw.currency_pairs
group by 1,2
)

This model compiles into the same SQL as seen in the Basic MATE Macro section:

compiled/stg_currency_pairs.sql
stg_currency_pairs as (
select
id as currency_pair_id,
currency_pair,
currency1/currency2 as exchange_rate
from
raw.currency_pairs
group by 1,2
)

Lastly, this SQL builds the same model as in the Basic MATE Macro section.

MATE Macro with Arguments and Default Values

We can also add default values to our macro arguments.

For instance, let's assume that we know that the most common currency pair that we need to calculate the exchange rate for is the EUR/USD. In fact, this is the most heavily traded currency pair in the world; thus, it is the most liquid currency pair.

Therefore, it makes sense to add the values of the EUR/USD (1/1.05) currency pair as our default currency pair values to calculate an exchange rate.

calc_exchange_rate.sql
{% macro calc_exchange_rate(first_currency=1, exchange_rate_currency=1.05) %}
{{ first_currency }} / {{ exchange_rate_currency }}
{% endmacro %}

If the default values for first_currency is used, the SQL for the stg_currency_pairs model becomes:

macros/stg_currency_pairs.sql
stg_currency_pairs as (
select
id as currency_pair_id,
currency_pair,
{{ calc_exchange_rate(exchange_rate_currency='currency2') }} as exchange_rate
from
raw.currency_pairs
group by 1,2
)

This template then renders as:

compiled/stg_currency_pairs.sql
stg_currency_pairs as (
select
id as currency_pair_id,
currency_pair,
1/currency2 as exchange_rate
from
raw.currency_pairs
group by 1,2
)