Macros That Return Values

Concepts covered: Macros and packages

Every macro so far prints SQL: its body renders to text and the text lands in the model. A macro can instead hand back a value, a list, a dictionary or a number, for other Jinja in the model to work with. return() does that, and the SQL that reaches the warehouse is then built by the loop or lookup that uses the value.

{% macro payment_methods() %}
    {{ return(['card', 'bank_transfer', 'gift_card']) }}
{% endmacro %}

{%- set methods = payment_methods() -%}

select
    order_id,
    {%- for method in methods %}
    sum(case when payment_method = '{{ method }}' then amount else 0 end)
        as {{ method }}_amount{% if not loop.last %},{% endif %}
    {%- endfor %}
from {{ ref('stg_payments') }}
group by order_id

select
    order_id,
    sum(case when payment_method = 'card' then amount else 0 end)
        as card_amount,
    sum(case when payment_method = 'bank_transfer' then amount else 0 end)
        as bank_transfer_amount,
    sum(case when payment_method = 'gift_card' then amount else 0 end)
        as gift_card_amount
from analytics.stg_payments
group by order_id

return() stops the macro at that point and hands the value to the caller. methods therefore holds a list of 3 strings, methods | length is 3, and for walks the 3 items. Adding a fourth method is one edit to the macro, and the next run gives this model, and every other model that loops over the list, a fourth column.

A macro without return() hands back the text its body printed. Setting a variable from cents_to_dollars works too, but the variable is a string holding the rendered expression, with whatever whitespace the body left in it, and a string cannot be walked as a list of columns. Printing a returned list with double braces writes the list's own text into the SQL, square brackets and quotes included, which the warehouse rejects, so a returned value is handled by Jinja and only its pieces are printed.

Returning a Dictionary

A list carries one value per item. A dictionary carries a value for each key, which fits a mapping the SQL needs on both sides, such as a status code and the label it is reported under.

{% macro status_labels() %}
    {{ return({'p': 'placed', 's': 'shipped', 'r': 'returned'}) }}
{% endmacro %}

{%- set labels = status_labels() -%}

select
    order_id,
    case
        when status is null then 'unknown'
    {%- for code, label in labels.items() %}
        when status = '{{ code }}' then '{{ label }}'
    {%- endfor %}
    end as status_label
from {{ ref('stg_orders') }}

select
    order_id,
    case
        when status is null then 'unknown'
        when status = 'p' then 'placed'
        when status = 's' then 'shipped'
        when status = 'r' then 'returned'
    end as status_label
from analytics.stg_orders

The first branch is typed by hand ahead of the loop, so a missing status reads unknown instead of null, and the CASE keeps at least one branch even if the dictionary were emptied, since a CASE with no WHEN at all is a database error. A second model that counts orders per label loops over the same dictionary, so the labels in the CASE and the labels in the count come from one file and cannot drift apart. The values here are typed into the macro by hand; nothing in this section reads them from the warehouse, and the whole expansion happens while Jinja renders, before any SQL is sent.

Building a List Before Returning It

{% macro dollar_columns() %}
    {%- set columns = [] -%}
    {%- for prefix in ['amount', 'tax', 'shipping'] -%}
        {%- do columns.append(prefix ~ '_cents') -%}
    {%- endfor -%}
    {{ return(columns) }}
{% endmacro %}

{%- set cents_columns = dollar_columns() -%}

select
    {%- for column in cents_columns %}
    {{ cents_to_dollars(column) }} as {{ column | replace('_cents', '') }},
    {%- endfor %}
    order_id
from {{ source('raw', 'orders') }}

select
    round(amount_cents / 100.0, 2) as amount,
    round(tax_cents / 100.0, 2) as tax,
    round(shipping_cents / 100.0, 2) as shipping,
    order_id
from raw.orders

columns starts empty, do appends one name per prefix in the macro's list without printing anything, and return() hands back ['amount_cents', 'tax_cents', 'shipping_cents']. The model calls cents_to_dollars once per name, the replace filter strips the suffix for the alias, and placing order_id after the loop lets every generated line end in a comma with no loop.last check. A fourth prefix in the macro's list adds a fourth column to every model that loops over it, and no model file changes.

About this lesson

This section is part of the Jinja and Macros: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.