Project 4: Text-to-SQL Analytics Assistant

Every company wants "chat with our data". You'll build an assistant that turns business questions into safe, validated SQL, runs them, and explains the result with a chart. You'll also measure accuracy on a test set.

What you'll build

• Ask "Top 5 cities by revenue last quarter?" and get SQL, a table, a chart and a one-paragraph insight.
• Safety: read-only connection, SELECT-only validation, row limits, schema allow-list.
• An evaluation on 40 questions with execution accuracy (does the result match the gold query?).

Architecture

Question → schema retriever (relevant tables + column descriptions + few-shot examples)
        → LLM generates SQL (structured output) → validator (sqlglot: SELECT-only, allowed tables, LIMIT)
        → execute (read-only) → on error: send error back to LLM once (self-correct)
        → result → chart + LLM explanation

Tech stack

SQLite or Postgres (a sample sales DB such as Chinook or Northwind), SQLAlchemy, sqlglot, an LLM, pandas, Plotly, Streamlit.

Step by step

1. Pick a dataset and write a semantic layer: table and column descriptions plus 10 example question→SQL pairs.
2. Schema retrieval: embed the table descriptions and include only the relevant tables in the prompt.
3. Generate SQL with instructions (dialect, LIMIT 200, no DML).
4. Validate with sqlglot.parse_one: reject anything that isn't a SELECT, and check the table names.
5. Execute on a read-only user, with a timeout.
6. Self-correct: on an error, re-prompt once with the error message.
7. Explain: the LLM summarises the result table (numbers come from the data, not from the LLM).
8. Evaluate execution accuracy on 40 questions; log the failures by type.

Starter code

import sqlite3, sqlglot, pandas as pd
from sqlglot import exp
ALLOWED = {"invoices", "customers", "invoice_items", "tracks"}
def safe(sql):
    tree = sqlglot.parse_one(sql, read="sqlite")
    if not isinstance(tree, exp.Select): raise ValueError("Only SELECT allowed")
    if {t.name for t in tree.find_all(exp.Table)} - ALLOWED: raise ValueError("Table not allowed")
    if not tree.args.get("limit"): tree = tree.limit(200)
    return tree.sql(dialect="sqlite")
def run(question):
    sql = llm_sql(question, schema=retrieve_schema(question), examples=EXAMPLES)
    try: q = safe(sql); df = pd.read_sql(q, sqlite3.connect("file:chinook.db?mode=ro", uri=True))
    except Exception as e:
        q = safe(llm_sql(question, schema=retrieve_schema(question), examples=EXAMPLES, error=str(e)))
        df = pd.read_sql(q, sqlite3.connect("file:chinook.db?mode=ro", uri=True))
    return q, df

Deploy it

A Streamlit app on Hugging Face Spaces with the SQLite file bundled, showing the generated SQL in an expander for transparency.

Put it on your resume

• Built a text-to-SQL assistant with schema retrieval, sqlglot validation (SELECT-only, table allow-list, row limits) and self-correction, reaching 78% execution accuracy on a 40-question benchmark.
• Added an auto-generated chart and LLM insight summary; deployed on Streamlit with a read-only database role.

Interview questions you'll get

• How do you stop the LLM from running DELETE or accessing restricted tables?
• How do you handle large schemas with hundreds of tables?
• How do you evaluate text-to-SQL? Exact match vs execution accuracy?
• What about ambiguous questions ("best customers")?

Stretch goals

• A metrics layer (dbt semantic layer) so "revenue" always means one definition.
• Clarifying questions when the request is ambiguous.
• Row-level security per user.