Skip to content

Semantic 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 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 asks by metric instead of writing the SQL itself.

New in 0.5.0

Semantic metrics shipped in v0.5.0. If ducklocal metrics --help fails, your copy is older; update to use it.

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, 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.

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. 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 ​

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 does. It takes query's --database, --format and --restricted 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:

OptionMeans
--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 SQLOne 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 NAt most N rows

How a request becomes SQL ​

The compiled SQL is one SELECT over the dataset the metrics count. It is LEFT JOINed 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 ​

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:

KindWhy
metric_unknown_nameNo metric or field has that name. The hint names the closest one
metric_fanoutThe 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_grainThe metrics count different datasets. Ask for each in its own query
metric_ambiguous_path, metric_no_pathThe model doesn't say exactly one way to join the datasets
metric_window_unsupported, metric_nested_aggregate, metric_not_aggregateThe metric is a window function, nests aggregates, or isn't an aggregate
metric_no_dialectThe metric has no expression in a dialect DuckLocal reads
metric_bad_grainA grain was asked of a field that isn't a time field
metric_filter_refusedA --where is more than one expression
metric_missingNo --metric was given
metric_no_datasetNo 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_invalidThe model itself is wrong, as described in 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.

Released under the Apache-2.0 License.