AskData: a semantic-layer text-to-SQL agent with guardrails, evals and an MCP server

Ask a sales database questions in plain English ("Top 3 cities by revenue in 2025") and get back an answer, the SQL that produced it, a chart and a step-by-step trace. It runs fully offline with no API key. An LLM planner (OpenAI or a local Ollama model) is optional.

I built this as a final-year B.Tech (AI & ML) project to learn how "AI for analytics" works in practice: how to stop an LLM from writing SQL that is plausible but wrong, how to keep an agent read-only, and how to measure all of it with evals instead of eyeballing outputs.

The problem

Text-to-SQL demos look great until you check the numbers. A model that writes raw SQL against raw tables can silently use the wrong definition of revenue (cancelled orders? discounts?), make up columns, or run a DELETE. Recent write-ups (LinkedIn Engineering's SQL Bot, dbt's 2026 semantic-layer benchmark) point the same way: let the model choose governed metrics and dimensions, and let deterministic code write the SQL.

AskData is a small, end-to-end version of that idea:

1. Semantic layer (askdata/semantic_layer.yaml): 6 governed metrics (revenue, orders, AOV, units, unique customers, return rate) and 8 dimensions, each defined once.

2. Planner: turns the question into a small JSON SemanticQuery (names only, never SQL). The default is an offline rule-based planner. An LLM planner can be switched on with an env var.

3. Compiler: deterministic SemanticQuery → SQL, with values passed as bound parameters.

4. Guardrails in three layers: a write-intent check before any planner, a static SQL checker, and a read-only SQLite connection with an authorizer callback.

5. Evals: 47 golden questions with hand-written gold SQL, plus a 20-query SQL attack suite. CI runs them on every push.

6. MCP server: exposes the agent as tools (ask, run_sql, list_metrics, describe_schema) for any MCP client.

Related MCP server: mcp-analytics

Architecture

flowchart LR
    U["User question"] --> I{"Write-intent check"}
    I -- "delete / drop / update" --> R["Refuse"]
    I -- "read-only" --> P["Planner: rule-based (default)<br/>or LLM via OpenAI / Ollama"]
    SL[("semantic_layer.yaml<br/>metrics + dimensions")] --> P
    P -- "SemanticQuery JSON, names only" --> V["Validate against semantic layer"]
    V -- "invalid" --> F["Fallback to rule-based or refuse"]
    V --> C["Deterministic compiler<br/>SQL + bound params"]
    C --> G["Static SQL guardrail<br/>SELECT-only, table allowlist"]
    G --> X[("SQLite read-only<br/>mode=ro + authorizer + row cap")]
    X --> S["Template summarizer<br/>no invented numbers"]
    S --> O["Answer + SQL + table + chart + trace"]
    O --> CLI["CLI"]
    O --> ST["Streamlit app"]
    O --> MCP["MCP server"]
    E["Eval harness<br/>47 golden Qs + 20 SQL attacks"] -. "runs in CI" .-> P

Features

• Governed metrics. Revenue always means net of discount, completed orders only. The definition lives in one YAML file.

• No SQL from the model. The planner outputs JSON (metrics, dimensions, filters, dates, order_by, limit), and code writes the SQL.

• Defense-in-depth guardrails. Write intent is refused before planning. The static checker blocks multiple statements, DDL/DML, ATTACH, PRAGMA, load_extension, system tables and unknown tables. The SQLite connection is opened mode=ro with PRAGMA query_only and an authorizer that denies anything except reads. Results are capped at 200 rows.

• Injection-safe filters. Filter values are bound parameters (there's a test that passes Hyderabad' OR 1=1 -- as a city name).

• Optional LLM, works offline. With OPENAI_API_KEY or OPENAI_BASE_URL set, the LLM planner is used. Invalid LLM output (unknown metric, bad JSON, network error) falls back to the rule-based planner, and the trace records why.

• Observability. Every answer carries a step trace (intent_check → plan → compile → guardrail → execute → summarize) with timings.

