Understanding SpotQL
What is SpotQL?
SpotQL is ThoughtSpot’s purpose-built semantic query language for interacting with your data models. It looks and reads like SQL — familiar enough for analysts and developers — but it fundamentally behaves differently from standard SQL.
SpotQL expresses intent. It never executes directly against your database.
When you or an AI agent (like Spotter) writes a SpotQL query, it is submitted to the ThoughtSpot semantic layer, which then translates it into the appropriate, governed database SQL. The result is an answer that is accurate, consistent, and trustworthy — every single time.
Why was SpotQL built?
Traditional natural language to SQL (NL-to-SQL) approaches have a critical weakness: the large language model (LLM) directly generates the database SQL that gets executed. This means:
-
The LLM decides which joins to use — and may choose incorrectly.
-
Row-level security and other access controls may be bypassed or ignored.
-
The output varies between runs; the same question may yield a different answer tomorrow.
-
Custom business logic (e.g., semi-additive metrics, custom calendars, level-of-detail calculations) may be misunderstood or missed entirely.
SpotQL is introduced to solve this problem. By inserting the ThoughtSpot semantic layer between the AI agent and the database, the governed, deterministic SQL is always what runs — not what an LLM guessed.
How SpotQL works
SpotQL works through a two-step API process.
Step 1 — Generate SQL API
You submit a SpotQL query along with a model identifier (GUID). ThoughtSpot translates the SpotQL into optimised, governed database SQL and returns it. At this stage you can inspect what SQL would run before any data is fetched.
Step 2 — FetchData API
You submit the same SpotQL query and model identifier again. ThoughtSpot executes the governed SQL against your database and returns the actual data results.
Both APIs accept:
-
The SpotQL query — written in SQL-like syntax against business-friendly column names
-
A Model GUID — the unique identifier of the ThoughtSpot data model you want to query
Behind the scenes, ThoughtSpot handles everything else: physical column name mapping, join resolution, RLS enforcement, and complex metric calculations.
The role of the semantic layer
The semantic layer is what makes SpotQL safe and consistent. When your SpotQL query is processed, ThoughtSpot’s engine — the same engine that powers Liveboards and Search — enforces all the rules defined in your data model.
| What you write in SpotQL | What ThoughtSpot enforces |
|---|---|
Business column names (e.g., |
Mapped to physical database column names |
No join conditions needed |
Join paths defined in the model are applied automatically |
No RLS filters needed |
Row-level security rules are applied automatically |
Relative dates (e.g., |
Custom calendars and date logic are respected |
Metric names (e.g., |
Semi-additive, LOD, and ratio definitions are applied correctly |
Multi-table questions |
Chasm traps and fan traps are resolved correctly |
This means a business analyst can write simple, readable SpotQL queries — and trust that the database query will be complex, correct, and secure.
SpotQL vs. natural language to SQL — what’s the difference?
This is the most important distinction to understand.
Natural language to SQL (NL-to-SQL)
User question → LLM generates database SQL → SQL executes directly
The LLM’s output is the query. There is no guarantee of:
-
Correct join logic
-
Row-level security being applied
-
Consistent, deterministic results
SpotQL
User question → LLM generates SpotQL → ThoughtSpot translates to governed SQL → Governed SQL executes
The LLM only generates the intent (SpotQL). ThoughtSpot’s governed engine produces the actual database query. This ensures:
Joins are always correct (defined in the model)
Row-level security is always applied (cannot be bypassed)
Metrics are always calculated the right way
Results are deterministic and traceable
Hallucinated or ungoverned SQL never touches your database
Handling complex and advanced queries
SpotQL is not limited to simple queries. It supports sophisticated multi-step analytical patterns.
Common table expressions (CTEs)
For complex questions — such as "What are the top 10 products sold in the last 3 years, and what were their sales last month vs. two months ago?" — SpotQL can generate multi-CTE queries that chain sub-results together cleanly.
Advanced data modelling support
SpotQL supports the same advanced modelling patterns as ThoughtSpot’s token-based answers:
-
Semi-additive metrics (e.g., Inventory Balance using a Last Value function)
-
Level-of-Detail (LOD) calculations (e.g., Category Quantity)
-
Ratio metrics
-
Chasm trap and fan trap resolution for multi-fact models
-
Custom calendars for non-standard fiscal periods
Despite this power, the SpotQL you write remains simple and readable — the complexity lives in the governed translation layer, not in your query.
Who uses SpotQL and how?
SpotQL is used in three primary ways.
1. Spotter (ThoughtSpot’s AI agent)
Spotter uses SpotQL internally every time it answers a question. When you ask Spotter a question in ThoughtSpot Cloud, it generates SpotQL behind the scenes and submits it through the governed APIs. You can see this in the Show Work view on any Spotter answer — the SpotQL input and output are both visible.
2. External AI agents and applications
Developers can call the SpotQL APIs directly from any application or AI tool (e.g., Claude, custom chatbots, data pipelines). A skills-based integration approach allows tools like Claude to interact with the APIs without writing code directly — by connecting a profile and specifying a model GUID, they can query any ThoughtSpot model.
3. Administrators and analysts
Administrators can inspect the SpotQL generated for any query to understand exactly what is being asked, verify that the semantic rules are working correctly, and troubleshoot data discrepancies. The ability to see both the SpotQL and the resulting database SQL provides full traceability.
Getting started with SpotQL APIs
To use the SpotQL APIs, you will need:
-
A ThoughtSpot Cloud instance with the SpotQL feature enabled
-
An authorisation token — a bearer token for your ThoughtSpot session (used in the API request header)
-
A Model GUID — the unique identifier of the ThoughtSpot data model (worksheet or table) you want to query
API endpoints
| API | Purpose |
|---|---|
|
Submit SpotQL → receive the governed database SQL that would run |
|
Submit SpotQL → receive the actual data results |
Example workflow
POST /api/v1/agentql/generate-sql
Headers: Authorization: Bearer <token>
Body:
{
"agentql": "SELECT [Product Category], SUM([Total Sales])",
"model_id": "<model-guid>"
}
Response:
{
"sql": "SELECT p.category_name, SUM(f.sales_amount)
FROM fact_sales f
JOIN dim_product p ON f.product_id = p.product_id
WHERE <RLS filter>
GROUP BY 1"
}Notice that in the SpotQL, business-friendly names are used with no joins specified. The response contains fully formed, governed database SQL with correct physical column names, join logic, and security filters applied.
Administrator notes
Row-level security (RLS)
RLS rules defined on your ThoughtSpot model are automatically applied to every SpotQL query. There is no way for an API caller to bypass these rules through SpotQL — security is enforced at the semantic layer, not at the caller’s discretion.
Model governance
The quality of SpotQL answers depends on the quality of your ThoughtSpot data model. Well-defined join paths, clear metric definitions, and properly configured custom calendars will all be reflected in the governed SQL output.
Frequently asked questions
Is it the same as NL-to-SQL?
No — the LLM never generates raw database SQL; ThoughtSpot’s engine does
Do I need to know SQL to use SpotQL?
No. If you are using SpotQL through Spotter or an AI agent, the SpotQL query is generated automatically for you. If you are a developer integrating the APIs, a basic understanding of SQL structure is helpful, but you do not need to know the physical database schema — only the business field names from the semantic model.
Does SpotQL replace ThoughtSpot Search?
No. SpotQL is a complementary capability, designed for AI-agent-driven and programmatic use cases. ThoughtSpot’s existing token-based search remains available and is powered by the same underlying semantic engine.
Can SpotQL access data I’m not authorized to see?
No. Because SpotQL is always resolved through ThoughtSpot’s governed engine, your row-level security rules are always enforced. An AI agent using SpotQL on your behalf can only access the data you are authorized to see.
Which advanced modeling features does SpotQL support?
SpotQL supports the full range of ThoughtSpot’s advanced data modeling capabilities, including semi-additive metrics, Levels of Detail (LODs), ratios, custom fiscal calendars, chasm trap resolution, and fan trap handling.
How does SpotQL handle complex, multi-step questions?
SpotQL supports Common Table Expressions (CTEs), which allow complex multi-step analytical questions to be expressed cleanly. For example, identifying a top-N set of entities in one step and then performing a time-comparison analysis on those entities in a second step — all within a single, readable SpotQL query.
Is it production-ready?
Yes, and actively expanding with new features
SpotQL represents a meaningful architectural advance: it lets AI agents and applications ask data questions in a readable, SQL-like language, while guaranteeing that the answer comes from a governed, secure, and deterministic execution path.