Doing It With AI

Cheatsheet: Doing It With AI

One page for Topic 4. AI-generated SQL is syntactically confident and logically fallible in the exact same ways as human SQL — and every expensive bug runs cleanly.

The AI-SQL audit checklist

Run this on every AI-generated (or any) query before its number goes anywhere:

Then two sanity checks:

• COUNT first. Run the skeleton as SELECT COUNT(*) before the full query. Expecting ~6 rows (one per month) and getting 4,000 means the grain is wrong — stop before anyone sees a number.
• Check one known answer. Pick a slice where the truth is already trusted (finance's signed-off month, one customer you know). Right on the known case → trust rises sharply; wrong → bug found for the price of one lookup.

The spec template for directing AI

Formula: business question + output grain + decisions already made.

Bad ask: "Show me revenue." Good ask: "Monthly gross revenue (sum of order_total, ignore refunds) for Jan–Jun 2026, one row per month, using half-open date ranges."

Reporting a bug back

Name mechanism + consequence + intent — never "it looks wrong":

• "This INNER JOIN to orders drops customers with no orders — I need all customers, so LEFT JOIN from customers."
• "BETWEEN on created_at (a timestamp) cuts off most of the last day — use >= start AND < next-day."
• "This counts sessions, not users — I need COUNT(DISTINCT customer_id)."

AI is extremely good at applying a fix once the defect is named, and near-useless at guessing what displeased you.

Query-plan reading: the whole syllabus

EXPLAIN shows how the database will run a query; EXPLAIN ANALYZE runs it and adds actuals. You need to recognize exactly three patterns:

Calibration:

• Seq scan on a small table, or when the query needs most of the table: fine. "Seq scan bad" is wrong often enough to be dangerous.
• cost is an abstract score, not seconds — only comparable between plans.
• Your job is triage, not tuning: quote the pattern specifically ("seq scan on events, 210 estimated vs 8.4M actual, feeding a nested loop") and hand it to an engineer.
• An AI saying "this query is efficient" is a guess about a database it has never seen. The plan is the ground truth.

The dashboard-audit workflow

The capstone method, transferable to any query that decides something:

1. State the question and expected output grain first — how many rows should come back, one row per what?
2. Audit each CTE/subquery separately — what is one row, and does any join change row count before an aggregate? (Fan-out before SUM = inflation.)
3. Audit every date filter — BETWEEN on a timestamp is guilty until proven innocent.
4. Audit every alias against its expression — especially business-metric names (active_users, revenue, conversion_rate).
5. Check derived ratios last — a ratio of two unaudited numbers compounds both errors.
6. Run the sanity checks — COUNT comparison, plus one known-answer slice against a number someone already trusts.
7. Report by mechanism, consequence, intent — that's what turns an audit into a one-round fix.

The three highest-yield questions on any dashboard query: did a join change row count before an aggregate, is BETWEEN sitting on a timestamp, and does each alias match its expression.

Traps

• Verifying by re-asking the same AI "is this correct?" — it confidently affirms its own work. Verify against the data, not against more prose.
• Accepting "runs without errors" as correct — every expensive bug in this track executes cleanly; syntax errors are the only ones the database catches.
• Skipping the audit because the query looks like the last one that was fine — one changed join or date filter reintroduces everything.
• Not specifying the output grain in the ask — the AI picks one, and you discover which during the audit.
• Treating plan cost as time, or demanding indexes everywhere — indexes slow every write; "add an index" is not your call.
• Trusting an AI's performance claims over the actual plan.
• Being the only reviewer on high-stakes numbers — anything feeding a board deck or a pricing decision gets a second human reviewer. That's policy, not doubt.