The layers

Data flows down from your warehouse tables to your team, agents and apps that read it. Each layer reads only from the layer above it.

• Source layer. One database dataset per warehouse table. kater sync writes these files for you.
• Shared layer. Kater datasets and semantic models. The data team defines dimensions, measures and joins here. Explore reads this layer.
• Workspace layer. A workspace root reads one shared model. Queries in the workspace read the root, and charts read their query.
• Consumers. Your team, your agents and apps read one or more workspace queries. Your team also builds the workspaces.
• Sandbox. Scratch files in your own brainstorm folder, for quick SQL. Nothing reads them.

Models are SQL, not YAML

Every model is a .sql file in your warehouse’s own SQL, with a few Kater calls. There is no YAML file of metrics to keep in step with it. The compiler reads the SQL and makes a definition card for each field. You, your agents and every editor read the same card.

A measure is one line

The Orders dataset of Kater’s sample shop declares Revenue:

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

Kater makes this card from those two lines. It is the card of the Definitions tab, of a hover in the SQL editor, and of VS Code. Open a row under Use to see the query it writes, then select Preview data.

• Computes. KATER__SUM(orders.amount) is the expression. A KATER__ function means the same on every warehouse.
• Inputs. NOT_ADDITIVE_ACROSS(currency) is a rule: each result row holds one currency, so no total adds euros to dollars.
• Description and format. -- @description and @format are comments in the SQL.
• Use. The ways a reader can use Revenue: evaluate it, pass it through, or build on top of it with a running total, a rank or a share of total.

A measure built from measures

A query of the Shop workspace divides Revenue by Customers:

1
SELECT
2
  shop.region_group,
3
  shop.country,
4
  shop.currency,
5
  shop."loyalty tier",
6
  shop.sum__amount,
7
  MEASURE(
8
    'revenue_per_customer',
9
    EVAL(shop.sum__amount) / NULLIF(EVAL(shop.count_distinct__customer_id), 0)
10
  ) AS "Revenue per Customer"
11
FROM workspace.shop AS shop
12
WHERE
13
  OPTIONAL_FILTER(
14
    'is_equal_to__country',
15
    KATER__IS_EQUAL_TO(shop.country, $is_equal_to__country),
16
    label='Country'
17
  )
18
  AND REQUIRED_FILTER(
19
    'text_contains_paid__channel',
20
    KATER__TEXT_CONTAINS(shop.channel, 'paid'),
21
    label='Channel contains paid'
22
  )
23
GROUP BY
24
  shop.region_group,
25
  shop.country,
26
  shop.currency,
27
  shop."loyalty tier"

Nobody wrote a currency rule for Revenue per Customer. Its card has one anyway: it reads Revenue, so Revenue’s rule comes with it.

• Required. One currency per result row, from Revenue.
• Always filtered. The query’s REQUIRED_FILTER. Its OPTIONAL_FILTER is under Optional filters, for a reader to turn on.
• Use. Each reader decides how to use it. A rank or a share of total is one call, and it stays within each currency.

How a field moves up

A new metric often starts in one query. When it proves useful, you promote it up a layer, and its meaning stays the same.

In a query. You declare it with MEASURE in one query. Only that query reads it.

To the workspace. You promote it to the workspace root. Every query in the workspace now reads one definition, and queries that computed the same formula point at it.

To the shared layer. You ask the data team to promote it. The data team approves the request, and every workspace on the connection can read it. The field now shows as verified.

See Promotion and verification.

How to promote a field

Like everything in Kater, there are three ways to promote a field:

Point and click Chat VS Code and CLI

In the workspace editor, open the menu of a field made in the query, on its column or in the field list. Select Promote to workspace. The promotion dialog shows where the field goes and its expression. Select Promote.

Ask your agent, such as Claude or ChatGPT, connected to Kater. It drafts the promotion and shows the card: where the field goes, its expression, and each query that defines the same thing, with its rows compared. Select Promote to apply it. See Ask Kater MCP.

In VS Code, run Kater: Promote a Field from the Command Palette. From a terminal, use kater model promote:

kater model promote revenue_per_customer --relation region_mix \
  --scope connections/duckdb_sample/workspaces/shop --dry-run

--dry-run shows the change and writes nothing. Run it again without --dry-run to write.

Promotion recommendations for admins and developers

Kater finds the same formula defined in more than one place and recommends a promotion for it. A nightly scan compiles every workspace on the connection and matches formulas by fingerprint, so two fields with different names still match. Open Admin › Objects and select Promotions. Each row shows the field and the model it moves to, who asked for it or that Kater found it, its expression, how many definitions it replaces, and its state. Select a row to see the drafted field and each definition it replaces, with its numbers before and after. Then select Apply or Dismiss. See Promotion requests.

What you get

Define a metric once, and every answer, app, dashboard and agent uses that same definition. In Kater you model your warehouse in layers of plain SQL files. The data team writes the shared definitions. Everyone else builds analysis on top of them.

