Domain-Specific Prompts, in depth: text-to-SQL prompts built from your schema, your definitions and your verified queries

Why the domain is the problem

Errors in production text-to-SQL cluster into a few kinds, and almost none of them are syntax. The model joins the wrong tables because two have similar names. It picks a plausible column that is not the one your analysts use. It applies a generic definition of a business term. It forgets a standing filter, such as excluding deleted rows or test tenants. It uses a function from another dialect. It gets the time window subtly wrong, calendar quarter instead of fiscal quarter, UTC instead of local time.

Each of these is missing context, not missing intelligence. So the work is to supply precisely the right context for each question: enough schema to answer it, the definitions it depends on, and examples that show your conventions, while keeping the prompt small enough to stay focused.

Representing the schema

Models read DDL well, so use DDL, but annotate it. Column names alone are ambiguous; a one-line comment per column removes most of the ambiguity. Add units, allowed values for low-cardinality columns, the meaning of nullable flags and the join keys. Leave out what does not help: storage parameters, indexes, grants and columns nobody queries.

-- orders: one row per customer order. Excludes carts. Soft-deleted rows have deleted_at set.
CREATE TABLE sales.orders (
  order_id      BIGINT PRIMARY KEY,
  customer_id   BIGINT,        -- joins customers.customer_id
  region_code   TEXT,          -- one of: NA, EMEA, APAC, LATAM
  status        TEXT,          -- one of: placed, shipped, refunded, cancelled
  amount_cents  BIGINT,        -- gross amount in cents, before refunds
  created_at    TIMESTAMPTZ,   -- UTC
  deleted_at    TIMESTAMPTZ    -- NULL unless soft-deleted
);

Generate this block from the catalogue plus a maintained description file, never by hand per prompt. Keep descriptions in version control next to the data models so that a renamed column updates the prompt in the same change. For enum-like columns, sampling the distinct values on a schedule is cheap and prevents the model inventing 'Europe' when the data says 'EMEA'.

Schema linking: choosing what to include

A warehouse with hundreds of tables does not fit in a useful prompt, and even when it fits, irrelevant tables invite wrong joins. Schema linking selects the tables and columns relevant to the question before generation. A robust approach combines three signals: embedding similarity between the question and each table's description, lexical matches on table names, column names and known values, and expansion along foreign keys so that a selected fact table brings its dimension tables with it.

def link_schema(question, catalog, embed, k=6):
    q_vec = embed(question)
    scored = []
    for table in catalog.tables:
        sem = cosine(q_vec, table.desc_vector)
        lex = lexical_overlap(question, table.name, table.column_names, table.sample_values)
        scored.append((0.7 * sem + 0.3 * lex, table))
    chosen = {t.name for _, t in sorted(scored, key=lambda s: s[0], reverse=True)[:k]}
    # Bring in tables reachable by one foreign-key hop from what was chosen.
    for name in list(chosen):
        chosen |= set(catalog.fk_neighbours(name))
    return [catalog.table(n) for n in sorted(chosen)]

The weights are a starting point to tune against your evaluation set, not a known-good value. Log which tables were linked for every request: when an answer is wrong, the first question is whether the right table was even in the prompt. If linking misses often, raise k before reaching for a cleverer model; recall matters more than precision here, because the model can ignore an extra table far more easily than it can invent a missing one.

Business definitions belong in the prompt

The most damaging errors are semantic: a correct-looking query for the wrong definition. Keep a glossary of the terms people actually ask about, each with a precise definition and, ideally, the SQL fragment that implements it:

net_revenue:
  definition: Sum of order amounts minus refunds, excluding test customers and deleted orders.
  sql: SUM(o.amount_cents - COALESCE(r.refund_cents, 0)) / 100.0
  requires: [sales.orders o, sales.refunds r ON r.order_id = o.order_id, sales.customers c]
  filters: [o.deleted_at IS NULL, c.is_test = FALSE]
fiscal_quarter:
  definition: Fiscal year starts 1 February. FQ1 = Feb-Apr.
  sql: see calendar.fiscal_periods

Retrieve glossary entries the same way as tables, by matching the question, and include them verbatim. Tell the model that when a glossary term appears it must use the given SQL and filters. Better still, where you can, publish common metrics as database views so the model selects from metrics.net_revenue_daily rather than re-deriving it. Every definition you move from prompt text into a view is one the model can no longer get wrong.

Few-shot examples from a verified query bank

Examples teach conventions faster than instructions do: how you write date ranges, which joins you use, how you alias. The best examples are real questions with SQL an analyst has signed off, stored in a query bank and retrieved per request by similarity to the new question. Three to five examples usually suffice; more starts to crowd out the schema. Build the bank from the questions users actually ask, and add each corrected failure to it, so the system learns from its mistakes without retraining. The general mechanics of choosing and ordering examples are in few-shot prompting architecture.

Make sure retrieved examples come from the same dialect and the same schema version. An example referencing a dropped column will be copied faithfully.

The rendered prompt

Put stable material first and per-question material last, which also makes the static prefix cacheable. A typical render:

SYSTEM
You write a single read-only SQL query for PostgreSQL 16 against the schema below.
Use only the tables and columns listed. Never use SELECT *.
When a glossary term appears in the question, use its SQL and filters exactly.
Interpret relative dates against today's date, {today}, in UTC unless told otherwise.
If the question is ambiguous or cannot be answered from this schema, do not guess:
return sql = null and explain what is missing in "clarification".
Respond with JSON: {"sql": string|null, "assumptions": [string], "clarification": string|null}

