# Building a text-to-SQL agent on a client's data warehouse: write the definitions first, the prompt second

> The SQL runs without error but returns a number the finance team does not recognise. This is the failure you will see most often. This guide shows how to catch it before the client does.

Original: https://fdetimes.net/en/guides/text-to-sql-agent-semantic-layer-first/

Vercel removed most of the tools from its text-to-SQL agent, and the agent still worked well. The reason was neither the prompt nor the model. According to accounts of the case, the approach worked because Vercel's semantic layer was already well documented.

For a forward deployed engineer, that detail says a lot about where your time will go when a client asks you to "build a chatbot for querying our data". Most of it will not go into the agent code. It will go into writing down, clearly, how every number in the client's data warehouse is calculated.

This guide builds a small agent in exactly that order: definitions first, then context selection, SQL generation and, finally, scoring. The code snippets are pseudocode or ad hoc configuration, trimmed for brevity. Fit them into whatever framework and model you use.

## What do you need before you start?

Three things: SQL solid enough to write correct queries yourself, a sample database that runs on your laptop, and access to any LLM. If you have never built an agent, take the short course *Building Your Own Database Agent*, made by DeepLearning.AI with Microsoft, first. It is a beginner course of about 1 hour 8 minutes that teaches you to build an agent translating natural language into SQL.

The course's stated goal is to use natural language to work with tabular data and SQL databases, making analysis faster and more accessible. The course gives you an agent that runs. The rest of this guide helps that agent answer correctly.

For a running example, imagine the client is a retail chain with two tables:

```sql
-- Hypothetical schema, simplified
orders(order_id, customer_id, amount, status, created_at)
refunds(refund_id, order_id, refund_amount, refunded_at)
```

## Step 1: Ask what "revenue" means before writing the prompt

The first question the client types will almost certainly be "What was revenue in September?". An untrained agent will generate this:

```sql
SELECT SUM(amount)
FROM orders
WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01';
```

It runs without error. Now put numbers on it: September had 1,000 orders totalling 500 million dong. Of these, 60 orders worth 30 million were cancelled, and the remaining orders saw refunds totalling 10 million.

The agent reports 500 million. The finance team, which counts revenue after cancellations and refunds, reports 500 − 30 − 10 = 460 million. One question in, the client has lost trust.

This is precisely the kind of failure described in an article on context engineering for data agents: the error is semantic, not syntactic. The author works for a company that sells a semantic layer product, so verify the claim on your own data. But the revenue example above shows the failure is entirely plausible.

So the first job is to sit down with whoever owns the client's numbers and write the definitions down. Below is a minimal definitions file in an ad hoc format that follows no particular tool's standard. (The keys are Vietnamese: `revenue` is revenue, `description` the description, `tables` the tables, `notes` the notes.)

```yaml
# semantic_layer.yaml (custom format, simplified)
metrics:
  revenue:
    description: "Revenue from non-cancelled orders in the period, minus refunds on those same orders"
    sql_file: revenue.sql
    tables: [orders, refunds]
    notes: "Period is based on the order's created_at. Sum orders and refunds in two separate queries"
```

Here the description reads: revenue from non-cancelled orders in the period, minus refunds on those same orders. The note says the period is based on the order's `created_at`, and that `orders` and `refunds` must be summed in two separate queries. The canonical SQL for the metric lives in its own file:

```sql
-- revenue.sql (simplified)
(SELECT COALESCE(SUM(amount), 0) FROM orders
  WHERE status <> 'cancelled'
    AND created_at >= :from_date AND created_at < :to_date)
-
(SELECT COALESCE(SUM(r.refund_amount), 0)
   FROM refunds r JOIN orders o ON r.order_id = o.order_id
  WHERE o.status <> 'cancelled'
    AND o.created_at >= :from_date AND o.created_at < :to_date)
```

Note the `notes` line. If you JOIN `orders` to `refunds` and sum `amount` in a single query, any order refunded twice has its `amount` counted twice. The SQL still runs and the number is still wrong, so the rule has to be written straight into the definition.

Once this step is done, read each `description` line aloud to the person who owns the numbers on the client side. If they change even one word, the definition is not finished.

## Step 2: Give the model only the tables it needs

A real client warehouse can hold hundreds of tables. The context engineering article recommends selecting only the few relevant tables and columns for each question rather than passing the whole schema. The simplest route goes through the semantic layer: whichever metric the question matches, take exactly the tables that metric declares.

```python
# Pseudocode, simplified
def build_context(question, semantic_layer):
    metrics = match_metrics(question, semantic_layer)   # keyword or embedding matching
    tables = {t for m in metrics for t in m["tables"]}
    return {
        "metrics": metrics,                 # definitions + canonical SQL
        "schema": describe(tables),         # only columns of the relevant tables
    }
```

To check it, print the context for 10 questions. If a revenue question pulls in the `inventory` table, your matching function is too broad. If the context is missing `refunds`, the fault lies in the definitions file, not the code.

## Step 3: Generate SQL and run it with read-only access

The prompt is now much shorter than you might have expected. You pass in the definitions, the trimmed schema and the question, and tell the model to prefer the metric's canonical SQL where one exists.

```python
# Pseudocode, simplified
ctx = build_context(question, semantic_layer)
sql = llm.generate(PROMPT.format(**ctx, question=question))
result = run_readonly(sql)   # DB account has SELECT permission only
return {"sql": sql, "result": result, "metrics_used": ctx["metrics"]}
```