When a marketer asks their agent for spend by platform in the last 3 months, the agent reads the Spend measure your data team defined, with its currency rule and its description. It does not write a SUM of its own.

• One definition per metric. A measure such as Spend is written once, in a shared model. Queries, dashboards, apps and agents read it by name.
• Numbers that stay right. The compiler refuses a query that adds up revenue across currencies, and a join never counts an order twice inside a measure.
• Context for AI. Descriptions, AI context and business questions sit beside each field, so an agent knows what a field means before it uses it.
• Plain files. Every model is a .sql file with YAML frontmatter. You can read it, compare it, test it and review it.

What each model can do

These rules are fixed policy in the compiler. Every editor, the CLI and the MCP apply the same rules.

A semantic model is optional. A workspace can read a Kater dataset directly.

Rules that follow from the layers

• Shared models take no parameters. Only workspace roots and queries declare parameters and optional filters. A shared model can hold a required filter with fixed values.
• Custom calculations live in queries. A rank or a running total over result rows is a query’s custom calculation. In a shared model a window is a dimension, and a workspace root runs no window. See Custom calculations.
• Explore runs one way. Explore reads Kater datasets and semantic models. A card leaves Explore as an ordinary query in a workspace, and nothing in a workspace flows back into Explore.

Every model is a Kater SQL file

A model file is YAML frontmatter between --- lines, then one SQL statement. Kater SQL is your warehouse’s own SQL with a few additions: DIMENSION, MEASURE, EVAL, RELATIONSHIP, the filter calls, the KATER__ functions and -- @ comment directives.

1
---
2
name: shop
3
model_type: semantic
4
label: Shop
5
---
6

7
SELECT
8
  o.order_id,
9
  o.region,
10
  ...
11
  c.country,
12
  o.sum__amount,
13
  ...
14
  DIMENSION('amount_band', KATER__BUCKET(o.amount, [25, 100])) AS "Amount Band"
15
FROM kater_dataset.orders AS o
16
-- @label Customer
17
LEFT JOIN RELATIONSHIP('customer', kater_dataset.customers AS c ON o.customer_id = c.customer_id, 'many_to_one')

The Shared models editor reads and writes this file. A field you tick, a join you add and a label you type are all changes to its SQL, and what you type in the SQL shows in the editor. The full syntax is in the Kater SQL reference.

What a connection holds

Each warehouse connection is a folder in your organisation’s content store. Kater hosts the content store. You work in it through the portal, the CLI, VS Code or your agent.

connections/<connection>/
├── connection.yaml              the warehouse and the schemas to sync
├── connection_context.md        notes agents read for this connection
├── .kater/dataset-sync/         sync state, kept with the content
├── ai_tools/                    agents, skills and workflows
├── modeling/                    source and shared layers
│   ├── db_objects/datasets/<DATABASE>/<SCHEMA>/<TABLE>.sql
│   └── kater_objects/
│       ├── datasets/<name>.sql
│       ├── semantic_models/<folder>/<NAME>.sql
│       └── tests/<model>.sql
├── workspaces/<workspace>/      connection workspaces
│   ├── workspace.sql
│   ├── query_contracts/<name>.sql
│   ├── charts/<name>.sql
│   ├── dashboard.yaml
│   └── apps/<app>/
└── users/<email>/               your Personal folder
    ├── workspaces/<workspace>/  your private workspaces
    ├── tests/<model>.sql        your own tests
    └── brainstorm/              scratch files and notes, never versioned

Only each connection’s modeling/ folder can be written back to GitHub. See Content store and GitHub write-back.

Where you edit models

Every surface writes the same files through the same operations. Each save is formatted and compiled, and a save that adds a compile error is refused. Scratch files are the one exception: they save as written.

Shared models

Go to /shared-model in the portal. With no model open, the page says “Open a model, or make a new one.” The Model Selector at the left lists your pinned models, every model by topic, and the Kater datasets. The search box finds models and fields, or takes a description of a new model for your agent. The New semantic model and New Kater dataset cards open the New dialog.

Open a model, and the editor shows it whole: its sources and the joins between them, its fields grouped by source, and its rows. Everything saves itself. There is no Save button, and the footer says Saving, then Saved. A model’s address is /shared-model/<connection>/<model>.

In Files

Open a semantic model or Kater dataset file in Files, and the same editor opens in the file’s tab. Files keeps its own menu bar, breadcrumb and explorer. Above the editor, the file’s view bar has Editor, Definitions and Explore, and a menu with the Model items.

In VS Code

Open a semantic model or Kater dataset file in VS Code, and the KATER panel shows the same editor below the SQL. Select a join in the panel, and its lines light up in the file. The editor’s menu items are Kater commands in the Command Palette, such as Kater: Add Source, Kater: Check Every Join and Kater: Generate Tests. See VS Code extension.

Next steps