name: hotdata-analytics description: Use this skill when the user wants OLAP-style SQL analytics in Hotdata — aggregations, GROUP BY, JOINs, reporting, exploratory queries, query run history, stored results, or materialized follow-up tables (Chain into instant databases). Activate for "analyze", "aggregate", "rollup", "pivot", "report", "metrics", "GROUP BY", "query history", "past queries", "query runs", "stored results", "materialize", "chain", "intermediate table", or sorted indexes for filters/range scans. Do not load for BM25/vector search or geospatial SQL — use hotdata-search or hotdata-geospatial. Requires the core hotdata skill for tables and auth. version: 0.44.1

Hotdata Analytics Skill

OLAP-style analytics in Hotdata: PostgreSQL-dialect SQL, query execution, run history, stored results, Chain materializations, and sorted indexes for filters and joins.

Prerequisites: Authenticate, workspace, and catalog discovery via the `hotdata` skill (ingest sources/ingest, databases tables, databases).

Related sub-skills (bundled alongside this one — Read on demand): `hotdata-search` (`../search/SKILL.md` — BM25, vector, retrieval indexes), `hotdata-geospatial` (`../geospatial/SKILL.md` — spatial SQL).

Execute SQL

hotdata query "<sql>" [--workspace-id <workspace_id>] [--database <database>] [--dialect hotsql|duckdb|postgres|snowflake] [--output table|json|csv]
hotdata query status <query_run_id>

• PostgreSQL dialect. Quote mixed-case identifiers: "CustomerName".
• `--dialect` (default hotsql): write SQL in duckdb/postgres/snowflake and the server transpiles it to HotSQL before running (e.g. Snowflake IFF(...), DuckDB len(...)). Writes follow the same rules as hotsql.
• Writes: INSERT (append only), CREATE TABLE, CREATE TABLE AS SELECT, and DROP TABLE run through query against the current database's own tables and return one row, rows_affected; UPDATE/DELETE are not supported in SQL yet (use keyed hotdata databases load --mode update|delete|upsert). Rules and refusals in the `hotdata` skill → Execute SQL Query.
• Use `hotdata databases tables list` for schema discovery — not information_schema via query.
• Fully qualified names: <catalog>.<schema>.<table>, <database>.<schema>.<table>.
• Query scope: every query runs inside one instant database (active or --database); it sees that database's own catalog plus whatever is attached to it only. To read another database's table, or join your own table against one, attach that database first: hotdata databases attach <database> — see `hotdata` skill → Querying across databases. No instant database set → "a database is required."
• Long-running queries may return query_run_id → poll with `query status` — exit 0 succeeded, 1 failed, 2 still running (poll again), 3 succeeded but the printed result is an incomplete/truncated preview. Do not re-run identical heavy SQL while polling.
• Big result sets: use -o csv or -o json — both stream the full persisted result with flat memory. -o table is a human view capped at 10,000 rows; past that the footer reads N of TOTAL rows — INCOMPLETE PREVIEW and the command exits 3. Details in the `hotdata` skill → Execute SQL Query.
• For workspace-wide joins and naming, load context:DATAMODEL when listed (hotdata databases context list → show DATAMODEL) — see `hotdata` skill.

OLAP patterns

Typical analytics SQL (all via hotdata query):

• Aggregations: COUNT, SUM, AVG, MIN, MAX with GROUP BY
• Joins: INNER / LEFT JOIN across <catalog>.<schema>.<table> names — every referenced catalog (the instant database's own or an attached one) must be in the current database's scope; attach catalogs first (hotdata databases attach)
• Filtering: WHERE on partition-friendly columns (consider sorted indexes below)
• Ordering: ORDER BY on metrics or dimensions
• Bounded exploration: always LIMIT while iterating; widen once validated

Column names from CSV uploads may be case-sensitive — use double quotes when not all-lowercase.

Query run history

Scoped to the current database (set with hotdata databases use); pass -d/--database <database> (name, catalog alias, or id) to target another, -w/--workspace-id for another workspace.

hotdata databases queries list [-d <db-id>] [--workspace-id <workspace_id>] [--limit <int>] [--cursor <token>] [--status <csv>] [--output table|json|yaml]
hotdata databases queries <query_run_id> [-d <db-id>] [--workspace-id <workspace_id>] [--output table|json|yaml]

• list — status, duration, row count, SQL preview (default limit 20). Filter: --status running,failed.
• <query_run_id> — full metadata, formatted SQL, result_id when present.
• Use history to find recurring WHERE / JOIN / GROUP BY patterns before adding indexes (search skill) or chains.

Stored results

hotdata databases results list [-d <db-id>] [--workspace-id <workspace_id>] [--limit <int>] [--offset <int>] [--output table|json|yaml]
hotdata databases results get <result_id> [-d <db-id>] [--workspace-id <workspace_id>] [--output table|json|csv]

• Prefer `databases results get <id>` over re-running identical heavy queries. It renders like query: csv/json stream the whole result, table is capped at 10,000 rows and exits 3 when the result is larger. Results and query runs scope to the current database; pass -d/--database <database> (name, catalog alias, or id) to target another.
• Query footers may include [result-id: rslt...]; also available from databases queries <query_run_id>.
• databases results list --limit defaults to 100 (max 1000) — unlike databases queries list, which defaults to 20.

Chain (materialized follow-ups)

Pattern: run SQL → materialize a smaller table → query the materialized name.

1. Base query

``bash hotdata query "SELECT ..." hotdata query status <query_run_id> # if async ``

1. Materialize into an instant database (parquet)

``bash hotdata databases create --catalog analytics # prints the new id hotdata databases use <id> # create does not switch hotdata databases load --catalog analytics --table slice --file ./slice.parquet ``

1. Chain query — use the catalog-qualified name <catalog>.public.<table>:

``bash hotdata query "SELECT * FROM analytics.public.slice WHERE ..." ``

Document stable chains in context:DATAMODEL → Derived tables (Chain).

Full procedure: references/WORKFLOWS.md.

Sorted indexes (filters and range scans)

For equality, range, and sort-heavy OLAP — not full-text or vector (see `hotdata-search`):

hotdata search create idx_orders_created --type sorted \
  --from <catalog-alias>.<schema>.<table> --column created_at [--async]

List and remove use the same hotdata search commands as in the search skill; only `--type sorted` is the analytics focus here. With --async, track the build via `hotdata jobs list` (see `hotdata` skill → Jobs).