Kater SQL is SQL, with a few additions

A query is a SELECT in your warehouse’s own SQL. Joins, CASE, window functions and your warehouse’s functions work as you write them. The compiler refuses a function that your warehouse does not have. Kater SQL adds these:

This query, from a real Paid Media Review workspace, compares each ad platform’s spend in a report month with the month before:

1
---
2
name: platform_month_over_month
3
model_type: query
4
label: Spend by platform, report month against the month before
5
parameters:
6
- name: is_between__union__date_day
7
  default_value:
8
  - '2026-08-01'
9
  - '2026-08-31'
10
  label: Report month
11
  kater_data_type: date
12
  subtype: range
13
  relative: true
14
---
15
SELECT
16
  ad_reporting.platform_name,
17
  ad_reporting.union__currency,
18
  KATER__MONTH(ad_reporting.union__date_day),
19
  EVAL(ad_reporting.sum__spend), /* @format currency(union__currency, 0) */
20
  CUSTOM_CALCULATION(
21
    'previous_month__sum__spend',
22
    KATER__PREVIOUS_PERIOD(ad_reporting.sum__spend, by => KATER__MONTH(ad_reporting.union__date_day))
23
  ) AS "Spend (Previous Month)", /* @format currency(union__currency, 0) */
24
  CUSTOM_CALCULATION(
25
    'previous_month_percent_change__sum__spend',
26
    KATER__PERCENT_CHANGE(
27
      EVAL(ad_reporting.sum__spend),
28
      KATER__PREVIOUS_PERIOD(ad_reporting.sum__spend, by => KATER__MONTH(ad_reporting.union__date_day))
29
    )
30
  ) AS "Spend (% Change vs Previous Month)" /* @format percent(1) */
31
FROM workspace.ad_reporting AS ad_reporting
32
WHERE
33
  OPTIONAL_FILTER(
34
    'is_between__union__date_day',
35
    KATER__IS_BETWEEN(ad_reporting.union__date_day, :is_between__union__date_day),
36
    label='Report month'
37
  )
38
GROUP BY
39
  ad_reporting.platform_name,
40
  ad_reporting.union__currency,
41
  KATER__MONTH(ad_reporting.union__date_day)

• The month before. CUSTOM_CALCULATION names a value computed over this query’s rows, and KATER__PREVIOUS_PERIOD gets the month before without a self-join.
• The report month. OPTIONAL_FILTER with :is_between__union__date_day makes it a parameter. An app or a reader can change it.
• The rest is SQL. It also uses EVAL to compute a measure in an expression, workspace.ad_reporting to read the workspace root, and @format to set how a value shows.

See Kater SQL for every addition.

Definitions come out of your SQL

You do not keep a separate file of metric definitions. The compiler reads your SQL and finds the definitions in it: every MEASURE, DIMENSION and CUSTOM_CALCULATION, and every field a query passes through. It does this for every semantic model, every workspace and every query. Your definitions are a byproduct of the work that you and your agent already do.

So you keep the power of SQL, with no YAML to keep in step with it, and you still get definitions, lineage and promotions after the fact.

Example: measures at the end of a chain of CTEs

The shared GA4 Client Lifecycle model finds each person’s first session in two CTEs. Then its last SELECT declares the lifetime measures:

1
WITH ranked_sessions AS (
2
  SELECT
3
    sessions.lifecycle_identity,
4
    sessions.session_start_date AS first_seen_start_date,
5
    clients.first_user_source,
6
    -- ...
7
    ROW_NUMBER() OVER (
8
      PARTITION BY sessions.lifecycle_identity
9
      ORDER BY sessions.session_start_timestamp, sessions.client_key, sessions.session_key
10
    ) AS first_session_rank
11
  FROM semantic.ga4_sessions AS sessions
12
  LEFT JOIN kater_dataset.dim_velir_ga4__client_keys AS clients
13
    ON sessions.client_key = clients.client_key
14
    AND sessions.stream_id = clients.stream_id
15
  WHERE
16
    NOT sessions.lifecycle_identity IS NULL
17
), identity_context AS (
18
  SELECT
19
    lifecycle_identity,
20
    first_seen_start_date,
21
    first_user_source
22
    -- ...
23
  FROM ranked_sessions
24
  WHERE
25
    first_session_rank = 1
26
)
27
SELECT
28
  context.lifecycle_identity AS "Lifecycle Identity",
