Course Content
Scenario-Based AI Engineering Questions
26 sections · 146 lessons
Users upload Excel files with 50+ columns and ask 'What was Q3 revenue for region X?' Vector search returns junk. How do you build RAG over structured tabular data?
What you need to know
When you embed spreadsheet rows as text chunks, a row like "North, 2025-Q3, 41,20,000, Electronics" becomes a vector that looks like every other row. Similarity search cannot add up 3,000 rows or filter by quarter. It returns a few rows that look similar and the model guesses a total.
Route by data type
- Ingest — load each sheet into a table (DuckDB or Postgres), infer column types, normalise names, and keep the original file for lineage.
- Describe — for each table, write and embed a description: name, columns with types, a few sample values, and a plain-English summary.
- Retrieve schema — for a question, find the 1 to 3 most relevant table descriptions.
- Generate and run SQL — the model writes a query; the application runs it read-only.
- Explain — the model turns the returned numbers into an answer and shows the SQL.
1import duckdb2con = duckdb.connect()3con.execute("CREATE TABLE sales AS SELECT * FROM read_xlsx('q3_sales.xlsx')") # DuckDB excel extension45sql = llm.write_sql(question, schema=describe_tables(["sales"]))6assert is_single_select(sql) # parse and reject anything else7rows = con.execute(f"SELECT * FROM ({sql}) LIMIT 1000").fetchall()8answer = llm.explain(question, sql, rows)For "What was Q3 revenue for the North region?" the model writes SELECT SUM(revenue) FROM sales WHERE region = 'North' AND quarter = 'Q3', and the database returns the exact figure.
Guardrails matter more than the prompt
| Guardrail | Why |
|---|---|
| Read-only database role | A bad query cannot change data |
| Parse the SQL; allow only a single SELECT | Blocks DROP, multiple statements, injection |
| Row limit and statement timeout | A runaway join cannot take down the database |
| Show the executed SQL with the answer | Users and analysts can check the logic |
| Column value hints (distinct regions, date formats) | The model writes 'North', not 'north region' |
Messy spreadsheets are the real work
Real uploads have merged header cells, two-row headers, totals rows mixed with data, and notes in random cells. That is an ingestion problem. Detect it at upload, show the user a preview of the parsed table, and ask them to confirm, rather than silently mis-parsing and giving confident wrong totals.
Where vector search still helps
Keep text RAG for the free-text parts: notes columns, the report commentary, policy documents. A router decides whether a question is about numbers (SQL path) or about text (RAG path), and some questions use both.
A real-life example
Scenario (illustrative numbers). A consumer-goods company's sales team uploads monthly Excel reports with 55 columns: region, state, distributor, SKU, units, revenue and more. Their chatbot, which embeds rows as text, answers "Q3 revenue for West" with a number that is off by 40% because it summed the five rows it happened to retrieve.
The team builds the text-to-SQL path. On 100 labelled questions with hand-written reference queries, execution accuracy is 91% for single-table questions and 72% for questions needing a join with the distributor sheet. They launch single-table questions first, show the SQL under every answer, and add join examples to the prompt before expanding.
Follow-up questions to expect
- "How do you measure it?" — Execution accuracy: does the generated SQL return the same result as a reference query on a labelled set?
- "What about very wide tables with hundreds of columns?" — Retrieve relevant columns, not just tables, and send only those in the schema prompt.
- "Could the model just read a small table directly?" — For a table of a few dozen rows, yes; for anything that needs counting or summing over many rows, let the database do it.