The SQL looks right. Prove it.

A plausible query is the beginning.

AI tools are useful for drafting SQL, explaining an unfamiliar model or proposing a refactor. The review still has to answer a more demanding question: does this query mean what the business asked?

A query can compile, run quickly and produce believable numbers while calculating the wrong thing. I would start the review with the shape of the result, then work backwards.

Write the grain in one sentence.

For example: “One row per customer per calendar month, including customers with no completed orders.” That sentence exposes choices that are easy to miss: how a month is defined, which status counts, and whether zero-activity customers belong in the result.

Make the AI tool state the intended grain and assumptions before it writes the query. Keep that explanation alongside the code during review.

Make the join earn its place.

Suppose one order has several line items. Joining orders to items expands the order into several rows. Summing an order-level total after that join can multiply the total. A tiny fixture with one order and two items is often enough to reveal the mistake.

-- Diagnostic: does a supposed one-row-per-order
-- result contain more than one row for any order?
select order_id, count(*) as row_count
from candidate_result
group by order_id
having count(*) > 1;

Returning no rows is one useful assertion for that grain. It is not a universal test: a result intentionally at line-item grain should contain repeated order IDs.

Use an adversarial fixture.

• Zero activity: a customer with no orders, to exercise join and filtering choices.
• One-to-many: an order with two items, to detect accidental multiplication.
• Missing values: a nullable field, to expose assumptions about counting and filtering.
• Time boundaries: events either side of midnight or a month boundary, with an explicit timezone.
• Duplicates: a repeated source event, to test the intended deduplication key.

Calculate the expected result independently before asking the tool to generate the assertion. Tests that simply restate the query can preserve the same mistake.

Keep the useful loop short.

Explain the question, generate a candidate, review its assumptions, run the fixture and inspect the difference. Ask for an explanation of each changed join or filter. Use real execution plans and measurements when performance becomes the question.

For reference, PostgreSQL’s table-expression documentation describes join behavior. dbt’s data tests offer a way to express assertions about model results.