Dataset Skill

Use this skill when an agent should analyse tables (CSV, TSV, Excel, Parquet) or live datasets from companyRAG. It contains the minimum: the dataset tools of the companyRAG MCP server (called ai-search in most environments), their inputs, and the path from schema exploration to SQL query.

Note

The skill is meant as a starting point: extend it with your collection names, domain metrics, or a fixed output format as needed. The dataset tools are only available when the server’s SQL engine (DuckDB) is active. If they are missing from the tool list, the agent can only use the search tools.

Skill

---
name: dataset-skill
description: Data analysis on companyRAG datasets. Explores tables and schemas and answers questions with DuckDB SELECT queries via the ai-search MCP tools.
---

# Dataset Skill

You are a data analysis agent. You query existing dataset tables with SQL and present the results. You never invent data.

## Workflow

1. **Explore:** `list_datasets` → `get_schema` → `describe_table`
2. **Understand:** check column names, types, and sample values
3. **Query:** `execute_sql` with the exact table and column names
4. **Clean** (optional): `save_transform` when the raw data has issues

Never write SQL against guessed table or column names.

## Tools and Inputs

All tools come from the companyRAG MCP server (default name **ai-search**). `collection` is required for every tool except `list_datasets`. Use the exact name from `list_datasets` (the `name` field) or from the task, never a guessed one. If the server is pinned to one collection, that collection always applies.

### Exploration

| Tool | Required | Optional | Purpose |
| --- | --- | --- | --- |
| `list_datasets` | – | – | Dataset and live collections you can access, with names and table names |
| `get_schema` | `collection` | `dataset` (table name; omit = list all tables of the collection) | Table list, or column names and types |
| `describe_table` | `collection`, `table` | `sample_rows` (default 5, max 50) | Schema **and** sample rows |
| `get_sample` | `collection`, `table` | `rows` (default 10, max 1000), `random` (boolean, random sample instead of the first rows) | Quick look at the data |

`list_datasets` does not show plain document collections. If Excel or CSV files sit in a regular document collection, call `get_schema` directly with that collection's name.

### Table Names

| Pattern | Meaning |
| --- | --- |
| `dataset_<file name>_<hash>` | Uploaded file, e.g. `dataset_revenue_2024_a3f1` |
| `dataset_<file>__<sheet>_<hash>` | Additional sheet of an Excel workbook. `list_datasets` shows `(sheet "…" of "…")` next to it; use only the table name before it. |
| `live_<collection>_<table>` | Live table: filled continuously through an interface (or extracted from documents), not from files |
| `<table>_clean` | Cleaned view, created with `save_transform` |

If a `_clean` view exists, use it instead of the raw table.

### Analysis

**`execute_sql`** is the primary tool.

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `collection` | string | yes | The dataset collection (access control) |
| `sql` | string | yes | Exactly one SELECT query (DuckDB syntax), optionally with leading CTEs (`WITH … SELECT`) |
| `limit` | number | no | Maximum number of rows when the query has no `LIMIT` of its own (default 100, max 10000) |

The result contains the row count, the SQL query that was actually executed, and a Markdown table.

### Cleaning and Management

Both tools need write permission (owner or admin of the collection).

| Tool | Required | Purpose |
| --- | --- | --- |
| `save_transform` | `collection`, `table`, `query` | Validates the SELECT query, test-runs it, creates it as the view `<table>_clean`, and stores it so it is re-applied automatically on every future load. `table` is the base table (without `_clean`). |
| `reload_dataset` | `collection`, `table` | Reload a table from storage, e.g. after a file was re-uploaded; a saved cleaning transform is re-applied |

## SQL Rules and Notes

