# Semantic metrics

> Define metrics once in an Apache Ossie model, then run them from DuckLocal's sidebar or the ducklocal metrics CLI, compiled to one DuckDB SELECT.

Source: https://ducklocal.app/docs/metrics

A semantic model states once what a number means: what counts as an error,
which calls are access changes, how two tables join. DuckLocal reads the model
from an [Apache Ossie](https://ossie.apache.org/) file, `model.ossie.yaml`, and
turns each request (these metrics, by these fields, under these conditions)
into one DuckDB `SELECT`. The same request on the same model always compiles to
the same SQL, so every chart, answer and agent that uses `error_rate` means the
same thing.

You can run a metric from the sidebar in the window, or with
`ducklocal metrics` on the command line. An agent with the
[official skill](cli.md#install-the-official-skill) asks by metric instead of
writing the SQL itself.

::: info New in 0.5.0
Semantic metrics shipped in [v0.5.0](https://github.com/JetSquirrel/DuckLocal/releases/tag/v0.5.0). If `ducklocal metrics --help` fails, your copy is older; [update](getting-started.md) to use it.
:::

## Write a model {#write-a-model}

Put `model.ossie.yaml` at the root of a project, next to or above its
dashboards. DuckLocal reads the flat Ossie 0.2 form:

- `datasets`: each has a `source` (the name of a table or view), an optional
  `primary_key`, and `fields`. A field marked `dimension: {is_time: true}` is a
  time field.
- `relationships`: `from`, `to`, `from_columns` and `to_columns`.
- `metrics`: at the root of the model, or under a dataset, where they are named
  `dataset.metric`.

Every expression is listed per SQL dialect. DuckLocal uses the first of
`DUCKDB`, `OSSIE_SQL_2026` and `ANSI_SQL` that the expression has, and DuckDB's
own parser reads it. This shortened model, from the
[CloudTrail example](example-cloudtrail.md), counts the `events` view that the
example's dashboard defines:

```yaml
version: 0.2.0.dev0
name: cloudtrail
ai_context:
  instructions: >
    event_time is UTC. A NULL error_code means the call succeeded.
datasets:
- name: events
  source: events
  fields:
  - name: event_time
    expression: {dialects: [{dialect: ANSI_SQL, expression: event_time}]}
    dimension: {is_time: true}
  - name: ip
    expression: {dialects: [{dialect: ANSI_SQL, expression: ip}]}
    dimension: {is_time: false}
    ai_context: {synonyms: [source_ip, address]}
metrics:
- name: event_count
  description: API calls.
  expression: {dialects: [{dialect: ANSI_SQL, expression: COUNT(events.event_time)}]}
- name: error_count
  description: Calls that were refused or failed.
  expression: {dialects: [{dialect: ANSI_SQL, expression: COUNT(events.error_code)}]}
- name: error_rate
  description: The share of calls that were refused or failed.
  expression: {dialects: [{dialect: ANSI_SQL, expression: "error_count * 1.0 / NULLIF(event_count, 0)"}]}
```

A metric can be built from other metrics, as `error_rate` is. A field or metric
with no usable dialect, or one DuckDB can't parse, is listed with the problem
and the rest of the model still works. A model with a duplicate name, a
relationship to a dataset that doesn't exist, or a `source` that isn't a table
name is refused as a whole.

## Run a metric from the sidebar {#sidebar}

The Schema tab of the sidebar has a **Metrics** section (**指标** in Chinese),
below **Dashboards**. It lists the models it finds beside the dashboards you
have opened recently:

- For each dashboard, DuckLocal looks for `model.ossie.yaml` in its folder and
  up to three folders above it. It stops at a folder holding
  `ducklocal-project.json`, which marks a [portable project](agent-workflows.md).
  A model found from several dashboards is listed once.
- Under each model is one row per metric. Hover the model for its path and the
  dashboard its datasets come from. Hover a metric for its description and
  synonyms.
- A metric that can't be queried is marked **not queryable**. Hover it to see
  why.

Click a metric, or its ▶ button, and DuckLocal compiles it, puts the SQL in the
active query editor and runs it. The 📈 button runs it broken down by day,
over the first time field of the dataset it counts.

The SQL starts with a line saying where the number comes from, for example
`-- Metric error_count, from model cloudtrail (model.ossie.yaml, sha256 …)`.
When the model's datasets come from a dashboard's `source` and `view` blocks,
those blocks go in front of the query as CTEs. The query then runs on the
window's own connection, whether or not the dashboard is open, and you can edit
it like any other query.

## Run a metric from the command line {#cli}

```bash
ducklocal metrics                       # the model in the current folder
ducklocal metrics compile --metric error_count,event_count --by events.ip \
  --where "events.user_name = 'ci-deploy'"
ducklocal metrics query --metric event_count --by events.event_time:day \
  --views reports/investigation.dash --restricted --allow-read data
```

The first argument is a model file, or a folder holding `model.ossie.yaml`.
Leave it out to use the current folder.

- `ducklocal metrics` prints the model's vocabulary as JSON:
  - `model`, with its `sha256` and `ai_context`.
  - Each metric, with its `description`, `synonyms`, and `queryable`. A
    queryable metric names its `base`, the dataset it counts; one that isn't
    has a `problem` saying why.
  - Each field as `dataset.field`, with `is_time`.
  - The relationships, each with its `cardinality`: `many_to_one` when
    `to_columns` are a key of `to`, `unknown` otherwise.
- `metrics compile` prints the SQL for a request, with its columns, base
  dataset, joins and the SQL's `sql_sha256`. It reads no data.
- `metrics query` compiles the request and runs the SQL the way
  [`ducklocal query`](cli.md#query-options) does. It takes `query`'s
  `--database`, `--format` and [`--restricted`](agent-workflows.md) options, and
  adds a `semantic` object with the compiled SQL, the request and the model's
  `sha256`, which is what to cite. `--views FILE.dash` puts that dashboard's
  `source` and `view` blocks in front of the SQL, for models whose datasets are
  defined there.

A request is made of:

| Option | Means |
| --- | --- |
| `--metric NAME[,NAME]` | The metrics to compute. Repeatable |
| `--by REF[,REF]` | Fields to break down by, as `dataset.field`. A bare field name works when only one dataset has it. `dataset.field:GRAIN` truncates a time field to `second`, `minute`, `hour`, `day`, `week`, `month`, `quarter` or `year`. Repeatable |
| `--where SQL` | One condition. A condition over fields filters rows (`WHERE`); one over metric names, such as `"error_count > 5"`, filters groups (`HAVING`). Repeatable; all of them apply |
| `--order-by NAME[:desc]` | An output column, or the `dataset.field` it came from. The default is the `--by` columns, ascending |
| `--limit N` | At most N rows |

## How a request becomes SQL {#compiled-sql}

The compiled SQL is one `SELECT` over the dataset the metrics count. It is
`LEFT JOIN`ed along many-to-one relationships to each dataset the request
names, so a row with no match still counts, under `NULL`. Output columns are
named after the field (`ip`, or `event_time_day` for a grain) or the metric.
When two would clash, they're qualified (`events.user_name`). A field
condition goes into `WHERE` and a metric condition into `HAVING`. Each
`--where` is a single expression: a subquery, a second statement, or anything
around the expression is refused.

## When a request is refused {#refusals}

DuckLocal refuses a request that it can't answer exactly, rather than return a
number that may be wrong. A refusal exits with code 2, and the error names a
`kind`:

| Kind | Why |
| --- | --- |
| `metric_unknown_name` | No metric or field has that name. The `hint` names the closest one |
| `metric_fanout` | The field is on the many side of a relationship, and joining it would count rows more than once. Break down by a field of the metric's own dataset |
| `metric_multi_grain` | The metrics count different datasets. Ask for each in its own query |
| `metric_ambiguous_path`, `metric_no_path` | The model doesn't say exactly one way to join the datasets |
| `metric_window_unsupported`, `metric_nested_aggregate`, `metric_not_aggregate` | The metric is a window function, nests aggregates, or isn't an aggregate |
| `metric_no_dialect` | The metric has no expression in a dialect DuckLocal reads |
| `metric_bad_grain` | A grain was asked of a field that isn't a time field |
| `metric_filter_refused` | A `--where` is more than one expression |
| `metric_missing` | No `--metric` was given |
| `metric_no_dataset` | No metric in the request reads a column, so there is no dataset to count. Qualify the aggregate in the model: `COUNT(events.event_id)`, not `COUNT(*)` |
| `model_invalid` | The model itself is wrong, as described in [Write a model](#write-a-model) |

A model file that can't be read exits with code 1 and kind `model_not_found`.
When a question needs SQL that the model can't express, write it with
`ducklocal query`, and say that the number isn't one of the model's metrics.
