One of the most common questions I get in data engineering is: "How do we let business teams get insights without writing SQL?"
The answer I landed on is a natural-language-to-SQL chatbot. Users type a question in English, and the app returns an answer (and the SQL that produced it). It's small in scope but huge in leverage.
Architecture
Streamlit UI
↓ question
Gemini (schema-aware prompt)
↓ generated SQL
BigQuery (executes safely)
↓ result + SQL
answer rendered in UIThe key design decisions:
- Schema grounding — the prompt includes table names, column names, and types. The model can't invent columns that don't exist.
- Read-only execution — the BigQuery connection uses a restricted service account. Write operations are simply not possible.
- Explainability — the user always sees the generated SQL, so they can verify and learn.
The prompt matters more than the model
The secret isn't the model — it's how you frame the context:
prompt = f"""
You are a BigQuery expert. Generate a SQL query for the question below.
Schema:
{table_schema}
Rules:
- Only use columns from the schema above.
- Use DATE_TRUNC for date-based aggregations.
- Return a concise answer in plain English after the SQL.
Question: {question}
"""Safety guardrails
Text-to-SQL in production needs guardrails:
- Validate every generated query before execution
- Limit result size (
LIMIT 1000) - Log every query for audit
- Sandbox execution against a read-only role
What I learned
- Schema-aware prompting removes 80% of hallucinated columns
- Few-shot examples beat long instructions for style consistency
- Showing the SQL builds user trust — analysts actually use it as a learning tool
The full project is small, but it's a perfect template for any "ask your data" product.