Trust, but Verify

AI makes SQL drafting faster. It also makes wrong SQL arrive faster. The dangerous case is not the query that fails with a missing column error. The dangerous case is the query that parses, runs, returns a tidy grid, and answers a slightly different question.

In 21.1 you treated AI as a copilot. In 21.2 you made prompts more specific with schema, grain, and metric definitions. This lesson is the other half of that bargain: when AI writes SQL, you become more responsible for reading it. The model can suggest a join, but it cannot know whether the join preserves the grain of your report. It can produce a confident explanation, but confidence is not a test.

Here is a static transcript. No live AI is running in this lesson, and the response is intentionally wrong.

Developer:
Using PostgreSQL and this schema, show net revenue by acquisition channel.
 
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:
net_revenue = sum(purchase_amount) for purchase events only.
Return one row per acquisition_channel.
 
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 joined_rows,
       sum(pe.purchase_amount) AS net_revenue
FROM cafe_users u
JOIN cafe_app_events e
  ON e.user_id = u.user_id
JOIN purchase_events pe
  ON pe.user_id = u.user_id
GROUP BY u.acquisition_channel
ORDER BY net_revenue DESC, u.acquisition_channel;

The query looks plausible. It uses the right tables. It filters purchases in a CTE. It groups by acquisition channel. It even sorts the report nicely. But it also joins cafe_app_events twice: once as all app events, and once as purchase events. That repeats each purchase once for every event by the same user.

The database will not reject that. It is valid SQL. Run it and you get a neat, wrong report:

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 joined_rows,
       sum(pe.purchase_amount) AS net_revenue
FROM cafe_users u
JOIN cafe_app_events e
  ON e.user_id = u.user_id
JOIN purchase_events pe
  ON pe.user_id = u.user_id
GROUP BY u.acquisition_channel
ORDER BY net_revenue DESC, u.acquisition_channel;

 acquisition_channel | joined_rows | net_revenue
---------------------+-------------+-------------
 referral            |          73 |     1245.45
 paid_search         |          55 |      776.10
 email               |          60 |      733.00
 organic             |           7 |       43.75
(4 rows)

The report sentence said one row per acquisition channel, but the metric grain is one purchase event. The query should start from purchase events and join users only to get the channel. There is no reason to join all app events into the revenue calculation:

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)

The corrected query proves the mistake with a real wrong-result comparison. The bad result says referral has 73 joined rows and 1245.45 revenue. The verified result says referral has 5 purchases and 83.85 revenue. Both outputs have four rows and both look like a dashboard table. Only the grain check exposes the fan-out.

The verification ritual

Use this ritual before trusting AI-written SQL, especially when it will feed a dashboard, customer-facing feature, migration, export, or decision.

First, read the SQL before running it. Name the grain of each major piece: "this CTE is one row per purchase event," "this join adds user dimensions," "the final report is one row per acquisition channel." If you cannot say the grain out loud, the query is not ready.

Second, check row counts and magnitude. Count before grouping when the answer depends on a filtered fact set. A revenue query based on purchase events should not suddenly have a joined row count much larger than the purchase count unless you can explain the multiplication. For reports, compare grouped totals back to a raw total. For joins, ask whether the join should preserve rows, multiply rows, or intentionally filter rows.

Third, spot-check a known subset. Pick one customer, one branch, one date, or one acquisition channel you can trace by hand. Follow the rows through the query. This is not busywork — it is how you catch the "almost right" join that no syntax checker can see.

Fourth, run EXPLAIN (COSTS OFF) when the query shape matters. You are not looking for a fake timing promise. You are checking structure: which tables are scanned, which filters happen before joins, whether a table appears twice, and whether the grouping stage matches your mental model. If the plan reads a different story than your report sentence, stop.

Fifth, test boundary cases. What happens to users with no purchases? Do zero-revenue days appear or disappear? Are NULL amounts excluded intentionally? Do date filters include the first and last day you promised? AI is especially likely to produce the ordinary path and skip the edges.

Sixth, use LIMIT while iterating on exploratory reads. A limit does not prove correctness, but it lets you inspect shape, duplicate rows, surprising NULLs, and column names before you expand the result. Remove or justify it before shipping a full report.

Seventh, use a read-only role by default. A read-only database identity lets AI-assisted tools inspect schema and run SELECT-shaped checks without being able to change production data. That boundary matters more as editor tooling gets more convenient.

Now run the ritual yourself. The editor below starts with the AI's plausible-but-wrong draft. Run it, read the joined_rows column against the purchase count, then work through the commented checks at the sql> prompt:

Write access is different

Never hand AI production write access. If an assistant suggests an UPDATE or DELETE, treat it exactly like your own risky SQL from Section 8: rehearse the target first with a SELECT that uses the same tables, joins, and predicates. Inspect the primary keys and row count. If the rehearsal surprises you, the write is wrong.

When the rehearsal passes, use the normal production discipline: transaction where applicable, small batches when appropriate, reviewed predicate, verify after the write with another SELECT, and commit only when the observed rows match the intent. AI can help draft the rehearsal query or explain an error message. It does not get to bypass the rehearsal.

The same standard applies to schema changes and migrations. A model may describe a plausible ALTER or backfill, but production changes need human review, rollback or forward-fix thinking, and evidence from a production-like copy. Convenience is not a permission model.

Keep your SQL sharp

Using AI daily does not mean letting your SQL muscles atrophy. Write the report sentence and grain yourself before you prompt. Read every generated query from FROM outward: base rows, joins, filters, grouping, final ordering. Ask the model to explain a query after you have formed your own explanation, then compare. Keep a small library of verified queries and errors so prompts improve without turning into blind trust.

Every so often, solve a report by hand before asking for a draft. You are not proving moral purity. You are preserving the ability to recognize a wrong join, a row-loss trap, and a metric that changed grain halfway through the query. AI makes writing easier; it makes reading more important.

Part V ends with that operating rule: AI drafts, humans verify, databases prove. Part VI goes under the hood so the plans, indexes, storage, transactions, and scaling choices behind those proofs become less mysterious.