29
  DIMENSION('first_seen_start_date', context.first_seen_start_date) AS "First Seen Start Date",
30
  context.first_user_source AS "First User Source",
31
  -- ...
32
  MEASURE('count_sessions', COUNT(DISTINCT sessions.session_key)) AS "Count Sessions",
33
  -- @description Lifetime count of sessions that met the GA4 engaged-session definition for the lifecycle identity.
34
  MEASURE('count_engaged_sessions', SUM(sessions.is_session_engaged)) AS "Count Engaged Sessions",
35
  MEASURE('sum__count_pageviews', SUM(sessions.count_pageviews)) AS "Count Pageviews"
36
FROM identity_context AS context
37
INNER JOIN semantic.ga4_sessions AS sessions
38
  ON context.lifecycle_identity = sessions.lifecycle_identity
39
GROUP BY
40
  context.lifecycle_identity,
41
  context.first_seen_start_date,
42
  context.first_user_source

The Marketing Sessions model joins it by a relationship and reads its measure:

1
SELECT
2
  -- ...
3
  MEASURE('lifetime_sessions', EVAL(lifecycle.count_sessions)) AS lifetime_sessions
4
FROM semantic.ga4_sessions AS sessions
5
LEFT JOIN RELATIONSHIP(
6
  'lifecycle_profile',
7
  semantic.ga4_client_lifecycle AS lifecycle
8
    ON sessions.lifecycle_identity = lifecycle.lifecycle_identity,
9
  'many_to_one'
10
)

Nobody wrote a definition for lifetime_sessions. Kater gives it a definition card anyway, made from the SQL above.

• Inputs. What you can group or filter it by, what a query must include, and what it cannot use.
• Computes. Its expression, the field it comes from, and the filters that always apply, here the relationship’s join.
• Step by step. The trace through each model, down to the rows it counts.
• Use. How a query reads it: evaluate it, pass it through, or build on top of it with a rank or a share of total.

The same card shows in a workspace’s Definitions tab, when you hover a field in the SQL editor, and in VS Code. Your agent reads the same definition with the Kater MCP’s explain_field tool.

Recommended promotions

Because Kater reads every query, it also sees when the same expression is written more than one time. The Promote tab in the workspace sidebar lists each repeated expression, with its formula and the number of queries that define it.

• To the workspace. Select Promote. The expression moves to the workspace root, and each query reads it from there.
• To the shared layer. An expression that more than one workspace writes goes to a shared model. Select Ask, and the data team decides.

See Promotion and verification.

Ways to write a query

Each way writes the same Kater SQL file in the workspace’s query_contracts/ folder. Use the one that fits the change, and change ways at any time.

Pick fields

Open a workspace in the editor. Select a field in the field list to add its column, and select it again to remove it. The query groups by the other fields, and a measure brings in the fields its currency rule needs. + Filter in the filter bar adds a filter, top rows or a row limit.

Use a field’s menu

A field’s menu lists what you can do with it: Sort, Windows, Compare with, Calculate at, filters, timeframes and formats. Each item writes the KATER__ call for you. Compare with › Previous Month writes the KATER__PREVIOUS_PERIOD calculation in the example above. Agents use the same menus through kater query fields.

See Fields.

Write an Excel formula

Type = in the formula bar, or in a new column, and write a formula over the columns, such as =D1/AVERAGE(D:D). The column shows its values as you type. Kater keeps the formula as a formula and stores its cell references as column names:

1
CUSTOM_CALCULATION(
2
  'calculation_1',
3
  EXCEL('=[@return_per_dollar]/AVERAGE([return_per_dollar])')
4
) AS "Calculation 1"

See Excel formulas.

Write SQL in the editor

SQL in the footer opens the query’s Kater SQL over its rows. It completes field names with CtrlSpace, and hover shows a field’s definition card.

• It saves itself. The text saves a second after the last key, errors and all. ⌘S saves at once.
• It compiles on request. Run or Compile Query checks the text and shows its problems, with quick fixes. Until then, the field list, the rows and the chart stay as the last compile left them.
• The compiled SQL. View › Compiled SQL shows what runs on the warehouse.

