Your AI Analyst

The founder wants an answer, and you're on holiday. What if anyone could just ask the data a question in plain English? Language models can now write SQL. It's powerful, and if you're not careful, it's dangerous. Let's build one, with the right guard rails.

• The founder has a question
• You're on holiday
• Ask in plain English

Questions into SQL

We give the model the table's columns, and tell it to return one SQL query. Temperature zero keeps it steady. Before running anything, we assert that it's a select statement, so that it can't change data. Then we run the query, and print the SQL too, so that it can be checked. Run it. It wrote a correct query, and gave us three cities. Look closely, and you'll see that it even handled both product and category. Now anybody can ask. But the model never sees the data. It only writes the query, and the database does the real work, which is why the numbers can be trusted.

import duckdb
from google import genai
from google.genai import types

client = genai.Client()
schema = "Table 'sales.csv' columns: date, store_id, product, units, category, discount, revenue, profit, city, region"

def ask_data(question):
    sql = client.models.generate_content(
        model="gemini-flash-latest",
        config=types.GenerateContentConfig(temperature=0, system_instruction=(
            f"{schema}. Write one DuckDB SELECT query. Return only SQL, no fences.")),
        contents=question).text.strip().removeprefix("```sql").removesuffix("```").strip()
    assert sql.lower().startswith("select")
    print(sql)
    return duckdb.sql(sql).df()

print(ask_data("Which three cities made the most profit from Tea?").round(0).to_string(index=False))

SELECT city
FROM 'sales.csv'
WHERE product = 'Tea'
GROUP BY city
ORDER BY SUM(profit) DESC
LIMIT 3
  city
 Osaka
 Milan
Bergen

Cleaning messy text

Language models are also great for cleaning data that rules can't handle. Here are three messy order notes, with slang and typos. We ask for a clean product name and size, using a schema. Run it. Lg latte sixteen o z becomes a latte, at four hundred and seventy three millilitres. It even converted the units. And expresso d b l became a double espresso. Doing that with rules would take days of work. But check a sample, because it can guess. The espresso line had no size at all, so the sixty is its own assumption. That's something that you need to know about.

from pydantic import BaseModel
from google import genai
from google.genai import types

class Clean(BaseModel):
    product: str
    size_ml: int | None

client = genai.Client()
for raw in ["lg latte 16oz!!", "EXPRESSO dbl", "flat wht (reg) 12 oz"]:
    r = client.models.generate_content(
        model="gemini-flash-latest",
        config=types.GenerateContentConfig(temperature=0, response_mime_type="application/json",
                                           response_schema=Clean),
        contents=f"Standardise this cafe order into a product name and size in ml: {raw}")
    print(f"{raw:<22} {r.parsed.product:<12} {r.parsed.size_ml}")

lg latte 16oz!!        Latte        473
EXPRESSO dbl           Double Espresso 60
flat wht (reg) 12 oz   Flat White   355

Rules for AI in analysis

Follow these rules. Never send customer data to a tool without permission. Use read only database accounts, so that a bad query can't do damage. Show the SQL or code, so that people can check it. Test it on questions with known answers. And remember that a wrong answer delivered confidently is worse than no answer. You're still the one who's responsible for the numbers.

Summary