Macros

Macros in Ascend SQL Transforms let you implement logic once and reuse it across multiple Components, making your data pipelines more maintainable and consistent.

Organization

Macros must be defined in .sql files within a macros directory in your Project root:

Screenshot

File tree

macros/
└── utils.sql

For example, if you have a macro called do_something that accepts one column argument, you would import and use it in a SQL Transform like this:

{% from 'macros/utils.sql' import do_something %}

SELECT
    {{ do_something('column') }}
FROM {{ref ('my_table' )}}

Example

This example creates a macro for data standardization that converts text to lowercase and trims whitespace. This avoids duplicating formatting logic across multiple Components that need consistent data cleaning.

Definition

macros/utils.sql

{% macro standardize(col_name) %}
    LOWER(TRIM({{ col_name }}))
{% endmacro %}

Parameters

• col_name: Name of the column to standardize

Logic

• Converts the column value to lowercase using LOWER()
• Removes leading and trailing whitespace using TRIM()
• Returns the cleaned column value for consistent data formatting

Usage

standardize_goat_names.sql

{% from 'macros/utils.sql' import standardize %}

SELECT
    id,
    {{ standardize('name') }} AS clean_name,
    breed,
    age,
    route
FROM {{ ref('read_goats', flow='extract-load-duckdb') }}

This example imports the standardize macro and applies it to clean the name column, ensuring consistent formatting across the dataset.