Asking an AI for SQL without being lied to

What an AI is actually good at here

Be fair to the tool before you are suspicious of it. A language model will write a window function you would have spent twenty minutes looking up. It will translate a query between Postgres and BigQuery dialects. It will explain a query plan you cannot read. It will produce a decent first draft five times faster than you can type one. This is real and you should use it.

What it cannot do is know things it was not told, and its failure mode is not silence. It is confidence.

Give it the schema, every time

The most common wrong query from an AI is not a logic error. It is a guess about your column names, dressed as knowledge. It will write orders.total_amount when your column is amount_cents. It will invent a users.last_login_at because most schemas have one.

So paste the actual definitions. \d orders in psql, or the CREATE TABLE statements, plus one sentence per table saying what a row means:

Here are three tables. One row of payments is one attempted charge, including failures. One row of orders is one basket someone checked out. An order can have several payment attempts.

That last sentence (the one about several payment attempts) prevents more wrong queries than anything else you can write, because it is exactly the fact that produces row multiplication in a join.

Ask for the assumptions before the SQL

A useful prompt shape:

Before writing the query, list what you are assuming about the schema and about the definitions of the terms I used. Then write the query.

This converts invisible guesses into a list you can check in ten seconds. It will say things like “I assumed ‘active’ means a login in the last 30 days” and “I assumed refunds are stored as negative amounts in the same table.” One of those is usually wrong, and it is much cheaper to catch it there than in the output.

Verify like you would verify a stranger’s work

Because that is what it is.

Run it with a LIMIT first and read the rows. Not the count. The rows.

Check the row count against what you expect. If the orders table has 40,000 rows and the joined result has 61,000, a join is multiplying, and the AI’s total revenue is now wrong in a way that looks entirely plausible. This is the single most dangerous failure in AI-written SQL: the result is not absurd, it is just too big by 40%.

Check one row by hand. Pick a customer, look them up in the source, verify their number.

Compare a total to something you already know. Last month’s figure, the finance export, anything.

Break it on purpose. Change a filter so the number should move. If it does not move, the filter is not doing anything.

The rule for anything that writes

Never run a generated UPDATE or DELETE directly. Run the WHERE clause as a SELECT first, and look at what would be affected:

-- Before: see exactly what the generated statement would touch
SELECT count(*), min(id), max(id) FROM orders WHERE status = 'pendng';

-- If you must run it, run it where you can take it back
BEGIN;
UPDATE orders SET status = 'pending' WHERE status = 'pendng';
-- inspect, then:
ROLLBACK;   -- or COMMIT, once you are sure

In that example the typo is in the data, not the statement, and the SELECT would have returned 3 rows instead of the 40,000 you feared, or zero when you expected thousands. Either way you learn it before anything changes.

The asymmetry worth remembering

SQL that fails is free. You see an error, you fix it, nobody is harmed. SQL that succeeds and is wrong costs you a decision, and it costs it silently, sometimes months later.

So spend your scepticism where the risk actually is. Not on the queries that error (those look after themselves) but on the ones that return a clean, reasonable-looking number on the first try. That is the output you should stare at hardest, whether a model wrote it or you did.