Template Expressions

Template expressions let you write dynamic SQL that reads dashboard controls (via {{ param(...) }}), reads active filters (via {{ filter(...) }}), branches with {% if %} blocks, and expands multi-select values into IN lists. The syntax is a small subset of Jinja, so it will feel familiar if you have used dbt or similar tools.

For simple cases, keep using the dedicated forms: [[param.x]] to substitute a single control value, or {{ filters | where }} to subscribe a card to the dashboard filter bar. Use template expressions when you need conditional SQL, multi-value IN lists, explicit defaults, or logic that combines parameters and filters.

When to use template expressions

Accessors at a glance

param and filter share the core accessors. Date range filters additionally expose .start and .end. Output accessors render SQL text inside {{ ... }}; condition accessors return raw values for use inside {% if ... %} blocks.

Parameter expressions

{{ param('id') }} resolves to the dashboard or card control with the matching ID. Card-scoped controls take precedence over dashboard-scoped controls with the same ID, matching the behavior of [[param.id]].

Single value

SELECT * FROM orders WHERE tenant = {{ param('tenant_id') }}

With tenant_id set to acme_corp:

SELECT * FROM orders WHERE tenant = 'acme_corp'

Numeric and boolean controls render without quotes:

SELECT {{ param('account_id') }} AS account_id

With account_id set to 42:

SELECT 42 AS account_id

List expansion

Multi-select controls render as parenthesized SQL lists ready for IN:

SELECT category, COUNT(*) AS order_count
FROM orders
WHERE id IN {{ param('ids').list }}
GROUP BY category

With ids set to [1, 2, 3]:

SELECT category, COUNT(*) AS order_count
FROM orders
WHERE id IN (1, 2, 3)
GROUP BY category

The simpler WHERE id IN ([[param.ids]]) form works for the same case. Use .list when you want the parentheses emitted automatically.

Defaults

Pass a second argument to param to supply a fallback when the control has no value:

SELECT {{ param('grain', 'month') }} AS grain

If the viewer has not set grain:

SELECT 'month' AS grain

Guarding on .present

To branch on whether a control has a value, use .present:

SELECT * FROM orders
{% if param('region').present %}
  WHERE region = {{ param('region') }}
{% endif %}

Use not param('x').present to run logic specifically when a control is empty -- for example, to apply a default row limit:

SELECT * FROM orders
ORDER BY order_date DESC
{% if not param('row_limit').present %}
  LIMIT 100
{% endif %}

A few behaviors worth knowing:

• Optional controls with no value resolve to NULL under the [[param.x]] syntax, but report .present == false under template expressions. That is the cleanest way to skip a predicate entirely.
• If a control is required and the viewer has not made a selection, the card shows the same error state as today. The query does not run, regardless of which syntax you use.

Filter expressions

{{ filter('name') }} complements the {{ filters | where }} helper. Use it when you need a specific filter's value, a list of its selected values, or conditional logic that depends on whether the filter is active.

Single value

SELECT * FROM orders WHERE region = {{ filter('region') }}

SELECT * FROM orders WHERE region = 'West'

List expansion

SELECT * FROM orders WHERE status IN {{ filter('status').list }}

SELECT * FROM orders WHERE status IN ('open', 'closed')

Reusing date range bounds on another field

A date range filter contains a start bound and an end bound. Use the filter-only .start and .end output accessors when the dashboard filter is defined on one date field but the card SQL needs to apply the selected range to another date or timestamp field.

For example, suppose the dashboard filter is named order_date, but this card needs to constrain shipped_at:

SELECT *
FROM orders
WHERE shipped_at BETWEEN {{ filter('order_date').start }}
                     AND {{ filter('order_date').end }}

With order_date set to January 1–31, 2026:

SELECT *
FROM orders
WHERE shipped_at BETWEEN '2026-01-01'
                     AND '2026-01-31'

Semaphor SQL-quotes and escapes both bounds. Do not add quotes around the expressions yourself.

Guarding on .present

Gate a predicate on the filter being set:

SELECT * FROM orders
WHERE 1 = 1
  {% if filter('segment').present %}
    AND segment = {{ filter('segment') }}
  {% endif %}

When segment is not active, the {% if %} block is dropped entirely.

Use not filter('x').present to run logic specifically when a filter is missing -- for example, to return zero rows until the viewer picks a tenant:

SELECT * FROM orders
WHERE 1 = 1
  {% if not filter('tenant_id').present %}
    AND 1 = 0
  {% else %}
    AND tenant_id = {{ filter('tenant_id') }}
  {% endif %}

With no tenant_id filter applied:

SELECT * FROM orders
WHERE 1 = 1
    AND 1 = 0

Conditional blocks

{% if %}, {% elif %}, {% else %}, and {% endif %} let you include or exclude SQL fragments based on control and filter state. Conditions can combine param and filter expressions freely.

Grain switcher driven by a control:

SELECT
  {% if param('grain').value == 'day' %}
    DATE_TRUNC('day', order_date)
  {% elif param('grain').value == 'week' %}
    DATE_TRUNC('week', order_date)
  {% else %}
    DATE_TRUNC('month', order_date)
  {% endif %} AS bucket,
  COUNT(*) AS orders
FROM orders
GROUP BY 1

With grain = 'day':

SELECT
  DATE_TRUNC('day', order_date) AS bucket,
  COUNT(*) AS orders
FROM orders
GROUP BY 1

Supported operators inside {% if %}:

• Comparison: ==, !=, <, >, <=, >=
• Boolean: and, or, not
• Membership: in (e.g. {% if 'VIP' in filter('tier').values %})

Combining parameters and filters

A card that combines a required tenant parameter, an optional segment filter, and a grain switcher:

SELECT
  {% if param('grain').value == 'day' %}
    DATE_TRUNC('day', order_date)
  {% else %}
    DATE_TRUNC('month', order_date)
  {% endif %} AS bucket,
  COUNT(*) AS orders
FROM orders
WHERE tenant_id = {{ param('tenant_id') }}
  {% if filter('segment').present %}
    AND segment IN {{ filter('segment').list }}
  {% endif %}
GROUP BY 1
ORDER BY 1

With tenant_id = 'acme_corp', grain = 'day', and a segment filter of ['enterprise', 'mid_market']:

SELECT
  DATE_TRUNC('day', order_date) AS bucket,
  COUNT(*) AS orders
FROM orders
WHERE tenant_id = 'acme_corp'
    AND segment IN ('enterprise', 'mid_market')
GROUP BY 1
ORDER BY 1

Safety

Template expressions SQL-quote and escape values automatically. You never wrap them in quotes yourself.

SELECT * FROM orders WHERE name = {{ param('name') }}

With name set to O'Brien:

SELECT * FROM orders WHERE name = 'O''Brien'

What's supported

Supported:

• param(id) and param(id, default) expressions
• filter(name) expressions
• Accessors: .sql, .list, .list_sql, .value, .values, .present
• Filter-only date range output accessors: .start, .end
• {% if %}, {% elif %}, {% else %}, {% endif %} blocks
• Comparison operators: ==, !=, <, >, <=, >=
• Boolean operators: and, or, not
• Membership: in

Not supported:

• Loops ({% for %})
• Variable assignment ({% set %})
• Raw blocks ({% raw %})
• Macros and custom filter pipelines
• Any template syntax on cached-source connections (S3, S3 Tables, Google Sheets, uploaded files) -- those datasets reject template expressions at save and run time

Related