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 expose the same set of accessors. 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')

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
• {% 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