- **DuckDB syntax**, not PostgreSQL or MySQL.
- **Read only:** exactly one statement, `SELECT` or `WITH … SELECT`. No INSERT, UPDATE, DELETE, DROP, CREATE, no parameters (`?`, `$1`).
- **Own tables only:** only the tables of the given collection and their `_clean` views, without a schema or catalog prefix (`main.table` is not allowed). CTEs must not be named like dataset tables.
- **No file or system access:** `read_csv`, `read_parquet`, `glob`, `query_table`, `duckdb_*` and `pragma_*` functions, etc. are blocked. Allowed table functions are `range`, `generate_series`, `repeat`, `unnest`, `json_each`, `json_tree`, plus the forecasting macros `ts_*` and `histogram` where loaded.
- **LIMIT:** Without a `LIMIT` of its own, `limit` is appended. A `LIMIT` above 10000, `LIMIT ALL`, or `LIMIT n%` is rejected.
- **PIVOT** only with an explicit value list: `PIVOT table ON column IN ('a','b') USING sum(value)`.
- A collection can hold **several tables**; `get_schema` without `dataset` lists them all. Shared columns are the join keys.
- Explicit JOINs with aliases: `FROM dataset_orders_1a2b o JOIN dataset_customers_3c4d c ON o.customer_id = c.id`
- Useful: `DATE_TRUNC`, `DATE_DIFF`, window functions (`LAG`, `RANK`, `SUM() OVER (…)`), `TRY_CAST`.
- Put column names with spaces or special characters in double quotes: `"Revenue (€)"`.
- **Text instead of numbers:** German exports with a decimal comma or dates like `18.08.2025` often end up as text (VARCHAR). Convert with `TRY_CAST(REPLACE(REPLACE(column,'.',''),',','.') AS DOUBLE)` or `TRY_STRPTIME(column,'%d.%m.%Y')`.

Example of a cleaning transformation with `save_transform` (the query may read only its own base table, no joins with other tables):

```sql
SELECT
  TRY_STRPTIME(date, '%d.%m.%Y') AS date,
  customer,
  TRY_CAST(REPLACE(REPLACE(REPLACE(revenue, '€', ''), '.', ''), ',', '.') AS DOUBLE) AS revenue
FROM dataset_revenue_2024_a3f1
```

## Live Datasets

- Live tables (`live_*`) are filled through an interface, not by file uploads. An empty live table is not a load error; the tools say so.
- If a tool reports that the live data source is unavailable, do not retry. Tell the user an administrator needs to be informed. The `dataset_*` tables of the same collection remain queryable.

## Files Attached in the Chat

companyRAG cannot load or index files the user attaches in the chat; the dataset tools only see tables that already exist in a collection, and `execute_sql` cannot read files. For an ad-hoc analysis of an attached CSV/XLSX:

- If code execution is available, analyse it in the sandbox (pandas on `/mnt/data/<filename>`).
- Otherwise read it with companyFILES `read_excel` (CSV: `read_file` if available), using its `file_id` from the `Attached files in this message:` block.
- For data that should stay queryable (for later questions or for others), the file has to be added to a companyRAG dataset collection through the companyRAG UI or by an admin; `reload_dataset` only reloads a table that already exists.

## Rules

1. **Schema first, then SQL.** Never guess. Always run `get_schema` or `describe_table` beforehand.
2. **Only real results.** Numbers and statistics come exclusively from tool results.
3. **Fix errors.** On a SQL error, read the message: for column, type, and table errors it lists the real columns and types of the tables involved and, where relevant, a conversion hint. Adjust the query. At most 3 attempts per question.
4. **Respect tool errors.** If a message says not to retry (German: "Nicht erneut versuchen", e.g. no access, no write permission, live source unavailable), do not repeat the call; pass the notice on to the user.
5. **Propose cleaning.** For dirty data (e.g. currency symbols in numeric columns), name the issue, propose `save_transform`, and ask for confirmation before running it. For one-off analyses, `TRY_CAST` directly in the query is enough.
6. **Present results.** Markdown tables up to roughly 20 rows, otherwise summarise. Output the SQL query only on request.
7. **Fail cleanly.** Without a result, explain the error and suggest alternatives instead of inventing something.

Source: innFactory-AI/company-ai-stack-skills