# semantic-model-kit

[![CI](https://github.com/25andresbernal/semantic-model-kit/actions/workflows/ci.yml/badge.svg)](https://github.com/25andresbernal/semantic-model-kit/actions/workflows/ci.yml)

A vendor-neutral semantic model that makes a warehouse legible to LLMs, with compilers to
Snowflake semantic views and Open Semantic Interchange, an MCP server that lets agents query
governed metrics, and an eval that measures the accuracy gain.

## Why this exists

A raw warehouse schema tells an LLM what columns exist. It does not tell it which of two join
paths is correct, which of two revenue columns finance means, or that a legacy status code
needs decoding before anyone reads it in natural language. Snowflake's engineering team names
four production failure modes that follow from asking a model to guess at all of that: metric
drift, join ambiguity ("a text-to-SQL system picking the wrong path silently returns a
confidently wrong number"), concept mismatch, and tool duplication between BI and agents.
([Why you need a semantic model](https://www.snowflake.com/en/blog/engineering/why-you-need-a-semantic-model/), Snowflake engineering blog.)

Two independent benchmarks measure the effect of resolving that ambiguity instead of asserting
it should be resolved. dbt Labs ran the ACME Insurance dataset (15 tables, 11 questions, 20 runs
each) through direct text-to-SQL and through a semantic layer: Claude Sonnet 4.6 went from 90.0%
to 98.2%, GPT-5.3 Codex from 84.1% to 100%, and the semantic-layer failures that remained were
explicit refusals, not confidently wrong numbers.
([Semantic layer vs. text-to-SQL](https://docs.getdbt.com/blog/semantic-layer-vs-text-to-sql-2026), dbt Labs, 2026-04-07.)
A second, independent paper found the same shape of result on a related ACME Insurance question
set (38 questions): semantic path compilation answered 97.4% correctly across all runs, against
55.3% for a direct text-to-SQL baseline.
([Bounded Semantic Planning and Deterministic Compilation for Reliable Enterprise Text-to-SQL](https://arxiv.org/abs/2608.16663), Yi Ai, arXiv 2608.16663, 2026-08-17.)

This kit exists to make writing that kind of governed model tractable: one YAML contract, a
linter that catches the disputes before an LLM ever sees them, compilers that keep the model
useful across whichever standard a warehouse runs, and an eval harness that proves the gain on
your own data rather than citing someone else's number.

Those figures are external. This repo's own eval numbers, further down, come from a deterministic fake model and are labeled as such.

## Demo

Real captured output, not illustrative. `semkit validate` against the Northgate Logistics
example, which resolves every join and revenue ambiguity in its own warehouse on purpose:

```
$ uv run semkit validate examples/northgate_logistics/model.yaml
examples/northgate_logistics/model.yaml: 0 errors, 0 warnings
```

`semkit query`, the deterministic metric compiler, grouping net revenue by customer region for
fiscal year 2025 (no LLM involved: this is a metric name, a group-by, and a filter turned into
SQL by the join graph, then run against DuckDB):

```
$ uv run semkit query examples/northgate_logistics/model.yaml \
    --metric net_revenue --by customers.region --where "calendar.fiscal_year = 2025"
customers.region    net_revenue
------------------  -----------
Great Lakes         461,525.44
Gulf Coast          620,047.94
Mountain West       466,440.85
Northeast Corridor  653,194.43
Pacific Northwest   957,477.47
```

`semkit compile` turns the same model into each target standard. The Snowflake
`CREATE SEMANTIC VIEW` output, trimmed (the header comment names the two
non-preferred relationships left out because Snowflake has no preferred-path flag):

```
$ semkit compile examples/northgate_logistics/model.yaml --to snowflake-sql
```

```sql
CREATE OR REPLACE SEMANTIC VIEW northgate_logistics
  TABLES (
    customers AS main.customers PRIMARY KEY (customer_id) WITH SYNONYMS ('clients', 'accounts') COMMENT = 'Northgate''s billing customers. One row per customer account.',
    sites AS main.sites PRIMARY KEY (site_id) WITH SYNONYMS ('locations', 'facilities') COMMENT = 'Physical operating locations (terminals, rail yards, trucking lanes, warehouses) that belong to a customer.',
    contracts AS main.contracts PRIMARY KEY (contract_id) WITH SYNONYMS ('agreements') COMMENT = 'Service contracts between Northgate and a customer for a given site.',
    work_orders AS main.work_orders PRIMARY KEY (work_order_id) WITH SYNONYMS ('service orders', 'tickets') COMMENT = 'Individual work orders performed at a site.',
    invoices AS main.invoices PRIMARY KEY (invoice_id) WITH SYNONYMS ('bills') COMMENT = 'Customer invoices. Has two dates (invoice_date and posted_date) and a net total; see the invoice_lines entity for the gross line-item amounts.',
    invoice_lines AS main.invoice_lines PRIMARY KEY (invoice_line_id) WITH SYNONYMS ('line items') COMMENT = 'Line items on an invoice: gross service charges and credit adjustments. See invoices.invoice_amount for the net total after credits.',
    calendar AS main.calendar PRIMARY KEY (date) WITH SYNONYMS ('dates', 'fiscal calendar') COMMENT = 'Day-grain date dimension used to resolve fiscal calendar questions.',
    site_safety_monthly AS main.site_safety_monthly PRIMARY KEY (site_id, month) WITH SYNONYMS ('safety exposure') COMMENT = 'Pre-aggregated safety exposure at the (site, month) grain: incident counts and labor hours worked, already at the grain the incident rate metric needs so that joining raw incidents to raw work orders (which would fan out and inflate both counts) is never necessary.',
    employees_dim AS main.employees_dim PRIMARY KEY (employee_id) WITH SYNONYMS ('workforce', 'staff') COMMENT = 'Fictional, non-identifying workforce dimension (no names, no personal data) used for headcount-shaped questions. Not joined to any other entity in this example.'
  )
...
```

The other targets work the same way: `--to snowflake-yaml`, `--to osi` (validated
against the vendored Apache Ossie schema), `--to metricflow`, and `--to context-pack`.

`scripts/demo.py` calls the MCP server the way a real client would (the SDK's in-memory
`Client`, no subprocess): describes a metric, runs a governed query showing the SQL it used,
explains why one join path was preferred over another, and searches for a term ("region")
genuinely ambiguous across more than one entity:

```json
{
  "metric": "net_revenue",
  "by": ["customers.region"],
  "sql": "SELECT customers.region AS \"customers.region\", SUM(invoices.invoice_amount) AS net_revenue FROM (...) AS invoices JOIN (...) AS customers ON invoices.customer_id = customers.customer_id GROUP BY customers.region ORDER BY customers.region LIMIT 5",
  "columns": ["customers.region", "net_revenue"],
  "rows": [["Great Lakes", 1514442.46], ["Gulf Coast", 2071366.76], ["Mountain West", 1719903.52]]
}
```

Full output, including `describe_metric`, `explain_join`, and the ambiguous `search_semantics`
call, is in [`scripts/demo.py`](scripts/demo.py); run it to see all of it.

## Architecture

```mermaid
flowchart LR
    YAML[model.yaml\nvendor-neutral contract]
    VALIDATE[semkit validate\nschema + lint rules]
    subgraph Compilers[semkit compile]
        SNOWYAML[snowflake-yaml]
        SNOWSQL[snowflake-sql]
        OSI[osi\nApache Ossie v1]
        MF[metricflow\ndbt semantic_models]
        CP[context-pack\nMarkdown + JSON]
    end
    QUERY[semkit query\ndeterministic metric compiler]
    DB[(DuckDB warehouse)]
    SERVE[semkit serve\nMCP server: tools,\nresources, one prompt]
    EVAL[semkit eval\nschema-only vs. context-pack\nvs. semantic-api]

    YAML --> VALIDATE
    VALIDATE --> Compilers
    VALIDATE --> QUERY
    QUERY --> DB
    SERVE --> QUERY
    SERVE -.->|semantic://context-pack| CP
    EVAL --> QUERY
    EVAL --> DB
```

## Quick start

Takes under five minutes, no API key required.

```bash
git clone https://github.com/25andresbernal/semantic-model-kit.git
cd semantic-model-kit

export PATH="$HOME/.local/bin:$PATH"   # if uv is not already on PATH
uv venv --python 3.12
uv pip install -e ".[dev]"

# Build the example warehouse (deterministic, seeded, safe to publish; gitignored output)
uv run python examples/northgate_logistics/generate_data.py

# See it work end to end
uv run semkit validate examples/northgate_logistics/model.yaml
uv run semkit query examples/northgate_logistics/model.yaml --metric revenue
uv run python scripts/demo.py

# Run the test suite
uv run pytest
```

### Connect the MCP server to a client

**Claude Desktop** (`claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "semantic-model-kit": {
      "command": "uv",
      "args": ["run", "--directory", "/absolute/path/to/semantic-model-kit",
                "semkit", "serve", "examples/northgate_logistics/model.yaml"]
    }
  }
}
```

**Claude Code:**

```bash
claude mcp add semantic-model-kit -- uv run --directory /absolute/path/to/semantic-model-kit \
  semkit serve examples/northgate_logistics/model.yaml
```

No environment variables are needed for the default stdio transport. Point `--transport http`
at a real deployment and set `SEMKIT_MCP_TOKEN` (see Configuration below) instead.

## Configuration

No configuration or API key is required for the quick start. `semkit serve MODEL.yaml` flags:
`--database PATH` (DuckDB file `query_metric` runs against, defaults to `warehouse.duckdb` next
to the model), `--transport stdio|http` (default `stdio`, no auth needed), `--port N` (default
`8000`, HTTP only). `SEMKIT_MCP_TOKEN`, required only for `--transport http`, is the bearer
token clients must send as `Authorization: Bearer <token>`; the server refuses to start over
HTTP without it.

## How it works

- **`model.py`**: Pydantic v2 models for the YAML contract, and the source of truth a JSON
  schema is exported from.
- **`lint.py`** and **`graph.py`**: the rules behind `semkit validate` (every metric has an
  owner and a verified question, every relationship resolves, synonyms do not collide) and the
  join-path resolver they share with the query compiler, so a validated model cannot produce an
  ambiguous join later.
- **`compiler/query.py`**: turns a metric name, a group-by list, and a filter list into one SQL
  statement against DuckDB, using that same join resolver.
- **`serve/`**: the MCP server. `server.py` registers eight tools (`list_entities`,
  `describe_entity`, `list_metrics`, `describe_metric`, `query_metric`, `search_semantics`,
  `explain_join`, `verified_questions`), two resources (`semantic://model`,
  `semantic://context-pack`), and one prompt. `context.py` renders the context-pack resource;
  `auth.py` is the HTTP transport's bearer-token middleware. `query_metric` is the only tool
  that touches DuckDB, and it raises a tool error naming the actual problem (unreachable entity,
  ambiguous join, non-additive-dimension violation) rather than guessing.
- **`cli.py`**: the `semkit` entry point (`validate`, `query`, `serve`, plus the compilers and
  eval command in [`docs/standards-comparison.md`](docs/standards-comparison.md) and
  [`docs/eval-method.md`](docs/eval-method.md)).
- **`examples/northgate_logistics/`**: a fictional logistics company with a seeded data
  generator, a fully resolved semantic model, and 40 questions with gold SQL.

## Design decisions

- **A vendor-neutral source model with compilers, instead of authoring in one vendor's format.**
  Snowflake semantic views, Open Semantic Interchange, and dbt's MetricFlow each have their own
  YAML shape and their own gaps. Authoring once and compiling out means a model built for a
  DuckDB proof of concept is not thrown away the day the warehouse becomes Snowflake.
- **Preferred join paths are declared, not inferred.** A join-inference heuristic is exactly the
  silent guess that produces a confidently wrong number when a warehouse offers more than one
  legitimate path between two tables. Marking one path `preferred: true`, and having
  `semkit validate` refuse an unresolved ambiguity, moves that decision to authoring time, where
  it is cheap to get right, instead of query time, where it is invisible.
- **A deterministic semantic query API sits beside free-form SQL, not in place of it.**
  `query_metric` and `semkit query` only express what this model certifies. For those
  questions, the answer compiles the same way every time and shows its SQL, rather than being
  generated fresh, and inconsistently, by a model each time someone asks.
- **Verified questions double as the eval set.** A separate test suite invites drift from the
  model's own documentation. Verified questions are both the regression test a PM checks before
  shipping a change and the exact input [`docs/eval-method.md`](docs/eval-method.md)'s harness
  runs.
- **The committed eval numbers come from a fake model, and say so.** A report scored by a real
  LLM would need a paid API key just to reproduce and would drift with every model version. The
  committed [`docs/EVAL-REPORT.md`](docs/EVAL-REPORT.md) uses a deterministic `FakeLLM` so CI
  stays offline; the real accuracy claims above come from the two cited external benchmarks, not
  from this repo's own committed run.

## Standards this compiles to

Snowflake semantic views, Apache Ossie (Open Semantic Interchange), and dbt MetricFlow, with Cube, LookML, and Databricks metric views compared alongside; what each captures and where the compile is lossy is in [`docs/standards-comparison.md`](docs/standards-comparison.md).

## What the eval measures

The eval runs the same 40 Northgate questions three ways and scores each answer
by result-set equivalence against gold SQL on DuckDB. The numbers below come from
this repo's committed run with `FakeLLM`, a deterministic stand-in that behaves
like a competent but context-blind model. They demonstrate the harness and the
failure classes, not any real model's accuracy; run with `--provider anthropic`
or `--provider openai-compatible` and a key to measure a real one.

| Mode | What the model receives | Accuracy | Correctly refused | Wrongly refused | Confident-wrong |
|---|---|---|---|---|---|
| schema-only | table and column names only | 28% (11/40) | 0 | 0 | 29 |
| context-pack | schema plus the compiled context pack | 82% (33/40) | 4 | 0 | 3 |
| semantic-api | metric and dimension catalog; kit compiles the SQL | 52% (21/40) | 4 | 12 | 3 |

Two things to read off that table. Confident-wrong answers, the failure the
Snowflake engineering blog warns about, fall from 29 to 3 once the model has the
context pack. And the semantic-api mode never picks a wrong join, date column,
or amount column; its lower headline number is because 14 of the 40 questions have
no declared metric, so it refuses them, which is the honest outcome for a governed
interface and the reason the two modes belong together.

See [`docs/eval-method.md`](docs/eval-method.md) for how the three modes (schema-only,
context-pack, semantic-api) are scored, and [`docs/EVAL-REPORT.md`](docs/EVAL-REPORT.md) for the
full per-question report.

## Roadmap

- A second, non-fake judge/LLM eval run against a real provider, published alongside the
  committed FakeLLM report rather than in place of it.
- A `refresh` command that re-validates a model against a live warehouse's actual schema.
- Pagination on `query_metric` and `search_semantics` for a model larger than one domain.

## Contributing

Issues and pull requests are welcome. Before opening one: `uv run ruff check .`,
`uv run ruff format .`, `uv run pytest`. If you change
`examples/northgate_logistics/generate_data.py`, rerun it and confirm `tests/test_query.py` and
`tests/test_serve.py` still pass against the regenerated warehouse.

## License

MIT. See [LICENSE](LICENSE).