The dangerous moment in AI-written SQL is not a syntax error. It is a query that looks sensible, runs successfully, and quietly changes a metric or scans an entire large table. “Can you calculate this?” rarely specifies time range, deduplication, real table relationships, or whether production access must remain read-only.
AI is useful for explaining schema, drafting queries, spotting missing conditions, and creating test cases. It should not have unrestricted production write access or define a business metric on its own. This lightweight workflow is for analytics and reporting requests using ChatGPT or Claude.
Give AI a minimal data contract
Do not export an entire schema. Supply only relevant read-only context: tables and fields, primary and join keys, field meanings, timezone, data latency, allowed period, and known null or duplicate rules. Real names, email addresses, phone numbers, tokens, connection strings, and full customer records do not belong in a prompt.
State the business definition as well. Does an active customer log in, pay, or complete an order within 30 days? Does an order mean created or successfully paid? These are not SQL skills and should never be guessed by a model.
Make AI ask questions before it writes a query
A strong workflow asks the model to list ambiguous conditions before generating code. Common questions include UTC versus business timezone, cancelled orders, refunds, cross-device identity, test accounts, and whether one-to-many joins could multiply revenue.
You are a data-analysis assistant. Given this minimal schema and metric definition, do not write SQL yet. List the definitions, join risks, data-quality risks, and performance risks that must be confirmed for a correct result. Classify questions as blocking, recommended, or ignorable. Do not assume unspecified tables or fields, and do not request raw personal data.
Once answers are confirmed, ask for two outputs: a query draft and an explanation checklist. The checklist should explain each CTE, where deduplication occurs, why filters exist, and which assumptions came from the business definition.
Read-only by default, limited scope, samples first
Run AI-drafted queries first in a read-only replica, development environment, or constrained analytics warehouse. Disallow INSERT, UPDATE, DELETE, MERGE, DROP, ALTER, and unconditional scripts by default. Even SELECT should limit dates, columns, and row count, and use EXPLAIN or a query plan to identify full scans.
Inspect 20–100 sample rows and intermediate aggregates before final totals. If results look wrong, do not immediately ask AI to fix the SQL. Compare keys, join counts, and source events first to determine whether the issue is data or definition.
Based on these confirmed definitions, draft read-only SQL. Use only supplied tables and fields; explicitly limit dates; place deduplication in its own CTE; prohibit write operations and SELECT *; provide a sample-validation query returning at most 100 rows before the aggregate query. Then explain joins, filters, timezone, and possible double-counting risks.
Three common causes of mismatched results
- One-to-many joins: customers join orders, which join line items, and counts or revenue multiply. Aggregate at the finest needed grain before joining.
- Date boundaries: BETWEEN, timezone conversion, and month-end timestamps lose data easily. Prefer half-open periods: greater than or equal to start and less than next period start.
- Status definitions: created, paid, fulfilled, and refunded are different. Keep the status list beside the query rather than hiding it in an unexplained WHERE clause.
Pre-publication checklist
- Are metric definitions, timezone, period, and statuses explicit?
- Is the query read-only and tested in a controlled environment?
- Can the expected grain after every join be explained?
- Were keys, amounts, and event counts sampled?
- Are test records excluded or explicitly disclosed?
- Are SQL version, run time, and data-latency notes saved?
AI can move analysts more quickly from natural language to reviewable SQL. It cannot replace data definitions, permission boundaries, or result validation. Supply minimal schema, ask questions first, validate with read-only samples, and then publish results.