Variable References & Expressions¶

Variables can be referenced in queries, filters, and expressions. Dataface supports simple references and Jinja-style expressions.

Simple Variable References¶

The easiest way to use variables:

queries:
  sales:
    metrics: [total_revenue]
    filters:
      region: region              # Direct reference to 'region' variable
      min_revenue: min_revenue    # Direct reference to 'min_revenue' variable

When you reference a variable directly, Dataface automatically: - Uses the variable's current value - Updates the query when the variable changes - Handles type conversion when needed

Jinja Expressions¶

For more complex cases, use Jinja syntax (similar to dbt):

queries:
  sales:
    metrics: [total_revenue]
    filters:
      # Basic reference
      order_date: "{{ date_range }}"

      # With default value
      region: "{{ region or 'North' }}"

      # With formatting
      order_date: "{{ date_range | date('%Y-%m-%d') }}"

      # With calculation
      min_revenue: "{{ min_revenue * 1.1 }}"

      # Conditional
      status: "{{ 'active' if include_inactive else 'completed' }}"

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 = '{{ variables.region }}'
    profile: 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 can be referenced namescaped under variables.<variable_name> or just directly with <variable_name>.

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 %}
    profile: 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.

The `filter` Function¶

filter(column: str, operator: str, value: Any, condition: bool | None = None) -> str

The filter function generates a SQL condition if the value is set (or the supplied condition is truthy), or returns 1=1 (true) if it is not. When you omit the condition, it defaults to checking that value is not None/undefined.

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

Arguments: 1. column: The database column to filter on. 2. operator: The SQL operator (e.g., =, >, LIKE, IN). Can be a variable. 3. value: The variable or value to test. 4. condition (optional): A boolean expression. If False, the filter is skipped (returns 1=1). If omitted, it defaults to checking whether value is defined (not None/undefined).

Examples¶

Basic Usage:

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE {{ filter('region', '=', region) }}
        AND {{ filter('total_revenue', '>=', min_revenue) }}
    profile: 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_op, age_val) }}
    profile: my_postgres

Conditional Application:

Use the 4th argument if you want to custom control exactly when a filter applies. By default it applies when all inputs are defined.

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE {{ filter('region', '=', region, region is not none) }}
    profile: my_postgres

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

Supported Operators¶

The filter function accepts any string as an operator. It does not validate against a fixed list, allowing you to use database-specific operators (e.g., ILIKE, ~, contains, @>).

Array Handling¶

As a SQL reminder, if the variable is an array (e.g., from a multiselect input), be sure to use the IN operator. Here we've made the region variable a multi-select and applied the filter using the SQL operator IN.

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

queries:
  sales:
    sql: |
      SELECT * FROM orders
      WHERE {{ filter('region', 'IN', regions) }}
    profile: 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, condition: bool | None = None, min_inclusive: bool = False, max_inclusive: bool = False) -> str

variables:
  date_range:
    data_type: date
    input: daterange

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

      -- Inclusive filtering (using <= and >=)
      WHERE {{ filter_date_range('order_date', date_range, min_inclusive=True, max_inclusive=True) }}
      -- Generates: WHERE order_date >= '2024-01-01' AND order_date <= '2024-01-31'
    profile: my_postgres

By default these ranges are non-inclusive meaning they use < and >. If you want them to be inclusive (using <= and >=) there are two boolean fields min_inclusive and max_inclusive that you can set.

Complete SQL Query Example¶

variables:
  region:
    data_type: string
    input: select
    options:
      static: ["North", "South", "East", "West"]

  date_range:
    data_type: date
    input: daterange

  min_revenue:
    data_type: number
    input: slider
    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
    profile: 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.start }} OR {{ date_range.start }} IS NULL)
        AND (created_at <= {{ date_range.end }} OR {{ date_range.end }} IS NULL)
        AND (total_revenue >= {{ min_revenue }} OR {{ min_revenue }} IS NULL)
    profile: 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) }}
    profile: my_postgres

Much cleaner and easier to read!

Expression Patterns¶

Date Formatting¶

Format dates for database queries:

queries:
  sales:
    metrics: [total_revenue]
    filters:
      order_date: "{{ date_range | date('%Y-%m-%d') }}"

Calculations¶

Perform calculations on variable values:

queries:
  sales:
    metrics: [total_revenue]
    filters:
      min_revenue: "{{ min_revenue * 1.1 }}"  # 10% buffer

Conditionals¶

Use conditionals for dynamic logic:

queries:
  sales:
    metrics: [total_revenue]
    filters:
      status: "completed"
      region: "North"

Null Handling¶

Handle null or empty values:

queries:
  sales:
    metrics: [total_revenue]
    filters:
      # Using filter function (recommended - handles null automatically)
      region: "{{ filter('region', '=', region) }}"

      # Manual null handling (if you need custom logic)
      region: "{{ region or 'North' }}"
      min_revenue: "{{ min_revenue or 0 }}"

Recommendation: Use the filter macro instead of manual null handling - it's cleaner and handles edge cases automatically.

Using Variables in Queries¶

Filter Conditions¶

Reference variables in query filters:

variables:
  region:
    data_type: string
    input: select
    options:
      static: ["North", "South", "East", "West"]

queries:
  sales:
    metrics: [total_revenue]
    dimensions: [month]
    filters:
      region: region  # Simple reference
      order_date: "{{ date_range }}"  # Jinja expression

Dynamic Values¶

Use expressions for dynamic values:

queries:
  sales:
    metrics: [total_revenue]
    filters:
      order_date: "{{ date_range.start }}"
      min_revenue: "{{ min_revenue * 1000 }}"

Using Variables in Charts¶

Chart-Level Filters¶

Override query filters at the chart level:

charts:
  filtered_chart:
    query: queries.sales
    type: bar
    x: month
    y: total_revenue
    filters:
      region: region  # Chart-specific filter

Interaction Targets¶

Set variables from chart interactions:

charts:
  month_selector:
    query: queries.sales
    type: bar
    x: month
    y: total_revenue
    interactions:
      click:
        action: set_variable
        target: "selected_month"  # Variable to update
        value_map:
          field: "month"

Variable Reference Syntax¶

Simple (Recommended)¶

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

Jinja (Advanced)¶

{{ var_name }} - Jinja reference
{{ dashboard.var_name }} - Explicit dashboard scope (optional)

When to Use Jinja¶

Use Jinja expressions when you need:

Default values: North
Formatting: {{ date_range | date('%Y-%m-%d') }}
Calculations: {{ min_revenue * 1.1 }}
Conditionals: inactive

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

Best Practices¶

Prefer Simple References¶

Use direct variable references when possible:

# Good: Simple and clear
filters:
  region: region

# Avoid: Unnecessary Jinja
filters:
  region: "{{ region }}"

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
filters:
  region: "*"

# Avoid: Too complex
filters:
  region: "*"

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