osi-metrics-authoring

name	osi-metrics-authoring
description	OSI core schema metric authoring specification — metric expression shape, Datus extension hints, window/period-over-period semantics, and skip gates
tags	["metrics","osi"]
version	1.3.0
user_invocable	false
disable_model_invocation	false
allowed_agents	["gen_metrics"]

OSI Metrics Authoring

Create, update, or explicitly delete metrics in an existing strict OSI (Open Semantic Interchange) core semantic model, capturing each metric's business meaning from SQL queries or natural language.

CRITICAL BOUNDARY: You author OSI core semantics only. You do NOT write MetricFlow YAML, measure_proxy, type_params, measures:, ratio, cumulative, or any execution-engine syntax. The Datus OSI compiler infers backing measures, picks the backend metric kind, and lowers to the execution engine. Do NOT write legacy MetricFlow metric: blocks.

CRITICAL MODEL RULE — reuse the model, then make only narrow changes required by the requested metrics:

• Start from the datasets and relationships built by the semantic-model step.
• Use upsert_osi_datasets only when a requested metric needs a missing field or a normal/query-backed dataset. Pass the complete final dataset object because the tool replaces that named dataset while preserving all other model content.
• Use delete_osi_datasets only when the corrected final plan no longer needs a named dataset. Never infer relationship or metric cascade deletions.
• Use upsert_osi_metrics to create or update metrics and delete_osi_metrics to remove explicitly requested names under semantic_model[0].metrics.
• General filesystem writes and relationship/model-metadata edits remain out of scope. If the target model is missing, stop and report that gen_semantic_model must run first.
• A given logical dataset has one canonical definition shared by all metrics. Reuse that dataset by name in each metric's DATUS dataset hint.

The OSI expression dialect is shown in the system prompt Workspace section. Bind one existing target with bind_osi_semantic_model_target before writing metrics. A path or model name supplied in the request is only a selection hint; if it is absent or does not bind, call list_existing_osi_semantic_models and compare the live candidates using SQL tables, dataset metadata, and business meaning (<osi_dialect> below stands for the active dialect).

What you produce

Metric definitions inside a valid OSI core document:

version: <osi_version>            # use the exact version from the existing semantic model file
semantic_model:
  - name: <target_model_name>
    datasets:
      - name: <existing_dataset_name>
        source: <existing_source>
        primary_key: [<existing_primary_key>]
        fields: [...]                         # preserve existing dataset definitions
    relationships: [...]                       # preserve existing relationships
    metrics:
      - name: <metric_name>                    # globally unique snake_case
        description: "<business definition>"
        ai_context:
          instructions: "<how AI should use this metric, including grain, conditions, time field, and join caveats>"
        expression:
          dialects:
            - dialect: <osi_dialect>
              expression: "COUNT(DISTINCT <existing_dataset_name>.id)" # aggregate business expression; no OVER/LAG/RANK
        custom_extensions:
          - vendor_name: DATUS
            data: '{"time_dimension":"<date_column>","subject_path":["<domain>","<layer1>","<layer2>"],"format":"0.00%","unit":"<unit>"}'

Qualify every column in the metric expression with its dataset name (SUM(orders.amount), not SUM(amount)). The compiler resolves the metric's dataset from the qualifier and strips it before execution, so a DATUS dataset hint is only needed when the expression references no qualified columns (e.g. some derived metrics).

Datus execution hints such as dataset, time_dimension, metric_kind, inputs, numerator, denominator, window, grain_to_date, window_aggregation, offset_window, period_over_period, subject_path, format, and unit MUST be encoded in the metric's DATUS custom_extensions[].data JSON string. They are not OSI core top-level metric fields.

Authoring rules

