# Semantic Layer

The semantic layer is Definite's record of what your data **means** — the
business names, measures, and join graph that sit between raw lakehouse tables
and the people (and agents) asking questions of them.

It is a thin, lakehouse-native store: seven tables in a `semantic` schema, served
by an HTTP API, an in-app Catalog editor, and the `definite semantic` CLI. There
is no separate metric service to run, no extra database to operate, and no MDL
DSL to learn. A model is YAML you author in your repo; what the runtime reads
is the rows in the lake.

## The data model

| Table | What it holds |
|---|---|
| `semantic.models` | One row per model — a relation plus the metadata about it (label, grain, owner). |
| `semantic.dimensions` | Groupable / filterable attributes. SQL expression + label + synonyms. |
| `semantic.measures` | Aggregate expressions, calculated measures, or `mode`/`column` shorthand with synonyms, format hints, certified flag. |
| `semantic.relationships` | Join edges between models — `left_model`, `right_model`, `left_key`, `right_key`. |
| `semantic.context` | AI / governance entries attached to a target (`measure:orders.gross_revenue`, `github_issues`, …). Carries glossary text, preferred-for / avoid notes, example questions. |
| `semantic.ontology_objects` | Higher-level business concepts such as customer, order, product, revenue, or return. (Physically lives in the `semantic` schema, but is owned by the ontology layer — see [ontology.md](/docs/ontology).) |
| `semantic.ontology_links` | Soft typed links from ontology objects back to semantic models, dimensions, measures, relationships, lakehouse tables, and columns. (Physically lives in the `semantic` schema, but is owned by the ontology layer — see [ontology.md](/docs/ontology).) |

A whole **model bundle** — model + its dimensions, measures, relationships, and
the context entries that target any of them — is the unit you author. The
`PUT /api/v1/semantic/models/{name}` route writes a bundle as one transaction
(replace-semantics: a dimension you remove from the file is dropped from the
table).

## Authoring surface

Three ways in, one source of truth:

- **YAML + CLI** — `definite semantic save -f model.yaml` or `definite semantic
  apply semantic/` to push a whole directory in CI. The recommended workflow:
  keep `semantic/*.yaml` in your repo, review changes in PRs, let CI apply.
