> For the complete documentation index, see [llms.txt](https://docs.rootcause.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.rootcause.ai/api-and-integrations/anchor-sql.md). # Anchor SQL Reference Anchor SQL is the platform's query language over the [ontology](/user-guide/ontology-concepts.md). It is SQL with the table layer removed: you name **concepts**, and the ontology decides which sources hold them, how to join those sources, how to align their time columns, and how to match their locations. The same language runs everywhere: over the REST API (`POST /api/v1/workspaces/{wsId}/ontology/query`), from the [Python SDK](/api-and-integrations/sdk-getting-started/sdk-ontology-queries.md) (`Ontology.sql`), and inside the [connected assistant](/api-and-integrations/api-access/mcp-integration/mcp-capabilities.md). *** ## The mental model A concept is the shared meaning of a column across every source that carries it. "Invoice Total" may live in a billing export, "Support Tickets" in a helpdesk extract, "Contract Type" in a CRM table, and the place names in a geo enrichment sidecar — but there is one concept for each, and one name you write. Three of those concepts are special. The workspace's **identifier**, **time**, and **location** concepts are its *anchors*: they are what the planner joins on. Referring to them is how you get cross-source alignment without writing it. That is the whole trade. You give up naming tables and join keys; you get a statement that does not change when the data is re-sourced, re-partitioned, or joined through a different intermediate table. ### One question, four sources *"Monthly invoiced revenue and average support load per country, for two-year contracts."* The data lives in four places: a billing export, a CRM account table, a helpdesk extract at a different grain, and a geography lookup. In standard SQL you spell out every relationship: ```sql SELECT date_trunc('month', b.invoice_date) AS month, g.country AS country, SUM(b.invoice_total) AS invoiced, AVG(t.ticket_count) AS avg_tickets FROM billing_export b JOIN crm_accounts c ON c.account_id = b.account_id JOIN geo_lookup g ON g.postcode = c.postcode LEFT JOIN ( SELECT account_id, date_trunc('month', opened_at) AS month, COUNT(*) AS ticket_count FROM support_tickets GROUP BY 1, 2 ) t ON t.account_id = c.account_id AND t.month = date_trunc('month', b.invoice_date) WHERE c.contract_type = 'Two year' GROUP BY 1, 2 ORDER BY 1 ``` In Anchor SQL you name the four things you want and how to bucket them: ```sql SELECT time(month), location(country), SUM("Invoice Total"), AVG("Support Tickets") WHERE "Contract Type" = 'Two year' GROUP BY time(month), location(country) ORDER BY time(month) ``` Nothing is hidden. The identifier join, the grain reconciliation between billing and helpdesk, and the geo lookup all still happen — they came from the ontology instead of from you. The compiled plan comes back on every response (`plan.chips`, `plan.joinPlan`, `plan.strategy`), so you can read exactly which sources were joined, on what, and with how much fan-out. *** ## Coming from SQL Habits that do not transfer, and what to do instead. | Habit | Why it does not apply | Instead | | ----------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------ | --------------------------------------------------------------------------------------------- | | `FROM orders` | There are no tables in the language. A concept may span several sources, and which ones are used is a planning decision | Omit `FROM` entirely | | `JOIN … ON a.id = b.id` | Join paths come from the ontology's identifier and time concepts | Reference the concepts; the planner joins. Pin with `ON "source"` if it picks the wrong spine | | `orders.revenue`, `o.revenue` | A qualifier names a table, and there is no table to name. `"Orders"."Revenue"` parses but then binds to nothing and fails with `unknown_concept` | `"Revenue"`, unqualified. Disambiguate with `FROM source:"…"` or `ON "…"` | | `date_trunc('month', created_at)` | Bucketing is a first-class operation on the time anchor, not a function over a named column | `time(month)` | | `GROUP BY 1, 2` | Ordinals refer to output positions; the resolver binds by name | Repeat the projection: `GROUP BY time(month), "Region"` | | `ORDER BY 2 DESC` | Same — an ordinal is not a name | Alias the projection and order by the alias, or repeat the aggregate | | Re-deriving a KPI (`SUM(rev) / COUNT(DISTINCT cust)`) | The workspace already has a definition; your reconstruction will drift from it | `SHOW METRICS`, then reference the metric by name | | `WHERE country = 'France'` after joining a geo table | Location matching is an anchor operation over enriched geography | `WHERE location = 'France'` or `WHERE location.country = 'FR'` | | `SELECT *` | The projection decides which sources join and which columns are read | Name the concepts you want | | `'single'` vs `"double"` quotes used loosely | Double quotes are always a **concept**; single quotes are always a **string literal**. `WHERE "Region" = "EMEA"` looks for a concept called EMEA | `WHERE "Region" = 'EMEA'` | *** ## Keyword reference Everything the dialect adds on top of ordinary `SELECT` syntax. Reserved words are written **unquoted**; concept names are always **double-quoted**. | Keyword | Meaning | Example | Replaces in standard SQL | | -------------------------------- | -------------------------------------------------------------------------------------------------------------------- | ---------------------------------------- | -------------------------------------------------------------------------- | | `"Concept Name"` | A concept, by name. Matches the concept's name first, then its schema field name, case-insensitively | `SELECT "Monthly Charges"` | A qualified column reference — and the joins needed to reach it | | `entity` | The workspace's identifier concept | `SELECT entity, "Plan Tier"` | The primary/foreign key column, and every `JOIN … ON` that uses it | | `time` | The workspace's time concept | `WHERE time >= '2025-01-01'` | Naming one table's timestamp column | | `time_from` | Inclusive start of the query's time window. Lifted out of `WHERE` into the plan's typed time range | `WHERE time_from >= '2025-01-01'` | `WHERE created_at >= …` repeated on every joined table | | `time_to` | Inclusive end of the same window | `WHERE time_to <= '2025-06-30'` | `WHERE created_at <= …`, likewise | | `location` | The workspace's location concept | `WHERE location = 'France'` | A join to a geography table plus a filter on it | | `location.` | Filter the location anchor at a named admin level: `postal_code`, `city`, `county`, `region`, `country`, `continent` | `WHERE location.country IN ('US', 'CA')` | Filtering the geo table's country column | | `time()` | Bucket the time anchor | `SELECT time(month)` | `date_trunc('month', t.ts)` | | `time("Concept", )` | Bucket a **named** time concept — required when the workspace has more than one | `time("Vehicle Date", week)` | `date_trunc('week', v.vehicle_date)` | | `location()` | Bucket the location anchor to an admin level | `SELECT location(country)` | Join to a geo table, then `GROUP BY geo.country` | | `location("Concept", )` | Bucket a **named** location concept | `location("Site Country", country)` | The same, against a chosen column | | ` ON "source"` | Pin a concept, anchor, or grain to one source, making it the spine the plan is built around | `entity ON "CRM Accounts"` | Choosing which table drives the `FROM` clause and which are joined onto it | | `FROM source:"name"` | Narrow name resolution to one source | `FROM source:"Billing Export"` | Nothing — it is not a table reference | | `FROM dataset:"name"` | Narrow to one derived dataset | `FROM dataset:"Cleaned Orders"` | Nothing | | `FROM tag:"name"` | Narrow to concepts carrying a tag | `FROM tag:"finance"` | Nothing | | `SHOW CONCEPTS [LIKE 'pattern']` | List concepts, optionally filtered | `SHOW CONCEPTS LIKE '%revenue%'` | Querying `information_schema.columns` | | `SHOW METRICS` | List the workspace's defined KPIs | `SHOW METRICS` | Reading a semantic-layer config by hand | | `SHOW SOURCES` | List sources and datasets, with their identifiers, time columns, and join links | `SHOW SOURCES` | `SHOW TABLES` | | `DESCRIBE "name"` | Detail on one concept, metric, source, or dataset | `DESCRIBE "Churn Rate"` | `DESCRIBE ` | `SELECT`, `WHERE`, `GROUP BY`, `HAVING`, `ORDER BY`, `LIMIT`, `AS`, `AND`/`OR`/`NOT`, `IN`, `BETWEEN`, `IS NULL`, `SUM`/`AVG`/`COUNT`/`MIN`/`MAX` and `COUNT(DISTINCT …)` all behave as you expect. `LIKE`/`ILIKE` behave as standard SQL. *** ## The statement, clause by clause ### Concepts by quoted name ```sql SELECT "Customer ID", "Monthly Charges" WHERE "Contract Type" = 'Month-to-month' ``` One reference reaches every source the concept is mapped into. If two sources both carry "Revenue" as distinct concepts, the reference is ambiguous and the error lists both with the sources they come from — narrow it with a scope or a pin. ### The anchors `entity`, `time`, and `location` bind to the workspace's identifier, time, and location concepts. They are the only unquoted names the language accepts (besides projection aliases). **Standard SQL** — the identifier is a column you name, and every join repeats it: ```sql SELECT c.customer_id, c.contract_type, c.monthly_charges, t.ticket_count FROM customers c JOIN billing b ON b.customer_id = c.customer_id JOIN tickets t ON t.customer_id = c.customer_id WHERE c.contract_type = 'Two year' ``` **Anchor SQL** — the identifier is `entity`, and the joins are implied by using it: ```sql SELECT entity, "Contract Type", "Monthly Charges", "Support Tickets" WHERE "Contract Type" = 'Two year' ``` `GROUP BY entity, time(month)` gives panel shape: one row per entity per period. If the workspace happens to contain a concept literally named `time`, `entity`, or `location`, the query still runs and a warning tells you the rule: unquoted resolves to the anchor, `"time"` quoted binds the concept. When a workspace holds several identifier or time concepts, a bare anchor is ambiguous and the error lists the candidates. Only grains have a named form (`time("Vehicle Date", week)`); there is no `entity("…")` and no named `time_from`/`time_to`. The fix is to abandon the anchor and name the concept directly — `"Contract Number"` instead of `entity`, `WHERE "Origination Date" >= '2020-01-01'` instead of `time_from` — and the error's `suggestedQuery` carries exactly that rewrite. ### Time windows `time_from` and `time_to` are lifted out of the filter list into the plan's typed time range, which the planner then applies to every source it touches, at each source's own grain. **Standard SQL** — the window is repeated per table, and getting it wrong on one silently drops rows: ```sql WHERE b.invoice_date >= DATE '2025-01-01' AND b.invoice_date <= DATE '2025-06-30' AND t.opened_at >= DATE '2025-01-01' AND t.opened_at <= DATE '2025-06-30' ``` **Anchor SQL**: ```sql WHERE time_from >= '2025-01-01' AND time_to <= '2025-06-30' ``` `time_from` accepts `>=` or `=`; `time_to` accepts `<=` or `=`; either end may be omitted for an open range. `time BETWEEN 'a' AND 'b'` is lifted the same way. Any other comparison on `time` — `time >= '2025-01-01'` on its own, for example — is a plain filter on the time concept rather than a plan-wide window. ### Grains A grain buckets an anchor axis. It is the replacement for `date_trunc`, and unlike `date_trunc` it also tells the planner what grain to align other sources to. **Standard SQL** — bucket, and then re-bucket in the join condition so a monthly series meets a daily one: ```sql SELECT date_trunc('month', b.invoice_date) AS month, SUM(b.invoice_total) FROM billing_export b GROUP BY 1 ``` **Anchor SQL**: ```sql SELECT time(month), SUM("Invoice Total") GROUP BY time(month) ORDER BY time(month) ``` Valid time grains: `nanosecond`, `microsecond`, `millisecond`, `second`, `minute`, `hour`, `day`, `week`, `month`, `quarter`, `year`. Asking for a grain finer than the data's native sampling interval fails with `grain_out_of_bounds` and names the finest grain it can serve. Valid location levels, finest to coarsest: `postal_code`, `city`, `county`, `region`, `country`, `continent`. When a workspace has more than one time (or location) concept, the bare form is ambiguous. Name the concept **inside** the reserved function: ```sql SELECT time("Vehicle Date", week), "Depot", AVG("Fuel Burn") GROUP BY time("Vehicle Date", week), "Depot" ORDER BY time("Vehicle Date", week) ``` `"Vehicle Date"(week)` is *not* valid syntax and is rejected outright — a quoted name that collides with a built-in function (`"Month"`, `"Left"`) would otherwise parse into something else entirely. Bare `time(week)` on a multi-time workspace raises `ambiguous_concept`, and the error's `suggestedQuery` rewrites **every** occurrence in the statement into the named form, so it is runnable as-is. `SELECT`, `GROUP BY`, and `ORDER BY` must all use the same form. ### Location Location matching runs against geo-enriched columns, not string equality on whatever the source happened to store. **Standard SQL** — join a lookup, hope the spellings agree: ```sql SELECT g.country, SUM(o.order_value) FROM orders o JOIN geo_lookup g ON g.postcode = o.postcode WHERE g.country IN ('US', 'CA') GROUP BY 1 ``` **Anchor SQL**: ```sql SELECT location(country), SUM("Order Value") WHERE location.country IN ('US', 'CA') GROUP BY location(country) ``` `WHERE location = 'France'` filters at whatever admin level the location concept's own type implies. A single statement may filter at one admin level only. If the location concept holds raw coordinates and geo enrichment has not run for that source, the statement fails with `needs_enrichment` — enrich the source, then retry the statement unchanged. ### Metrics A KPI defined in the workspace is a concept like any other, referenced by name, verbatim. **Standard SQL** — restate the definition and hope it matches whatever the dashboard uses: ```sql SELECT contract_type, COUNT(*) FILTER (WHERE churned_at IS NOT NULL)::float / COUNT(*) AS churn_rate FROM customers GROUP BY 1 ``` **Anchor SQL**: ```sql SELECT "Contract Type", "Churn Rate" GROUP BY "Contract Type" ORDER BY "Churn Rate" DESC ``` Run `SHOW METRICS` before writing any aggregate — it lists each metric's unit, its direction (higher is better, or lower is better), its definition rendered in concept names, and the anchors it can be broken down by. Three rules follow from a metric already being an aggregate: * It cannot be re-aggregated. `SUM("Churn Rate")` is rejected. * It cannot be a `GROUP BY` key (`metric_not_groupable`). * It filters in `HAVING`, not `WHERE`. Writing it in `WHERE` is routed to `HAVING` with a warning where the predicate can be split out; where it cannot, the statement fails with `metric_in_where`. ### Pins ` ON "source name"` fixes which source a concept, anchor, or grain resolves against, and makes it the spine the rest of the plan is built around. ```sql SELECT entity ON "CRM Accounts", "Account Name", SUM("Invoice Total") GROUP BY entity ``` Any quoted concept, reserved word, or grain call can carry one, including inside an aggregate: ```sql SELECT time(day), AVG("Sensor Temperature"), AVG("Ambient Temperature" ON "Weather Feed") GROUP BY time(day) ORDER BY time(day) ``` A pin anywhere in the statement is also a workspace-wide hint: when an unrelated name matches concepts in several sources, the one bound to a pinned source wins. Reach for a pin when the planner picks the wrong spine, or when a `pin_no_edge` / `pin_unknown_source` error tells you to. Every join is **left-outer from the spine**: spine rows without a match survive with nulls, and rows that exist only in a joined source are dropped. That means the spine decides the population — pinning it to a smaller source silently shrinks every total, at a healthy-looking fan-out of 1.0x, because fan-out only measures inflation. When the chosen spine is smaller than a source it joins, the response carries a warning saying which rows are excluded. ### Scope `FROM` exists only to narrow **name resolution**. It never references a table, and most statements need none. ```sql SELECT "Revenue" FROM source:"Billing Export" SELECT "Revenue" FROM dataset:"Cleaned Orders" SELECT "Revenue" FROM tag:"finance" ``` One scope per statement. `FROM orders` — a bare name with no qualifier — fails with `unsupported_syntax`, and the `suggestedQuery` is the same statement with the `FROM` clause dropped. If you meant a scope, write `FROM source:""`: it takes a source's display name, not a dataset id. A well-formed scope that names something unknown does get a respelled suggestion pointing at the nearest source or dataset. Scope and pin do different jobs: scope shrinks the pool of names a reference may bind to; a pin decides which source a *specific* reference resolves against and which source anchors the plan. ### Filters `WHERE` filters rows. `=`, `!=`, `>`, `>=`, `<`, `<=`, `BETWEEN`, `IN`, `LIKE`/`ILIKE`, `IS NULL`, and `AND`/`OR`/`NOT` combinations all work: ```sql SELECT "Order ID", "Order Value" WHERE "Status" != 'cancelled' AND "Order Value" BETWEEN 100 AND 5000 AND "Region" IN ('EMEA', 'APAC') ``` `LIKE` uses standard SQL wildcard semantics — `%` matches any run, `_` one character, the pattern is anchored, and matching is case-sensitive; `ILIKE` is its case-insensitive form. `LIKE 'etail'` carries no wildcard and matches only the exact string; write `LIKE '%etail%'` for a substring. Use `IN (…)` for exact membership. `HAVING` filters aggregates and metrics. A plain column condition in `HAVING` fails with `having_requires_aggregate` and tells you to move it to `WHERE`; the reverse is corrected automatically where it can be. ### Aggregates and expressions `SUM`, `AVG`, `COUNT`, `COUNT(*)`, `COUNT(DISTINCT …)`, `MIN`, `MAX`. Arithmetic between aggregates, `CASE`, `CAST`, and scalar functions are all allowed in a projection: ```sql SELECT "Region", SUM("Revenue") / COUNT("Order ID") AS average_order_value GROUP BY "Region" ORDER BY average_order_value DESC ``` `DISTINCT` is valid only as a **whole** projection — `COUNT(DISTINCT "Customer ID")` is fine, `SUM("Revenue") / COUNT(DISTINCT "Customer ID")` is not, and fails with `unsupported_syntax`. Split it into two projections and divide client-side, or define it once as a metric. `ORDER BY` takes a projection alias, a concept, a grain, or an aggregate the statement already selects. An aggregate that is *not* selected fails with a `suggestedQuery` that adds it to the projection. Ordinals (`ORDER BY 2`) are not names and do not resolve. Output column names are derived when you do not supply one: a plain reference keeps the concept's name, an aggregate becomes `_`, and a grain becomes `__`. Alias explicitly with `AS` whenever the result is consumed programmatically — a derived name is a function of workspace metadata and can move under you. *** ## What is not supported Each of these is refused at compile time with `unsupported_syntax`, never silently reinterpreted. The other two codes mean something narrower: `unknown_scope` is a well-formed scope naming a source the workspace has not got, and `unknown_concept` is a quoted concept that does not exist. | Construct | Why | What to do instead | | ------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------- | | `FROM
` | There is no table namespace to resolve against; a concept's storage is a planning decision | Omit `FROM`. Use `FROM source:/dataset:/tag:` only to disambiguate a name | | `JOIN` | Join paths are derived from the ontology's identifier and time concepts. A hand-written join would be a second, contradictory source of truth | Name the concepts. If the spine is wrong, pin it with `ON "source"` | | Subqueries and CTEs | A statement compiles to a single planned pipeline, not a nestable relational algebra | Run two statements, or materialise the inner result as a dataset and query the concepts it produces | | `UNION` / `EXCEPT` / `INTERSECT` | Set operations need two independent relations, which the single-pipeline model has no place for | Build a dataset that stacks the sources, then query it | | `SELECT *` | The projection is what tells the planner which sources must join and which columns to read; a star has no answer | Name the concepts, or `SHOW CONCEPTS` first | | `SELECT DISTINCT` | There is no row-level dedup step in the compiled pipeline | `GROUP BY` the concepts you want distinct, or `COUNT(DISTINCT …)` | | `DISTINCT` nested in an expression | The engine can express one distinct projection, not a distinct sub-term of a larger one | Project `COUNT(DISTINCT …)` on its own and combine the columns downstream | | Window functions (`OVER (…)`) | A window sits outside the expression set the projection compiler accepts | Aggregate at the grain you need and window client-side, or build a dataset | | `OFFSET` | Paging is key-based, not offset-based | Page with `startKey` / `nextStartKey` | | Multiple statements in one request | One statement, one plan, one result union | Send them separately | | `GROUP BY 1` / `ORDER BY 2` | Ordinals index output positions; the resolver binds by name | Repeat the projection, or order by its alias | | Table qualifiers (`o."Revenue"`) | A qualifier names a table. Parsed, it becomes a concept named `o.Revenue`, which does not exist | Use the bare concept name | | `INSERT` / `UPDATE` / `DELETE` / DDL | The query surface is read-only | Use the REST datasets and ontology endpoints | *** ## Metadata commands Discovery happens in-dialect, and the names these return are the exact tokens you quote in the next statement — there is no id round-trip. | Command | Returns | Columns | | -------------------------------- | ------------------------------------------------- | ---------------------------------------------------------------------------- | | `SHOW CONCEPTS` | Every concept in the workspace | `name`, `kind`, `classification`, `type`, `unit`, `sources`, `grain`, `tags` | | `SHOW CONCEPTS LIKE '%revenue%'` | Concepts matching a pattern | as above | | `SHOW METRICS` | Defined KPIs, referencable by name | `name`, `unit`, `direction`, `definition`, `anchors` | | `SHOW SOURCES` | Sources and derived datasets in scope | `name`, `kind`, `rows`, `columns`, `identifiers`, `time`, `joins` | | `DESCRIBE "Monthly Revenue"` | One concept, metric, source, or dataset in detail | `property`, `value` | `LIKE` uses SQL wildcard semantics (`%` any run, `_` one character), matches case-insensitively, and matches the **whole** name — `LIKE 'revenue'` does not match "Monthly Revenue", `LIKE '%revenue%'` does. `classification` on a concept row is what makes the anchors work: `identifier` concepts answer to `entity`, `time` to `time`/`time(…)`, `location` to `location`/`location(…)`. The `joins` column on `SHOW SOURCES` is the shortest way to see which sources actually connect before writing a cross-source statement. `DESCRIBE` requires a quoted name; a bare `DESCRIBE Revenue` fails with a `parse_error` carrying the quoted form as its suggestion. Metadata commands come back as a `metadata` response: tabular rows, no plan. *** ## The REST endpoint `POST /api/v1/workspaces/{wsId}/ontology/query` — requires the **`ontology:read`** scope. Request body: | Field | Type | Description | | ---------------- | -------------------------------- | ------------------------------------------------------------------------------------------------------ | | `anchorSql` | string, required | The statement — a single SELECT, or a SHOW/DESCRIBE command | | `limit` | integer, optional | Rows per page, 1–10 000 (default 1000) | | `startKey` | integer, optional | Resume paging from a previous response's `nextStartKey` | | `validateOnly` | boolean, optional | Compile and plan without executing; returns the `validated` arm | | `projectionMode` | `related` or `minimal`, optional | `related` (default) adds ontology-linked context; `minimal` returns only selected concepts and anchors | There is no transport-level total row cap. `limit` controls one response page; continue with `nextStartKey` until it is absent. Keep `anchorSql` and `projectionMode` unchanged across pages. An SQL `LIMIT` is different: it caps the statement's total result and therefore the final page. ```bash curl -X POST "https:///api/v1/workspaces/ws_123/ontology/query" \ -H "Authorization: Bearer pk_your_key" \ -H "Content-Type: application/json" \ -d '{ "anchorSql": "SELECT time(month) AS month, SUM(\"Revenue\") AS revenue GROUP BY time(month) ORDER BY time(month)", "limit": 500 }' ``` The endpoint **always responds 200** for a statement that reached the engine; the body is a union under `data`, discriminated by `ok` and then by `kind`: ```json { "data": { "ok": true, "kind": "rows", "sql": "SELECT time(month) AS month, SUM(\"Revenue\") AS revenue ...", "columns": ["month", "revenue"], "rows": [{"month": "2025-01-01", "revenue": 182000.5}], "rowCount": 500, "totalRowCount": 1832, "truncated": false, "nextStartKey": 500, "schemaEntries": [], "units": {"revenue": "usd"}, "plan": {"chips": [], "joinPlan": [], "strategy": "single"}, "warnings": [], "dataViewId": null } } ``` | `kind` | Meaning | | ----------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `rows` | A materialised result: `rows`, `columns`, `schemaEntries`, per-column `units`, the compiled `plan`, `warnings`, and `nextStartKey` when there is another page | | `metadata` | SHOW/DESCRIBE output: `command`, `columns`, `rows` | | `validated` | A `validateOnly` pass: `sql`, `plan`, and `warnings`, without execution | Results are not persisted — `dataViewId` is null on this route. To keep one, create a dataset via `POST /api/v1/workspaces/{wsId}/datasets`. ### Reading the plan `plan` is how the statement explains itself, and it is what `validateOnly` exists to give you cheaply: * `chips` — an ordered list of `{kind, label, detail}`, where `kind` is one of `scope`, `spine`, `join`, `filter`, `grain`, `aggregate`, `metric`, `projection`, `warning`. Every filter appears, including the time window (`filter` / `time window` with the resolved bounds), so the plan states not just how the sources were joined but what cut the rows. This is the human-readable summary of what the planner decided. * `joinPlan` — one `{sourceDatasetId, targetDatasetId, conceptName, strategy, fanout}` per join actually performed. `strategy` is `identifier`, `time_exact`, `time_granularity`, `time_asof`, `geo_exact`, `geo_rollup`, `geo_near`, or `concat`; geography steps carry an extra `level` field naming the admin level they matched on; `fanout` is the row multiplication that join caused, and is the first thing to look at when a `SUM` comes back larger than expected. * `strategy` — the shape of the whole plan: `single` (one source, no join), `star`, `identifier_chain`, `ts_chain`, `geo_chain` (sources joined through geography), or `concat`. Dry-run anything expensive before executing it: ```bash curl -X POST "https:///api/v1/workspaces/ws_123/ontology/query" \ -H "Authorization: Bearer pk_your_key" \ -H "Content-Type: application/json" \ -d '{"anchorSql": "SELECT entity, time(day), SUM(\"Events\") GROUP BY entity, time(day)", "validateOnly": true}' ``` ### Paging A `rows` response with more data carries `nextStartKey`. Pass it back as `startKey` with the **same statement** to fetch the next page; the last page has no `nextStartKey`. There is no `OFFSET`. *** ## The error model A statement the engine refuses is still a 200 — the union member with `ok: false`: ```json { "data": { "ok": false, "error": { "code": "unknown_concept", "message": "Unknown concept \"Monthly Chargse\".", "span": [7, 24], "candidates": [ {"kind": "concept", "id": "cn_9f2", "name": "Monthly Charges", "score": 0.94} ], "suggestedQuery": null, "perSource": [] } } } ``` Every error is structured for a one-round repair. It carries either `candidates` (ranked near-misses — what you probably meant, each tagged `concept`, `metric`, `derived`, `source`, `dataset`, `tag`, or `grain`) or a `suggestedQuery` (the corrected statement, ready to run), plus a `span` — half-open character offsets `[start, end)` into your **original** statement text, so an editor can highlight the offending fragment or splice a fix in place. `perSource` explains a failure dataset by dataset: `no_join_path` and `pin_no_edge` say why each side could not be joined, and `needs_enrichment` / `enrichment_running` say which datasets lack a geo sidecar. | Code | Meaning | Fix | | --------------------------- | --------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | | `parse_error` | The statement did not parse | Follow the message; `span` points at the fragment | | `unsupported_syntax` | Valid SQL, but outside the dialect (JOIN, subquery, UNION, window, `SELECT *`, …) | See [What is not supported](#what-is-not-supported) | | `unknown_concept` | No concept, metric, source, or dataset by that name — or an unquoted word that is neither reserved nor an alias | Take the top candidate; check the name is double-quoted | | `ambiguous_concept` | The name, or a bare `time(…)`/`location(…)`, matches more than one concept | Run the `suggestedQuery`; or scope with `FROM source:"…"`, or pin with `ON "…"` | | `unknown_scope` | `FROM` had no qualifier, or names nothing in the workspace | Use `source:`/`dataset:`/`tag:`; `SHOW SOURCES` to check the name | | `reserved_word_misuse` | `entity`/`time`/`location` used where it cannot go, or a grain named a concept of the wrong classification | Follow the message; classify the concept, or name a valid one | | `metric_in_where` | A metric sits inside a compound `WHERE` predicate that cannot be split | Restate it as its own `HAVING` condition | | `having_requires_aggregate` | A plain column condition in `HAVING` | Move it to `WHERE` | | `grain_out_of_bounds` | The data cannot support a grain that fine | Use the suggested coarser grain | | `metric_not_groupable` | A metric used as a `GROUP BY` key | Group by a concept or grain instead | | `no_join_path` | No join path links the referenced sources — no shared identifier, and not every source has a time column | `perSource` explains each side; the sources may simply not connect | | `pin_no_edge` | The pinned source has no join edge into the rest of the plan, or the concept is not bound to it | Pin a source the concept is actually bound to | | `pin_unknown_source` | `ON "…"` names no source in scope | Fix the name; `SHOW SOURCES` lists them | | `empty_projection` | Nothing to select | Add at least one projection | | `needs_enrichment` | A location concept holds raw coordinates with no geo sidecar | Run location enrichment on that source, then retry unchanged | | `enrichment_running` | Geo enrichment for that location concept is already running; `enrichmentProgress` carries its live status | Retry the statement unchanged once enrichment lands | | `execution_error` | Compiled fine, failed at runtime | Follow the message | | `translation_failed` | Only from the natural-language endpoint: the question could not be turned into a statement | Retry, or write the Anchor SQL yourself | The intended loop: apply the fix and retry **once**. If that retry fails, use `SHOW CONCEPTS`, `SHOW SOURCES`, or `DESCRIBE` and copy an exact returned token rather than guessing another name. Re-sending the same statement against the same error is never productive. Treat the code list as open — handle unknown codes by surfacing `message` rather than failing. *** ## Worked examples **Entity 360** — everything about the entities matching a filter, with the planner joining whatever sources hold those concepts: ```sql SELECT entity, "Contract Type", "Monthly Charges", "Support Tickets" WHERE "Contract Type" = 'Two year' ``` **Time-aligned two-source compare** — two series from two sources at different native grains, aligned to daily; no join written: ```sql SELECT time(day), AVG("Sensor Temperature"), AVG("Ambient Temperature" ON "Weather Feed") GROUP BY time(day) ORDER BY time(day) ``` **Location and time rollup:** ```sql SELECT location(country), time(month), SUM("Order Value") GROUP BY location(country), time(month) ORDER BY time(month) ``` **KPI by segment** — the workspace's own definition, by name: ```sql SELECT "Contract Type", "Churn Rate" GROUP BY "Contract Type" ORDER BY "Churn Rate" DESC ``` **Filtered aggregate with HAVING and a stable sort key:** ```sql SELECT "Product Category", SUM("Revenue") AS revenue, COUNT(DISTINCT "Customer ID") WHERE time_from >= '2025-01-01' GROUP BY "Product Category" HAVING SUM("Revenue") > 100000 ORDER BY revenue DESC LIMIT 20 ``` **Named grain on a multi-date workspace:** ```sql SELECT time("Vehicle Date", week), "Depot", AVG("Fuel Burn") GROUP BY time("Vehicle Date", week), "Depot" ORDER BY time("Vehicle Date", week) ``` **Pinned spine** — force the account list to drive the plan rather than the billing export: ```sql SELECT "Account Name" ON "CRM Accounts", SUM("Invoice Total") GROUP BY "Account Name" ORDER BY SUM("Invoice Total") DESC LIMIT 25 ``` **Panel shape** — one row per entity per period, the input a temporal twin expects: ```sql SELECT entity, time(month), SUM("Invoice Total"), AVG("Support Tickets"), "Plan Tier" GROUP BY entity, time(month), "Plan Tier" ORDER BY time(month) ``` *** ## Next steps * [Ontology Queries from Python](/api-and-integrations/sdk-getting-started/sdk-ontology-queries.md) * [What You Can Do over MCP](/api-and-integrations/api-access/mcp-integration/mcp-capabilities.md) * [Build Ontology](/user-guide/ontology-concepts.md) * [REST API Reference](/api-and-integrations/rest-api-reference.md)