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:

id	currency_pair	currency1	currency2
cp_001	USD/JPY	1	130.03
cp_002	GBP/USD	1	1.25
cp_003	USD/CHF	1	0.98
cp_004	AUD/USD	1	0.71
cp_005	USD/CAD	1	1.28
cp_006	EUR/USD	1	1.05

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

currency_pair_id	currency_pair	exchange_rate
cp_001	USD/JPY	0.0077
cp_002	GBP/USD	0.80
cp_003	USD/CHF	1.052
cp_004	AUD/USD	1.408
cp_005	USD/CAD	0.78
cp_006	EUR/USD	0.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 EUR/USD. 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
)