Parameterized Filters

What this covers

A parameterized filter is a named placeholder in a model's query predicates that is resolved to a concrete value at query time. This page explains what parameters are, when to use them, how to define them, how values are resolved, and how BI tools pass parameter values through JDBC session variables.

What parameters solve

Many analytical models include filters that vary by user, session, or audience — a region code, a date range, a product category. Without parameters, the modeller has three unsatisfying options: hard-code a default (inflexible), define separate models per filter value (duplication), or rely on downstream tools to append WHERE clauses (inconsistent).

Parameters give the modeller a single declaration point for each variable filter. The query engine resolves the value from the most authoritative source available, substitutes it into the SQL, and handles type-aware expansion (IN lists, BETWEEN ranges, quoted strings). The model stays clean; the variability moves to the parameter layer.

Parameter types

Type validation happens at resolution time. A multi_value parameter supplied with a single string is auto-wrapped into a one-element list. A number parameter supplied with a non-numeric value fails with a clear error.

Defining a parameter

1. Open a model in Model Builder.
2. Click the Parameters icon in the toolbelt to open the Parameters panel.
3. Click Add Parameter.
4. Fill in:

• Name — must start with @ (e.g., @region, @date_start). The @ prefix is what the query engine scans for during substitution.
• Display name — optional friendly label shown in admin UIs.
• Type — one of the five types above.
• Default value — the value used when no higher-precedence source provides one. Type-dependent: a string for string, a number for number, a JSON array for multi_value, a {"from": ..., "to": ...} object for date_range, or true/false for boolean.
• Allowed values — optional whitelist for string and multi_value types. When set, values outside the list are rejected at resolution time.
• Description — optional documentation.

1. Click Save.

Parameters are unique per model by name. Attempting to create a duplicate name returns HTTP 409.

Resolution order

When a query references a parameter, the engine resolves its value using three sources, in order of precedence (highest first):

1. Persona default filter. If the query is bound to a persona and that persona defines a default filter matching the parameter, the persona's value wins.
2. JDBC session variable. If the BI tool set app.<param_name> via a SET command earlier in the session, that value is used.
3. Model default value. The default defined on the parameter definition.

If no value is available from any source and the parameter has no default, the query fails with a structured error naming the unresolved parameter.

JDBC session variables

BI tools connected via JDBC (port 5433) can set parameter values per session using the standard PostgreSQL SET command:

SET app.region = 'APAC';
SET app.date_start = '2025-01-01';
SET app.date_end = '2025-12-31';

The app. prefix is required. Session variables persist for the lifetime of the JDBC connection and are cleared when the client disconnects.

This mechanism works with any PostgreSQL-compatible client: DBeaver, Tableau (custom SQL), psycopg2, JDBC drivers. The gateway captures the SET commands and passes the values to the query router as part of the query context.

Worked example

A modeller defines two parameters on the sales model:

A DBeaver user connects and runs:

SET app.region = 'EMEA';
SELECT country, SUM(revenue) FROM sales WHERE region = @region GROUP BY country;

The query engine resolves @region to 'EMEA' (from the session variable, which takes precedence over the default). The @report_date parameter is not referenced in this query, so it is not resolved.

A different user connects via a persona that defines default_filters: {"region": "APAC"}. Their query:

SELECT country, SUM(revenue) FROM sales WHERE region = @region GROUP BY country;

The engine resolves @region to 'APAC' — the persona filter takes precedence over both the session variable (not set in this session) and the model default.

Editing and deleting parameters

• Edit — click the pencil icon on a parameter row. All fields except the name are editable. Changing the type clears the default value and allowed values.
• Delete — click the delete icon and confirm. Queries that reference a deleted parameter will fail at resolution time until the reference is removed or the parameter is recreated.

Best practices

• Use parameters for values that vary by audience. If every user sees the same filter, a regular WHERE clause is simpler.
• Set sensible defaults. A parameter without a default requires every caller to supply a value. Use a default that returns a safe, non-empty result set.
• Constrain with allowed values. For string and multi_value parameters, an allowed-values list prevents unexpected filter values and makes the parameter self-documenting.
• Name consistently. Adopt a convention like @report_date_start, @report_date_end, @region_filter so parameters are recognisable in queries.

API reference

All require modeler role on the model's project.

Related

• Configure Personas — persona default filters interact with parameter resolution
• Define Dimensions
• JDBC Connection Guide