sql-expert

🚨 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.