Text-to-SQL: Schemas & Prompts

Text-to-SQL means asking AI to turn ordinary language into SQL, and it gets better when the prompt stops being a wish and starts being a small spec. The most useful context is not clever wording — it is the schema, the result grain, the metric definition, and one or two examples of your team's query style.

The model cannot infer your database from a report title. "Revenue by channel" might mean order total, net of refunds, paid invoices only, subscription MRR, purchase events, or a finance-owned ledger. The difference is not grammar. It is business meaning.

The schema is the prompt

A vague prompt asks the model to guess:

The better prompt gives DDL, short for data definition language, which is the SQL shape of tables, columns, keys, and constraints. You do not need to paste every migration your company has ever run. You need the smallest honest schema slice for the question: the tables involved, the columns the model may use, the join keys, and the constraints that change meaning. If the metric depends on a nullable column, say so. If an enum-like text column has known values such as signup and purchase, list them. If the output row should be one row per customer, day, branch, or acquisition channel, state that grain directly.

Bad prompt:
Show revenue by acquisition channel.
 
Better prompt:
Use PostgreSQL.
 
Minimal schema:
cafe_users(user_id PRIMARY KEY, acquisition_channel, home_branch, loyalty_tier)
cafe_app_events(event_id PRIMARY KEY, user_id REFERENCES cafe_users,
                event_ts, event_type, branch, order_id, purchase_amount)
 
Metric definition:
net_revenue = sum(cafe_app_events.purchase_amount)
grain = one purchase event
filter = event_type = 'purchase'
join to users = cafe_app_events.user_id = cafe_users.user_id
 
Report sentence:
Return one row per acquisition_channel with purchase count and net_revenue.
Use table aliases, qualify columns, and sort highest revenue first.
 
AI response:
WITH purchase_events AS (
  SELECT e.user_id,
         e.purchase_amount
  FROM cafe_app_events e
  WHERE e.event_type = 'purchase'
)
SELECT u.acquisition_channel,
       count(*) AS purchases,
       sum(pe.purchase_amount) AS net_revenue
FROM purchase_events pe
JOIN cafe_users u
  ON u.user_id = pe.user_id
GROUP BY u.acquisition_channel
ORDER BY net_revenue DESC, u.acquisition_channel;

That response contains SQL, so it has to survive the same check as any human-written answer. Here is the exact query, run against the real cafe_analytics dataset:

WITH purchase_events AS (
  SELECT e.user_id,
         e.purchase_amount
  FROM cafe_app_events e
  WHERE e.event_type = 'purchase'
)
SELECT u.acquisition_channel,
       count(*) AS purchases,
       sum(pe.purchase_amount) AS net_revenue
FROM purchase_events pe
JOIN cafe_users u
  ON u.user_id = pe.user_id
GROUP BY u.acquisition_channel
ORDER BY net_revenue DESC, u.acquisition_channel;

 acquisition_channel | purchases | net_revenue
---------------------+-----------+-------------
 referral            |         5 |       83.85
 paid_search         |         5 |       68.80
 email               |         5 |       59.50
 organic             |         1 |        6.25
(4 rows)

Notice what the better prompt did. It gave the model the available columns, the join key, the intended output grain, and the metric rule. That does not prove the answer is correct forever, but it removes a whole class of guesses.

Iterate with errors

Error messages are not embarrassing. They are useful context. When a draft fails, do not ask "fix this" with no evidence. Send the exact error, the relevant schema line, and what you expected the query to do.

This intentionally wrong draft uses u.id, a plausible but nonexistent user key:

SELECT u.acquisition_channel,
       sum(e.purchase_amount) AS net_revenue
FROM cafe_app_events e
JOIN cafe_users u
  ON u.id = e.user_id
WHERE e.event_type = 'purchase'
GROUP BY u.acquisition_channel;

ERROR:  column u.id does not exist
LINE 5:   ON u.id = e.user_id
             ^

A good follow-up prompt is small and concrete: "PostgreSQL returned column u.id does not exist. The schema says cafe_users(user_id PRIMARY KEY, ...). Keep the same report sentence and fix only the join key." That keeps the model from rewriting the whole query while you are only correcting one fact.

The same trick works for wrong names, missing GROUP BY columns, ambiguous column errors, and dialect translations. Give the model the engine's complaint, then make it patch the smallest piece. You still run the result, read the row count, and compare it to the report sentence.

Semantic layers beat repeated explanations

A semantic layer is a reviewed place where your team defines business metrics once, including name, grain, filters, joins, owner, and examples. It might be a dbt model, a view, a metrics catalog, a YAML file, or a plain document your team actually maintains. The tool matters less than the definition being shared.

This is the grain discipline from Section 17 in another outfit. A metric definition is a grain contract. If the contract says one purchase event, the query should not accidentally count app opens, signups, or user rows. If a report needs user dimensions, the definition tells the model which join is allowed.

Few-shot prompting is the lightweight cousin of a semantic layer. Few-shot examples are one or two solved patterns you include so the model can imitate the shape. Use examples from your own query library, not random internet snippets:

• "Revenue by branch" uses purchase_events as the first CTE, filters to purchase events, groups by branch, and sorts by net_revenue.
• "Revenue by acquisition channel" joins purchase events to cafe_users only after reducing to the purchase-event grain.

Those examples are not there to impress the model. They are guardrails for naming, grain, filters, joins, and house style.

Keep the example library small enough that people use it. A folder of reviewed report queries, a dashboard model file, or a team note with three canonical patterns beats a hundred stale snippets. Good examples show the boring decisions: alias names, CTE names, where filters belong, how final ordering works, and which totals are reconciled. The model imitates those habits, and reviewers can spot when the draft drifts away from them.

Seed data drafts are drafts

AI is useful for generating test data and seed scripts because it can produce boring rows quickly. That is also why it is risky: it can invent impossible dates, violate constraints, use nondeterministic functions, or create rows that do not test the edge case you need.

Treat generated seed SQL as a draft to inspect before running. Ask for fixed dates, explicit column lists, small row counts, and edge cases that match constraints. Then run it in a scratch database, not production.

AI seed-script draft — inspect before running, not yet accepted:
CREATE TABLE scratch_orders (
  order_id int PRIMARY KEY,
  ordered_at date NOT NULL,
  net_amount numeric(6,2) NOT NULL CHECK (net_amount >= 0)
);
 
INSERT INTO scratch_orders (order_id, ordered_at, net_amount)
VALUES (1, DATE '2026-04-01', 12.50),
       (2, DATE '2026-04-02', 18.00),
       (3, DATE '2026-04-02', 0.00);
 
Inspection checklist:
- Are dates fixed, not current-date based?
- Do column lists match the table?
- Does at least one row test the zero-amount edge case?
- Do constraints reject the bad data you expect them to reject?

If that seed becomes part of a lesson, test, or migration rehearsal, it stops being "AI output" and becomes your SQL. The standard returns: run it, check command tags, inspect rows, and keep it deterministic.

Better prompts do not make AI authoritative. They make AI drafts easier to inspect. Next, 21.3 gives you the full verification ritual for the worst case: AI-written SQL that runs cleanly and returns a plausible but wrong grid.