BI SQL Knowledge (Seshat BI)

A navigational interface to a SQL reasoning and validation knowledge base. It teaches an agent to reason about SQL correctly -- with table grain and aggregation correctness as its foundation, the way filter context is the foundation of bi-dax-knowledge.

This file is short by design. It does not contain the knowledge base. Do not read every file in this skill. Follow the flow below.

Mandatory flow (do not skip a step, do not lose the compass)

Router  ->  this SKILL.md  ->  INDEX.md  ->  ONLY the relevant file(s)  ->  output contract / checklist

1. Open INDEX.md first. It routes by task and by symptom to the specific file(s) you need.
2. Read only what the route names -- one or two knowledge files, one JSON, one checklist. Reading the whole base is an anti-pattern; it wastes context and dilutes the answer.
3. Ground the grain before anything else. State what one row of the input/result means (knowledge/sql-core-concepts.md, SC-003). Most SQL bugs are grain errors.
4. End on an artifact: a validation gate (patterns/sql-validation-patterns.json, VP-), a checklist (checklists/), or a diagnostic verdict (knowledge/sql-diagnostics-playbook.md, PB-SQL-). Never end on prose alone.

What this skill is for

Reasoning about, reviewing, and validating SQL used in BI pipelines: profiling a source; grain / keys / uniqueness; joins and fan-out amplification; aggregation correctness (COUNT / SUM / AVG, GROUP BY); COUNT and NULL semantics; deduplication; validation and reconciliation queries; silver/gold transformation logic (DML, reshaping, cleaning, set operations, date recipes, gaps/islands, hierarchy, metadata-driven profiling); window and date/time analytics; SQL anti-patterns (including shell/encoding pitfalls like matching a non-ASCII/RTL literal on a command line); basic performance reasoning; and evidence-gated, read-only interpretation of supplied PostgreSQL JSON execution plans.

What this skill is NOT for

Not a SQL tutorial; not a chapter-by-chapter book summary; not a replacement for any book; not a database execution tool; not a runtime validator; not a dbt/Dagster project; not an automatic PostgreSQL tuning service; not the Power BI dashboard or DAX layer. It reasons about SQL and supplied plans; it never runs them.

Routing boundaries (pick the right skill before working)

Where it sits in the pipeline:

source -> [source-mapping / retail-onboard-table: mapping / readiness]
       -> [bi-sql-knowledge: profile / validate / reconcile / transform -> silver/gold]
       -> [bi-dax-knowledge: measures / model]
       -> [powerbi-dashboard-design: visuals / pages]

A validated gold table with a known grain and verified unique keys is the hand-off to bi-dax-knowledge: grain and uniqueness feed DAX additivity reasoning.

Stop rules

• Stop and ask for metadata, do not guess. When two cheap checks -- a COUNT(*) comparison and a uniqueness check -- can't localize a problem, request schema/source metadata (primary keys, expected grain, null semantics, SLA, expected volumes). Establishing these facts is the job; inventing them is not.
• Stop at the routing boundary. If the real task is DAX, a dashboard, or source mapping, hand off to the skill above instead of answering here.
• Stop before executing. This layer produces SQL reasoning and gate shapes. It does not run queries, wire a runtime, build a CLI, or publish anything.
• Stop without plan evidence. Bare SQL can support a hypothesis, not a PostgreSQL performance verdict. Plan review requires a supplied, sanitized JSON plan and representative context.
• Never fake a pass. Validation/reconciliation results are evidence-based; a gate with no evidence is blocked, never pass.

ID conventions (full detail in references/id-conventions.md)

Concepts SC-001..070 - plan concepts EP-001..014 - plan patterns EP-PAT-* - anti-patterns SQL-AP-001..061 - validation gates VP-* - diagnostic playbooks PB-SQL-01..19 - analyzer-rule candidates SARC-* (staged) - promoted draft analyzer rules SAR-* (static draft, not runtime-enforced).

Boundaries

Not implementation, not database execution, not dbt/Dagster, not a runtime validator (validation patterns are reasoning templates, not an executing engine), not a replacement for any book, not automatic PostgreSQL tuning. An agent knowledge layer. All examples are original Seshat BI / retail examples on a fictional schema; no book text or datasets are reproduced (references/copyright-safety.md).