TS Object Model Agentql Query

ThoughtSpot: Query a Model with AgentQL

AgentQL was previously called SpotQL. The query language and the underlying API are unchanged; only the name is new. The CLI command is now ts agentql (the old ts spotql still works as a deprecated hidden alias). The server contract keeps the spotql spelling — the callosum /data/spotql/* endpoints and the spotql_query request field are stable identifiers, not the product name.

Turn a question about a ThoughtSpot Model into an answer. You (the agent) write an AgentQL statement grounded in the rules in references/, then run it through two ts commands: generate-sql (ThoughtSpot compiles it to warehouse SQL and validates it) and fetch-data (ThoughtSpot executes it and returns rows). You never compile or translate AgentQL yourself — ThoughtSpot does the AgentQL→SQL compilation, deterministically, so the warehouse SQL and the results are exactly what the platform would run. The result is a review the user can inspect at the level they care about — the data table, the AgentQL, the warehouse SQL, or the raw JSON.

This skill is the primitive. It is the foundation other things build on: an onboarding tutorial, a drop-in for your own agent, or the per-question engine of an accuracy / regression / feature test suite. See references/use-cases.md for those compositions — they are uses of this skill, not built into it.

What this skill does

If asked "what can you do?", this is the answer. Given a question about a ThoughtSpot Model I can:

• Answer it — write the AgentQL and hand it to ThoughtSpot, which compiles it to the warehouse SQL and executes it; I return the rows as a table.
• Show the work — the AgentQL, the generated warehouse SQL, the raw JSON, or just the data table, at whatever depth you want.
• Help you integrate — hand you ready-to-paste API request bodies and point you at references/integration.md (auth, endpoints, response parsing) to call AgentQL from your own product or agent.
• Explain the rules — what AgentQL can and can't express: aggregation (SUM vs AGG), the date/time UDFs, query patterns (top-N, year-over-year, semi-additive measures), and the known limitations.
• Explain why AgentQL exists — the architecture and its trust/correctness guarantees versus raw warehouse SQL (the LLM's SQL is never executed; RLS/CLS, Model joins/filters, governed metrics, custom calendars and multi-fact trap resolution are all applied deterministically). See references/architecture.md.

The one requirement is below: the Model must be backed by an external cloud data warehouse.

AgentQL requires an external cloud data warehouse. The AgentQL endpoints only work on Models backed by Snowflake / Databricks / BigQuery / etc. A Model over Falcon, imported data, or system data (DEFAULT datasource) is rejected with "This API only supports external cloud data warehouses". If you hit that, the Model isn't queryable via AgentQL — say so plainly.

References

Prerequisites

• A ThoughtSpot profile — run /ts-profile-thoughtspot if none exists.
• The ts CLI (pip install -e tools/ts-cli), version 0.31.0+ (provides ts agentql, including ts agentql classify-columns for Step 2).
• The target Model is backed by an external cloud data warehouse (see the note above).

All ThoughtSpot calls go through the ts CLI, which handles auth, token caching, and the keychain — never construct API requests directly.

Flow

Pick the depth from how the user framed the request:

• Business question ("what were sales by region last quarter?") → run the flow, then show the answer table. Keep AgentQL and warehouse SQL out of the way unless asked.
• Developer / learning ("show me the AgentQL", "what SQL does this generate?", "I'm integrating this") → show every artifact: the AgentQL you wrote, the warehouse SQL, the rows, and the raw JSON the commands emit.

Step 1 — Pick the profile and the Model

If multiple profiles exist in ~/.claude/thoughtspot-profiles.json, ask which to use, then confirm it authenticates:

ts auth whoami --profile "{profile}"

Always ask the user which Model to query. Accept any of these — you do not need a name to search for if you already have an identifier:

• A GUID — use it directly as {model_guid}.

• A ThoughtSpot URL — extract the GUID from the path. Users often have the Model open in a browser, e.g. …/#/data/tables/4da3a07f-… or …/#/data/embrace/4da3a07f-… — the GUID is the path segment after tables/ / embrace/.

• A name, or nothing — fall back to search and let the user pick:

  ts metadata search --subtype WORKSHEET --name "%{search}%" --profile "{profile}"
  

  Present matches with name + GUID + owner + modified date so the user can disambiguate.

Models are LOGICAL_TABLE with header type: WORKSHEET. Whichever path you took, confirm the resolved Model display name back to the user — you need it verbatim for the FROM clause in Step 3.

Step 2 — Learn the schema

Export the Model's TML to see its columns:

ts tml export {model_guid} --profile "{profile}"

The TML body is in the edoc field. It is a structured document — JSON or YAML depending on build (yaml.safe_load parses both; ts tml export … --parse returns it already parsed). For a Model it is rooted at model: with these parts:

• model.columns[] — each entry's name is the exact identifier you must use in AgentQL (case-sensitive). The column kind is at properties.column_type (ATTRIBUTE or MEASURE) — note it is nested under properties, not a direct child of the column.
• model.formulas[] — formula definitions. A formula column references one by carrying a formula_id that matches a formulas[].id; that formula's expr is where the aggregation logic lives.

Classify every column — this drives the SUM-vs-AGG decision in Step 3. Don't eyeball the TML for this: run

ts agentql classify-columns --model {model_guid} --profile "{profile}"

This calls the same aggregate-function detector ts-object-answer-promote uses (BL-087 — one canonical keyword list, not two drifted copies), applied to every model.columns[] entry. It returns a JSON array of {name, column_type, kind, needs_agg, aggregation, wrapper}. The wrapper field is the directly-actionable output — the AgentQL function to wrap a reference to that column in (or None for attributes):

Semi-additive is the one inversion of the aggregate-formula rule. A measure whose outermost op is last_value/first_value cannot use AGG(...) — the AgentQL→SQL serializer can't emit its query_groups() and fails with NON_CONVERTIBLE_FUNCTION ("Non standard sql function QueryGroups"). Wrap it in SUM(...) instead: that forces a per-group materialisation that resolves query_groups() and passes the already-collapsed snapshot value through unchanged (verified at grand-total, grouped, and time-series grain). The trigger is the outermost op only — sum(last_value(...)) is a normal aggregate_measure (use AGG; an extra SUM double-aggregates → NESTED_AGGREGATE).

Match each column you plan to use in Step 3 against its wrapper/kind in this output — do not re-derive the classification by reading the TML expr yourself. See agentql-rules.md § Aggregation for the full rule and the "compile-it-to-check" probe if a column is still ambiguous after classification. If TML export is FORBIDDEN, you lack access to that Model — pick another or ask the user.

Step 3 — Write the AgentQL

Read references/agentql-rules.md first. Then write one AgentQL statement for the question. The essentials (full list in the rules file):

• FROM "Model Display Name" AS "t1" — the one Model only, always aliased.
• Every column reference alias-prefixed and double-quoted: "t1"."Product Category".
• Raw measures get a real aggregate (SUM is the default): SUM("t1"."Amount"). Aggregate-formula columns (formula already contains sum/count/group_aggregate/ cumulative_*/…) get AGG("t1"."# Employees") — never SUM (that errors NESTED_AGGREGATE_NOT_SUPPORTED). Semi-additive columns (semiadditive_measure — outermost op last_value/first_value) are the exception: wrap in SUM(...), not AGG (which errors NON_CONVERTIBLE_FUNCTION). Just follow each column's wrapper from Step 2. Attributes go in GROUP BY. See agentql-rules.md. Alias only computed/aggregate expressions, in Title Case. Never alias a plain model column.
• Never SELECT *, COUNT(*), or subqueries (IN (SELECT …) compiles but fails at execution — use the semi-join rewrite in patterns.md). Set operations and aggregate×literal arithmetic work on current builds — see the rules and limitations.md for the caveats.
• Dates: use the AgentQL UDFs (YEAR_NUMBER, DIFF_MONTH, START_OF_CURRENT_MONTH(), …), never DATE_TRUNC/NOW()/CURRENT_DATE.