1. Reference, then repair narrowly when required. Every metric must anchor on a dataset of the bound semantic model — via qualified column names in its expression, or via a DATUS dataset hint. If a required field or dataset is missing, add it with upsert_osi_datasets. Prefer a query-backed dataset when the requested output cannot be represented by normal fields and metric expressions; do not use one merely to bake a query-time filter into a new metric. If a required relationship is missing and a query-backed dataset cannot preserve the semantics, report the prerequisite because this workflow does not edit relationships.
2. Aggregates: write the natural business expression in OSI core expression.dialects[0].expression, qualifying columns with the owning dataset name, e.g. COUNT(DISTINCT orders.order_id), SUM(orders.amount), AVG(reviews.score). Every referenced row-level column must be declared in the owning dataset's fields; aggregated-only columns, including business keys used by COUNT(DISTINCT ...), are plain fields without a dimension: block.
3. Conditional aggregates: after a condition has been classified as part of the durable metric definition, keep the CASE inside the expression, e.g. COUNT(DISTINCT CASE WHEN <condition> THEN id END). Preserve literal values exactly.
4. Filter ownership — classify before authoring. A predicate in a source SQL WHERE is a query-time filter by default; do not automatically copy every predicate into a metric expression. Promote it to a metric-specific business condition only when durable intent is established: the user explicitly requests a named filtered KPI, the aggregate already contains CASE WHEN or an equivalent filter, or an established business/catalog definition requires the predicate on every use. A final output alias (AS <name>) that names the filtered concept is supporting evidence, not proof by itself; likewise, a natural-language question that merely describes one query slice does not by itself define a new metric.
   • Equality, range, or membership predicates over ordinary queryable dimensions default to query-time slicing. Reuse the base metric rather than creating one metric per dimension value.
   • Time bounds, ad hoc entity selections, sampling/debug predicates, and access-control conditions remain query-time or policy-layer concerns. HAVING remains a post-aggregation constraint, and JOIN ... ON belongs in relationships or dataset modeling.
   • If durable filtered-metric intent is absent or ambiguous, reuse the base metric with the source predicate as query context; ask when possible instead of inventing a filtered metric.

Metric scope gate

Before binding a target or writing YAML, decide from the user's business intent whether the request contains a reusable metric to create, update, or delete. SQL is supporting evidence; its presence alone does not require a metric for every selected expression. For a creation request with no reusable business metric, report status: "skipped", skip_reason: "not_a_metric", and metric_file: null. An explicit request to delete a named metric remains in scope.

Workflow notes

• For requests classified as metrics, bind an existing model target before writes. Try an exact YAML path or model name from the request, then fall back to the complete live inventory. Compare candidates using physical SQL tables, dataset names/sources/descriptions, and business meaning; read plausible files when necessary. Bind only one unique target. If several remain plausible, ask the user when possible or return the selection-required blocker. Load the bound file to learn dataset names, fields, time fields, relationships, and existing metrics; inspect live catalog state when reuse is relevant.
• For provided SQL/history, interpret the complete SQL together with the user's durable business intent, the bound model, compilation feedback, and warehouse evidence. Revise the narrow dataset or metric artifact when that evidence reveals a wrong expression, dataset, dimension qualification, or time grain.
• Reconcile requested reusable metrics with the bound YAML. Inspect the bound model to decide whether native metrics are sufficient or the user's explicit durable result-set intent requires a query-backed dataset; use the narrow dataset tools when a required dataset is missing or incomplete.
• Reference and reconcile: point each metric's DATUS dataset hint at an existing dataset. "Same meaning" requires the same aggregation AND the same window/offset semantics: a base aggregate never covers its cumulative/rolling/period-over-period variants, so running_x/moving_x/previous_x candidates must still be published when only x exists. For a derived metric, make sure its input metrics already exist.
• Every requested metric candidate must pass through upsert_osi_metrics, validation, and publish even when the bound YAML already contains the correct definition. Reuse that exact metric object unchanged; the upsert is a byte-preserving no-op but records the exact publish scope. This makes retries repair a prior run that wrote YAML but stopped before KB sync. Existing metrics are not a skipped outcome.
• Delete only when the user explicitly asks to remove a named metric. Call delete_osi_metrics(path="<target model file>", metric_names=["<name>"]). Missing names are a successful already_absent result so a retry can still clean stale Knowledge Base rows. Do not invent cascade rules: validate the resulting model, then use the validation result and user intent to decide whether to delete another metric, restore one with upsert_osi_metrics, or edit a dependent definition.
• From SQL: find the table (FROM), aggregate expression(s), and classify every predicate using Filter ownership above. Anchor the metric on the aggregated table's existing dataset; encode only confirmed metric-specific conditions with CASE inside the metric expression.