SQL Workflow

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 cond_1;             -- still same? keep it
  SELECT COUNT(*) FROM table WHERE cond_1 AND cond_2;  -- drops? cond_2 is culprit
  

• Too many rows: Check for fan-out (duplicate join keys) or missing GROUP BY
• CTE debugging: Use debug_cte_query to run each CTE independently and find which step breaks

6. Saving Output

Once you have the correct result:

1. Write final SQL to result.sql:

   Write tool: path="result.sql", content="<your SQL query>"
   

2. Write the result as CSV to result.csv:

   Write tool: path="result.csv", content="col1,col2,...\nval1,val2,..."
   

   • Always include a header row with column names
   • Use comma as delimiter
   • Quote string values that contain commas or newlines

7. Turn Budget Management

• First 3 turns: Schema exploration only (schema_overview, describe_table on 2-3 tables, explore_column on key categorical columns). STOP exploring.
• Turns 4 through (N-3): Write query iteratively — execute and verify each step.
• Last 3 turns: Finalize result.sql and result.csv. If you have a working query, SAVE IT NOW — do not keep iterating.

If your query works and passes all verification checks, SAVE IMMEDIATELY — do not continue exploring "just in case".

8. Common Benchmark Traps

• Rounding: Do NOT round unless the question explicitly asks for rounded values. The evaluator uses tolerance-based comparison — full precision is always safer.
• Column naming: Match the question's phrasing exactly. If the question says "total revenue", name the column total_revenue, not sum_revenue or revenue_total.
• CSV format: No trailing newline, no BOM, comma delimiter, double-quote strings containing commas.
• Empty result: If the correct answer is 0 or empty, write a CSV with just the header row (or header + "0").
• Date/time format in CSV: Use ISO 8601 (YYYY-MM-DD) unless the question specifies otherwise.
• String case in CSV: Preserve the case from the database — do not uppercase/lowercase unless the question explicitly asks.
• Fan-out from JOINs: Always check COUNT(*) vs COUNT(DISTINCT key) after every JOIN
• Wrong NULL handling: Use IS NULL / IS NOT NULL, not = NULL
• Date format mismatch: Check the actual format stored in the column with explore_column
• Case sensitivity: Use the correct case-insensitive function for your backend
• Interpretation errors: Before saving, re-read the original question. Verify:
  • Filter conditions match domain values (check with explore_column if unsure)
  • "Excluding X" means the right thing (NOT IN vs EXCEPT vs WHERE NOT)
  • Metrics match domain definitions (e.g., "scored points" in F1 = points > 0, not just participated)