Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

AI in Marketing › AI Applications in Marketing

Checking AI-Written SQL Step by Step

A routine for using AI to write SQL against marketing data and proving the answer right: schema context, definitions, joins, filters and reconciliation.
Tutorialadvanced10 min readUpdated

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 Attribution rule assigns an order to a channel. Whether the unit is the Session, 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.

FailureWhat it looks likeHow the routine catches it
Wrong definitionA tidy answer to a different questionWriting the definition first and reading the query against it
Wrong grainCounts of rows where people were meantChecking what one row represents at each step
Join fan-outTotals larger than the source system'sChecking key uniqueness before and after each join
Filter moves the baselineTotals smaller than expected, or a left join acting as an inner joinChecking each filter's position and effect
Date or time zone errorMonths that do not match the platform's reportChecking the date column, boundaries and zone
Missing deduplicationThe same order or customer counted twiceDistinct 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

  1. 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.

  2. 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.

  3. 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.

  4. 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 grain. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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 resultIndependent totalExpected differencesGap that signals an error
Spend by channel and dayThe ad platform's own spend report for the periodTime zone of the account versus the warehouse; late adjustmentsWarehouse spend above the platform's, usually fan-out
Orders or revenue by channelThe e-commerce platform's order report or finance's revenueRefunds, cancellations, test orders, currency conversionChannel totals summing to more than all orders, usually double attribution
New customersA count of first orders from the customer tableDefinition of new; merged duplicate accountsFar more new customers than first orders, usually missing Deduplication
Sessions or usersThe analytics tool's report for the same property and datesConsent, filters, sampling in the tool's interfaceSessions far above the tool's, usually a join at touch level
Conversion rateNumerator and denominator reconciled separatelyEach part's own differencesA rate that looks right while both parts are wrong
A trend over timeThe same query run for a period already reported and agreedLate data, restatementsThe 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.

Illustrative example — not a real result

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 KPI before reconciliation. Numbers that reach a report should have been proved first.

Frequently asked questions

If the AI-written query runs without errors, is the answer right?

No. A query that runs has valid syntax; it can still count the wrong thing. The common failures in marketing data, joining at the wrong grain so rows are duplicated, filtering away rows that belong in the baseline, using the wrong date column or time zone, counting rows instead of distinct customers, all run cleanly and return numbers that look reasonable. Proof comes from reading the query against the definition and reconciling the result to an independent total.

Do I need to know SQL to check AI-written queries?

You need to be able to read it, which is a smaller skill than writing it. Knowing what each clause does, how joins multiply rows, what a GROUP BY sets as the grain and how filters interact with joins is enough to catch most errors. The SQL for marketers guide teaches that level, and this routine assumes it.

Should I give the AI tool access to the live database?

Only through an account your organisation has approved for the purpose, with read-only access, and preferably to a governed metrics layer or modelled tables rather than raw event data. The model needs the schema and definitions to write a sensible query; it does not need to see customer records to do so. Running the query yourself, after reading it, keeps a person between the model and the data.

Terms used on this page

Frameworks

Where this page is used

Part of these learning paths

Also related

Related