variables.sql

Configure your pipeline with SQL

Variables — configure your pipeline with SQL

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

Variables let you define configuration inside your pipeline.

No YAML files. No separate config system. Just SQL.

Use variables for:

• Business logic (currency, tax rates, thresholds)
• Environment-specific settings
• Retention policies and limits
• Values reused across models and scripts

Variables are part of the runtime:

• .env — external configuration (secrets, credentials)
• variables.sql — pipeline logic and behavior

Built-in variables (ondatra_run_time, ondatra_load_id, snapshots) are set automatically.

Examples

Business config

SET VARIABLE default_currency = 'SEK';
SET VARIABLE vat_rate = 0.25;
SET VARIABLE fiscal_year_start = DATE '2024-07-01';

Policies

SET VARIABLE archive_after_days = 365;
SET VARIABLE delete_after_days = 730;

Dynamic values

-- Derived from data
SET VARIABLE latest_order_date = (
    SELECT MAX(order_date) FROM mart.orders
);

Usage

Variables are available everywhere:

• SQL models — getvariable('name')
• Scripts — getvariable("name")

SELECT *
FROM staging.orders
WHERE currency = getvariable('default_currency')

currency = getvariable("default_currency")  # "SEK"

Variables are evaluated at runtime — not templated. They behave like values in the database, not string substitutions.

If a variable is not set:

• SQL — returns NULL
• Scripts — returns empty string

For advanced usage, see the DuckDB variable documentation.