macros.sql

Reuse SQL without templating

Macros — reuse SQL without templating

Phase: Post-catalog | Order: 5 | Required: No

Macros let you reuse logic across models.

Unlike templating systems, these are real SQL functions — executed by DuckDB, not preprocessed.

No Jinja. No string templating. Just SQL.

Use macros when you want to:

Reuse calculations across models
Encapsulate business logic
Define small helper functions
Avoid repeating SQL

Built-in ondatra_* macros are created automatically — this file is for your own macros.

Examples

Pure computation

-- Simple reusable logic
CREATE OR REPLACE MACRO safe_divide(a, b) AS
    CASE WHEN b = 0 THEN NULL ELSE a / b END;

CREATE OR REPLACE MACRO cents_to_dollars(cents) AS
    cents / 100.0;

Business logic

-- Read from a table
CREATE OR REPLACE MACRO default_currency() AS (
    SELECT currency FROM mart.company_settings LIMIT 1
);

Table macros

-- Return a result set
CREATE OR REPLACE MACRO recent_orders(n) AS TABLE
    SELECT * FROM mart.orders ORDER BY order_date DESC LIMIT n;

Usage in Models

Macros are available in all models automatically.

-- models/mart/revenue.sql
-- @kind: table
SELECT
    order_date,
    safe_divide(total_amount, quantity) AS unit_price,
    cents_to_dollars(total_amount) AS amount_dollars
FROM staging.orders
WHERE currency = default_currency()

-- Table macros work as FROM sources
-- models/mart/recent_summary.sql
-- @kind: view
SELECT
    COUNT(*) AS order_count,
    SUM(amount) AS total
FROM recent_orders(100)

Table references work the same as in models — no special prefixes needed.

Macros are part of the runtime — not a separate layer. They run inside DuckDB and behave like native SQL functions.

Macros are stored in DuckDB’s in-memory catalog and made available to all models automatically. You don’t need to configure anything.

DuckDB supports both scalar macros (return a single value) and table macros (return a result set). For advanced usage, see the DuckDB macro documentation.