See Queries under Analyze for the whole editor.

Write SQL in VS Code

With the Kater extension, each query file is a Kater Query Contract file. The kater-lsp language server checks it as you type, and gives completion, hover, go to definition, formatting and quick fixes. The Kater panel shows the same editor as the portal.

See VS Code extension.

Ask your agent

Describe the analysis. The agent lists the fields, then sends a query.new request like this one:

{
  "namespace": {"kind": "workspace", "path": "connections/snowflake_prod_connection/users/yvonne@kater.ai/workspaces/paid_media_review"},
  "fields": [
    {"field": "union__date_day", "operation": "timeframes.set", "parameters": {"unit": "month"}},
    {"field": "platform_name"},
    {"field": "union__currency"},
    {"field": "return_per_dollar"}
  ],
  "conditions": [
    {"field": "union__date_day", "operation": "is_within_last", "parameters": {"amount": 12, "unit": "month", "complete": true}, "required": true}
  ],
  "order": [{"field": "union__date_day"}]
}

From a terminal, kater query new --request-file REQUEST.json sends the same request. kater new query --target-path WORKSPACE --name NAME --sql-file BODY.sql saves SQL that you wrote.

Authoring rules

These rules apply to every query, whoever writes it. The compiler checks most of them and says what to change.

1. One step, one query

A query has no CTEs and no subqueries as steps. A step that needs its own SELECT is its own query, and the next query reads it as query.<name>. Each step then has a name, a definition card and rows you can check, and other queries can read it too.

Paid Media Review answers “which platform had the highest return, most months?” in three queries:

1
SELECT
2
  KATER__MONTH(ad_reporting.union__date_day),
3
  ad_reporting.platform_name,
4
  ad_reporting.union__currency,
5
  ad_reporting.return_per_dollar /* @format currency(union__currency, 2) */
6
FROM workspace.ad_reporting AS ad_reporting
7
WHERE
8
  REQUIRED_FILTER(
9
    'is_within_last_12_month__union__date_day',
10
    KATER__IS_WITHIN_LAST(
11
      ad_reporting.union__date_day,
12
      12,
13
      'month',
14
      complete => TRUE,
15
      end => DATEADD(
16
        DAY,
17
        1,
18
        (
19
          SELECT MAX(reported.union__date_day)
20
          FROM workspace.ad_reporting AS reported
21
          WHERE reported.union__date_day < DATE_TRUNC('MONTH', CURRENT_DATE)
22
        )
23
      )
24
    ),
25
    label='Reporting Date is within the last 12 complete months of data'
26
  )
27
GROUP BY
28
  KATER__MONTH(ad_reporting.union__date_day),
29
  ad_reporting.platform_name,
30
  ad_reporting.union__currency

1
SELECT
2
  monthly.union__date_day,
3
  monthly.platform_name,
4
  monthly.union__currency,
5
  EVAL(monthly.return_per_dollar)
6
FROM query.platform_monthly_return AS monthly
7
GROUP BY
8
  monthly.union__date_day,
9
  monthly.platform_name,
10
  monthly.union__currency
11
QUALIFY
12
  KATER__IS_IN_TOP(EVAL(monthly.return_per_dollar), 1)
13
    OVER (PARTITION BY monthly.union__date_day, monthly.union__currency)

1
SELECT
2
  leaders.platform_name,
3
  leaders.union__currency,
4
  MEASURE('count__union__date_day', KATER__COUNT(leaders.union__date_day))
5
    AS "Months with the highest return, of the last 12" /* @format number(0) */
6
FROM query.monthly_return_leaders AS leaders
7
GROUP BY
8
  leaders.platform_name,
9
  leaders.union__currency
10
ORDER BY
11
  3 DESC NULLS LAST

• The filter is required. No reader can turn off the 12-month window, because the answer depends on it. end sets the window’s last month to the last complete month in the data.
• Top 1 keeps ties. Two platforms that tie in a month both stay. PARTITION BY ranks within each month and currency, so spend in two currencies is never compared.

A query that already has a CTE gets the hint cte_can_be_query. Its fix moves the CTE into a query of its own, as does kater model edit QUERY --path FILE --extract-stage CTE --write. A CTE stays when it is recursive, when a subquery reads it, or when moving it would change a measure’s numbers. A semantic model can use CTEs, as GA4 Client Lifecycle does.

