Variable References & Expressions¶

Variables are referenced inside your SQL with Jinja expressions: from simple substitution to conditionals, calculations, and the parameterized filter() helper.

Simple Variable References¶

The easiest way to use a variable is directly in your SQL:

variables:
  priority:
    input: text
queries:
  tickets:
    sql: |
      SELECT status, COUNT(*) AS ticket_count FROM dundersign.tickets
      WHERE priority = '{{ priority }}'
      GROUP BY status
    source: my_postgres

When you reference a variable, dbt Charts automatically: - Uses the variable's current value, bound as a query parameter - Re-runs the query (and updates its charts) when the variable changes

Jinja Expressions¶

For more complex cases, the full power of Jinja is available inside the SQL (similar to dbt):

queries:
  tickets:
    sql: |
      SELECT status, COUNT(*) AS ticket_count FROM dundersign.tickets
      WHERE created_at >= '{{ date_range[0] }}'                        -- date_range is a [start, end] list
        AND priority = '{{ priority or "normal" }}'                    -- default value
        AND ticket_type = '{{ "incident" if include_incidents else "task" }}'  -- conditional
      GROUP BY status
    source: my_postgres

Filtering with Variables¶

You can connect dbt Charts variables to your SQL using Jinja's conditional logic, which should be familiar to anyone using dbt. You can utilize the full power of Jinja2's logic giving you full control when you need it.

Simple Variable Replacement¶

The simplest way to filter is direct substitution:

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE status = '{{ status }}'
    source: my_postgres

Above we use Jinja2 template syntax to insert the status variable into the query. Whenever the variable changes, this query will change and re-run, automatically updating any charts utilizing it.

Variables are referenced directly by name; there's no variables. or other namespace prefix.

Conditional Filtering¶

Often variables will be unset by default, in which case we don't want to apply a filter the the query. For instance by default we may want a dashboard to show all regions and have the variable there for users who want to drill down.

To handle unset variables, we can use Jinja if blocks. This is the standard way to write dynamic SQL:

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE 1=1
      {% if status %}
        AND status = '{{ status }}'
      {% endif %}
    source: my_postgres

This works but is verbose, especially as you get into complexities of a filter allowing null and checking the difference between undefined and null and none values.

{% if variable is defined %} - Check if variable exists
{% if variable is not none %} - Check if variable has a value
{{ variable | default('val') }} - Provide fallbacks

To make this cleaner, we've created a few helper macros that will conditionally apply the filter.

Filter Macros¶

dbt Charts provides the filter macro to simplify this common pattern. It handles the conditional logic, null checking, and syntax for you.

Security Note: Filter macros use parameterized queries to prevent SQL injection. Variable values are passed as parameters to the database, never interpolated directly into SQL strings. See Queries: Parameterized Queries for details.

The `filter` Function¶

filter(column: str, value: Any, operator: str = '=', none: str = 'allow') -> str

The filter function generates a parameterized SQL condition when the value is set. When the value is unset (None, empty string, or an empty list), it returns 1=1 (no constraint, show all rows) by default, or 1=0 (show nothing) with none='deny'.

Syntax: {{ "{{" }} filter('<column>', <value>, ['<operator>']) {{ "}}" }}

Arguments: 1. column: The database column to filter on (validated as an identifier). 2. value: The variable or value to test; always bound as a query parameter. A list value automatically becomes an IN (...) clause. 3. operator (optional, default =): The SQL operator (for example, >=, LIKE). Validated against an allowlist. Can be a variable. 4. none (keyword-only, default 'allow'): What an unset value means: 'allow' emits 1=1 (unfiltered), 'deny' emits 1=0 (no rows).

Examples¶

Basic Usage:

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE {{ filter('status', status) }}
        AND {{ filter('priority', priority) }}
    source: my_postgres

Dynamic Operator:

Sometimes you may want to change the operator applied to the filters. Say for example you have a dashboard with a variable age. You may want to filter the dashboard by all users below that age, or equal to it, or above it.

You can do this simply by making another variable for the operator allowing users to choose between "Greater than", "Less than", or "Equal to" in the UI.

