Semantic layer interop (OSI)

detectkit can read and write Open Semantic Interchange (OSI) — the vendor-neutral YAML format for defining a metric once and consuming it across BI, AI and monitoring. The goal is one governed definition of a KPI (revenue, churn, latency…) so a dashboard, an AI agent and a detectkit alert all mean the same thing.

OSI is an interchange format, not an execution engine. The SQL for an OSI model is generated by the semantic layer that owns it (dbt MetricFlow, Cube, Snowflake…). detectkit therefore does not run OSI live — it converts at the edges: import an OSI metric into a normal detectkit metric, or export your metrics back into an OSI fragment.

The whole feature is isolated and additive. The converter package (detectkit/semantic/) is not imported by the load → detect → alert pipeline, the dtk osi command takes no lock and writes no internal table, so it cannot affect a running project. Remove it and detectkit behaves exactly as before.

Which part do you need? For most projects the immediately useful piece is ai_context (see the section below) — descriptive KPI grounding that works on any metric with no OSI model and nothing extra to install. The dtk osi converters are a forward bridge for teams that already run a governed semantic layer (Cube, dbt MetricFlow, Snowflake…): OSI adoption is still early, so if you don’t yet have an OSI model to point at, reach for ai_context today and keep the converters ready for when you do.

Install

OSI compilation for the ClickHouse target uses sqlglot to transpile ANSI SQL → ClickHouse. It is an optional dependency:

pip install 'detectkit[osi]'

The core library, and every other dtk command, never import sqlglot. The Cube target and dtk osi export work without it.

dtk osi import — scaffold a metric from an OSI model

Resolve one metric from a governed OSI model and generate a normal native detectkit metric (SQL query, interval, a starter detector, the metric’s ai_context). Think of it as an “enhanced dtk init”: you review the output and commit it like any hand-written metric — there is no runtime dependency on OSI.

Given an OSI model:

ecommerce.osi.yml

semantic_model:
  - name: ecommerce
    datasets:
      - name: store_sales
        source: analytics.store_sales        # physical table (OSI dataset.source)
        fields:
          - name: sold_at
            dimension: { is_time: true }      # the time grain column
    metrics:
      - name: total_sales
        description: Total sales revenue across all transactions
        ai_context:
          synonyms: ["total revenue", "gross sales"]
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: SUM(store_sales.ss_ext_sales_price)

Preview the SQL, then scaffold the metric:

# preview only — no file written
dtk osi compile ecommerce.osi.yml --metric total_sales --interval 1h

# scaffold metrics/total_sales.yml
dtk osi import ecommerce.osi.yml --metric total_sales --interval 1h --out metrics/

The generated metric is ordinary detectkit YAML:

# metrics/total_sales.yml  (generated — review before committing)
name: total_sales
interval: 1h
description: Total sales revenue across all transactions
ai_context:
  synonyms: [total revenue, gross sales]
query: |
  SELECT
      toStartOfInterval(sold_at, INTERVAL 3600 SECOND) AS timestamp,
      SUM(store_sales.ss_ext_sales_price) AS value
  FROM analytics.store_sales AS store_sales
  WHERE sold_at >= toDateTime('{{ dtk_start_time }}')
    AND sold_at <  toDateTime('{{ dtk_end_time }}')
  GROUP BY timestamp
  ORDER BY timestamp
detectors:
  - type: mad
    params: { threshold: 3.0 }

The query is a normal Jinja template — the loader injects the {{ dtk_start_time }} / {{ dtk_end_time }} window exactly as for hand-written SQL.

Interval is required because OSI is grain-agnostic: the time grain is a detectkit choice, not part of the OSI model.

You can also do this interactively: the dtk ui metric Builder’s From OSI sub-tab takes a pasted OSI model, lets you pick a metric and target, and compiles through the same code path as dtk osi import — the compiled SQL, description and ai_context seed the form, fingerprint comment included.

What compiles, and what is refused