Two things belong in the very first version. The agent's database account gets read access only. Every answer comes with the SQL and the name of the metric used, so the client's people can check it themselves.

An author writing for Forbes Tech Council warns of governance risks in AI-driven analytics: transparency, fairness and accountability. Returning the SQL alongside the answer is the cheapest way to meet the transparency requirement.

## Step 4: Score with questions that contain traps

A test suite that only checks whether the SQL runs will give full marks to the 500 million answer above. So every test question must come with an answer: a number confirmed by the client's own people.

| Question | Semantic trap | Checked against |
|---|---|---|
| Revenue in September? | Cancelled orders, refunds | The figure in the financial report |
| How many customers bought in Q3? | Counting orders instead of customers | COUNT(DISTINCT customer_id) written by an analyst |
| Cancellation rate last week? | Which orders form the denominator | The definition in semantic_layer.yaml |

When an answer is wrong, do not rush to fix the prompt. Ask whether a definition is missing. Wobby, which builds analytics agents for production environments, argues that the process of building and steadily refining the semantic layer is where the value is created.

Every error you catch should become a `notes` line and a new question in the test set. The note does not guarantee the agent will never make the mistake again, but the test question will tell you immediately if it does.

**Key point:** When the agent answers wrongly, fix the definition before you fix the prompt.

## Step 5: Keep the agent thin

When errors appear, the natural reflex is to add tools: one to inspect the schema, one to guess joins, one to repair SQL. The Vercel case went the other way. It removed most of its tools, and the lesson drawn was that architectures built to compensate for a model's current weaknesses can become a burden as models improve.

So put your effort into the semantic layer, the part that will keep its value across model generations, and keep the scaffolding around the model to a minimum. Whenever you are about to add a tool, ask whether a better line in a definition would solve the problem instead.

## Which traps should you plant yourself?

The most common mistake is to drop the agent into the warehouse as it stands and expect it to work things out. Wobby says plainly that doing this with poorly organised data and vague business definitions produces wrong results. The traps below are hypothetical examples to plant in your sample database to see whether the agent falls in.

The time zone trap: suppose `created_at` is stored in UTC while the client reads reports in Vietnam time (UTC+7). An order placed at 3am on 1 October Vietnam time is stored as 8pm on 30 September UTC, so it lands in September's revenue.

Insert a few such orders, then ask the client's numbers owner which month they belong to and record the answer in `notes`.

The cross-period refund trap: an order created on 28 September is refunded on 5 October. Under the definition in Step 1, the refund is deducted from September, which means September's revenue today and next week may differ. If the agent filters on `refunded_at`, it will deduct the amount from October, and neither figure will match the report.

The row-multiplying JOIN trap: add two refund records for the same order and see whether the agent JOINs and then sums `amount`. The last trap is stuffing the whole schema into the prompt because the context window has room, then scoring on whether the SQL runs rather than on whether the number is right.

Suggested exercise: plant all three data traps above, write three matching questions with hand-calculated answers, and run the agent before and after adding the `notes` notes. The gap between the two runs is what you will present to the client.

## What does this skill look like on a client site?

You will sit with the person responsible for the numbers to agree the 5 to 10 most important metrics and request read access to the warehouse.

Devfi, in a commentary on AI-driven data analysis, names three barriers to adoption: cultural resistance, data privacy concerns and the limits of technical infrastructure.

A read-only account and answers that include the SQL go some way towards addressing the data concerns, and a test set whose answers are approved by the client's own people gives them a way to verify the results themselves. Cultural resistance and infrastructure limits are beyond the reach of any technique above, and you should tell the client so from the start.

When reading job descriptions for FDE or data-focused solutions engineer roles, look for phrases such as "semantic layer", "metrics definition" and "evaluation". On your CV, instead of writing "built a text-to-SQL chatbot", state how many metric definitions you agreed with the business side, how many questions your test set contained, and how far the correct-answer rate rose.

The agent you build this week will be replaced by a better model. The revenue definition the client signed off on will still be in use when that happens.

**Try this week:**

- Pick a sample database and write a definitions file for 3 metrics, such as revenue, active customers and cancellation rate, each with its canonical SQL
- Write 10 test questions with correct answers, at least 5 of them with built-in semantic traps such as cancelled orders, refunds or time zones
- Take DeepLearning.AI's Building Your Own Database Agent course (about 1 hour 8 minutes) to get a first working agent, then run your test set against it

## Sources

- [Building Your Own Database Agent](https://www.deeplearning.ai/short-courses/building-your-own-database-agent/)

- [How AI Will Transform Data Analysis in 2025](https://www.devfi.com/ai-transform-data-analysis-2025/)

- [How AI Has Changed The World Of Analytics And Data Science](https://www.forbes.com/councils/forbestechcouncil/2025/01/28/how-ai-has-changed-the-world-of-analytics-and-data-science/)

- [Building Production Analytics Agents with Semantic Layer Integration](https://zenml.io/llmops-database/building-production-analytics-agents-with-semantic-layer-integration)

- [Simplifying Text-to-SQL Agents by Removing 80% of Tools](https://www.zenml.io/llmops-database/simplifying-text-to-sql-agents-by-removing-80-of-tools)

- [Context Engineering for Data Agents](https://datalakehousehub.com/blog/context-engineering-for-data-agents)