Validate an analyst SQL query with five independent checks

A SQL query is not validated merely because it runs or matches yesterday's dashboard. It needs evidence that the intended population, grain and calculation were preserved. Useful checks should fail for different reasons, so one hidden assumption does not make every test pass together.

This guide combines five checks around a single question: completed-order value before refunds. The synthetic commerce lab provides eight eligible orders worth 104,000 paise and deliberate defects that expose common mistakes.

The checks are teaching patterns, not a universal certification of analytical correctness. Their value comes from adapting them to the metric contract and testing whether they detect plausible failures.

1. Check the grain and key

Start by stating the expected output grain. If the result is one row per order, test that order_id is unique at that stage:

SELECT order_id, COUNT(*) AS rows_per_order
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

The fixture returns no exceptions. Repeat an equivalent check after transformations that could change the grain, especially joins.

A distinct order count alone is insufficient. A joined result can contain every correct order ID while repeating each amount across several item rows. Compare physical row count with unique keys and inspect the measure's natural grain.

2. Reconcile the eligible population

SELECT status, COUNT(*) AS orders,
       SUM(order_total_paise) AS value_paise
FROM orders
GROUP BY status
ORDER BY status;

The fixture contains one cancelled order worth 15,000 paise, eight completed orders worth 104,000 and one pending order worth 5,000. This explains the eligible population instead of showing only the final filter result.

When customer details are added, check unmatched keys separately. O1009 remains an eligible completed order even though C999 is missing from the customer table. An INNER JOIN would reduce the population to seven and the value to 95,000; that is a change the check should reveal.

3. Use an independent control calculation

The header total and item-line total should agree under this fixture's contract:

SELECT SUM(i.line_total_paise) AS completed_item_value_paise
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed';

Expected: 104,000 paise. This uses item-level amounts rather than repeatedly summing the header after a join.

For diagnosis, reconcile per order as well as in aggregate. Two opposite errors can cancel in a grand total. Also understand the limits of independence: if both source columns were generated by the same faulty upstream logic, agreement does not prove that the business event was recorded correctly.

A useful control is independent enough to catch the failure you are concerned about, not simply a second query that repeats the same expression.

4. Test boundaries and exceptional inputs

Choose cases that challenge the rule: exact date boundaries, zero denominators, NULL values, duplicate deliveries, unmatched references and multiple refunds.

For the January period, the fixture includes an order at the last second of January and another at the first instant of February. A half-open interval should include the former and exclude the latter. For discounts, two NULL values should remain distinguishable from five known zeros.

Do not rely exclusively on randomly selected “normal” records. Boundary cases are often where an otherwise plausible query disagrees with its business definition. The date-boundary lesson provides a concrete example.

5. Prove a check can detect a deliberate error

A test that always passes provides little confidence. In an isolated copy of the fixture, change O1001's header amount from 12,000 to 13,000 paise while leaving its item lines unchanged. The line-to-header reconciliation should identify O1001.

The lab runner performs this mutation after its reference queries and confirms that the mismatch is detected:

python 11-Blog-Programme/labs/commerce-sql/build_and_verify.py

It reports 20 reference-query checks and one deliberate-mutation check. The mutation occurs in an in-memory teaching database, not in production data or the saved raw CSV files.

This technique tests the usefulness of a control. It does not prove that the control detects missing customers, duplicate events or every possible monetary error; those require their own checks.

Save a compact verification record

The lab writes its actual results to verification-results.json. A portfolio project can use the same principle: make the evidence inspectable instead of asking an interviewer to trust a screenshot of a successful notebook.

SQL engine documentation helps establish expression and join behaviour, while the business contract establishes what should be calculated. Neither substitutes for the other. SQLite SELECT, aggregate functions.

Exercise: intentionally replace the customer LEFT JOIN with INNER JOIN, then show which checks detect the lost order. Next, replace a sum with SUM DISTINCT and show which control detects the missing legitimate amounts. A strong answer explains the failure mechanism, not only the corrected number.

These habits fit the SQL, pipeline and AI-verification work in NeuraPath's Data Analytics with Generative AI programme. They give you a repeatable way to challenge a query before its output becomes a business recommendation.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the preceding task in Build a SQL data dictionary from a reporting question.
• Return to the cluster foundation in SQL row grain: stop double-counting orders before you query.