Monitoring a subtly-wrong series is worse than no integration, so detectkit compiles only provably per-bucket-additive measures and hard-refuses everything else (with a message pointing you at query_file:):

Compiles	Refused
SUM, COUNT, COUNT(DISTINCT), AVG, MIN, MAX	window functions (… OVER (…))
ratios of the above, e.g. SUM(x) / NULLIF(COUNT(DISTINCT y), 0)	non-aggregate expressions (a raw column)
	unsupported / unknown aggregates

OSI carries no aggregation-type marker, so semi-additive measures (balances, “last value in period”) can’t be detected automatically — the generated SQL is always printed for review, and such measures should be hand-written. A re-import that changes the compiled SQL changes the sql-fingerprint in the file header; since detectkit resumes from the last datapoint, backfill/clean if you change a definition.

Cube target — number-parity with dashboards

Pass --target cube to compile a Cube SQL-API query (MEASURE(...) + DATE_TRUNC) instead of direct ClickHouse SQL. Point the metric’s profile at a Postgres connection on Cube’s SQL port (CUBEJS_PG_SQL_PORT), and detectkit runs the metric through Cube — so the alert is computed from the same governed definition as a Cube-backed dashboard, matching its number by construction.

dtk osi import ecommerce.osi.yml --metric total_sales --interval 1h \
  --target cube --cube store_sales --time-field sold_at --out metrics/

SELECT
    DATE_TRUNC('hour', store_sales.sold_at) AS timestamp,
    MEASURE(store_sales.total_sales) AS value
FROM store_sales
WHERE store_sales.sold_at >= '{{ dtk_start_time }}'
  AND store_sales.sold_at <  '{{ dtk_end_time }}'
GROUP BY 1
ORDER BY 1

The Cube target needs the interval to map to a standard Cube granularity (minute / hour / day / week / month).

Key options

--target {clickhouse,cube}, --dataset, --time-field, --where, --cube / --cube-measure / --time-dimension (cube target), --seasonality a,b, --detector <type>, --out <file|dir>, --force.

detectkit-specific overrides (a physical table, a ClickHouse expression, the where, the cube name…) can also live on the OSI model itself, in a custom_extensions entry under the detectkit vendor name — so the same model stays portable while carrying what detectkit needs.

dtk osi export — publish metrics back to OSI

Export native detectkit metrics into an OSI fragment so your governed layer (and the AI agent / BI) sees them. Each metric becomes an OSI metrics entry carrying its ai_context plus the exact detect/alert config in a custom_extensions[detectkit] block (a JSON string, per the OSI spec):

dtk osi export --out semantic/detectkit.osi.yml     # all metrics
dtk osi export --select tag:critical                # a subset, to stdout

Because a detectkit metric’s query is an arbitrary group-by-time SQL that does not decompose into a clean portable OSI measure, the OSI expression is a placeholder and the real definition rides in the custom_extensions — a lossless snapshot so the definition travels with the fragment, while other OSI tools still get the metric name + ai_context.

One-way carrier, not a round-trip. dtk osi import does not reconstruct a metric from the custom_extensions[detectkit] block — re-importing an exported fragment scaffolds a fresh metric from the OSI measure. Keep your metric YAML as the source of truth; export publishes the definition outward (to BI / the agent / a catalog), it is not backup/restore.

ai_context — grounding without OSI

You don’t need the converters to benefit from OSI’s grounding model. Any metric can carry an ai_context block ({instructions, synonyms, examples}) — the same shape OSI uses. It is descriptive only: it never changes a default alert (the synonyms are opt-in {synonyms} / {synonyms_line} template variables) and feeds the dtk tune cockpit and your assistant the KPI’s business meaning.

What’s deferred

A live osi_source binding — where detectkit resolves an OSI model at run time instead of scaffolding a metric — is intentionally not built yet. The scaffold-and-review flow is safer while the OSI spec is young; revisit a live binding once OSI stabilizes and a Cube/MetricFlow OSI converter ships.

See the dtk osi CLI reference for the full option list.