- **Catalog UI** — the **Catalog** page in the Definite UI has a model editor:
  create / edit / delete dimensions and measures with a form. Good for ad-hoc
  exploration. Edits write straight to the tables, so an edit made in the UI
  does **not** propagate back to your YAML — see [Drift](#drift) below.
- **HTTP API** — `PUT`, `PATCH`, `DELETE`, `GET` against `/api/v1/semantic/*`.
  The CLI and the UI are both thin clients of this surface.

## HTTP API

All endpoints require a bearer token (the same session token the CLI uses).

| Method | Path | What it does |
|---|---|---|
| `GET` | `/api/v1/semantic/models` | List every model. |
| `GET` | `/api/v1/semantic/models/{name}` | One model bundled with its dimensions, measures, relationships, and context. |
| `PUT` | `/api/v1/semantic/models/{name}` | Atomic save — body is a `ModelSpec` (replace-semantics). |
| `PATCH` | `/api/v1/semantic/models/{name}` | Patch one child object in place. Body: `{kind, keys, patch}`. |
| `DELETE` | `/api/v1/semantic/models/{name}` | Drop the model and all of its children. |
| `POST` | `/api/v1/semantic/context` | Add (or replace) one context entry — `{target, context_type, content}`. |
| `GET` | `/api/v1/semantic/search?q=...` | Token-aware ranked search across models / dimensions / measures. |
| `GET` | `/api/v1/discovery/search?q=...` | Search ontology and semantic metadata together; returns bounded per-layer results, partial errors, and next actions. |

For an analytical question, start with unified discovery rather than choosing
one layer yourself. A successful request searches both layers; an empty result
set is a discovery miss, not proof that the underlying lakehouse data is
absent. If one layer fails, the response preserves results from the other and
sets `partial: true`. Only failure of both layers returns `503`.

## The CLI

Semantic commands are thin wrappers over the API. Same auth and `--api-url`
flags as `definite run` — explicit `--token`, then `DEFINITE_TOKEN`, then the
session saved by `definite login`.

| Command | What it does |
|---|---|
| `definite discover <query>` | Search ontology and semantic metadata together before querying data. |
| `definite semantic list` | List every model (name, label, relation). |
| `definite semantic get <name>` | Show one model. Default output is YAML — pipe to a file to round-trip. |
| `definite semantic save -f <file>` | Create or replace one model from a YAML file. Atomic. |
| `definite semantic apply <dir>` | Save every `*.yaml` in `<dir>` — the GitOps sync. |
| `definite semantic pull <name>` | Dump a live model back to `<name>.yaml` (or `-o path.yaml`). |
| `definite semantic delete <name>` | Drop the model and all of its children. `--yes` to skip the prompt. |
| `definite semantic search <query>` | Rank models / dimensions / measures by normalized query terms across name, label, synonym, and description. |

Add `--format json` on any subcommand to get machine output (Fi runs do this
automatically when stdout is not a TTY).

### Examples

```bash
# List every model
definite semantic list

# Apply every YAML file in semantic/ — what CI runs on merge
definite semantic apply semantic/

# Edit a model with the Catalog UI, then re-capture it back into your repo
definite semantic pull orders -o semantic/orders.yaml

# Find every measure with a "revenue" synonym or label
definite semantic search revenue

# Recommended first step for an analytical question (quotes are optional)
definite discover monthly visitors by brand

# Drop a model non-interactively
definite semantic delete legacy_orders --yes
```

Search is case-insensitive and token-aware: a query such as `monthly visitors`
can match terms distributed across a label, description, and synonyms, while
exact names and phrases rank first. Longer exploratory queries return partial
matches ordered by the number and quality of matched terms. Search and list read
the current semantic catalog on every call, so a newly saved model is available
immediately; there is no background learning or refresh delay.

## YAML format

One file, one model. The body is the API's `ModelSpec` shape; an optional
top-level `context:` list is fanned out to `POST /context` by `save` so a
single file applies the whole thing.

```yaml
name: github_issues
label: GitHub Issues
relation: github.issues
description: Issues synced from the GitHub repo.
metadata:
  grain: one row per issue
  owner: data-team

dimensions:
  - name: state
    label: State
    expression: state
    metadata:
      synonyms: [status]
  - name: created_month
    label: Created Month
    expression: "date_trunc('month', created_at::timestamp)"
    metadata:
      is_time: true

measures:
  - name: issue_count
    label: Issue Count
    expression: count(*)
    metadata:
      certified: true
      synonyms: [issues, tickets, bugs]
  - name: open_issue_count
    label: Open Issues
    mode: count
    column: id
    filter: "state = 'open'"
  - name: open_rate
    label: Open Rate
    expression: "measure(open_issue_count) * 1.0 / nullif(measure(issue_count), 0)"

relationships: []

context:
  - target: measure:github_issues.issue_count
    context_type: ai_usage
    content:
      preferred_for: [counting issues]
      avoid:
        - "select * from github.issues"
      example_questions:
        - How many open issues are there?
```

A ready-to-apply copy of this file lives at `examples/semantic/github_issues.yaml`
in the repo.

**Field reference.** `name` is required and is the model's stable identifier
(it is what `apply` keys on). `relation` is the lakehouse table the model is
over (`schema.table`). `metadata` is a free-form JSON map — by convention
`grain`, `owner`, and per-dimension/measure `synonyms`, `is_time`, `certified`,
`format` are honoured by the UI and Fi.

**Measures.** A measure can be authored directly with `expression`, or with
the shorthand fields `mode`, `column`, and optional `filter`. `expression`
wins if both forms are present. Supported modes are `count`, `sum`, `avg`,
`min`, `max`, and `count_distinct`; every mode requires `column`. Use
`expression: count(*)` for whole-table counts.

**Reference warnings.** On save, `relation` is resolved against the lake's
`information_schema` and every compiled measure `column` against that
relation. Anything that does not resolve comes back in the save response's
`warnings` list rather than failing the write, so you can author a model ahead
of the pipeline that lands its table. A `relation` in a schema that does not
exist yet has severity `unresolved`; a relation missing from a schema that
does exist, or a column missing from a relation that does, is `missing`. Both
`relation` and `column` are spliced into the compiled SQL verbatim, so values
that hold SQL rather than a name (a subquery relation, `column: "*"`, a `CASE`
expression) are skipped instead of guessed at. See
[ontology.md](/docs/ontology) for the same mechanism on link targets.

```yaml
measures:
  - name: revenue
    mode: sum
    column: amount

  - name: active_users
    mode: count_distinct
    column: user_id
    filter: "deleted = 0"
```

**Calculated measures.** A measure `expression` may reference another measure
on the same model with `measure(name)`. The compiler expands each referenced
measure's aggregate inline before generating the final SELECT. Cycles, unknown
measure names, malformed `measure(...)` calls, and expansion chains deeper than
10 levels are rejected at compile time with a 400 error.

```yaml
measures:
  - name: clicks
    mode: sum
    column: click_count

  - name: blocks
    mode: sum
    column: block_count

  - name: block_rate
    expression: "measure(blocks) * 1.0 / nullif(measure(clicks), 0)"
```

**Context targets** address a specific thing:

- `<model_name>` — the model itself (e.g. `github_issues`).
- `measure:<model>.<name>` — a measure (e.g. `measure:github_issues.issue_count`).
- `dimension:<model>.<name>` — a dimension.

`context_type` is a string you pick (`glossary`, `ai_usage`, `ownership`, …).
Anything stored under `content` is JSON — Fi reads `synonyms`, `preferred_for`,
`avoid`, and `example_questions` to ground its answers.

## Ontology

The ontology — the top-level layer of business concepts (`customer`, `revenue`,
`company`, …) that link via soft typed references down to semantic objects, raw
tables/columns, scripts, docs, or nothing at all — now has its own surface. See
[ontology.md](/docs/ontology) for its API, CLI, and YAML format.

## Drift

The CLI keeps the YAML files in **your** git; the lakehouse keeps the rows.
That split is dbt-shaped: files are the authoring source, the warehouse holds
the applied state.

A consequence: the **Catalog UI edits the tables directly**. If someone tweaks
a measure in the UI and you re-run `definite semantic apply semantic/`, the
file wins and the UI edit is overwritten. Pick a discipline:

- Treat UI edits as exploratory; keep `apply` (from files, in CI) authoritative.
- Or keep prod edits file-only and use `pull` to escape-hatch a UI change back
  into its file before applying.

`pull` is the round-trip lever — re-capture a model that has drifted, diff it,
and commit.

## Read paths

The same `semantic.*` tables back several read surfaces:

- **Catalog UI** — list / view / edit models.
- **Fi** — the agent starts analytical questions with unified `discover`, then
  follows likely concepts/models with describe and semantic-query tools. The
  targeted `search_ontology` and `search_semantic` MCP tools remain available
  for follow-up clients (see [MCP Server](/docs/mcp)).
- **Custom SQL** — the tables are first-class in the lakehouse, so
  `select * from semantic.measures where ...` works from any SQL surface.

All three are reading the same rows. There is no second source of truth at
runtime.

## Running queries

The semantic layer stores models — to actually count issues, the engine
compiles a JSON query naming dimensions and measures into SQL and runs it
through the same lakehouse + RLS + audit-log path as raw SQL.

### The JSON shape

```json
{
  "model": "github_issues",
  "measures": ["issue_count", "open_issues"],
  "dimensions": ["state", "created_month"],
  "filters": [
    {"dimension": "state", "operator": "in", "values": ["open", "closed"]},
    {"dimension": "created_month", "operator": "gte", "values": ["2026-01-01"]}
  ],
  "order": [{"field": "created_month", "direction": "desc"}],
  "limit": 1000,
  "offset": 0
}
```

A bare identifier (`state`, `issue_count`) refers to a field on the FROM
model. A dotted identifier (`repos.repo_name`) refers to a field on a
foreign model — the engine auto-joins by walking the model's
`relationships` (one-hop only in v1).

**Filter operators:** `equals`, `not_equals`, `in`, `not_in`, `gt`, `gte`,
`lt`, `lte`, `contains`, `is_null`, `is_not_null`. Filters target
dimensions only; HAVING-style measure filters are deferred.

**Order:** field is a bare or dotted name (need not appear in `dimensions`
/ `measures`). Direction is `asc` (default) or `desc`.

**Defaults & limits:** `limit` defaults to 10000; hard ceiling is 100000.
`offset` defaults to 0. At least one of `dimensions` or `measures` is
required; if there are measures, a `GROUP BY` over every dimension is
generated; if there are only dimensions, the SELECT is `DISTINCT`.

### Endpoints

| Method | Path | Returns |
|---|---|---|
| `POST` | `/api/v1/semantic/query` | `{columns, rows, row_count, sql}` |
| `POST` | `/api/v1/semantic/query/compile` | `{sql}` — no execution, no audit row |

### CLI

```bash
# Inline form
definite semantic query --model github_issues \
  -m issue_count -m open_issues \
  -d state -d created_month \
  --where 'state in open,closed' \
  --where 'created_month gte 2026-01-01' \
  --limit 100

# JSON file form
definite semantic query -f examples/semantic/queries/issue_count_by_state.json

# Compile only (no execution) — print the SQL the engine would have run
definite semantic query -f q.json --compile
```

`--format json|table|csv` works for query just like for `definite run query`.
Tables default for a TTY, JSON otherwise.

### Errors

| HTTP | When |
|---|---|
| 400 | unknown dimension or measure on the model |
| 400 | invalid model YAML/API shape (unknown field, duplicate name, missing relation, invalid measure mode) |
| 400 | calculated-measure reference cycle, unknown reference, malformed `measure(...)`, or expansion depth over 10 |
| 400 | filter targets a measure (filters must target dimensions) |
| 400 | unsupported filter operator |
| 400 | ambiguous join (multiple relationships connect two models) |
| 400 | unreachable join (no relationship from FROM model to foreign model) |
| 400 | limit exceeds 100000 |
| 404 | model not found |
| 403 | data-access denial — the caller has no grant on the underlying relation |

RLS is enforced on the compiled SQL: a user without a grant on `github.issues`
cannot run a `model: github_issues` query even though the model name is
"their" semantic concept. Admins bypass.

### Limitations (v1)

- One-hop joins only. Chains across three or more models will fail with
  "no relationship from X to Z".
- A relationship's `left_key` / `right_key` must reference dimensions whose
  `expression` is a plain column name — compound expressions on join keys
  are rejected at compile time.
- Cross-model queries do not rewrite authored expressions to alias-qualify
  column references. If two joined tables share a column name and a
  dimension's `expression` is the bare name, DuckDB will error on the
  ambiguity; fix by qualifying the column in the dimension itself.
- Calculated measures can reference measures on the same model only. Cross-model
  calculated measures need explicit SQL today.
- Measure-on-measure (HAVING) filters are not supported.
