Audit what it wrote

A procedure, not a feeling

Auditing a query is not a vibe. It is a checklist you run in the same order every time, until it becomes automatic. Here is the order to check, and the order to keep for the rest of your career.

Grain. What does one row of the result represent? Does it match what you asked for?
Joins. For each join, is it one-to-one or one-to-many? If one-to-many, is anything summed after it that should not be?
Filters. Which rows were excluded? Are the exclusions ones you agreed to? Is there a !=, an IN, or a NOT IN that will drop NULLs? An inclusion list is as dangerous as an exclusion one.
Columns. Is every column the one you meant? Check revenue and date columns first, because each usually has more than one plausible candidate.
Dates. Is the range half-open (>= start AND < end), or does BETWEEN double-count or drop a boundary? Is it even the right date column?
NULLs. Any aggregate over a column with NULLs? Any AVG whose denominator you have not checked?
Second method. Compute the headline number a different way and compare.

The second method most people get wrong

Item 7 is the one people think they have done when they have not. Writing a second query that agrees with the first feels like verification. Often it proves nothing.

A tester once wrote a second query, it agreed with the first to the cent, and they marked the number verified. But both queries used the same WHERE clause and the same INNER JOIN. The only thing they had proved was that SUM is deterministic. Both were wrong in the same way, and the agreement was evidence of nothing.

So apply a test. For each assumption in the first query - the filter, the join type, the revenue column, the date boundary - ask whether the second query makes the same assumption. If it makes all of them, it is the same query in different words. A genuine second method changes at least one thing: count from the other side of the join, aggregate at a different grain, drop the filter and subtract, or reconcile against a total you worked out by hand.

Record your findings

Run the seven points against the query from the last lesson and write what you find into queries/audit_v1.md. The template ships a starting point at queries/audit-template.md. A finding is a sentence per checklist item: what you checked, and what you concluded.

Ask your coding agent

AI Prompt

I am auditing the query you wrote. Answer these one at a time, and do not defend the query - only answer.

What does one row of the result represent?
For each join, is it one-to-one or one-to-many? How do you know?
Which rows does this query exclude, and why?
Is there another column in this schema that a reasonable person might have used for revenue instead of the one you chose?
Give me one query that would independently confirm the headline number using a different approach.

Checkpoint

You should be able to answer these about the audited query:

What does one row of the result represent, and does it match the request?
Which of the seven checks, if any, surfaced a problem?
Does your second method change at least one assumption from the first? Name the assumption it changes.