Scenario-Based AI Engineering Questions

Course Content

Scenario-Based AI Engineering Questions

26 sections · 146 lessons

Your LLM-generated SQL works 95% of the time. The remaining 5% silently produces incorrect analytics dashboards. How do you validate and safely execute AI-generated database queries?

How one order becomes three in a SUM10012,500shoes10012,500socks10012,500beltorder_idorder totalitemJoining orders to order_items repeats the total per item, so SUM gives 7,500 for a 2,500 order.
The query runs, the chart renders, and revenue is wrong — which is why grain checks look at the result, not the syntax.

What you need to know

Why the 5% is silent

The common errors do not fail: they return numbers that look fine.

ErrorWhat happens
Join fan-outJoining orders to order items repeats each order's total per item, so SUM(total) is too big
Wrong grainA "per region" query returns one row per store
Missing filterCancelled or test orders are counted
Wrong definition"Revenue" computed before discounts instead of after
Wrong date logicA time zone or "last month" boundary shifts totals

Validate before running

Python
import sqlglotfrom sqlglot import expALLOWED = {"orders_certified", "customers_certified", "regions"}def validate(sql: str) -> str:    tree = sqlglot.parse_one(sql, read="postgres")    if not isinstance(tree, exp.Select):        raise ValueError("only SELECT is allowed")    tables = {t.name for t in tree.find_all(exp.Table)}    if not tables <= ALLOWED:        raise ValueError(f"unapproved tables: {tables - ALLOWED}")    for join in tree.find_all(exp.Join):        if not join.args.get("on") and not join.args.get("using"):            raise ValueError("join without a condition (cartesian product)")    where = tree.args.get("where")    if where is None or "tenant_id" not in {c.name for c in where.find_all(exp.Column)}:        raise ValueError("tenant filter missing")    return tree.limit(10000).sql(dialect="postgres")

The parser turns SQL into a tree, so checks are exact rather than regex guesses: only SELECT, only certified tables, no join without a condition, a required tenant filter, and a row limit added.

After running

  1. Execute safely — read-only role, replica, statement timeout, row cap.
  2. Check the result — row count against expectation, null rates, and grain: a per-region query must not return more rows than there are regions.
  3. Cross-check high-stakes queries — generate twice from different phrasings; if results differ, send to a human.
  4. Explain — show the SQL and its assumptions in plain English ("excludes cancelled orders; dates in IST").
  5. Promote for dashboards — an analyst reviews the query once, and it is saved and versioned.

A real-life example

Scenario, numbers made up. An e-commerce company's "ask your data" tool builds a monthly revenue dashboard. For three months, revenue is overstated by 18%, and a planning meeting uses the figure. The query joined orders to order_items and summed the order total, repeating it once per item.

The fix: the model now queries orders_certified, where net_revenue is defined once, and join paths come from the semantic layer. Parser validation blocks uncertified tables, and a grain check flags any result with more rows than the expected groups. Dashboard queries are saved after analyst review and are no longer regenerated on refresh. In an eval of 300 questions with known answers, silent wrong results fall from 5% to under 1%; most of the remainder now fail loudly instead.

Follow-up questions to expect

  • "How do you evaluate text-to-SQL?" — Execution accuracy: run the generated and the reference query on the same data and compare result sets, over a set of real business questions.
  • "Why not just give the model the full schema?" — Hundreds of tables with similar names invite wrong joins. A small set of certified views with descriptions is easier to use correctly.
  • "What about write queries?" — Not from this tool. Analytics generation is read-only by role, not just by validation.