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 ReOrc 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 cents_to_dollars(column_name, scale=2) %}
    ({{ column_name }} / 100)::numeric(16, {{ scale }})
{% endmacro %}

The snippet above defines a macro named cents_to_dollars which converts cent amount to dollar amount by specify two parameters: column_name and scale (optional parameter).

Define a macro

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

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

2. Click + Add New.

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

   
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, we can apply the cents_to_dollars macro as below:

select
  id as payment_id,
  {{ cents_to_dollars('amount') }} as amount_usd,
  ...
from {{ ref("stg_payments") }}

Use macros from a package

In addition to user-defined macros and built-in functions, you can get access to a wealth of predefined macros via dbt packages. These packages offer the resources and macros designed for common transformation use cases, helping you speed up development and avoid reinventing the wheel.

For details on importing packages, see: Packages and dependencies.

To call a macro imported from a package, simply prefix the call with the package name:

select 
    order_id,
    {{ dbt_utils.safe_add(['subtotal', 'tax', 'shipping_fee']) }} as total_amount,
from {{ source('jaffle_shop', 'raw_orders' }}