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 asource(the name of a table or view), an optionalprimary_key, andfields. A field markeddimension: {is_time: true}is a time field.relationships:from,to,from_columnsandto_columns.metrics: at the root of the model, or under a dataset, where they are nameddataset.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:
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
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.yamlin its folder and up to three folders above it. It stops at a folder holdingducklocal-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
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 dataThe first argument is a model file, or a folder holding model.ossie.yaml. Leave it out to use the current folder.
ducklocal metricsprints the model's vocabulary as JSON:model, with itssha256andai_context.- Each metric, with its
description,synonyms, andqueryable. A queryable metric names itsbase, the dataset it counts; one that isn't has aproblemsaying why. - Each field as
dataset.field, withis_time. - The relationships, each with its
cardinality:many_to_onewhento_columnsare a key ofto,unknownotherwise.
metrics compileprints the SQL for a request, with its columns, base dataset, joins and the SQL'ssql_sha256. It reads no data.metrics querycompiles the request and runs the SQL the wayducklocal querydoes. It takesquery's--database,--formatand--restrictedoptions, and adds asemanticobject with the compiled SQL, the request and the model'ssha256, which is what to cite.--views FILE.dashputs that dashboard'ssourceandviewblocks 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
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:
| 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 |
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.