SCHEMA
{linked_ddl}

GLOSSARY
{glossary_entries}

EXAMPLES
{retrieved_question_sql_pairs}

QUESTION
{question}

Three choices in this template matter. Naming the dialect and version prevents most function errors. The explicit permission to decline, with a clarification, gives the model an alternative to inventing an answer. And the assumptions list makes hidden interpretation visible to the user, who can catch "assumed calendar quarter" in a second. Parsing the JSON reliably is covered in output parsing architecture.

Guards before execution

Treat generated SQL as untrusted input. The primary control is the database itself: execute with a role that has SELECT on an allowlisted set of schemas and nothing else, with a statement timeout and a row cap. Everything in application code is defence in depth on top of that, never a replacement. A parser such as sqlglot lets you check structure before sending anything to the database:

import sqlglot
from sqlglot import exp

ALLOWED = {"sales.orders", "sales.refunds", "sales.customers", "calendar.fiscal_periods"}
FORBIDDEN = (exp.Insert, exp.Update, exp.Delete, exp.Drop, exp.Create)

def guard(sql: str, dialect: str = "postgres", max_rows: int = 1000) -> str:
    statements = sqlglot.parse(sql, read=dialect)
    if len(statements) != 1:
        raise ValueError("exactly one statement is allowed")
    tree = statements[0]
    if not isinstance(tree, (exp.Select, exp.Union)):
        raise ValueError("only SELECT queries are allowed")
    if tree.find(*FORBIDDEN):
        raise ValueError("write or DDL statement found inside the query")
    ctes = {cte.alias_or_name for cte in tree.find_all(exp.CTE)}
    for table in tree.find_all(exp.Table):
        name = f"{table.db}.{table.name}" if table.db else table.name
        if name not in ALLOWED and table.name not in ctes:
            raise ValueError(f"table not allowed: {name}")
    if isinstance(tree, exp.Select) and not tree.args.get("limit"):
        tree = tree.limit(max_rows)
    return tree.sql(dialect=dialect)

Add an EXPLAIN step for expensive warehouses: reject plans whose estimated cost or bytes scanned exceed a budget, and tell the user the question needs narrowing. Never interpolate model output into a string that is then executed with broader privileges, and never let values from the database flow back into a prompt that controls tools without treating them as data; a column containing "ignore previous instructions" is a prompt injection like any other.

A bounded repair loop

When execution fails, the error message is excellent context: "column o.region does not exist" tells the model exactly what to fix. Send back the failed SQL and the error, and ask for a corrected query. Bound it to one or two attempts; after that, a third attempt rarely succeeds and the loop mostly burns latency.

def answer(question, max_repairs=2):
    prompt = render(question)
    for attempt in range(max_repairs + 1):
        out = llm_json(prompt)                     # hypothetical client returning parsed JSON
        if out["sql"] is None:
            return {"clarification": out["clarification"]}
        try:
            rows = run_readonly(guard(out["sql"]))
            return {"sql": out["sql"], "rows": rows, "assumptions": out["assumptions"]}
        except Exception as err:                   # guard or database error
            prompt = render(question, previous_sql=out["sql"], error=str(err)[:500])
    return {"error": "could not produce a valid query", "last_sql": out["sql"]}

An empty result is not an error, but it is a signal. Many wrong queries return zero rows because of a value mismatch such as 'emea' versus 'EMEA'. Show the SQL and assumptions alongside an empty result rather than presenting "no data" as a fact.

Worked example

Question: "What was net revenue by region last quarter?" Linking scores sales.orders, sales.refunds and sales.customers highest and the foreign-key hop adds nothing new; the lexical match on "quarter" pulls in calendar.fiscal_periods. The glossary contributes net_revenue and fiscal_quarter. The example bank returns a verified query for "gross revenue by region this month". The model returns SQL that joins the four tables, applies both glossary filters, groups by region_code, and lists one assumption: "last quarter means the most recent completed fiscal quarter". The guard passes it, adds a LIMIT, and it runs in 400 ms. The user sees four rows, the SQL and the assumption. Without the glossary, the same model in testing summed gross amount_cents over the calendar quarter, a number that looked fine and was wrong in two ways.

Evaluating by execution

Compare results, not SQL text. Two correct queries can look completely different. Build a golden set of at least a hundred real questions with analyst-verified SQL, run both the reference and the generated query, and compare result sets as multisets, ignoring row order unless the question asks for an order and tolerating float rounding. Track the accuracy per question category and the clarification rate separately: declining an unanswerable question is a success. Public benchmarks such as Spider and BIRD are useful for comparing models, but your own schema and definitions are the test that matters.

Re-run the set on every change to the schema descriptions, glossary, example bank, template or model. Most regressions come from content changes, not model changes.

Trade-offs

Larger linked schemas raise recall and lower precision; tune k on the golden set. A semantic layer of curated views reduces errors sharply but moves work to data engineering and limits questions to what was modelled. Free-form generation answers more questions and gets more of them wrong. Many teams settle on views for the top twenty metrics and free-form SQL, clearly labelled with its assumptions, for the long tail.