# Extract Knowledge > Mine shortest atomic facts from (question + gold_sql) pairs into ./knowledge/*.md; either by simulating SQL drafting (lite) or by driving the gen_sql subagent in blind iteration (deep) - Skill: `datus-ai/extract-knowledge` (Agent Skill) - Install (CLI): `npx skillmds@latest add datus-ai/extract-knowledge` - Raw SKILL.md: https://api.skillmd.com/api/skills/datus-ai/extract-knowledge/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: datus-ai (https://skillmd.com/u/datus-ai) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/datus-ai/extract-knowledge --- # Extract Business Knowledge from Gold SQL Pairs You receive one or more `(question, gold_sql)` pairs. For each pair you must: 1. Produce a SQL candidate whose result set matches `gold_sql` **exactly** — either by simulating the drafting yourself (lite mode) or by driving a blind `gen_sql` subagent (deep mode). In deep mode, **never expose the gold SQL or any gold result values to the subagent**. 2. Diff the candidate against `gold_sql` to surface the *business knowledge gap* — the rules, joins, filters, granularity, or business definitions that a generic SQL agent would not know. 3. Filter the gap through the "worth-writing" test, then persist each surviving atom as the **shortest possible fact** into `./knowledge/.md`, and refresh the `## Knowledge` index in `./AGENTS.md`. The goal is **the shortest description that lets an LLM answer correctly**. Matching the SQL is only the means; reusable atomic facts are the deliverable. ## Critical Rules - **Never expose `gold_sql` to the subagent.** Not the full SQL, not snippets, not concrete result values, not column-level data. The subagent must stay "blind" — only then do its mistakes reveal what knowledge is missing. - **Never quote gold's concrete values when talking to the subagent.** Follow-up prompts may only describe *symptoms* and *qualitative directions*. - Across retries, always reuse the subagent's `session_id` via the `task` tool — that's the only way the subagent remembers prior attempts. ## Input Resolve the pair source in this priority order (use the first one that works, do not keep looking): 1. **Explicitly supplied in the current user message** — single pair, or multiple (list, CSV file path, YAML/JSON). 2. **Files / paths attached to the current message** — if the user passes a path, `read_file` first, then parse. 3. **Recovered from recent conversation context** — when the user invokes `/extract-knowledge` without explicit pairs: - Scan backward from the latest message and locate the most recent user-approved `(question, SQL)` — i.e. a data question the user asked, followed by an assistant SQL the user continued to refine, accepted, or did not reject. - Use the user's original question as `question`, and the final adopted SQL as `gold_sql`. - **Confirm via `ask_user`** by showing the recovered `question` and a prefix of `gold_sql`. **Do not run silently** — context recovery has ambiguity risk (you may have latched onto an intermediate draft). If none of the above yields a clear `(question, SQL)`, call `ask_user` to request the pair from the user. ## Mode Selection (lite / deep) Once the input is resolved, decide which mode to run before entering the workflow: - **lite (default)** — do not call the `gen_sql` subagent. The main agent itself simulates "given this question + the current datasource schema, how would I draft the SQL," diffs that mental draft against `gold_sql`, and mines the gap directly. **Pros:** fast, zero subagent calls, single-pass. **Cons:** no independent blind-generator validation; depends on the main agent's discipline to "pretend not to know the answer." - **deep** — full pipeline. Drive the `gen_sql` subagent through multi-round blind iteration (≤5 rounds) until result match, then mine facts from the final diff. **Pros:** independent blind validator, iteration surfaces finer-grained boundary facts. **Cons:** consumes subagent tokens, requires multiple `execute_sql` executions. **Decision rule:** 1. When the `ask_user` tool is available, call it once and ask the user to choose lite or deep; include the one-line tradeoff. Default suggestion = lite. 2. When `ask_user` is unavailable (no such tool, or running non-interactively), **default to lite** and note in the final output: "defaulted to lite; rerun and choose deep for stricter validation." Record the chosen `mode` and route to the right workflow branch. ## Workflow (one pair at a time) ### Step 1 — Validate `gold_sql` (both modes) Run `execute_sql(sql=)`. - If it errors: report to the user and **skip this pair**. Do not invent questions to salvage a broken gold. - Otherwise: cache the result (row count, column names, small preview). This is the factual baseline for later comparison. ### Steps 2–4 (lite mode) — Main agent simulates and diffs Take this branch only when `mode = lite`; skip the deep branch entirely. 1. **Temporarily ignore `gold_sql`.** Mentally switch to a state where you only know `question` + the current datasource schema. **Do not** let the specific shape of `gold_sql` influence this draft. 2. **Write a draft SQL** (no need to `execute_sql`-execute it; the draft is only a comparison baseline) — this is the version a SQL agent unfamiliar with this project's business conventions would produce given that schema. 3. **Diff the draft against `gold_sql` along these axes:** - Table choice, table aliases, missing mapping tables - Join type and join keys - WHERE filters (especially constant filters, status codes, tenant / platform predicates) - Group-by granularity, aggregate functions, deduplication strategy - Output columns, column order, column aliases - Boundary conditions, strict vs non-strict inequalities - Business-term-to-field mapping (e.g. which expression encodes "active" / "retained") 4. **Treat each diff item as a candidate fact** and pass it into Step 5's "worth-writing" filter. There is no match / mismatch verdict in lite mode — diff items drive extraction directly. Lite mode does not enter the "5-round exhaustion" branch and does not write Open Gaps (no iteration history exists). ### Step 2 (deep mode) — First subagent call Take this branch only when `mode = deep` and continue with Steps 3 and 4 below. Invoke: ``` task( type="gen_sql", prompt=, # natural-language question only description="extract-knowledge: initial attempt for " ) ``` The returned envelope contains `result.sql` (or `result.sql_file_path` for long SQL), `result.response`, and `result.session_id`. **Save the `session_id`** — every later retry must reuse it. ### Step 3 (deep mode) — Execute and compare Run the subagent's SQL via `execute_sql` and compare against the cached gold result: - Row count match? - Column names / count match at the semantic level (aliases may differ)? - After sorting both sides by the same key, do sampled rows match? - For precise verdicts, use `execute_sql` to run difference probes (two-way `EXCEPT`, key-column `SUM` / `COUNT(DISTINCT ...)` consistency checks, etc.) Verdict: **match** or **mismatch**. ### Step 4 (deep mode) — Mismatch: diagnose and retry on the same session Diagnose qualitatively. Common symptoms → likely missing knowledge: | Symptom | Likely missing knowledge | |---------|--------------------------| | Too many rows | Missing filter, missing join, wrong granularity | | Too few rows | Extra filter, join condition too strict | | Aggregate value off | Wrong measure column, missing dedup, wrong unit | | Extra / missing columns | Output shape mismatch, projection rule missing | | Time bucketing differs | Granularity / business calendar / fiscal-year rule | | Unexpected NULLs | Wrong join type (LEFT vs INNER), missing COALESCE rule | When designing the follow-up prompt, obey: - Describe symptoms and direction only. Never leak gold values. Never restate gold SQL's exact wording to the subagent. - **The "direction" portion of your prompt should already read like a draft of the future knowledge fact.** I.e. when you write the prompt you have mentally passed it through the "worth-writing" filter: every business rule / mandatory filter / field trap you point the subagent at must be transcribable as a knowledge fact in Step 5 by simply switching from imperative to declarative voice. Benefits: prompts are shorter and more focused; Step 5 becomes near-zero-cost (cherry-pick validated directions from prompt history, strip imperative voice, write to file); prompts and knowledge content stay aligned. - Avoid shotgun guessing ("maybe the join is wrong? or the aggregation? or null handling?") — such prompts cannot be transcribed into any fact and waste tokens. Then call: ``` task( type="gen_sql", session_id=, # must be the previous round's id prompt=, description="extract-knowledge: refine #" ) ``` Loop Step 3 → Step 4 for **at most 5 rounds (including the first)**. If still mismatched after round 5: stop retrying, but **do not discard the process information** — promote every confirmed knowledge gap (the rules the subagent finally got right each round, the business rules / field traps / mandatory filters you've already pinpointed in follow-up prompts) into facts via Step 5, then log the remaining un-aligned differences in Open Gaps via Step 7. ## "Worth-Writing" Test (read before Step 5) For every candidate diff item, ask these 4 questions in order. **If any answer is "yes," drop it.** 1. Without this knowledge, could a SQL agent with the question + schema still get it right? 2. Can this information be inferred directly from `INFORMATION_SCHEMA` / table comments / column names? 3. Is this generic SQL knowledge (not specific to this business / dataset)? 4. **Can this fact be mechanically composed from other facts already in the file?** (If two existing facts combine to produce it, the combination is derivative — do not write it.) **Record atomic facts only** — anything else counts as derivative and is not written. Common atomic categories worth writing: - **Field encoding** — bit-position meanings, enum mappings, status codes, special-value semantics - **Business measure definitions** — the criteria behind business terms - **Mandatory constant filters** — constant predicates whose omission corrupts results - **Boundary traps** — strict inequalities, interval endpoint open/closed conventions - **Implicit table joins** — relationships that must go through a mapping table; direct joins forbidden - **Same-name field divergence** — the same field name has different semantics in different contexts - **Required timing / parameter constraints** — business-mandated fixed parameters **Self-check:** after writing a group of facts, look back — can you drop any single one and still let the LLM answer correctly? If yes, that one was derivative. Drop it. ### Step 5 — Mine atomic facts (both modes) **Trigger:** - **lite mode** — enter immediately after Steps 2–4 (lite) produce the draft-vs-`gold_sql` diff. - **deep mode** — match achieved *or* 5 rounds exhausted. Both cases enter — partial-alignment progress still carries knowledge value. **lite mode:** every diff item from Steps 2–4 (lite) is a candidate fact. **deep mode:** diff the subagent's final SQL against `gold_sql` (also against the subagent's *first* attempt — the middle is informative). On failure, additionally walk through every follow-up prompt you sent; the "directions you've already qualitatively named" are confirmed fact sources. **Systematic corpus scan (both modes, additive) — mine encodings the per-pair diff misses.** The diff only surfaces facts a generic agent got *wrong on this question*; a literal that the draft happened to guess, or that no pair exercised, slips through. So, when you have access to the validated-SQL corpus (not just the single pair), also scan its `WHERE` / `SELECT` clauses **structurally** for non-inferable atoms, independent of any single diff: - **Literal filter values / coded enumerations** — every constant predicate (`col = ''`, `col IN (...)`, bit/flag tests) where the literal carries business meaning → a *term ↔ column ↔ value* fact (e.g. a business term maps to a specific column holding a specific code). - **Business-term → column / expression mappings** — when a question's term resolves to a particular column or computed expression in `SELECT`/`WHERE` → record the mapping. - **Question-word → column trigger mappings** — when a recurring *interrogative phrasing* (not just a noun) consistently resolves to one column or expression across pairs (e.g. one wording always hits column A while a near-synonym wording hits column B), record the trigger fact: *question says ⟨phrasing⟩ → use ⟨column/expression⟩*. These disambiguate near-duplicate columns that schema inspection cannot. - **Recurring mandatory constant filters** — a constant predicate that appears across many pairs and whose omission corrupts results → a required-filter fact. - **Threshold facts always carry the comparison operator's strictness.** A threshold without its boundary semantics is half a fact: record `> 500` vs `>= 500` (and `BETWEEN`'s inclusivity) exactly as the validated SQL has it — strict-vs-inclusive mismatches flip rows at the boundary and are among the most common silent errors. This pass extracts **structure** (literals in `WHERE`, term→column in `SELECT`), never hardcoded domain vocabulary — it works for any dataset. Every candidate it produces is just another candidate: funnel it through the same "worth-writing" test (so generic/inferable facts are still dropped) and the same Step 6 dedup/conflict gates (so it never duplicates what the diff already wrote). Pass every candidate through the "worth-writing" test. Only survivors become facts. Each fact contains only: - `statement` — one sentence stating the fact itself. **Do not explain "why it matters."** Critical warnings (boundary values, easy-to-miss traps) go in inline parenthetical notes. - `example` (optional) — **only when the one-sentence statement cannot remove ambiguity** by itself; then attach a minimal SQL snippet. **Forbidden:** - Restating the rule in SQL (don't say "use `> 7`, not `>= 7`" and then paste a `WHERE x > 7` snippet) - Writing "violating this would be wrong" as a standalone field — pure filler - Writing as a standalone fact anything that mechanically composes from other facts ### Knowledge File Layout ``` business domain → topic → facts (one .md) (## heading) (list items / table rows / paragraphs / optional ### subblock) ``` - **Business domain = one file** — a slice of business broad enough that its rules co-evolve. Path: `./knowledge/.md`. Prefer fewer, wider domains over many narrow ones. - **Topic (`##`) = a group of facts sharing context** — typically the constraints of one table, a set of measure definitions, or a related mapping group. - **Fact representation, in priority order:** 1. **Bulleted list item** — multiple independent facts under the same topic (most common) 2. **Table row** — several parallel field / enum / branch mappings 3. **Paragraph** — a conceptual fact a sentence or two can explain 4. **`### ` subblock** — only when a SQL example must be attached **Hard constraints:** - A topic block must hold ≥2 independent facts (otherwise merge into a wider topic) - Heading depth never exceeds `###` - Do not write `Derived from` / `Why it matters` / `When it applies` label fields - The file must contain no question text (trace via git history instead) - **Strong preference: reuse first, create new last.** Before persisting anything, list `./knowledge/` and read each file's Domain intro. Prefer adding a new topic or fact under an existing domain over opening a new file. **Template for a new business-domain file:** ```markdown # > **Domain:** . ## - fact 1 - fact 2 (note ...) | Field / Branch | Value / Verdict | |---------------|-----------------| | ... | ... | ### \`\`\`sql \`\`\` ``` ### Step 6 — Persist (fact-level dedup / conflict gates) For every classified fact, walk the gates **in order**. Do not skip steps. #### 6.1 Resolve target domain file - If `./knowledge/` does not yet exist, just `write_file` the first file (no `ask_user` needed for an empty-directory case). - If the fact does not belong to any existing domain, **before** creating a new file you must `ask_user`: list existing domains, propose a new slug, let the user choose reuse / new / custom. - Never silently open a new file when a reasonable reuse path exists. #### 6.2 Detect duplicates and conflicts inside the file `read_file` the target. Extract existing facts per topic (list items + table rows + paragraph facts + ### subblock headings). Compare each candidate against existing facts **semantically** (not by string): | Outcome | Definition | Action | |---------|-----------|--------| | **Duplicate** | Same fact, same scope, same direction. | **Silent skip** (idempotent). Record in final report. | | **Refinement** | Existing fact is a strict subset of the new one (lower precision, missing a condition, narrower scope). | **`ask_user`**: replace / merge / keep both with a scope qualifier. Default suggestion = merge. | | **Conflict** | Same scope, opposite direction / mutually contradictory. | **`ask_user` is mandatory**. Show both side-by-side. Options: keep old / replace with new / keep both with an explicit conditional gate (you must spell out the condition). **Never resolve a conflict silently.** | | **Complementary** | Same topic, different facet. | **Append** a same-form fact under the same topic (list item / table row / paragraph). | | **Derivable** | The candidate mechanically composes from existing facts. | **Drop, do not write**. The "worth-writing" test (question 4) is supposed to catch this earlier — this is a safety net. | | **New topic / new domain** | No relevant existing topic. | Create the heading via 6.3. | #### 6.3 Write or edit the file - Prefer `edit_file` for minimum diff. - When a new fact changes the appropriate representation of an existing topic, **whole-block rewriting that topic is allowed** (only touch that block); avoid letting persistent append accumulate redundancy. - Heading conventions: - New domain → `write_file` the Domain intro followed by the first topic heading - New top-level topic → insert a `## ` block - New fact into existing topic → append as list item / table row / paragraph; only add a `### ` subblock when a SQL example is needed **Do not** insert `---` separators between facts — markdown headings / lists already separate them, and `---` makes `edit_file` insertion points ambiguous. ### Step 7 — Update the AGENTS.md index Maintain the `## Knowledge` section of `./AGENTS.md`, so that later main agents handling related business-domain tasks can enter from AGENTS.md, see what `knowledge/` holds, and `read_file` the relevant domain file as context. **AGENTS.md handling order:** 1. **`./AGENTS.md` missing** → `write_file` a minimal skeleton (just `# ` + the `## Knowledge` section; leave the rest for `/init` to fill in later). Do not wait for the user to run `/init` first. 2. **AGENTS.md exists but lacks the `## Knowledge` section** → insert after `## Artifacts` (or at end of file if Artifacts is also absent). 3. **Rescan `./knowledge/`** and **rewrite the entire `## Knowledge` section** sorted alphabetically by domain title — guarantees the index stays consistent across runs. **Fixed structure of the `## Knowledge` section:** ```markdown ## Knowledge `./knowledge/` holds this project's atomic business facts, organized per business domain (maintained by `/extract-knowledge`). Before handling a task that touches a domain, `read_file` the corresponding file for context. - [](knowledge/.md) — - [](knowledge/.md) — ### Open Gaps - — ``` - One index line = one business-domain file (not per topic, not per fact). Scope sentence comes from that file's `> Domain:` intro. - When `./knowledge/` is empty, omit the index list and `### Open Gaps`; keep only the section intro paragraph. - `### Open Gaps` omits entirely when there are no entries; when present, group by domain. **5-round mismatch handling (deep mode only):** keep the facts Step 5–6 already wrote in their domain files, then append a line `- — ` under `### Open Gaps`, and continue with the next pair. Lite mode has no iteration history and does not write Open Gaps. ## Final Output Return a single human-readable summary covering: the mode used (lite / deep); how many pairs were processed; how many matched / failed (deep) or were aligned (lite); which `knowledge/*.md` files were created / edited; how many facts were added; any conflicts still needing attention. When lite was used because `ask_user` was unavailable, add one line: "defaulted to lite; rerun and choose deep for stricter validation." **Do not return a JSON envelope.** ## Tools You'll Use - `execute_sql(sql=...)` — execute gold SQL and difference probes (deep mode additionally executes subagent SQL). In lite mode it is only used for Step 1's gold validation. - `task(type="gen_sql", prompt=..., session_id=...)` — **deep mode only**; delegate SQL generation, reuse session across retries. - `read_file`, `write_file`, `edit_file` — manage `./knowledge/*.md` and `./AGENTS.md`. **Always** `read_file` the target domain file before edits (6.2 depends on it). - `ask_user` — required for: ambiguous input parsing; confirming a context-recovered pair; mode selection (lite / deep); new-domain confirmation (6.1); refinement decisions (6.2); **every fact conflict** (6.2). Phrase questions with numbered options. ## Forbidden - In lite mode, do not call `task(type="gen_sql", ...)` — the mode selection already declared no subagent. - In deep mode, do not call `task(type="gen_sql", ...)` before confirming `gold_sql` actually runs. - In deep mode, do not open a new `gen_sql` session for retries — always pass the previous round's `session_id`. - Do not write SQL on behalf of the user's question — that's the subagent's job (deep mode). Your role is orchestrator and knowledge curator. - Do not put gold SQL into `./knowledge/*.md`. Knowledge is *atomic facts mined from the gap*, not the answer itself. - Do not write derivative facts — anything that mechanically composes from other facts is dropped. - Do not write `Derived from` / `Why it matters` / `When it applies` label fields; do not include any question text in the files. - Do not attach a SQL example to every fact — only when the one-sentence statement cannot remove ambiguity by itself. - When an existing domain can plausibly cover the topic, do not create a new file. Reuse + slightly extending the intro is almost always right. - Do not resolve fact conflicts silently. Conflicts **must** go through `ask_user` — the user decides replace / coexist / scope-gate. - Do not insert `---` separators between facts. - Do not give rules generic names (`### Rule 1`, `### Note`). The one-sentence rule itself is the heading. - Topic heading depth must not exceed `####`. Deeper means the domain was sliced wrong — fold the levels or split the file. - A single-fact topic must not be its own `##` — merge it into a wider topic.