variables:
  age_op:
    input: text
    default: ">="
  age_val:
    input: number
    default: 21

queries:
  users:
    sql: |
      SELECT * FROM users
      WHERE {{ filter('age', age_val, age_op) }}
    source: my_postgres

Unset Means Nothing, or Everything:

An unset filter is ambiguous: does no selection mean show all rows or show none? filter() defaults to show-all (1=1); pass none='deny' when an empty selection should return no rows (for example, permission-style filters):

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE {{ filter('status', status, none='deny') }}
    source: my_postgres

For dependent inputs, conditional layouts, and other advanced behavior, see Advanced Variables.

Supported Operators¶

The filter function validates operators against an allowlist to prevent SQL injection. Supported operators include:

Comparison: =, !=, <>, >, <, >=, <=
Pattern matching: LIKE, NOT LIKE, ILIKE, NOT ILIKE
Set operations: IN, NOT IN
Null checks: IS, IS NOT
Range: BETWEEN, NOT BETWEEN
PostgreSQL regex: ~, ~*, !~, !~*

Using an unsupported operator will raise an error at query execution time. The allowlist is what filter() accepts, not what every warehouse runs. ILIKE exists on Postgres, Redshift, DuckDB, Snowflake, Databricks and Spark; the ~ regex operators only on Postgres, Redshift and DuckDB. BigQuery, MySQL, SQL Server, SQLite and Trino reject ILIKE, and every warehouse outside that Postgres family rejects ~. On those, write the case-folding in SQL (LOWER(name) LIKE LOWER({{ q }})).

Date values. When the value is a calendar date (a date or datepicker variable, or a daterange endpoint), filter() compares the column as a DATE the same way filter_date_range() does: CAST(created_at AS DATE) >= DATE '2024-01-15'. A TIMESTAMP column then works on every warehouse, and a row at 13:00 on the chosen day counts as that day. A list of dates gets the same treatment; a list that mixes dates with other values is an error. Because the column is wrapped in a cast, a plain index on it no longer applies, and whether a warehouse still prunes partitions on the wrapped column is up to that warehouse; compare a DATE column when that cost matters.

Lists. A list value takes IN (the default) or NOT IN; an ordering operator (>=, <, ...) with a list is an error rather than a silent IN.

Array Handling¶

If the variable is an array (for example, from a multiselect input), filter() emits an IN (...) clause automatically; no operator needed:

variables:
  statuses:
    default: ["new", "solved"]

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE {{ filter('status', statuses) }}
    source: my_postgres

Date Range Filtering¶

Date ranges are another common filter that we've made simpler with the filter_date_range function (specialized macro):

filter_date_range(column: str, value: DateRange) -> str

The column is compared as a DATE, so a TIMESTAMP column works without an author-supplied cast, and BigQuery no longer rejects comparing a TIMESTAMP column against DATE bounds directly. The exact SQL is chosen per warehouse (CAST(... AS DATE) on most, DATE(...) on SQLite, which has no DATE type). Because the column is truncated to a date, the range is inclusive of the whole end day: a TIMESTAMP at 13:00 on the end date still matches.

variables:
  date_range:
    input: daterange

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE {{ filter_date_range('created_at', date_range) }}
      -- Generates: WHERE CAST(created_at AS DATE) BETWEEN '2024-01-01' AND '2024-01-31'
    source: my_postgres

Complete SQL Query Example¶

variables:
  priority:
    options:
      static: ["low", "normal", "high", "urgent"]

  date_range:
    input: daterange

queries:
  tickets:
    sql: |
      SELECT
        status,
        priority,
        COUNT(*) as ticket_count
      FROM dundersign.tickets
      WHERE {{ filter('priority', priority) }}
        AND {{ filter_date_range('created_at', date_range) }}
      GROUP BY 1, 2
      ORDER BY ticket_count DESC
      LIMIT 100
    source: my_postgres

Comparison: Before and After¶

Before (boilerplate):

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE status = 'solved'
        AND (priority = {{ priority }} OR {{ priority }} IS NULL)
        AND (created_at >= {{ date_range[0] }} OR {{ date_range[0] }} IS NULL)
        AND (created_at <= {{ date_range[1] }} OR {{ date_range[1] }} IS NULL)
    source: my_postgres

