A custom SQL dataset is a Kater dataset with custom_sql: true. Its body is one read-only query in your warehouse’s own SQL, with CTEs, unions, QUALIFY or UNNEST as you need them. Its final SELECT declares the fields everyone else reads. Kater also calls it a SQL dataset.

When to use one

Example

This dataset keeps the latest version of each order, then turns its tags array into one row per tag.

1
---
2
name: order_tags
3
model_type: kater_dataset
4
label: Order Tags
5
custom_sql: true
6
---
7

8
WITH latest AS (
9
  SELECT
10
    o.order_id,
11
    o.tags,
12
    o.ordered_at
13
  FROM shop.sales.orders AS o
14
  QUALIFY ROW_NUMBER() OVER (PARTITION BY o.order_id ORDER BY o.ordered_at DESC) = 1
15
)
16
SELECT
17
  -- @format id
18
  DIMENSION('order_id', latest.order_id) AS "Order ID",
19
  DIMENSION('tag', t.value) AS "Tag",
20
  DIMENSION('tag_position', t.index) AS "Tag Position",
21
  MEASURE('tagged_orders', COUNT(DISTINCT order_id)) AS "Tagged Orders"
22
FROM latest,
23
  UNNEST(latest.tags) WITH ORDINALITY AS t(value, index)

A custom SQL dataset in the editor

Open one from the Model Selector in Shared models, or open its file in Files. The SQL is the dataset, so the editor opens with it:

• The SQL pane, open. It shows the whole file, frontmatter included, with the file’s path and its problems. There is no joins area.
• Written in SQL. The field list shows the fields the final SELECT declares, under Written in SQL.
• Refresh. The toolbar has Refresh, which asks the warehouse for the query’s output columns again. Model › Refresh custom SQL dataset does the same.
• Rows. The grid shows the dataset’s rows.

Keys

Rules

• One read-only query. CTEs and unions are fine. Scripts, writes and the warehouse’s own variables are not supported.
• Every final output is declared. Each column of the final SELECT is a DIMENSION or a MEASURE. A final SELECT * or an undeclared column is refused. AS is only a label.
• Measures aggregate the dataset’s own dimensions. In the example, COUNT(DISTINCT order_id) counts the published order_id dimension. A measure over a private column that is not published is refused. A reader re-totals measures at its own grouping.
• It reads widely. Warehouse tables, synced or not, and the Kater datasets, semantic models, workspace roots and queries you can read.
• Its text stays as you wrote it. A save compiles it like any model, but does not reformat it.
• Sync skips it. Schema sync never changes a custom SQL dataset.
• DuckDB files are read-only. Kater opens a DuckDB file read-only, so nothing in the query can change it.

How Kater knows its columns

When you save a custom SQL dataset, Kater asks the warehouse for the query’s output columns and their types. It reads metadata only, never rows. Kater keeps the answer in a hidden generated file beside the dataset, such as .order_tags.sql.metadata.yaml. Do not edit it. It travels with the dataset through kater git clone and kater git pull, so compiles work offline.

A save through Kater refreshes this file. If you change the dataset outside Kater, such as directly in a local copy, refresh it before you use it: select Refresh on the editor’s toolbar, or run the dataset.refresh operation with kater invoke dataset.refresh --request-file REQUEST.json.

Create one

In Shared models, select New › Kater dataset. On Manual, select Write it in SQL instead, with joins or several tables, enter the Name and Folder, and select Create. Then write the query in the SQL pane. On Prompt AI, describe the dataset, select Generate prompt and select your agent under Open in. Your agent writes it. It reads the custom SQL guide first.

From a terminal:

kater new kater-dataset --custom-sql --name order_tags \
  --target-path connections/duckdb_sample/modeling/kater_objects/datasets \
  --sql-file order_tags.sql --dry-run

Run it again without --dry-run to write the file. --sql-file gives the SQL body, and Kater writes the frontmatter.

Common errors