sql-expert

---
name: sql-expert
description: Expert system for generating, validating, and optimizing ClickHouse SQL. Use this when the user needs data, queries, or analysis.
metadata:
  author: System
  disable-slash-command: true
---

> ## 🚨 CRITICAL RULE: MANDATORY VALIDATION
> You **MUST** call `validate_sql(sql)` for **every** new query you generate.
> **Context Note**: Historical validation steps are pruned to save tokens, but this does NOT excuse you from validating new queries in the current turn. Always validate before executing.

# 1. Schema Discovery & Context
- **Missing Schema**: If you do not have the table schema, you MUST use `get_tables` and `explore_schema` first.
  - *Optimization*: If the user already mentioned exact field names, pass them in the `columns` argument of `explore_schema` instead of loading the full table schema.
- **Exact Identifier Rule**: Treat exact identifier-like tokens from the user question as candidate columns on the first schema lookup. This especially applies to ClickHouse metric names such as `ProfileEvent_*`, `CurrentMetric_*`, and flattened event columns on `system.*` tables.
- **Missing Columns**: If you don't see the expected column, retry `explore_schema` with a narrower `columns` list based on the user-mentioned identifier or the closest confirmed column names.
- **Schema Fidelity**: Only use columns that are confirmed to exist in the table schema from `explore_schema`. Do not assume standard columns exist if they are not in the tool output.
- **User Context**: If the user asks about "my data", use `WHERE user = '<clickHouseUser>'`.
- **System Tables**: For queries on `system.*` tables (e.g., `system.query_log`, `system.parts`, `system.merges`), defer to the `clickhouse-system-queries` skill - it contains table-specific patterns, predicates, and resource metrics that this skill does not cover. For `system.query_log`, do not generate SQL until `references/system-query-log.md` has been loaded via `skill_resource`, and do not call `search_query_log` for chart/time-series requests.

# 2. Syntax Rules (The Grammar)
- **Tables**: ALWAYS use fully qualified names (e.g., `database.table`).
- **Semicolons**: NEVER include a trailing semicolon (`;`).
- **Enums**: Use exact string literals for Enum columns.
- **Safety**: ALWAYS use `LIMIT` for data exploration queries.

# 3. Optimization Rules (Best Practices)
- **Time filters**: Always filter by the partition key (usually `event_date` or `timestamp`) first. Use **bounded time windows** (e.g., last 24h, 7 days) unless the user asks for all history.
- **Primary Keys (CRITICAL)**: ClickHouse indexes are sparse. You **MUST** filter on the **leading column** of the Primary Key if you filter on any secondary column.
  - *Bad*: `WHERE event_time > now() - 1h` (If PK is `event_date, event_time`, this scans everything).
  - *Good*: `WHERE event_date >= toDate(now() - 1h) AND event_time > now() - 1h` (Uses index, handles midnight crossover).
- **Approximation**: Use `uniq()` instead of `uniqExact()` unless precision is explicitly requested.
- **Joins**: Put the **smaller table on the RIGHT**. Use `GLOBAL IN` only for distributed queries.

# 4. Execution Workflow
1. **Generate**: Create the SQL following the rules above.
2. **Validate (MANDATORY)**: Call `validate_sql(sql)`.
   - *If invalid*: Read the error, fix the SQL, and retry (max 3 attempts).
3. **Decide Action**:
   - *Visualization*: IF the user wants a chart, DO NOT execute. Pass the SQL to the visualization skill logic.
   - *Data*: IF the user wants answers (lists, counts), call `execute_sql(sql)`.
   - *Code Only*: IF the user asks to "write SQL", just output the code block.