What it gives your team

A measure is one line of SQL

Finance and marketing both report Revenue. In Kater it is one line of the Orders dataset:

1
SELECT
2
  DIMENSION('order_id', orders.order_id) AS "Order ID",
3
  DIMENSION('region', orders.region) AS "Region",
4
  DIMENSION('currency', orders.currency) AS "Currency",
5
  DIMENSION('amount', orders.amount) AS "Amount",
6
  -- ...
7
  -- @description Order amount before discounts.
8
  MEASURE('sum__amount', KATER__SUM(orders.amount), NOT_ADDITIVE_ACROSS(currency)) AS "Revenue", /* @format currency(currency, 2) */
9
  -- ...
10
FROM shop.sales.orders AS orders

Each part of those two lines becomes a part of its card:

The frontmatter at the top of a file holds the model’s name and label. Every field is in the SQL. There is no second file to keep in step with it.

Write a measure

1
MEASURE('name', aggregation, NOT_ADDITIVE_ACROSS(dimension)) AS "Label"

• 'name' is its identity. Other models read it by this name. AS gives it a label only.
• The aggregation is SQL. Use a KATER__ aggregation, such as KATER__SUM(orders.amount), or a formula of other measures, each in EVAL.
• NOT_ADDITIVE_ACROSS is optional. It names the dimensions that each result row must hold to one value.
• Declare it in the outermost SELECT. Never in a CTE or a subquery.
• Name it only when another model reads it. In a query, a value that only that query shows can stay unnamed.

See Measures for every aggregation, and Formats, descriptions and AI context for the comments.

The reader decides how to use it

The data team defines Revenue one time. Each person who reads it decides what it means in their analysis. The card’s Use section lists the choices, each with the query it writes. On the card above, open Share of total, then select Preview data.

• Each Build on top row is one call. A running total, a rank, a share of total or a change from the day before.
• The rule comes along. Kater writes the PARTITION BY that the currency rule needs, so a share is never a share of euros and dollars together.
• The new field has a card too. Open Share of total and read Definition under its rows: it is worked out over the query’s rows, separately for each currency.

See Pass through, evaluate or declare.

Build a measure from measures

Marketing wants revenue per customer. A query of the Shop workspace declares it from two measures:

1
SELECT
2
  shop.region_group,
3
  shop.country,
4
  shop.currency,
5
  shop."loyalty tier",
6
  MEASURE(
7
    'revenue_per_customer',
8
    EVAL(shop.sum__amount) / NULLIF(EVAL(shop.count_distinct__customer_id), 0)
9
  ) AS "Revenue per Customer"
10
FROM workspace.shop AS shop
11
-- ...

• MEASURE names it. A query that reads it works it out again at its own grouping, from the two totals. It is never an average of rows.
• EVAL computes each measure inside the formula. Revenue and Customers each keep their own definition.

Its Inputs say what a reader can do with it:

A measure like this often starts in one person’s query. When it proves useful, you promote it to the workspace, then to the shared layer. It moves as the same line, and its card stays the same. See How a field moves up.

Rules travel with the measure

A marketer asks for revenue by region. The query has no currency:

1
SELECT
2
  shop.region,
3
  shop.sum__amount
4
FROM workspace.shop AS shop
5
GROUP BY
6
  shop.region

The compiler refuses it with not_additive_across: “Revenue cannot be added up across Currency. Group by Currency or filter it to one value.” The EMEA row would add euros to dollars. The quick fix, Group by Currency, adds shop.currency to the SELECT and the GROUP BY.

Nobody wrote this rule in the query. It is part of Revenue, so it reaches every query that reads Revenue, every measure built on it, such as Revenue per Customer, and every query an agent writes. The compiler checks other rules the same way:

See Measures you cannot add up and Diagnostics and fixes.

Joins count each row once

Customers is declared on the Customers dataset, one row for each customer. The Shop model joins each order to its customer, and passes Customers through:

1
SELECT
2
  o.order_id,
3
  o.region,
4
  -- ...
5
  c.count_distinct__customer_id,
6
  -- ...
7
FROM kater_dataset.orders AS o
8
LEFT JOIN RELATIONSHIP('customer', kater_dataset.customers AS c ON o.customer_id = c.customer_id, 'many_to_one')
9
WHERE
10
  REQUIRED_FILTER('is_not_null__order_id', KATER__IS_NOT_NULL(o.order_id), label='Order ID is set')

The card names the join and the filter under Added here:

Group Customers by an order’s region, and each customer counts once in each region they ordered in:

The rows add up to 976, but the shop has 240 customers. Ask for Customers with no grouping, and Kater counts the distinct customers again from the order rows: 240. A join never repeats a measure’s rows, so a total is never a sum of other totals. See Measures across joins and unions.

KATER__ functions say what they mean

The Shop model groups regions with KATER__MAP:

1
  DIMENSION(
2
    'region_group',
3
    KATER__MAP(o.region, ('EMEA', 'Europe'), ('AMER', 'Americas'), default_label => 'Rest of World')
4
  ) AS "Region Group",

Open Step by step on its card. Kater reads the call and writes each step in words.

• One meaning on every warehouse. The compiler turns each KATER__ call into Snowflake SQL or DuckDB SQL.
• The menus write them. A field’s menu, the filter bar and Use write KATER__ calls, and the card reads them back.
• A dimension has its own Use rows. Group by it, filter by it, or make a flag from one of its values.

See KATER__ functions for each one, with its arguments and an example.

Why SQL and not YAML

Most semantic layers keep metrics in YAML files beside the SQL that computes them. Kater keeps them in the SQL:

We chose SQL for these reasons:

• One place for the logic. A metric, its joins and its rules are in one file. You read it, compare it, test it and review it like any other SQL.
• You never leave the model. When a metric needs a window, a CTE or a function of your warehouse, you write it in the model. It keeps its card, its rules and its lineage.
• Definitions come out of the work. The compiler finds the definitions in every model and every query. A measure that an analyst writes in a query has a card when it is saved. When two queries write the same formula, Kater recommends a promotion.
• Every editor writes the same file. The portal, VS Code, the CLI and your agent all write the same SQL through the same operations, and each save is compiled.

Built for agents

Language models write very good SQL. Kater SQL is SQL, so an agent writes it the way your team does, with no new language to learn.

• The compiler checks every write. An agent’s query meets the same rules as yours: the currency rule, the joins and the names. A wrong total is refused before anyone reads it.
• The agent reads the card you read. The Kater MCP’s explain_field tool gives the agent the same definition as the cards on this page. It knows what Revenue means before it uses Revenue.
• The Use rows are its operations. An agent builds a query from each field’s operations, the same ones Use lists. Asked for each region’s share of revenue, it applies Share of total, and Kater writes the call with its PARTITION BY.
• Every answer reads your definitions. An agent does not make a new formula for Revenue from the tables. It reads the measure that your data team defined, so its answer matches your dashboards.

See Agents pick fields first on the Queries page, and Kater MCP.

Next steps