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:

queries:
  sales:
    sql: |
      SELECT month, SUM(revenue) AS total_revenue FROM orders
      WHERE region = '{{ region }}'
      GROUP BY month
    source: my_postgres

When you reference a variable, Dataface 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:
  sales:
    sql: |
      SELECT month, SUM(revenue) AS total_revenue FROM orders
      WHERE order_date >= '{{ date_range[0] }}'                   -- date_range is a [start, end] list
        AND region = '{{ region or 'North' }}'                    -- default value
        AND revenue >= {{ min_revenue * 1.1 }}                    -- calculation
        AND status = '{{ 'active' if include_inactive else 'completed' }}'  -- conditional
      GROUP BY month
    source: my_postgres

Filtering with Variables¶

You can connect Dataface variables to your SQL using Jinja's conditional logic, which should be familar 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:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE orders.region = '{{ region }}'
    source: my_postgres

Above we use Jinja2 template syntax to insert the region 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:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE 1=1
      {% if region %}
        AND region = '{{ region }}'
      {% endif %}
    source: my_postgres

This works but is verbose, espeically 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¶

Dataface 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 (e.g., >=, 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:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE {{ filter('region', region) }}
        AND {{ filter('total_revenue', min_revenue, '>=') }}
    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:
    data_type: string
    default: ">="
  age_val:
    data_type: 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 (e.g. permission-style filters):

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE {{ filter('region', region, 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.

Array Handling¶

If the variable is an array (e.g., from a multiselect input), filter() emits an IN (...) clause automatically — no operator needed:

variables:
  regions:
    data_type: array
    default: ["North", "South"]

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE {{ filter('region', regions) }}
    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

variables:
  date_range:
    input: daterange

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE {{ filter_date_range('order_date', date_range) }}
      -- Generates: WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
    source: my_postgres

Complete SQL Query Example¶

variables:
  region:
    options:
      static: ["North", "South", "East", "West"]

  date_range:
    input: daterange

  min_revenue:
    min: 0
    max: 1000000

queries:
  sales:
    sql: |
      SELECT
        DATE_TRUNC('month', created_at) as month,
        region,
        SUM(revenue) as total_revenue,
        COUNT(*) as order_count
      FROM orders
      WHERE status = 'completed'
        AND {{ filter('region', region) }}
        AND {{ filter_date_range('created_at', date_range) }}
        AND {{ filter('total_revenue', min_revenue, '>=') }}
      GROUP BY 1, 2
      ORDER BY month DESC, total_revenue DESC
      LIMIT 100
    source: my_postgres

Comparison: Before and After¶

Before (boilerplate):

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE status = 'completed'
        AND (region = {{ region }} OR {{ region }} 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)
        AND (total_revenue >= {{ min_revenue }} OR {{ min_revenue }} IS NULL)
    source: my_postgres

After (with filter functions):

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE status = 'completed'
        AND {{ filter('region', region) }}
        AND {{ filter_date_range('created_at', date_range) }}
        AND {{ filter('total_revenue', min_revenue, '>=') }}
    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:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE order_date >= '{{ date_range[0] }}'
        AND order_date <= '{{ 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:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE revenue >= {{ min_revenue * 1.1 }}  -- 10% buffer
    source: my_postgres

Conditionals¶

Use conditionals for dynamic logic:

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE status = '{{ 'active' if include_inactive else 'completed' }}'
        AND region = '{{ region if region else 'North' }}'
    source: my_postgres

Null Handling¶

Handle null or empty values:

queries:
  sales:
    sql: |
      SELECT * FROM orders
      -- Using filter() (recommended — unset variables handled automatically)
      WHERE {{ filter('region', region) }}
      -- Manual defaults (if you need custom logic)
        AND backup_region = '{{ region or 'North' }}'
        AND revenue >= {{ min_revenue or 0 }}
    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:
  region:
    options:
      static: ["North", "South", "East", "West"]

queries:
  sales:
    sql: |
      SELECT month, SUM(revenue) AS total_revenue FROM orders
      WHERE {{ filter('region', region) }}
        AND {{ filter_date_range('order_date', date_range) }}
      GROUP BY month
    source: my_postgres

Dynamic Values¶

Use expressions for dynamic values:

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE order_date >= '{{ date_range[0] }}'
        AND revenue >= {{ min_revenue * 1000 }}
    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: {{ region or 'North' }}
List indexing: {{ date_range[0] }} — a daterange variable is a [start, end] list
Calculations: {{ "{{" }} min_revenue * 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('region', region) }} 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 region = '{{ region if region else '*' }}'

-- Avoid: Too complex
WHERE region = '{{ region if region and region in ['North', 'South', 'East', 'West'] else '*' }}'

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