Macros

A macro in Jinja is similar to function in programming languages. It allows you to define reusable pieces of code that accept parameters, process logic, and return values. Jinja macros are used in SQL models to encapsulate transformation logic, avoid repetitive SQL code, and improve maintainability.

In Recurve project, you can define macros as assets and use them across different modules and multiple models.

Syntax

Jinja macros are defined within the {% macro %} and {% endmacro %} statements. Similar to Python functions, they accept parameters, process logic, and return values.

For example:

{% macro filter_by_status(min_amount, status='completed') %}
    select *
    from {{ source('jaffle_shop', 'orders') }}
    where status = '{{ status }}'
    and amount >= {{ min_amount }}
{% endmacro %}

The snippet above defines a macro named filter_by_status which queries all order records from the orders table, based on the values of the parameter min_amount and the optional parameter status.

Define a macro

To define a project-level macro, follow these steps:

1. In Recurve, open your project and go to Library > Macro.

2. Click + Add New.

3. Provide the name, description, and SQL code for the macro.

   
4. Click Add.

The new macro will be displayed in the Macro section of Library.

By default, a created macro is active and ready for use in models. You can deactivate the macro by toggling off the Active option.

Call the macro

With the macro defined and active, you can call it from any model in your project. Macros are called with the {{ ... }} expression.

For example, using the filter_by_status macro, we can further filter to get the completed orders with high value.

with filtered_orders as (
   {{ filter_by_status(min_amount=50, status='completed') }}
),

enriched_orders as (
   select 
       order_id,
       amount,
       order_date,
       customer_id,
       case when amount > 100 then 'high_value' else 'standard' end as order_tier
   from filtered_orders
)

select * from enriched_orders