Macros (macros/)

Macros are reusable Jinja templates that can be called from your SQL models. They help you DRY (Don’t Repeat Yourself) your SQL code by encapsulating common patterns, calculations, or logic into reusable snippets. If you are new to Jinja, the following are some resources to get started:

File structure

Macros are stored in the macros/ directory at the root of your Squirrels project:

macros/
├── metrics.sql           # macro definitions
└── subdirectory/         # macros can be nested in subdirectories
    └── utils.sql

Macros are loaded recursively from the macros/ directory and its subdirectories. All macros from all files are available globally across your SQL models.

Supported file extensions

Squirrels loads macro files with any of the following extensions:

.sql
.j2
.jinja
.jinja2

All files with these extensions in the macros/ directory (and subdirectories) are automatically loaded when the project initializes.

Creating macros

Macros use Jinja’s {% macro %} syntax. Here’s the basic structure:

macros/my_macros.sql

{%- macro macro_name(arg1, arg2) -%}
    -- SQL or Jinja code here
    {{ arg1 }} = {{ arg2 }}
{%- endmacro -%}

Basic example

Here’s a simple macro that generates filter conditions:

macros/filters.sql

{%- macro date_range_filter(column, start_date, end_date) -%}
    {{ column }} >= {{ start_date | quote }}
    AND {{ column }} <= {{ end_date | quote }}
{%- endmacro -%}

Using macros in models

Macros can be used in any SQL model (builds, dbviews, or federates) by calling them with {{ macro_name(args) }}:

models/federates/fed_my_model.sql

SELECT *
FROM {{ ref("my_table") }}
WHERE {{ date_range_filter("date", ctx.start_date, ctx.end_date) }}

Using the `indent` filter

When embedding multi-line macro output, use the indent filter to maintain proper SQL formatting:

SELECT
    column1,
    {{ my_macro() | indent(4) }}
FROM table

The number passed to indent specifies the number of spaces to add to each line after the first.

Built-in Jinja filters

Squirrels provides several useful Jinja filters for use in macros and models:

filter

Wraps a value in quotes. Accepts optional parameters q (quote character, default: ') and attribute (attribute name if variable is an object, default: None).

{{ ctx.category | quote }}          -- 'Electronics'
{{ ctx.category | quote(q='"') }}   -- "Electronics"
{{ ctx.object | quote(q='"', attribute="name") }}

filter

Joins a list of values with a delimiter. Accepts optional parameters d (delimiter, default: ,) and attribute (attribute name if items are objects, default: None).

{{ ctx.categories | join }}              -- Food, Bills, Shopping
{{ ctx.categories | join(d=' OR ') }}    -- Food OR Bills OR Shopping
{{ ctx.objects | join(attribute="name") }}

filter

Quotes each value in a list and joins them with a delimiter. Accepts optional parameters q (quote character, default: '), d (delimiter, default: ,), and attribute (attribute name if items are objects, default: None).

{{ ctx.categories | quote_and_join }}    -- 'Food', 'Bills', 'Shopping'

Macros from packages

If you use Squirrels packages (defined in squirrels.yml), macros from those packages are also available. Package macros are loaded from their respective macros/ directories before your project’s macros.

squirrels.yml

packages:
  - git: https://github.com/org/shared-analytics.git
    revision: v1.0.0
    directory: shared_analytics

Macros from sqrl_packages/shared_analytics/macros/ will be available in your models.

When package macros and project macros have the same name, the project macro takes precedence since project macros are loaded last.

Build models - Static models that use macros
Dbview models - Dynamic SQL models that use macros
Federate models - Dynamic models that use macros

Get started

Concepts

Project files

File structure

Supported file extensions

Creating macros

Basic example

Using macros in models

Using the `indent` filter

Built-in Jinja filters

Macros from packages

​File structure

​Supported file extensions

​Creating macros

​Basic example

​Using macros in models

​Using the indent filter

​Built-in Jinja filters

​Macros from packages

​Related pages

File structure

Supported file extensions

Creating macros

Basic example

Using macros in models

Using the `indent` filter

Built-in Jinja filters

Macros from packages

Related pages