• Eval harness. Execution accuracy against hand-written gold SQL, split into dev (used while building) and holdout (written up front, never tuned on), plus refusal accuracy, a guardrail attack suite and latency percentiles.

• Three interfaces. CLI, Streamlit app and MCP server, all built on the same AskDataAgent.

Quickstart

git clone https://github.com/Shibukedang27/askdata-semantic-agent.git
cd askdata-semantic-agent
python -m venv .venv && source .venv/bin/activate
pip install -r requirements-dev.txt

python -m askdata ask "Top 3 cities by revenue in 2025"   # builds data/shop.db from the CSVs on first run
python -m askdata eval                                     # golden evals + guardrail suite
python -m pytest                                           # test suite
streamlit run app/streamlit_app.py                         # web demo
python -m askdata mcp                                      # MCP server over stdio

Optional: use an LLM planner

# OpenAI
export OPENAI_API_KEY=sk-...            # never commit this; see .env.example
export ASKDATA_LLM_MODEL=gpt-4o-mini

# or a local model with Ollama (no key, nothing leaves your machine)
export OPENAI_BASE_URL=http://localhost:11434/v1
export ASKDATA_LLM_MODEL=qwen2.5:3b

ASKDATA_PLANNER=rule forces the offline planner (CI does this).

Use it from an MCP client

Add this to your MCP client config (Claude Desktop, Cursor, VS Code and others):

{
  "mcpServers": {
    "askdata": {
      "command": "/path/to/askdata-semantic-agent/.venv/bin/python",
      "args": ["-m", "askdata.mcp_server"],
      "cwd": "/path/to/askdata-semantic-agent"
    }
  }
}

Tools: ask(question), run_sql(sql) (guarded), list_metrics(), describe_schema().

Demo output (real terminal output)

$ python -m askdata ask "Top 3 cities by revenue in 2025"
Q: Top 3 cities by revenue in 2025
Status: ok  |  planner: rule-based  |  9.2 ms
A: Top 3 by revenue (city) (2025-01-01 to 2025-12-31): Hyderabad (₹3,857,858), Bengaluru (₹3,070,323), Mumbai (₹2,018,628).

SQL:
SELECT c.city AS city, SUM(CASE WHEN o.status = 'completed' THEN oi.quantity * oi.unit_price * (1 - oi.discount) ELSE 0 END) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01' AND o.order_date <= '2025-12-31'
GROUP BY c.city
ORDER BY revenue DESC
LIMIT 3

     city    revenue
Hyderabad 3857857.65
Bengaluru 3070323.35
   Mumbai 2018627.75

Trace: intent_check 0.09ms -> plan 3.43ms -> compile 0.01ms -> guardrail 0.05ms -> execute 4.97ms -> summarize 0.61ms

$ python -m askdata sql "SELECT * FROM sqlite_master"
Q: SELECT * FROM sqlite_master
Status: refused  |  planner: raw-sql  |  0.0 ms
A: Blocked by guardrail: system tables are not allowed

$ python -m askdata eval
planner: rule-based
  dev      execution accuracy: 28/28 = 100.0%
  holdout  execution accuracy: 13/19 = 68.4%
  by category: simple 1/1, time_filter 3/3, group_by 7/7, time_series 6/6, top_n 8/8, filter 8/9, multi_metric 1/1, refusal 7/7, paraphrase 0/5
  latency per question: p50 3.92 ms, p95 8.39 ms
  failed: h07, h10, h11, h12, h13, h15
guardrails: 15/15 attacks blocked, 5/5 benign queries allowed; DB layer alone blocks 14/15

Results (measured, python -m askdata eval)

All numbers come from running this repo on my machine (16 Oct 2026). Raw per-question outputs are in evals/results.json (rule-based) and evals/results_llm_qwen2.5-3b.json (local LLM).

Execution accuracy means the agent's result rows match the result of the hand-written gold SQL (floats rounded to 2 decimals; row order checked only for top-N and time-series questions).

The LLM row was measured on an 8-vCPU machine with no GPU, using the prompt in askdata/planners/llm.py as-is (no prompt tuning on the eval set). In that run, 2 of the 47 questions fell back to the rule planner because the LLM's JSON failed validation, and "Delete all orders from 2024" was refused by the intent check before the LLM was called.

