osi-semantic-authoring

name	osi-semantic-authoring
description	OSI core schema semantic model authoring specification — field roles, structural keys, Datus extension hints, relationships, and validation
tags	["semantic-model","osi"]
version	2.3.0
user_invocable	false
disable_model_invocation	false
allowed_agents	["gen_semantic_model","gen_metrics"]

OSI Semantic Authoring

Describe tables as strict OSI (Open Semantic Interchange) core schema documents plus Datus business hints.

CRITICAL BOUNDARY: You author OSI core semantics only. You do NOT write MetricFlow data_source:, measures:, identifiers:, agg_time_dimension, create_metric, or any execution-engine YAML. The Datus OSI compiler lowers OSI core documents to the configured backend.

The OSI expression dialect is shown in the system prompt Workspace section. Plan the target model name and file with plan_osi_semantic_model_target after identifying the fact and dimension tables; use the returned values exactly (<osi_dialect> below stands for that dialect).

Field roles — the three-way decision

OSI core separates three column roles by structure, not by type labels:

Role	How it is declared	Examples
Dimension — used for GROUP BY / filtering	A field with a dimension: block	code columns, names, statuses, dates
Key — identifies rows / joins	Dataset primary_key / unique_keys, relationship from_columns/to_columns. NOT a field type.	declared PK columns, FK join columns
Measure source — only aggregated	A field without a dimension: block (just name/expression/description)	balances, amounts, quantities, precomputed rates

The presence of the dimension: block IS the dimension declaration. A field without the block is a plain row-level expression: it documents the column and may back metric expressions, but it is not exposed for grouping (get_dimensions will not list it). NEVER write dimension: {is_time: false} to mean "this is not a time column" — omit the entire block for non-dimension fields.

What you produce

One valid OSI core document for the current business domain / semantic model scope. The authoritative document shape — object structures, field lists, version, and the Datus execution subset notes — is the OSI Core Authoring Specification section of the system prompt; author against it exactly. This skill adds the decision rules the specification cannot express: which columns become which role, and how to fix validation conflicts.

Authoring rules

1. Root schema is fixed. Root keys are only version and semantic_model. semantic_model is a list. Do NOT write top-level datasets:, relationships:, or metrics:.
2. Use OSI core dataset shape. Dataset source is a string, not {table: ...}. Dataset columns are fields, not dimensions. Field expressions are expression.dialects[], not expr. Use the exact OSI expression dialect from the system prompt in every expression.dialects[].dialect.
3. Datus-only hints go into custom_extensions. The only field-level hint is time_granularity (on the time field). Dataset-level: source_type: "query" for query sources. Relationships use the native OSI core name as the stable joined-dimension path prefix; do not add a relationship alias extension. Do NOT emit field type hints — roles are expressed structurally per the table above.
4. Semantic model boundary. One OSI semantic_model represents the current business domain. Put all related logical datasets needed by the provided SQL history in this semantic model, with relationships declared once under the semantic model object.
5. Canonical logical datasets. For the same source and row grain, create one canonical dataset that metrics can reference by name. Create a separate dataset only when the logical row grain or fixed business scope is genuinely different.
6. Dataset description and ai_context are required. description: one concise human sentence with the business entity and row grain. ai_context.instructions: when to use the dataset, the row grain (spell out the full grain explicitly — this is where grain lives when no primary key is declared), the primary time field, important row-selection columns, relationship caveats.
7. Separate physical primary keys from verified logical keys.
   • Write primary_key ONLY when source metadata or an explicit data contract declares it: a PRIMARY KEY in DDL, or pk: true columns in describe_table. Historical SQL and profiling must never manufacture a physical primary key.

Worked example — monthly snapshot table

Input (describe_table): branch_loan_quality_monthly — a monthly loan-quality statistics table, no declared primary key, columns: snapshot_date (date), branch_no/assess_dim_code/scope_code (varchar code columns), branch_name (varchar), loan_balance/npl_balance/overdue_balance (numeric balances), npl_rate/overdue_rate (numeric precomputed rates).

Correct field layout:

        # no primary_key: the source declares none — the grain ("one row per month per branch per assessment dimension per scope") goes in ai_context.instructions
        fields:
          - name: snapshot_date
            expression: {dialects: [{dialect: <osi_dialect>, expression: snapshot_date}]}
            dimension: {is_time: true}
            custom_extensions: [{vendor_name: DATUS, data: '{"time_granularity":"month"}'}]
          - name: branch_no
            expression: {dialects: [{dialect: <osi_dialect>, expression: branch_no}]}
            dimension: {}
          - name: assess_dim_code
            expression: {dialects: [{dialect: <osi_dialect>, expression: assess_dim_code}]}
            dimension: {}
          - name: scope_code
            expression: {dialects: [{dialect: <osi_dialect>, expression: scope_code}]}
            dimension: {}
          -  
             { [{ ,  }]}
             {}
                                
             { [{ ,  }]}
             
          
                                    
             { [{ ,  }]}
             
          

WRONG (do not do this): declaring loan_balance or npl_rate as fields with a dimension: block (or dimension: {is_time: false}); inventing primary_key: [branch_no, ...] when the DDL declares none; promoting JOIN columns to unique_keys without a passing full-table candidate-key verification; adding {"type":"numeric"} hints.

Workflow notes

• Resolve the target before writing. Priority: an explicit user-provided semantic model name; an existing model containing the core fact table; an inferred business domain; the core fact table as fallback.
• For deletion or other existing-YAML maintenance without an exact model name/file, call list_existing_osi_semantic_models and select from the complete live inventory using dataset name, source, description, and business meaning. Give the unique existing model name to the planner; ask when multiple models remain plausible.
• Put the core fact table first in fact_tables. Pass dimensions separately; dimension tables never participate in naming.
• When the resolver returns an existing file, preserve its semantic model name permanently, even when adding dimensions. Read and update that file instead of creating a renamed model.
• For a new resolved target, call upsert_osi_datasets directly; the first non-empty batch creates the document atomically. For an existing target, read it first and use narrow mutations.
• For an existing target, keep its current semantic model name and preserve all unrelated datasets, relationships, and metrics. Add or update datasets with upsert_osi_datasets, and remove only explicitly requested names with delete_osi_datasets; never replace the file with a partial document containing only the requested objects.
• Use the original SQL only as source evidence. Call inspect_semantic_sources with the physical tables that need modeling, and use the live schema plus SQL usage to decide fields, keys, relationships, and time dimensions. Do not treat planner guesses as frozen schema decisions.
• Before validation, cross-check every requested metric expression against the authored datasets and add any missing aggregate, distinct-key, predicate, or arithmetic input as a field with the role defined by rule 8.
• Treat source_columns / target_columns returned by relationship discovery as one ordered composite when key_arity > 1. Put every complete target list you intend to use into the single batch validate_semantic_key_candidates call; never validate or author its components independently.
• When a critical modeling choice is ambiguous (which column set is the grain, which is the primary time dimension), ASK before generating.
• Call validate_semantic(scope="semantic_model", semantic_model_name="<planned semantic model name>") without a custom checks subset after writing or editing the OSI semantic model, and fix errors with edit_file until the adapter's complete default profile passes; treat warnings about "aggregates column X which is also a dimension" as instructions to drop that field's block or the field itself.