How to use Macros in a SQL Query

Writing SQL statement is sometimes a challenge not only for the difficulty in writing them but also for the repetitive parts, which make the process even more painful and prone to errors.

SQL Models in Y42 has integrated Jinja templating language in its editor, making your SQL statements more powerful. Within Jinja you can use macros, which should be considered like functions, making the repetitive pieces of SQL code programmatic.

As you can see in the image below, when Jinja gets executed (the code in the left of the image) it gets compiled as a pure SQL statement (the code in the right of the image), and it will have the same result set.

The only difference is that Jinja and Macros are more scalable, because if you need to add more columns you simply need to add the column names in the variable list. The code gets more cleaner and slim.

Adding Macros

This is a complete Jinja + Macro statement example used in the Y42 SQL Model editor:

{% macro summing(column_name, decimal_spaces=2) %}
    round(Sum({{ column_name }}), {{ decimal_spaces }}) as sum_{{ column_name }}
{% endmacro %}

{% set to_be_summed_columns = ["Units_Sold", "Gross_Sales", "Profit"] %}

SELECT 
    `Segment`,
    {% for column in to_be_summed_columns %}
        {{ _self.summing(column) }}
        {% if not loop.last %}, {% endif %}
    {% endfor %}
FROM {{ ref('src/Integrations/financial_data/Financial Sample.xlsx') }}
GROUP BY 1

Setting the macro.

The Macro function can be stetted using the below statement.

{% macro macro_function_name(parameter) %}
    macro sql expression...
{% endmacro %}

In the example above we used:

{% macro summing(column_name, decimal_spaces=2) %}
    round(Sum({{ column_name }}), {{ decimal_spaces }}) as sum_{{ column_name }}
{% endmacro %}

Calling the macro inside the SQL statement

You can call the macro using:

{{ self.name_of_the_macro(Parameter) }}

In our example:

 {{ _self.summing(column) }}

Using Jinja & Macros saves time, makes the SQL statement more concise and less faulty.

Learn more about using Jinja in this article.