Filters decide which rows count. An optional filter is one a reader can switch on, with a value they supply through a parameter. A required filter is a fixed rule that always applies. Both are named, labelled and written in the model’s SQL, so the filter bar, the CLI and agents all read the same thing.

Three kinds of filter

Example

The Ad Reporting workspace root gives every query two optional filters: a reporting window in months and a list of campaigns. This is Snowflake, so parameters are written :name.

1
---
2
name: ad_reporting
3
model_type: workspace
4
label: Ad Reporting
5
parameters:
6
- name: reporting_date
7
  label: Reporting Date
8
  kater_data_type: number
9
  default_value: 12
10
- name: campaign_name
11
  label: Campaign Name
12
  kater_data_type: string
13
  subtype: multiselect
14
  allowed_values:
15
    from_field:
16
      ref: union__campaign_name
17
---
18
SELECT
19
  *
20
FROM semantic.ad_reporting
21
WHERE
22
  OPTIONAL_FILTER(
23
    'reporting_date',
24
    KATER__IS_WITHIN_LAST(union__date_day, :reporting_date, 'month', complete => TRUE),
25
    label='Reporting Date'
26
  )
27
  AND OPTIONAL_FILTER(
28
    'campaign_name',
29
    KATER__IS_ONE_OF(union__campaign_name, :campaign_name),
30
    label='Campaign Name'
31
  )

A query can add its own. This one, on DuckDB, writes parameters as $name, and holds a fixed rule too:

1
WHERE
2
  OPTIONAL_FILTER(
3
    'is_equal_to__country',
4
    KATER__IS_EQUAL_TO(shop.country, $is_equal_to__country),
5
    label='Country'
6
  )
7
  AND REQUIRED_FILTER(
8
    'text_contains_paid__channel',
9
    KATER__TEXT_CONTAINS(shop.channel, 'paid'),
10
    label='Channel contains paid'
11
  )

Filter rules

• Filters sit at the top of WHERE. Each is a direct AND condition of WHERE. Not under OR or NOT, and not in HAVING, QUALIFY, a join or a CASE.
• The label is the third argument. Write label='Region'. Description and AI context go in -- @description and -- @ai_context comments directly above the filter.
• Names say what the filter does. The convention is <condition>__<field>, such as is_one_of__region. A filter’s parameter shares its name.
• A required filter takes no parameters. Even a parameter with a default makes it invalid.
• A default never switches a filter on. It only supplies a value when the filter is on.
• Every query reads the root’s filters. A query inherits the filters and parameters of its workspace root and of the queries it reads.
• Filters on a measure stay in their query. A workspace filter applies before grouping, so a filter after grouping belongs to the query that groups.

Parameters

A parameter is a value a reader supplies. Only workspace roots and queries declare them, in the frontmatter’s parameters: list. A root’s parameters are shared by every query in the workspace, so a query can read :reporting_date without declaring it again.

Write a parameter as your warehouse’s own placeholder: :name on Snowflake and $name on DuckDB. Each save and each compile adds an entry for every parameter the SQL reads that has none. Edit the entry to add a label, a default or a list of choices.

Parameters cannot go in CASE, IF or IFF conditions, HAVING, QUALIFY, a join’s ON, or an EVAL SET. A parameter used outside a filter, such as a target in arithmetic, is always needed unless it has a default.

How a run uses them

A run switches filters on by name. From a terminal, pass them in a file keyed by each model’s id from kater model list:

kater run --select connections/duckdb_sample/workspaces/shop/query_contracts/region_mix.sql --inputs-file inputs.json

A workspace keeps its saved values and selected filters in default-inputs.json, in the same shape:

{
  "rel1_6FpTma5qXPUnVtX3wWYGQ60uwp9UFBA9xboZqQw3IRM": {
    "parameters": {
      "last_n_days": 30
    },
    "filters": [
      "event_date_dt_last_n_days"
    ]
  }
}

The filter bar in the editor

In the workspace editor, the filter bar is one row of pills under the query’s toolbar. Filters in the toolbar hides it or shows it. The pills come in two groups, each led by its mark:

• Workspace. The root’s filters as pills, such as “Session Start Date is within the last 30 days”. A filter that is off is dashed and faded, with a ring. A filter that is on is solid, with a dot, and the rows follow it. Click the dot or the ring to turn the filter off or on. Those that do not fit wait under N more.
• A parameter no filter reads shows as a parameter pill, with its name and value.
• This query. The query’s own filters, a date range among them, its top rows, and its row limit.
• + Filter. Filter by a field, Top rows by a measure, Row limit and Custom filter, each a form in the same popover.

Click a workspace pill to change it in place. Its popover names the filter and where it comes from, such as “Marketing Sessions filter bar”, then the switch, the condition and a control for the value. Edit in SQL editor at its end opens the SQL that writes the filter.

A query pill’s popover has Applied, the condition’s form, Move to workspace, and Remove filter. Move to workspace moves the pill to the Workspace section of the filter bar, where it applies to every query in the workspace. A query whose SQL you wrote by hand shows a lock after its filters, which says why on hover.

Filters on the workspace overview

The overview’s Workspace filters section shows each root filter as a card, with its switch, condition and value. Drag a card by its grip to change the order.

Edit a filter and its parameter

Select a card’s … to open its settings. One form edits the filter and the parameter it reads together:

• Filter. Label, Description and AI context.
• Its parameter. Name, Label, Type, Default and Values from. The name is read only, because the SQL reads the parameter by that name.
• Remove filter takes the filter out of the root.

Add a filter or a parameter

Select Add beside Workspace filters:

• Filter with a value to pick (Parameterized): a filter whose value the reader sets, through a parameter.
• Filter that always applies (Static): a condition with no value to pick.
• Parameter: a value with no filter, such as a target that a query reads in a calculation.

Parameters

The Parameters section lists only the parameters no filter reads, with their type, where their values come from, their default and their value. A filter’s own parameter is edited from that filter’s card. A parameter the frontmatter declares that no SQL reads yet is listed too, with Remove. A missing default reads null.

More › Show SQL parameter names shows each parameter’s SQL name. More › Reset filters to defaults puts every filter and value back to the saved defaults.

Add filters without writing SQL

• The filter bar. Select + Filter, then Filter by a field, a field and a condition. Edit › Add filter opens the same form.
• The field menu. A field’s Filters opens with Workspace, selected first, for a filter every query can switch on, and This query, for the open query alone. A measure offers only This query.
• The workspace overview. Add beside Workspace filters.
• The terminal. kater model edit MODEL --path FILE --workspace-inputs-file edits.json takes add_custom_filter, update_filter, remove_filter, reorder_filters and update_parameter. add_custom_filter takes a condition no menu offers, such as clicks > conversions, and the writer wraps it in the filter call.

Common errors