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. The order matters: the cheap checks that would invalidate everything else come first. Here is the order to check, and the order to keep for the rest of your career.

1. Grain. What does one row of the result represent? Does it match what you asked for?
2. 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?
3. 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.
4. Columns. Is every column the one you meant? Check revenue and date columns first, because each usually has more than one plausible candidate.
5. 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?
6. NULLs. Any aggregate over a column with NULLs? Any AVG whose denominator you have not checked?
7. Second method. Compute the headline number a different way and compare.

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.

Your task

Run the seven points against the query from the last lesson, queries/agent_v1.sql. Copy the template at queries/audit-template.md to queries/audit_v1.md and fill it in - one sentence per checklist item, saying what you checked and what you concluded. For the second method, make sure your check changes at least one assumption from the original; if it reuses the same filter and the same join, it is the same query in different words and it proves nothing.

Check your understanding

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

Do it with your agent

Say next lesson and your agent teaches this, asks you these questions, then sets the task above. Do it by hand, then say review my work - it checks your work against a rubric and tells you what to fix or marks the lesson done.