AI system design interview: design text-to-SQL for business users
Give the model a curated description of the schema and metric definitions (a semantic layer), retrieve the relevant tables and example queries for each question, generate SQL, validate it (parses, allowlisted tables only, no writes, row limits), run it with the user's own read-only permissions, and render the numbers from the result, not from the model. Most errors are wrong joins and wrong metric definitions, so define metrics once and show users the SQL.
Question
Let sales leaders ask questions like "what were Alex's sales in the last 6 months?" against the company database.
Clarify
- Which databases and how many tables.
- Who asks, and which data each person may see.
- How metrics like "sales" are defined: booked, invoiced, net of returns?
- Whether answers need charts, tables or a sentence.
Architecture
| Stage | Component |
|---|---|
| Semantic layer | Table meanings, column descriptions, metric definitions, example queries |
| Retrieval | Find relevant tables and examples for the question |
| Generation | SQL from the question plus retrieved context |
| Validation | Parse, allowlisted tables, no writes, row and time limits |
| Execution | Read-only, under the user's own permissions |
| Answer | Table or chart from the result, short explanation using exact values |
| Clarification | Ask when the question is ambiguous ("which Alex?") |
Deep dive: correctness
"Sales" can mean three different numbers depending on the team. If the model decides, two people asking the same question get different answers. Define each metric once in the semantic layer, make the model use those definitions, and show the generated SQL so analysts can check it.
Failure modes
| Failure | Mitigation |
|---|---|
| Wrong metric | Metric definitions in the semantic layer |
| Wrong date range ("last 6 months") | Resolve dates in code, show the range used |
| Ambiguous names | Ask a clarifying question |
| Slow or expensive queries | Timeouts, row limits, query cost checks |
| Model paraphrases numbers wrongly | Render numbers from the result set |
| Data the user shouldn't see | Execute with the user's permissions, not a service account |
Evals
Build a set of real questions with verified SQL or verified results, and score on result match, not SQL text match, since different SQL can give the same correct result. Break failures down by step (pick tables, write SQL, execute, explain) with a transition failure matrix.
What interviewers listen for
Read-only execution, exact values from data, and a semantic layer for metric definitions.
From the floor
Across AI Engineer HQ builds, the rule we apply is that exact values (amounts, dates, counts) come from code and data, and the model's job is to understand the question and explain the result, which is the whole design of text-to-SQL in one sentence.
Related
- Exact values pattern
- Access control for RAG and agents
- Transition failure matrix
- Data quality for retrieval