Step 4 — Validate and get the warehouse SQL

ts agentql generate-sql '{spotql}' --model {model_guid} --profile "{profile}"

Returns JSON {status, executable_sql, errors}. If status is SUCCESS, executable_sql is the warehouse SQL ThoughtSpot compiled — this is the "database SQL". If status is anything else, read errors[] (e.g. COLUMN_NOT_FOUND, QUERY_GEN_ERROR), fix the AgentQL against the rules, and retry. Do not execute a statement that failed validation.

Step 5 — Execute

ts agentql fetch-data '{spotql}' --model {model_guid} --profile "{profile}"

Returns JSON {status, columns, rows, errors}. columns are {index, type} — AgentQL returns per-query column GUIDs, not names, so columns are identified by SELECT ordinal. You wrote the SELECT, so you know what each ordinal means: label them from your own column list when you present results.

Step 6 — Review

Present the result at the depth from the top of this section:

• Answer (always): render rows as a table, with headers from your SELECT list (not col0/col1). This rendered table is your presentation of the JSON — the commands emit JSON; you make it readable.

• 🧠 Generated AgentQL (developer): the statement you wrote.

• 🗄️ Warehouse SQL (developer): executable_sql from Step 4.

• 📋 Request bodies (developer): the ready-to-paste API bodies for the session's query, pre-filled with the AgentQL you wrote and the Model GUID — so the user can run it from the REST playground or their own code. (See references/integration.md for auth and response parsing.)

  POST /callosum/v1/v2/data/spotql/generate-sql
  { "spotql_query": "<the AgentQL from Step 3>", "model_identifier": "<model_guid>" }
  
  POST /callosum/v1/v2/data/spotql/fetch-data
  { "spotql_query": "<the AgentQL from Step 3>", "model_identifier": "<model_guid>" }
  

• ❌ Errors: if any status was not SUCCESS, show the code + message and what you changed (or why it can't be answered).

If the user wants the machine-readable form (for piping or their own code), give them the raw JSON from Steps 4–5 — that is the building-block interface.

Building on this skill

The six common uses — interactive tutorial, agent building-block, accuracy benchmarking, functional regression testing, new-feature testing, and known-limitation retesting — are all compositions over Steps 3–5 (write AgentQL → run → compare). They are documented in references/use-cases.md, not implemented here: this skill stays the single-question primitive so consumers can build suites on top of its JSON output without re-deriving the query mechanics.

Changelog