After (with filter functions):

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE status = 'solved'
        AND {{ filter('priority', priority) }}
        AND {{ filter_date_range('created_at', date_range) }}
    source: my_postgres

Much cleaner and easier to read!

Expression Patterns¶

Date Range Indexing¶

A daterange variable resolves to a plain [start, end] list, not an object, index into it directly (there's no date_range.start/.end, and no date Jinja filter):

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE created_at >= '{{ date_range[0] }}'
        AND created_at <= '{{ date_range[1] }}'
    source: my_postgres

For the common BETWEEN case, prefer the filter_date_range() macro (below) over manual indexing.

Calculations¶

Perform calculations on variable values:

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE due_at >= '{{ date_range[0] }}'  -- start of window
    source: my_postgres

Conditionals¶

Use conditionals for dynamic logic:

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE status = '{{ "new" if include_open else "solved" }}'
        AND priority = '{{ priority if priority else "normal" }}'
    source: my_postgres

Null Handling¶

Handle null or empty values:

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      -- Using filter() (recommended; unset variables handled automatically)
      WHERE {{ filter('priority', priority) }}
      -- Manual defaults (if you need custom logic)
        AND priority = '{{ priority or "normal" }}'
    source: my_postgres

Recommendation: Use the filter() helper instead of manual null handling; it's cleaner and handles edge cases automatically.

Using Variables in Queries¶

Filter Conditions¶

Reference variables in WHERE clauses:

variables:
  status:
    options:
      static: ["new", "solved"]
  date_range:
    input: daterange
    default: ["2024-01-01", "2024-12-31"]

queries:
  tickets:
    sql: |
      SELECT status, COUNT(*) AS ticket_count FROM dundersign.tickets
      WHERE {{ filter('status', status) }}
        AND {{ filter_date_range('created_at', date_range) }}
      GROUP BY status
    source: my_postgres

Dynamic Values¶

Use expressions for dynamic values:

queries:
  tickets:
    sql: |
      SELECT * FROM dundersign.tickets
      WHERE created_at >= '{{ date_range[0] }}'
        AND {{ filter('priority', priority) }}
    source: my_postgres

Using Variables in Charts¶

Interaction Targets¶

Setting variables from chart clicks is planned but is not part of the authored chart schema today. Strict chart validation rejects interactions: blocks. For now, use variables in query SQL, and use chart-level link: when a click should navigate to another page.

Variable Reference Syntax¶

Simple (Recommended)¶

var_name - Direct reference
"var_name" - Quoted (if value must be string)

Jinja (Advanced)¶

{{ var_name }} - Jinja reference (no namespace prefix, bare name only)

When to Use Jinja¶

Use Jinja expressions when you need:

Default values: {{ priority or 'normal' }}
List indexing: {{ date_range[0] }}: a daterange variable is a [start, end] list
Calculations: {{ "{{" }} min_amount * 1.1 {{ "}}" }}
Conditionals: {{ 'active' if flag else 'inactive' }}

For simple cases, direct references are cleaner and easier to read.

Best Practices¶

Prefer the Helper Over Hand-Rolled Conditionals¶

{{ filter('status', status) }} handles unset values, lists, and parameter binding in one call; reach for it before writing {% if %} blocks by hand.

Use Jinja for Complex Logic¶

Use Jinja when you need: - Conditional logic - Calculations - Formatting - Default values

Keep Expressions Simple¶

Complex expressions can be hard to understand and maintain:

-- Good: Clear and readable
WHERE priority = '{{ priority if priority else 'normal' }}'

-- Avoid: Too complex
WHERE priority = '{{ priority if priority and priority in ['low', 'normal', 'high', 'urgent'] else 'normal' }}'

Test Expressions¶

Test your expressions with different variable values to ensure they work correctly.

Variables – Defining variables
UI Elements – All input types and options
Advanced Variables – Dependent variables and conditional layouts
Queries – Using variables in queries
Charts – Using variables in charts