2. Use the KATER__ function when one exists

A KATER__ function means the same on every warehouse, and the field menus, the filter bar and the definition card can read it. kater model inspect FILE gives the hint kater_function_available where your SQL computes what a KATER__ function does, and --fix applies it.

3. Select a field bare

ad_reporting.return_per_dollar passes the measure through: it keeps its definition, and a query that reads this one can group it again. Write EVAL(...) only inside an expression, or in HAVING, QUALIFY or ORDER BY.

1
SELECT
2
  ad_reporting.platform_name,
3
  ad_reporting.union__currency,
4
  -- Passed through: still the shared Spend measure.
5
  ad_reporting.sum__spend,
6
  -- Evaluated inside an expression.
7
  1000 * EVAL(ad_reporting.sum__spend) / NULLIF(EVAL(ad_reporting.sum__impressions), 0) AS "Spend per 1,000 impressions"
8
FROM workspace.ad_reporting AS ad_reporting
9
GROUP BY
10
  ad_reporting.platform_name,
11
  ad_reporting.union__currency

A custom calculation that you read from another query is a finished value. Select it bare. EVAL of it is the error custom_calculation_evaluated.

4. Leave an output unnamed

Write expression AS "Label". An unnamed output is correct, and the compiler sets its type from its formula. Keep a name that an operation writes, such as previous_month__sum__spend.

5. Name a field only when a later query reads it

• A value computed from a measure is never a DIMENSION. DIMENSION('revenue_value', EVAL(sum__amount)) gets the warning measure_value_declared_as_dimension.
• A source field keeps its one name. DIMENSION('currency', a.currency) gets the warning dimension_renames_field. Select a.currency instead.

6. One name, one formula

In a workspace, one name means one formula, and each formula is defined one time. A name says what, from what and how: spend_change_vs_previous_month, not change.

• repeated_formula. A field computes what another field of the workspace computes. Select that field instead. When the other field is in a query, add the formula to the root one time, and read it from there. These are the expressions the Promote tab recommends.
• name_reused. A field uses a name that another field uses for another formula. Choose a name that says how it is different.

7. Filters are calls

A filter is a KATER__ condition inside OPTIONAL_FILTER or REQUIRED_FILTER, not a bare WHERE.

• OPTIONAL_FILTER. A reader or an app can turn it off or change its value, as the Report month above shows.
• REQUIRED_FILTER. It always applies. Use it when the answer depends on it, as the 12-month window above does.
• On a measure. A fixed condition, such as “is greater than 1,000”, goes in HAVING. Top N and Bottom N go in QUALIFY, keep ties, and take PARTITION BY. The field menu’s filters write both, with the EVALs they need.

See Filters and parameters.

8. Do not format in SQL

No TO_CHAR, and no CASE for display. A format is a comment, and display names are a map:

1
ad_reporting.return_per_dollar /* @format currency(union__currency, 2) */,
2
DIMENSION(
3
  'platform_name',
4
  KATER__MAP(
5
    ad_reporting.platform,
6
    ('google_ads', 'Google Ads'),
7
    ('facebook_ads', 'Meta Ads'),
8
    ('tiktok_ads', 'TikTok Ads'),
9
    default_label => 'Other'
10
  )
11
) AS "Platform"

The value stays a number or a code, so filters, sorts and later queries still work. A map that every workspace needs goes in the shared model.

9. Compile and run

kater compile --select WORKSPACE checks the whole workspace. kater run --select FILE runs the query. Read the rows before you call it done.

More examples

A workspace root. The root of Paid Media Review reads the shared model with SELECT * and adds the definitions its queries share:

1
---
2
name: ad_reporting
3
model_type: workspace
4
label: Paid Media Review
5
---
6
SELECT
7
  *,
8
  -- @description The ad platform by the name people use for it: Google Ads, Meta Ads or TikTok Ads.
9
  DIMENSION(
10
    'platform_name',
11
    KATER__MAP(
12
      ad_reporting.platform,
13
      ('google_ads', 'Google Ads'),
14
      ('facebook_ads', 'Meta Ads'),
15
      ('tiktok_ads', 'TikTok Ads'),
16
      default_label => 'Other'
17
    )
18
  ) AS "Platform",
