NL→SQL Analytics Engine

Ask a business question in plain English → get safe, read-only SQL, a table, and a chart. A self-serve analytics layer over a 16.5K-row e-commerce warehouse, built with Python + SQLite, a deterministic schema-aware parser (no black-box LLM), a validated read-only safety layer, a CLI, a Streamlit app, and now an interactive BI dashboard.

▶ Live interactive dashboard: https://harshithbandari.github.io/nl-sql-analytics-engine/

Every number on the dashboard is produced by running real English questions through the engine and executing the generated SQL against the warehouse — nothing is hand-typed.

question (English)  ->  intent (metric + dimension + filters + order/limit)
                    ->  generated SQL  ->  safety guardrails  ->  result + chart

What the dashboard shows

• Overview — $6.2M GMV across 5,471 orders / 1,193 customers; revenue by category & channel, monthly trend, AOV by channel.
• Ask in English — click any business question and watch the engine parse it, emit the generated SQL, and chart the result live.
• Segments & Geography — revenue by customer segment and country, orders by status, units by category.
• Under the Hood — the safety layer rejecting DROP / DELETE / stacked ; statements, automatic LIMIT injection, and the passing unit-test suite.

Why I built the parser myself (no LLM)

The natural-language layer is a deterministic, schema-aware parser, not an API call to an LLM. Every query it produces is transparent and unit-tested, so in an interview I can explain exactly why any given SQL was generated. There's a clean seam to plug an LLM backend in later — but the graded, running version is the one I wrote and can defend line by line.

Key results

• Parses 5 metrics (revenue, orders, customers, units, AOV) across 8 dimensions (category, channel, segment, country, status, product, month, year) plus filters and top-/bottom-N ranking.
• Generates correct multi-table SQL with only the JOINs each question needs (order_items → orders → customers/products).
• Safety layer blocks every unsafe query — DROP, DELETE, UPDATE, INSERT, stacked ; DROP TABLE … — before it reaches the DB, and auto-injects a LIMIT on unbounded queries.
• Unit tests cover intent detection, SQL generation, JOIN resolution and the guardrails; all passing.
• Demo warehouse: 1,200 customers, 118 products, 5,471 orders, 16,532 order items (~$6.2M revenue), Nov/Dec seasonality baked in.

How to run

pip install -r requirements.txt
python src/generate_data.py            # builds ecommerce.db (~16.5K rows)
python ask.py "top 10 products by revenue"   # one-off question
python ask.py                          # interactive REPL
streamlit run app.py                   # interactive web app
python -m pytest -q                    # tests
python src/build_dashboard.py          # regenerate data.js for the dashboard

How it works

Stage	File	What it does
Schema	src/nlsql/schema.py	Tables, metrics, dimensions, synonyms, JOIN graph. Repoint at another DB by editing this file.
Parse	src/nlsql/engine.py	Detects metric, dimension, filters, ordering, limit; resolves the minimal JOIN set; renders SQL.
Explain	src/nlsql/explain.py	Renders the intent back into plain English.
Safety	src/nlsql/guardrails.py	Read-only validation + LIMIT injection.
Interfaces	ask.py, app.py, index.html	CLI/REPL, Streamlit app, and the interactive dashboard.
Dashboard data	src/build_dashboard.py	Runs the questions through the engine and writes data.js.
Tests	tests/test_engine.py	Deterministic tests over parsing, JOINs and guardrails.

Tech stack

Python, SQL (SQLite), pandas, ECharts, Streamlit, pytest.

The database is synthetic but modeled on the structure and scale of the public Online Retail II (UCI) and Olist e-commerce datasets, so the schema, the questions, and the generated SQL transfer directly to a real warehouse.