Why generated SQL needs proof
Translating a marketing question into SQL means making a series of choices the question rarely spells out. Which table holds the truth for orders. Whether "customer" means a person, an account or an email address. Whether a refunded order counts. Which date marks the event, and in which time zone. Which AttributionAssigning conversion credit to touchpoints. rule assigns an order to a channel. Whether the unit is the SessionA group of one user's interactions with a site or app in one visit, ended by inactivity., the user or the order. A model makes each of these choices, often reasonably, and the result does not say which choices it made.
The model also does not know the data. It sees column names, and perhaps a sample, but it does not know that the sessions table has one row per session per channel touch, that test orders are flagged in a column nobody documented, or that the spend table repeats a row whenever a campaign is renamed. Those facts decide whether a query is right, and they live in the heads of the people who built the warehouse and in its documentation.
The failures that matter produce no error. Syntax errors are easy, because the database refuses to run the query. A join that multiplies rows, a filter in the wrong place or a date boundary off by a day runs perfectly and returns a number close enough to be believed. The only defence is a routine that treats the query as the answer, reads it against a written definition, and proves the result against a total obtained another way. The academy checkpoint on proving AI-written queries, tags and markup tests this judgement at a group of veterinary practices, from what must be written before the model is asked to why dropping records until two totals agree is the wrong fix.
| Failure | What it looks like | How the routine catches it |
|---|---|---|
| Wrong definition | A tidy answer to a different question | Writing the definition first and reading the query against it |
| Wrong grain | Counts of rows where people were meant | Checking what one row represents at each step |
| Join fan-out | Totals larger than the source system's | Checking key uniqueness before and after each join |
| Filter moves the baseline | Totals smaller than expected, or a left join acting as an inner join | Checking each filter's position and effect |
| Date or time zone error | Months that do not match the platform's report | Checking the date column, boundaries and zone |
| Missing deduplication | The same order or customer counted twice | Distinct counts compared with row counts |
Before you start
- Read access to the data. A read-only account on the warehouse or BI tool, approved for this use.
- An approved AI tool. One your organisation permits for this work. Send it the schema and definitions, not customer records.
- Schema documentation. Table and column descriptions, keys, what one row represents in each table, and known quirks. If none exists, write the part you need as you go; it will be reused.
- Agreed metric definitions. Ideally from a metrics layer, as the metrics layer and definitions guide describes; otherwise from the team's measurement plan.
- An independent total. A figure from another source for the same thing: the ad platform's spend, the e-commerce platform's order count, finance's revenue, or a simpler query you trust.
- Somewhere to save verified queries. A shared repository or BI tool where queries live with their definitions.
Checking one query, step by step
Write the question and its definition
Write the question in one sentence, then the definition of every measure and dimension in it. "New customers acquired through paid social last month" needs: what makes a customer new (first ever completed order), which orders count (completed, not cancelled or test), which channel rule applies (the organisation's agreed attribution rule, by name), which date marks the event (order completion date), which time zone, and what "last month" means (calendar month). If a governed definition exists, quote it. This sentence and its definitions are what every later step checks the query against, so write them before asking the model anything.
Give the model the schema
Give the model the relevant tables, their columns with types and descriptions, the keys, what one row represents in each table, and the known quirks: test flags, duplicate rows, renamed campaigns, the time zone timestamps are stored in. Include the written question and definitions. Ask for a query that uses only the tables and columns supplied, uses named parameters for the period, and comes with a plain-language explanation of each step. Tell it to list any assumption it had to make. Where a metrics layer exists, point the model at it rather than at raw tables, so the query inherits the agreed definitions.
Generate and read the query
Run nothing yet. Read the query clause by clause against the definition. Which tables does it read, and are they the governed ones? What does it count, and is that the measure defined? Which columns carry the date and the channel? Compare the model's explanation and its listed assumptions with what the SQL actually does; models sometimes describe the query they meant to write. Mark each clause as matching the definition, differing from it, or unclear, and resolve every difference before going further.
Check the grain
For each table and each intermediate step, write down what one row represents: one order, one order line, one session, one customer per day per channel. Then check that the final measure matches the final grainWhat one row of a table represents, such as one order, one order line, one session or one customer per day; queries and metric definitions must match it.. Counting rows in an order-line table counts lines, not orders; summing a customer-level value from a session-level table multiplies it by sessions per customer. Where the definition counts people or orders, the query should count distinct identifiers, and comparing the distinct count with the row count at each step shows whether rows have been duplicated somewhere.
Check joins for duplication
For every join, check whether the key is unique on each side. Run a quick count of rows per key on each table; if any key appears more than once on a side where the query assumes it is unique, the join will repeat rows on the other side. The SQL for marketers guide describes this fan-out and its usual cure: aggregate each side to the join grain before joining. Compare the row count before and after each join. Check join types too: an inner join silently drops rows with no match, which can remove orders that have no recorded session, and that may or may not be what the definition intends.
Check filters and date ranges
Read every filter and ask what it removes. Confirm that test, internal and cancelled records are excluded as the definition says, and that no filter removes records the definition includes. A condition on the right-hand table of a left join, placed in the WHERE clause, turns the join into an inner join; it belongs in the ON clause or in the step that builds that table. For dates, confirm the column (order created or order completed), the boundaries (start inclusive, end exclusive is the safe pattern), and the time zone, which must match the time zone of the report the result will be compared with.
Reconcile to an independent total
Now run the query, and before using the answer, compare a total from it with the independent figure gathered earlier. Drop the breakdown and compute the overall total for the period, then compare it with the platform's or finance's number for the same scope. Expect small, explainable gaps, such as time zone differences or late refunds, and write down the explanation. An unexplained gap means a step above was wrong; go back rather than adjusting the result. The table below lists common reconciliation pairs.
Save the verified query with its definition
Save the query with the question, the definitions, the tables used, the checks run, the reconciliation result and its explanation, the date verified and the person who verified it. Name it so others can find it. Next time the same question is asked, the verified query is the starting point, and the model can be given it as an example. If the schema or a definition changes, mark the query for re-verification. A library of verified queries is what makes plain-language querying safe to hand to more people, because the hard choices have already been made and checked.
Reconciliation patterns
Reconciliation compares the query's result with a total obtained independently, at the same scope. The two will rarely match exactly; the point is that the difference is small and explained. The academy exercise on checking a sewing machine retailer's AI-written query and tag practises this routine on a board-pack revenue query, tracing each fix through to the shop's own monthly report and explaining the small gap left, beside a separate proof for a generated sample request tag.
| Query result | Independent total | Expected differences | Gap that signals an error |
|---|---|---|---|
| Spend by channel and day | The ad platform's own spend report for the period | Time zone of the account versus the warehouse; late adjustments | Warehouse spend above the platform's, usually fan-out |
| Orders or revenue by channel | The e-commerce platform's order report or finance's revenue | Refunds, cancellations, test orders, currency conversion | Channel totals summing to more than all orders, usually double attribution |
| New customers | A count of first orders from the customer table | Definition of new; merged duplicate accounts | Far more new customers than first orders, usually missing DeduplicationRemoving double counts when one conversion is reported by more than one source. |
| Sessions or users | The analytics tool's report for the same property and dates | Consent, filters, sampling in the tool's interface | Sessions far above the tool's, usually a join at touch level |
| Conversion rate | Numerator and denominator reconciled separately | Each part's own differences | A rate that looks right while both parts are wrong |
| A trend over time | The same query run for a period already reported and agreed | Late data, restatements | The agreed period not reproducing |
When there is no independent source, write a second, simpler query by hand that counts the same thing a different way, for example without the joins, and compare. The data quality guide covers the routine checks that keep the sources themselves trustworthy, which reconciliation depends on. A verified query is one part of a finished analysis, and the routine for reviewing an analysis before it is shared checks the comparisons, counts and claims built on it. The same discipline applies upstream of the warehouse, where the tutorial on checking AI-written tracking code proves a generated data layer push or tag before it starts collecting the data these queries read. Before a generated query's answer is shared, the review checklist for AI-written code asks for the grain, join, reconciliation and saved-version checks in one list. When a generated figure has already reached a report and nobody can reproduce it, the diagnosis for AI-generated report numbers that do not match separates a double-counting join from a definition, formula, metrics layer, review or sample cause.
A paid social new-customer count that was double the truth
Haverbrook Garden Supply, an invented online garden retailer, asks a model for "new customers from paid social in September and cost per new customer". The warehouse has an orders table (one row per order), a touches table (one row per marketing touch per order) and a daily spend table. The model's query joins orders to touches on order ID, keeps each customer's first order, filters touches to paid social and counts customer IDs. It returns 1,184 new customers at £8.10 each. The grain check shows the touches table has several rows per order, and the query counted rows, not distinct customers. Reconciling to the customer table, which shows 1,960 first orders across all channels in September, the 1,184 is plausible on its face; but counting distinct customers gives 702, and applying the agreed last-paid-touch rule gives 596. The time zone check moves 11 orders into August. The verified answer is 585 new customers at £16.40 each, which matches finance's monthly total within 2 orders. The team saves the query with its definition and adds "touches has many rows per order" to the schema notes the model receives.
Common mistakes
- Asking before defining. Without a written definition there is nothing to check the query against.
- Trusting the model's explanation. Read the SQL; explanations can describe a different query.
- Running the query and reading only the output. A clean table proves the syntax, not the answer.
- Ignoring the grain. Row counts and distinct counts diverge exactly where the error is.
- Joining before aggregating. Non-unique keys multiply rows and inflate every sum.
- Filtering the right side of a left join in WHERE. It quietly removes the rows the left join was meant to keep.
- Comparing months across time zones. Boundaries that differ by hours shift orders between periods.
- Adjusting the result to match. An unexplained gap means a step was wrong; fix the query.
- Not saving the verified query. The next person asks the same question and starts from scratch.
- Using the result as a headline KPIA metric tied directly to an objective. before reconciliation. Numbers that reach a report should have been proved first.