19
  -- @description Platform-attributed conversion value for each dollar of spend.
20
  MEASURE(
21
    'return_per_dollar',
22
    EVAL(ad_reporting.sum__conversions_value) / NULLIF(EVAL(ad_reporting.sum__spend), 0)
23
  ) AS "Return per dollar", /* @format currency('USD', 2) */
24
  -- @description Spend divided by clicks.
25
  MEASURE(
26
    'cost_per_click',
27
    EVAL(ad_reporting.sum__spend) / NULLIF(EVAL(ad_reporting.sum__clicks), 0)
28
  ) AS "Cost per click" /* @format currency('USD', 2) */
29
FROM semantic.ad_reporting AS ad_reporting

The simplest query. Four fields, passed through. Each keeps its definition from the root:

1
---
2
name: journey_summary
3
model_type: query
4
label: Journey summary
5
---
6
SELECT
7
  user_journeys.journey_sessions,
8
  user_journeys.journey_users,
9
  user_journeys.completion_rate,
10
  user_journeys.steps_per_session
11
FROM workspace.user_journeys AS user_journeys

Queries power apps

Every figure in an app comes from a query in a workspace. The app’s page names the query by its full path, and the field it shows:

<kater-chart
  query="connections/snowflake_prod_connection/users/yvonne@kater.ai/workspaces/paid_media_review/platform_monthly_return"
  kind="line" dimension="union__date_day" measures="return_per_dollar"
  heading="Return per dollar spent, by platform and month"></kater-chart>

<kater-value
  query="connections/snowflake_prod_connection/users/yvonne@kater.ai/workspaces/paid_media_review/platform_month_over_month"
  field="previous_month_percent_change__sum__spend"></kater-value>

At the end of every app, Sources lists every figure, grouped under the query it comes from, with the workspace and the filters it ran with. Select a query’s name to open that query in its workspace, in a new tab.

In the app editor, Add Content › Data lists every query you can read. In This App shows the workspaces the app reads, with a dot on each query the page uses. Everything Else lists every other workspace. Expand a workspace to see its queries and their charts. Hover a query for Add to App, Go to and Preview.

See Apps and Build an app.

Keys

• Files and names. Queries live in the workspace’s query_contracts/ folder. One file can hold several queries, each with its own frontmatter block. Kater still calls a saved query a query contract in a few places, such as this folder’s name.
• Identity is the full path. A query is named by its workspace’s full path plus its name, never by its label.
• What a query reads. Its workspace root, or other queries in the same workspace. It is the one place, with tests and the Sandbox, where custom calculations live.

Fields made in a query

A field that the query declares itself belongs to that query.

• It leads the field list. While the query is open, the field shows under Added in this query, ticked. Measures are blue and custom calculations green, in the list and in the column header.
• Other queries do not see it. In Queries, it shows only under its own query’s row until someone promotes it.
• Its menu is its own. It leads with Promote to workspace, and has Edit calculation. On a column, it also has Convert to Excel formula, or Convert to SQL for one that is an Excel formula.
• Removing it deletes it. Its column menu ends with Delete field, which deletes its definition from the query. While another column reads it, removing is greyed, with the reason, and a second item removes both in one change.

Charts read a query

A saved chart is a small model in the workspace’s charts/ folder. Its frontmatter holds the chart spec. Its SQL reads one parent query, and can rename, filter, sort and limit that query’s columns. It never aggregates: totals, ranks and comparisons stay in the query. See Charts.

Run and check

• In the portal. The Run button’s menu has Run Query, Run Selected and Compile Query. Query › Refresh data in the workspace name’s menu runs the query again for new data.
• From a terminal. kater run --select PATH runs a saved query. It needs no compile first. Rows come from the result store when anyone ran the same inputs in the last 15 minutes, and --fresh runs it again.
• Compile. kater compile --select PATH formats the file, declares the parameters it reads, and exits non-zero on an error.
• Lineage. Query › Lineage in the workspace name’s menu traces each field down to the columns it reads.
• Keep the answer. Query › Add as test saves the query as a test. See Tests.

Warnings and errors