Quick summary
Summarize this blog with AI
A SQL query can run successfully and still answer the wrong question. A plausible total is especially unhelpful: one missing customer and one duplicated order can cancel each other out. To test a query, write down the expected rows independently, run the transformation on a small dataset, and compare the complete result in both directions.
This guide builds a runnable PostgreSQL test using only VALUES and common table expressions. It creates no tables and changes no data. You will test a monthly customer report, deliberately break it, and turn the checks into a repeatable review process.
Separate three questions that need different tests
Query validation has three layers. A small fixture checks whether the SQL implements a rule. Checks against source data establish whether the real inputs satisfy the assumptions. A business review confirms that the rule answers the intended question. Passing one layer does not establish the other two.
| Layer | Example question | Useful evidence |
|---|---|---|
| Transformation logic | Are customers with no paid orders retained? | A fixture with an expected zero row |
| Input quality | Is there actually one customer row per customer ID? | A uniqueness check on the relevant source population |
| Business meaning | Should this report include refunds or unassigned orders? | An approved definition and reviewed examples |
An execution plan answers a different question: how the database intends to run the SQL. It can reveal expensive work, but it cannot decide whether your definition of a paid order is right.
Write the expected result before writing the query
Our example contract is deliberately specific:
- Return one row per region present in the customer table, including regions with no qualifying orders.
- Count paid orders placed during August 2026 in UTC: include August 1 at midnight and exclude September 1 at midnight.
- Sum the order amount once per qualifying order. Amounts represent one currency and are non-null in valid input.
- Exclude cancelled orders and orders whose status is unknown.
- Orders without a matching customer do not appear in the regional result; report them separately as a quality exception.
- Assume unique, non-null order IDs and customer IDs. Validate those assumptions outside the transformation.
For this fixture, West has three paid orders worth 160, East has one worth 40, and North has none. A paid order worth 60 belongs to an unknown customer. That 60 must appear in the exception investigation, not silently disappear from the reconciliation.
Run a complete expected-result test
Paste the following into a PostgreSQL query editor. A passing test returns zero rows. A failure returns the unexpected or missing rows, with a label telling you which side they came from.
WITH
customers(customer_id, region) AS (
VALUES (1, 'West'), (2, 'East'), (3, 'West'), (4, 'North')
),
orders(order_id, customer_id, ordered_at, status, amount) AS (
VALUES
(101, 1, TIMESTAMPTZ '2026-08-05 12:00:00+00', 'paid', 100::numeric),
(102, 1, TIMESTAMPTZ '2026-08-10 12:00:00+00', 'paid', 50::numeric),
(103, 2, TIMESTAMPTZ '2026-08-12 12:00:00+00', 'paid', 40::numeric),
(104, 2, TIMESTAMPTZ '2026-08-15 12:00:00+00', 'cancelled', 90::numeric),
(105, 1, TIMESTAMPTZ '2026-09-01 00:00:00+00', 'paid', 80::numeric),
(106, 1, TIMESTAMPTZ '2026-07-31 23:59:59+00', 'paid', 70::numeric),
(107, 99, TIMESTAMPTZ '2026-08-16 12:00:00+00', 'paid', 60::numeric),
(108, 2, TIMESTAMPTZ '2026-08-17 12:00:00+00', NULL, 25::numeric),
(109, 1, TIMESTAMPTZ '2026-08-01 00:00:00+00', 'paid', 10::numeric)
),
actual AS (
SELECT
c.region,
COUNT(o.order_id) AS paid_orders,
COALESCE(SUM(o.amount), 0::numeric) AS paid_amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'paid'
AND o.ordered_at >= TIMESTAMPTZ '2026-08-01 00:00:00+00'
AND o.ordered_at < TIMESTAMPTZ '2026-09-01 00:00:00+00'
GROUP BY c.region
),
expected(region, paid_orders, paid_amount) AS (
VALUES
('East', 1::bigint, 40::numeric),
('North', 0::bigint, 0::numeric),
('West', 3::bigint, 160::numeric)
),
unexpected AS (
SELECT region, paid_orders, paid_amount FROM actual
EXCEPT ALL
SELECT region, paid_orders, paid_amount FROM expected
),
missing AS (
SELECT region, paid_orders, paid_amount FROM expected
EXCEPT ALL
SELECT region, paid_orders, paid_amount FROM actual
)
SELECT 'unexpected' AS difference, * FROM unexpected
UNION ALL
SELECT 'missing' AS difference, * FROM missing
ORDER BY difference, region;
To inspect the report itself, keep the CTE definitions through expected and replace the remaining comparison CTEs and final query with SELECT * FROM actual ORDER BY region;. The result should match the three expected rows above.
The order filters belong in ON here because the contract retains every customer region. Moving those filters into WHERE removes the null-extended rows for customers without qualifying orders. Also, COUNT(o.order_id) counts matched orders; COUNT(*) would count the placeholder row retained by the outer join.
The amount column uses exact decimal arithmetic. The zero substitution is justified by the report's empty-group rule. It is not permission to replace missing amounts on real orders with zero: those are invalid inputs requiring a separate check.
Why compare both directions with EXCEPT ALL?
Checking only actual-minus-expected misses expected rows that never appeared. Checking only expected-minus-actual misses extra output. Use both directions.
PostgreSQL's EXCEPT removes duplicates unless ALL is specified. That means plain set comparison can overlook an extra copy of an otherwise correct row. This tiny example demonstrates the difference:
WITH actual(region, amount) AS (
VALUES ('West', 160), ('West', 160)
), expected(region, amount) AS (
VALUES ('West', 160)
)
SELECT region, amount FROM actual
EXCEPT ALL
SELECT region, amount FROM expected;
-- One extra ('West', 160) row is returned.
Choose the comparison columns explicitly, in the same order, with compatible types. Row order is not part of this test; if ordering is a promised interface, test it separately. For null-sensitive scalar comparisons, PostgreSQL provides IS DISTINCT FROM; ordinary <> does not flag every difference involving null. These behaviors are documented in PostgreSQL's set-operation reference and comparison reference.
Prove that the test can catch a mistake
A test that has only passed is weak evidence. Make one intentional change at a time, observe a failure, and restore the original query.
| Deliberate defect | Expected consequence |
|---|---|
| Change LEFT JOIN to INNER JOIN | The North row disappears. |
| Remove the paid-status condition | East incorrectly includes the cancelled and unknown-status orders. |
| Change the upper bound from < to <= | The September 1 order adds 80 to West. |
| Replace COUNT(o.order_id) with COUNT(*) | Unmatched customers are counted as orders. |
| Remove COALESCE around SUM | North returns a null amount instead of zero. |
This exercise tests the test's sensitivity. It does not exhaust every possible bug. Add cases when the contract gains a new rule or a real defect reveals a missing scenario.
Check assumptions against real inputs
Small fixtures cannot prove that tomorrow's source data will remain valid. On an approved reporting dataset, check key uniqueness and nullability, allowed statuses, unmatched foreign keys, and source completeness. Use the same reporting period as the report where appropriate; a key assumed unique across an entire dimension needs a check at that scope.
The following standalone example shows how a key check should expose both duplicate and null IDs:
WITH customer_keys(customer_id) AS (
VALUES (1), (1), (2), (NULL::integer)
)
SELECT customer_id, COUNT(*) AS row_count
FROM customer_keys
GROUP BY customer_id
HAVING customer_id IS NULL OR COUNT(*) > 1
ORDER BY customer_id NULLS LAST;
-- Returns ID 1 with two rows and NULL with one row.
For our full fixture, the August paid-order control total is 260 across five orders. The matched regional output is 200 across four orders, and the unmatched-customer exception is 60 across one order. Those populations reconcile exactly. Comparing 260 directly to 200 without accounting for the documented exclusion would report a misleading failure.
Do not automatically deduplicate an invalid dimension to make the test pass. Multiple rows may represent legitimate history, in which case the join needs effective-date logic. Or they may represent a source defect. Identify which before changing the query.
Turn the checks into a release gate
- Save the business rule, fixture, expected rows, and query together in version control.
- Run the fixture test after every relevant SQL change. Treat returned differences as a failed check.
- Run input checks before publishing the real output. Define which failures block release and which require review.
- Reconcile independent control totals at the same grain, date range, currency, and source snapshot.
- Record the query version, parameter values, source batch, and check outcomes so someone else can reproduce the run.
A database client returning exit code zero only means the test query executed. Your runner must inspect the result: a nonzero difference count should fail the job. A timeout, permission error, or missing expected file must also fail visibly; none of those means zero differences.
For a changing database, two separate reads may see different committed data. A short, read-only repeatable-read transaction can provide a stable PostgreSQL snapshot when your environment permits it; a fixed source batch is another option. Neither mechanism proves that upstream ingestion is complete. See the PostgreSQL isolation documentation for snapshot behavior.
Keep production checks proportionate. Start with a bounded reporting partition and agreed controls, and coordinate broader scans with the data owner. Read-only SQL can still consume substantial resources.
FAQ
Is comparing row counts enough?
No. Two results can have the same number of rows and different customers, amounts, or classifications. Counts are useful controls; expected-row comparisons and business-rule checks provide different evidence.
Should I calculate expected results with a second copy of the same SQL?
For a small fixture, work out the expected values by hand. A second query that copies the original joins and filters can copy the same mistake. At larger scale, use an independently understood source total and reconcile exclusions explicitly.
Do I need a testing framework?
No framework is needed to start with these read-only SQL checks. If your team already uses a testing system, add fixtures and assertions there so failures participate in the existing release process.
What if values are floating point?
Use a documented absolute or relative tolerance suited to the metric, and compare keys separately from values. Do not apply an arbitrary tolerance to hide unexplained differences. Exact decimal amounts in this example require exact agreement.
Related reading
Use the guide to join multiplication to diagnose cardinality failures, and the metric reconciliation playbook when two existing reports disagree. This tutorial supplies the small expected-result tests you can keep after that investigation is finished.
