Template variables

Template expressions let one pipeline definition work for many values: a date, a region, a threshold. You write them in double braces with Jinja syntax, and Beetl replaces them with text before any SQL runs. The Pipelines concept page has the short version; this page has the details.

Where templates work

Source aliases, stage names, the storage tier and partition columns are plain text. A run mapping uses its own source.* values instead of run.*; see The run mapping.

Inside {{ }} in stage SQL, the editor suggests your variable names, the run.* names your sources provide, and the fn.* helpers.

vars: values stored with the pipeline

Pipeline variables live in the Variables panel at the top of the editor. Choose Add variable, then enter a name and a value. Use them for anything you would otherwise hard-code: dates, rates, thresholds, a region.

• A name starts with a letter or underscore, continues with letters, digits and underscores, is at most 128 characters, and is unique in the pipeline. event, vars, fn and secret are reserved.
• Every value is a non-empty string. To do arithmetic in the template, convert it with a filter, for example {{ vars.lookback_days|int + 1 }}.
• Variables are part of the pipeline definition. A changed value reaches deployed runs only after you save and deploy.
• Variables stay with their pipeline. A pipeline triggered by this one's destination does not see them.

run: values for one run

run.* values change from run to run, and none of them is stored in the definition. Every run.* name referenced anywhere in the templates is required, and each run gets its values from one of these places:

Run value names follow the same rules as variable names, and every value must be non-empty.

fn: helpers

Beetl evaluates helpers each time it renders the pipeline, so every call returns a fresh value: two fn.uuid() calls in one stage differ, and fn.now() in a stage and in a predicate can differ by a moment. Helpers describe when the run happens, not which data it processes. A run triggered by an update to yesterday's partition still gets today's fn.today(), so use run.dt when the date should follow the data.

Quoting

Beetl inserts each value as plain text, exactly as written, without adding quotes or escaping anything. Put the quotes in your SQL:

SELECT *
FROM orders
WHERE region = '{{ vars.region }}'                -- string: quote it
  AND order_date >= DATE '{{ run.dt }}'           -- date: typed literal
  AND amount > {{ vars.min_amount }}              -- number: no quotes

A value that contains a single quote ends the SQL string early and breaks the statement, so keep values to dates, numbers, codes and identifiers.

Missing values

Rendering is strict. If a template references a value that does not exist, the preview, deploy or run fails before any SQL runs; Beetl never substitutes an empty string. The error names the variable when it can:

Missing pipeline variable: vars.region. Add it in Variables or remove the template reference.
Missing run variable: run.dt. Provide it in the run context or source mapping.

A misspelled name counts as missing. So does a reference inside an SQL comment, because Beetl renders templates before it reads the SQL: -- WHERE dt = '{{ run.dt }}' still requires run.dt. Delete commented-out references instead of keeping them.

Literal braces

{{ }}, {% %} and {# #} are template syntax anywhere in a templated field. If your SQL needs a literal {{, for example inside a JSON string, write it as an expression that outputs the text:

SELECT '{{ '{{' }}placeholder}}' AS literal_braces

Examples

Incremental by partition

The source orders is partitioned by dt and has Trigger on update on, so each write to a day starts a run with that day in run.dt.

SELECT order_id, customer_id, amount, dt
FROM orders
WHERE dt = DATE '{{ run.dt }}'

Set the destination to Overwrite with the predicate dt = '{{ run.dt }}' and partition it by dt. Each run replaces only the day it was given, and the rest of the destination stays as it was. See Overwrite with a predicate.

One pipeline per region

Add a variable region with the value eu, filter on it, and use it in the destination name orders_{{ vars.region }}.

SELECT order_id, customer_id, amount, region
FROM orders
WHERE region = '{{ vars.region }}'

This pipeline writes orders_eu. A second pipeline with the same stages and region set to us writes orders_us, with its own deploys and run history.

Rolling window

Add a variable lookback_days with the value 30 to keep the last 30 days, counted from the day the run happens:

SELECT customer_id, SUM(amount) AS revenue_30d
FROM orders
WHERE order_date >= DATE '{{ fn.today() }}' - INTERVAL '{{ vars.lookback_days }} days'
GROUP BY customer_id

Pair it with a full-table overwrite so each run replaces the previous window.

Related