↑ ↓ to move, Enter to open, Esc to close

    Chidi's Blog

    Letting managers ask data questions in plain English, safely

    A plain-English question becomes one read-only SELECT that passes the guard, while DROP TABLE is blocked; 12 of 12 unsafe prompts blocked.

    A manager types “Which store had the best margin in December?” and wants an answer, not a ticket for the data team. That is the promise of a plain-English analytics agent. Two things can go wrong, and my BI Operations Agent is built around preventing both: the answer can be inconsistent, and the query can be unsafe.

    Problem one: the same metric, three definitions

    Ask a loose text-to-SQL tool for “gross margin” on Monday and again on Friday and it may write two different formulas. One might subtract returns, another might not. The numbers disagree and nobody trusts either.

    The fix is a semantic layer. In the agent it is a YAML file that declares the approved metrics (revenue, gross margin, margin percentage, order count, average basket, return rate), the dimensions you can group by, and the joins that are allowed. The question gets mapped onto those definitions, so “gross margin” always compiles to the same formula. A person in finance can read the file and agree that it says what they mean.

    Problem two: the model writes the SQL

    Whatever produces the SQL, whether a language model or a rule-based compiler, you should treat its output as untrusted input. If someone asks the agent to “drop the sales table”, or hides an instruction in a question, the database must not care.

    The tempting defence is a text filter: reject any query containing the word DROP or DELETE. It is also a weak one. Words can hide in comments, in odd casing, in a second statement after a semicolon, or inside a subquery. A filter that is too strict also blocks harmless queries.

    My guard does not read text. It parses the query into a syntax tree using the sqlglot library and then checks the structure:

    • Exactly one statement is allowed, and it must be a SELECT or a WITH query.
    • Every table named anywhere in the tree, including subqueries, CTEs and UNION branches, must be on the semantic layer’s allow-list.
    • Writes, schema changes, PRAGMA, ATTACH, SQL comments and unknown functions are rejected.
    • A row limit is added automatically, and the database connection itself is opened read-only.

    The last point is the important one. Even if the guard had a bug, the database would still refuse to write. Two independent layers mean that one mistake is not enough.

    How I tested it

    I wrote a benchmark of 50 business questions with known answers, and 12 adversarial prompts: injection attempts and requests to leak or change data. The data is a synthetic two-year Nigerian retail dataset that the repo generates (10 stores, 6 cities), not real transactions.

    The results, from the evaluation file in the repo:

    • 48 of 50 benchmark questions answered correctly (96.0%).
    • 12 of 12 adversarial prompts blocked, with no unsafe SQL executed.
    • 14 of 25 correct on reworded questions that were held out.

    That last number is the one I would draw attention to. The 50 benchmark questions are the phrasings the agent’s templates were written around, so 96% flatters it. When I reworded 25 questions, it got 14 right, which is 56%. By area, revenue questions did well and ranking questions did not. I report it because it shows where the work remains, and it is the number I would expect a user to experience more often.

    So the claim I am comfortable making is narrow. The safety layer held up against every attack in my set, and the metrics are consistent because they are defined once. The question understanding is not yet good enough to trust on unseen wording. That is the next piece of work: broader templates, more held-out questions, and measuring the miss rate on each.

    What to take from this

    If you plan to give non-technical staff a way to query company data, three decisions matter more than the model you pick: define your metrics once, validate generated SQL by structure rather than by keywords, and make the database connection read-only so that a failure of the first two is still harmless. Then test with questions the system was not built for, and publish the lower number.

    The project is on my GitHub profile, with the benchmark and the evaluation file.