SQL queries

POST /v1/query runs one read-only ClickHouse SELECT or WITH query over your organization's Captures, Items, and connected Explore model. Explore AI in the dashboard and the MCP run_query tool use the same execution. It is not a database connection: every query runs through the API, scoped to the organization that owns the API key.

Queries are available on every plan and consume no credits.

Read the model first

GET /v1/query/model describes the tables a query can read: columns with their types and meanings, grains, join relationships, canonical definitions such as current Offers and active Listings, and the rules a correct query follows. Pass entities to describe only the tables you need; omit it for all of them.

curl -s "https://api.extralt.com/v1/query/model?entities=stores,current_offers" \
  -H "Authorization: Bearer $EXTRALT_API_KEY" | jq '.entities[].table'

Most tables keep several versions of a row until ClickHouse merges them. Read them with FINAL after the table or its alias, as the model's rules say: FROM extralt.listings AS l FINAL. extralt.current_offers already resolves current versions, so read it without FINAL.

The model describes the tables as they are today. They change as the product model evolves, so read the model again when a query stops matching it.

Run a query

By default the response is JSON with every row the query selects. Bound the rows with a LIMIT clause. A JSON response holds at most 8 MB: when the selected rows don't fit, it returns the first rows that do and sets truncated to true.

curl -s -X POST "https://api.extralt.com/v1/query" \
  -H "Authorization: Bearer $EXTRALT_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT country, currency, count() AS offers, avg(price) AS average_price FROM extralt.current_offers WHERE availability = '\''available'\'' AND condition = '\''new'\'' GROUP BY country, currency ORDER BY offers DESC"
  }' | jq

Rows are JSON objects in the order of the SELECT. 64-bit integers, decimals, and non-finite floats are strings, so prices and IDs keep their exact values. The Python examples use HEADERS from Making requests.

Load results into a notebook

Set format to parquet or jsonl to stream every row the query selects as a file. Parquet keeps column types, so it loads directly into pandas, Polars, or DuckDB. In Hex or Jupyter, run it from a Python cell.

import io

import pandas as pd
import requests

response = requests.post(
    "https://api.extralt.com/v1/query",
    headers=HEADERS,
    json={
        "sql": """
            SELECT s.host, o.country, o.currency, o.price, o.availability, o.observed_at
            FROM extralt.current_offers AS o
            JOIN extralt.stores AS s FINAL ON s.org_id = o.org_id AND s.id = o.store_id
        """,
        "format": "parquet",
    },
)
response.raise_for_status()
offers = pd.read_parquet(io.BytesIO(response.content))

Limits and errors

• A query is one statement of at most 20,000 bytes. The API rejects the word SETTINGS anywhere in it, including aliases and string literals, because the API owns query settings.
• Each query runs under a read-only ClickHouse role with at most 30 seconds of execution, plus memory and read limits. A query that exceeds them returns 422 scope_too_large: filter or aggregate earlier, or select fewer columns.
• A parquet or jsonl download must finish within 35 seconds of the request. A download still running then ends with an error, so a slow connection can fail a large file.
• A JSON response holds at most 8 MB and reports anything left out with truncated; a single row larger than that returns 422 scope_too_large. Use parquet or jsonl for larger results: they have no size limit.
• When ClickHouse is handling too many queries at once, the API returns 503 upstream_unavailable. Retry the same request shortly.
• Syntax errors, unknown columns, and tables outside the model return 400 invalid_request with ClickHouse's message.
• Queries count toward your organization's rate limit.

When an Analysis fits

Price position, Price movements, Availability changes, and Assortment overlap are packaged Analyses with fixed eligibility rules, denominators, and coverage reporting. Use them when they answer the question, and SQL for everything else.

Agents connected through MCP use the same execution through get_query_model and run_query. run_query returns as many of the selected rows as fit in one MCP tool result and sets truncated when rows were left out. The result is at most 128 KB and carries the rows twice, as data and as text, so about 60 KB of rows fit.