/sql-workflow

name: sql-workflow
description: "Use this skill before writing any SQL query. Covers: output shape inference (cardinality clues from the question), efficient schema exploration, iterative CTE-based query building, structured verification loop (row count, NULL audit, fan-out check, sample inspection), error recovery protocol, saving output to result.sql and result.csv, turn budget management, and common benchmark traps."
type: skill

SQL Workflow Skill

0. Load Knowledge Base Context FIRST

Project-specific conventions, decisions, and quirks live in the Knowledge Base. Always consult before exploring the schema.

Step 0a — `get_knowledge`

Call once at the start of every task with a 1-line `task_description`. Returns the always-loaded baseline (org/project understanding + conventions) plus up to 5 task-relevant decisions/debugging/quirks. Treat the returned `## title` blocks as authoritative for naming, joins, and known traps.

Step 0b — `search_knowledge(query=...)`

Call when you hit something unexpected — column meaning unclear, ambiguous join, surprising row count. Pass a 2–4 word query. It is a pure read — no side effects.

Step 0c — `propose_knowledge`

Call ONLY after you have completed work and verified a finding. Use it to record:

• `category="decisions"` for choices made (auto-accepted).
• `category="debugging"` for root-cause traps you hit and resolved (auto-accepted).
• `category="quirks"` (scope=connection) for connector/dialect oddities (auto-accepted).
• Do NOT propose `understanding` — humans only. Do NOT propose `conventions` or `domain-rules` as part of automated runs unless explicitly asked (these queue for human review).
• Title must be a slug (`^[a-z0-9-]+$`, ≤120 chars). Body is markdown.
• On duplicate-title: re-call with `overwrite=true` only if the prior doc is genuinely outdated.

What NOT to do

• Do not paste raw KB text back into SQL comments — reference the doc title instead.
• Do not call `propose_knowledge` mid-exploration — only after success.

1. Schema Exploration — Do This First

Before writing any SQL, load KB context (Phase 0 above) then understand the data:

0. **Read local schema files first** (if schema/ directory exists in workdir):

• `schema/DDL.csv` — all CREATE TABLE statements (if it exists)
• `schema/{table_name}.json` — column names, types, descriptions, sample values

Reading these files costs zero tool calls and gives you table structure + sample data. Only call MCP tools for information not in the local files (e.g., row counts, live data exploration). 1. Call `list_tables` to get all schemas and tables — only if no local schema files exist or you need row counts. 2. Call `describe_table` on the tables that seem relevant to the question (only if JSON files lack detail) 3. Call `explore_column` on categorical columns to see distinct values (for filtering/grouping) 4. Call `find_join_path` if you need to join tables and the relationship is unclear

Stop exploring after 3-5 tool calls. Write SQL based on what you've found.

2. Output Shape Inference — Before Writing SQL

Read the task question carefully for cardinality clues:

• "for each X" → GROUP BY X, one output row per X
• "top N" / "top 5" → LIMIT N or QUALIFY RANK() <= N
• "total / sum / average" → single row aggregate
• "list all" → detail rows, no aggregation
• "how many" → COUNT, result is 1 row 1 column

Write a comment at the top of your SQL:

-- EXPECTED: <row count estimate> rows because <reason from question>

Critical checks:

• If the question asks for a single number, the result MUST be 1 row × 1 column
• If the question says "how many", verify the CSV has exactly 1 row with a COUNT value
• If "top N" appears in the question, verify the CSV has at most N rows

3. Iterative Query Building — Build Bottom-Up

Do NOT write a 50-line query and run it all at once:

1. Write the innermost subquery or first CTE first 2. Run it standalone with `query_database` — verify row count and sample values 3. Add the next CTE, verify again 4. Continue until the full query is built

Example incremental pattern:

-- Step 1: verify source
SELECT COUNT(*) FROM orders WHERE status = 'completed';

-- Step 2: verify join partner cardinality
SELECT COUNT(*), COUNT(DISTINCT customer_id) FROM orders;

-- Step 3: build first CTE, verify
WITH order_totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT COUNT(*), COUNT(DISTINCT customer_id) FROM order_totals;

-- Step 4: add final aggregation

4. Execution and Structured Verification

mcp__signalpilot__query_database
  connection_name="<task_connection_name>"
  sql="SELECT ..."

After executing, run these checks IN ORDER before saving:

1. **Row count sanity**: Does 0 rows make sense? Does 1M rows make sense for a "top 10" question? 2. **Column count**: Does the result have the right number of columns for the question? 3. **NULL audit**: For each key column — unexpected NULLs indicate wrong JOINs:

   SELECT COUNT(*) - COUNT(col) AS nulls FROM (your_query) t

4. **Sample inspection**: Look at 5 rows — are values in expected ranges? Do string columns have meaningful values (not join keys)? 5. **Fan-out check**: If JOINing, compare `COUNT(*)` vs `COUNT(DISTINCT primary_key)`:

   SELECT COUNT(*) AS total_rows, COUNT(DISTINCT <pk>) AS unique_keys FROM (your_query) t;

If they differ, you have duplicate rows from a fan-out JOIN. 6. **Re-read the question**: Does your output actually answer what was asked?

5. Error Recovery Protocol

• **Syntax error**: Use `validate_sql` before `query_database` to catch errors without burning a query turn
• **Wrong results**: Do NOT just re-run the same query. Diagnose: which JOIN is wrong? Which filter is too aggressive?
• **Zero rows**: Binary-search your WHERE conditions — remove them one at a time to find the culprit:

  SELECT COUNT(*) FROM table WHERE c