AI for SQL and Data Work

A generated query almost never fails to parse. It runs, returns a plausible number, and the number is wrong — which is a far more dangerous failure mode than a syntax error, because nothing announces it.

The failures are semantic, not syntactic

Models write syntactically valid SQL reliably; it is a small, regular, heavily-represented language. What they cannot do is know things about your data that are not written down anywhere they can see.

• The join that fans out. Joining orders to order_items and summing orders.total multiplies revenue by the number of line items. The query is correct SQL and the answer is garbage.
• The filter everyone on the team knows about. deleted_at IS NULL, is_test = false, status <> ‘draft’. Tribal knowledge, absent from the schema, omitted by anything that has not been told.
• The column whose name lies. created_at that is really imported_at; amount in cents in one table and dollars in another; user_id in the events table that points at an external identity provider’s id, not at users.id.
• The invented enum literal. WHERE status = ‘completed’ when your values are done, cancelled, pending. Returns zero rows, which reads as a real answer.
• NULL semantics. NOT IN against a subquery containing a NULL returns nothing at all; an inner join silently drops the rows an outer join would keep. Both are famous, both are still produced.
• Timezones and boundaries. “Yesterday” in whose timezone, and is the end of the range inclusive.

Every one of these is a gap in what the model was shown, not a gap in what it can do. The academic literature agrees: BIRD (Li et al., NeurIPS 2023) extended the older Spider benchmark (Yu et al., EMNLP 2018) specifically by adding an external knowledge field to each question — because schema alone was not sufficient to write the correct query, even for a human. If a benchmark had to add a human-written hint field, your prompt needs one too.

Five layers of context, by cost

Ordered cheapest first. Most teams do the first and skip the second and third, which are where the accuracy is.

Layers 2 and 3 can be generated once and refreshed automatically:

-- Layer 3: find the low-cardinality columns from the planner's own stats,
-- then emit one SELECT DISTINCT per candidate. No table scans needed to pick.
SELECT tablename, attname, n_distinct
FROM pg_stats
WHERE schemaname = 'public'
  AND n_distinct BETWEEN 1 AND 20        -- positive n_distinct = exact count
ORDER BY tablename, attname;
--  orders | status | 4     ->  SELECT DISTINCT status FROM orders;

-- Layer 2: dump every column comment, which is the material worth a day's work
SELECT c.relname AS table_name, a.attname AS column_name, d.description
FROM pg_description d
  JOIN pg_class c     ON c.oid = d.objoid
  JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = d.objsubid
WHERE c.relkind = 'r'
ORDER BY 1, 2;

The fan-out check

One check catches the most common silent wrongness, and it takes ten seconds. After any query with a join, ask whether the row count matches the grain you expect:

-- the query returned 41,882 rows. How many orders are there really?
SELECT count(*) FROM orders WHERE created_at >= '2026-07-01';
--   12,904

-- 41,882 > 12,904, so the join multiplied rows: any SUM(orders.total)
-- in that query is inflated by roughly 3.2x.

-- The fix is aggregate-then-join, not join-then-aggregate:
SELECT o.id, o.total, i.n_items
FROM orders o
JOIN (SELECT order_id, count(*) AS n_items
      FROM order_items GROUP BY order_id) i ON i.order_id = o.id;

Make it a habit and it becomes automatic. Better, make it a rule in your prompt: after writing the query, state the expected grain (one row per what?) and the check that would confirm it. A model asked to state the grain frequently catches its own fan-out while writing.

Guardrails at execution time

Reviewing a generated query is a weak control, because the wrong ones look right. Design the dangerous outcomes out instead.

• A read-only role. Not a convention — a database role with SELECT and nothing else, on a replica. An UPDATE without a WHERE is then impossible rather than unlikely, and that is the whole difference.
• A statement timeout. SET LOCAL statement_timeout = ‘30s’. A generated cross join against two large tables will otherwise take the database with it.
• An EXPLAIN gate. Run EXPLAIN first, parse the estimated cost and rows, and refuse to execute above a threshold. This catches the missing join condition before it runs rather than after.
• An injected LIMIT on anything a human will look at, and a row cap on anything a program will.
• Always show the SQL. An interface that returns only an answer is unauditable; the query is the reasoning, and it is the only part a data-literate reader can check.
• Never paste rows into the prompt. Schema and distinct values of non-sensitive enums, yes. Customer records, no — data in prompts ends up in logs.

The parameterisation point still applies wherever generated SQL is embedded in application code rather than run ad hoc: a model writing an f-string query is CWE-89 by construction, and the fix is the same as it always was.

The real fix at scale

Everything above is prompt engineering around a schema that was designed for an application, not for a question-answering system. Past a certain size the better investment is to stop asking the model to navigate 400 raw tables and give it twelve curated views instead.

A semantic layer — dbt models, a metrics layer, or just a handful of well-named views — moves the tribal knowledge out of the prompt and into the database, where it is versioned, tested and shared with the humans. fct_orders already has the soft-delete filter applied, already excludes test tenants, already has revenue in one unit. The model’s job shrinks from “reconstruct our business logic from a schema” to “write a query against six clean tables”, which is the task it is genuinely good at.

That is also the durable answer to the accuracy question. Better prompting raises the ceiling a little; removing the ambiguity raises it a lot, and the removal benefits every consumer of the warehouse rather than just the model.