AskWarehouse — Text-to-SQL Analytics Agent

Business users need to ask a warehouse questions in plain English, but a text-to-SQL system that returns confident, wrong SQL is worse than one that asks for clarification or fails safely. Safety and correctness had to be built as code, not hoped for from a prompt.

The pipeline: schema retrieval by embedding — never the full schema — then an ambiguity check, dialect-aware SQL generation, self-critique, and a stack of static guards before execution: a read-only AST check via `sqlglot` that rejects anything that isn't SELECT/WITH or touches a PII-denylisted column, a forced LIMIT, and an EXPLAIN-based cost check using the database's own cardinality estimates.

Execution runs against DuckDB opened `read_only=True` at the storage-engine level, so a write attempt fails regardless of what the generated SQL says. On error, a repair loop feeds the exact database error message back to the model for up to three attempts, before falling back to a clarification question or a refusal.

Everything runs on a local Qwen2.5-Coder-7B-Instruct model (4-bit quantised) — zero per-query API cost — with a provider interface that swaps in a hosted model transparently if an API key is set.

On BIRD mini-dev (100 stratified questions), execution accuracy climbed from 32.0% (single-shot, full schema) to 42.0% with the full pipeline: schema retrieval and a value index each added a few points, self-critique alone cost a point on its own, and the repair loop was the single biggest jump — turning a wrong-column guess into a corrected second attempt using the database's own error as feedback.

On a hand-written 109-question evaluation against the project's own warehouse (with a semantic layer): 64.2% execution accuracy, 90.8% valid SQL. Ambiguity detection reaches 95% accuracy with a 0% over-ask rate (never asks on a question that was actually clear) and a 10% under-ask rate.

Safety enforced as code — AST parsing and a read-only connection opened before generation even runs — is categorically more trustworthy than safety hoped for from a system prompt. And the repair loop, not a smarter single-shot prompt, was what actually moved accuracy the most.