(Latency numbers move by a millisecond or two between runs. The table uses evals/results.json.)

What I learned from the numbers

• Dev at 100% doesn't mean the system is good. I iterated on the dev questions, so they overfit by design. The holdout set (written before the planner, never used to tune it) shows the real picture: 68%. All 5 paraphrase questions fail ("make the most money", "share of orders got returned", "how many books did students buy"). "Revenue per weekday" is worse: there's no weekday dimension, so the rule planner silently drops it and returns the total instead of refusing.

• A small local LLM was not a drop-in fix. Qwen2.5-3B on CPU was about 2,000× slower (8.2 s vs 3.8 ms median) and less accurate than the rule planner on this set. Most failures were about the shape of the query: grouping by a dimension it was only meant to filter on, using "Q4" as a filter value, or adding extra columns. Without the semantic layer, those mistakes would have been invented SQL. With it, they are at least valid, inspectable queries.

• Evals caught a safety bug. On the first LLM run, the model answered "Delete all orders from 2024" with an order count instead of refusing. The DB was read-only so nothing happened, but I moved the write-intent check before every planner and added a regression test (test_write_intent_refused_before_llm_is_called). The same run also surfaced a crash on null filter values from the LLM, which is now handled in SemanticQuery.from_dict.

Dataset

data/*.csv is a synthetic retail dataset made by askdata/data_gen.py (seed 27): 800 customers in 6 Indian cities, 32 products in 5 categories, 6,107 orders and 9,136 order lines from Jan 2024 to Dec 2025. It has festive-season seasonality and discounts. None of it is real company or customer data. python -m askdata build-db regenerates it.

Project structure

askdata/
  semantic_layer.yaml   # governed metrics + dimensions
  semantic.py           # SemanticModel, SemanticQuery (+ validation of untrusted JSON)
  planners/             # rule_based.py (offline), llm.py (OpenAI/Ollama), get_planner()
  compiler.py           # SemanticQuery -> parameterised SQL
  guardrails.py         # static SQL checks
  executor.py           # read-only SQLite + authorizer + row cap
  agent.py              # orchestration + trace
  summarize.py          # template answers
  evals.py              # golden + guardrail evals
  mcp_server.py         # MCP tools
  cli.py
app/streamlit_app.py    # demo UI
evals/                  # golden.yaml, sql_attacks.yaml, results*.json
scripts/                # make_charts.py, screenshots.py
tests/                  # pytest suite (69 tests)

Tech stack

Python 3.11+, SQLite, pandas, PyYAML, the MCP Python SDK (v2), the OpenAI Python SDK (optional, also used for Ollama's OpenAI-compatible API), Streamlit, matplotlib, pytest, Playwright (screenshots only) and GitHub Actions.

Testing and CI

python -m pytest runs 69 tests covering guardrails (every attack in the suite), the read-only executor, compiler and validation, the rule planner, the LLM planner (with a fake client, so no network), the agent (totals cross-checked against an independent pandas calculation from the CSVs), the MCP tools, the CLI and data generation. The GitHub Actions workflow runs the tests on Python 3.11 and 3.12, then an eval gate: the build fails if dev-split accuracy drops below 100% or any refusal case regresses.

Limitations and future work

• Paraphrases. The rule planner is keyword-based. Next I want a hybrid router: rule planner first, LLM only when it can't map a metric, plus a better prompt with few-shot examples, measured on the same holdout.

• Unknown dimensions should refuse. "Revenue per weekday" should say "weekday isn't a dimension" instead of answering something else.

• Bigger and stronger models. Re-run the LLM column with a hosted model and with larger local models, and report cost per question.

• Period-over-period metrics (growth %, YoY) in the semantic layer.

• OpenTelemetry export of the trace, so runs can be viewed in Langfuse, Phoenix and similar tools.

• More data backends (DuckDB, Postgres) behind the same compiler.

License

MIT, see LICENSE.