SQL for QA & data validation

For QA engineers, SQL is not only a developer language. It is a way to inspect state independently of the UI/API, validate invariants, compare systems, and create evidence about data quality.

Validate API results against the database

A strong comparison starts by matching scope and transformations. If an API applies authorization, currency conversion, soft-delete filters, or timezone formatting, a raw table query is not automatically the expected result.

Use SQL to reproduce the server-side business scope as closely as possible, then compare stable identifiers and fields.

Find orphan references

The sample dataset deliberately contains an order that references a non-existent customer.

SELECT o.id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL
ORDER BY o.id;

Run the query to see the result.

This is a high-value integrity check where foreign keys are missing, disabled, deferred, or data arrived from external systems.

Check duplicates by business key

SELECT email, COUNT(*) AS duplicate_count
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC, email;

Run the query to see the result.

The challenge is selecting the business key, not writing GROUP BY.

Validate aggregate invariants

A payment/order invariant might require captured payment amount to equal order total. Join the relevant relations and isolate violations.

SELECT o.id AS order_id,
       o.total,
       p.amount AS payment_amount,
       p.status AS payment_status
FROM orders AS o
JOIN payments AS p ON p.order_id = o.id
WHERE p.status = 'captured'
  AND p.amount <> o.total
ORDER BY o.id;

Run the query to see the result.

A result of zero rows can be a useful assertion when the query explicitly describes invalid states.

Validate status transitions

Use LAG over event history to compare current and previous states. Then encode allowed transitions in CASE or join to a transition-rules table. This catches impossible state jumps that individual row checks cannot see.

SELECT entity_id,
       occurred_at,
       LAG(status) OVER (
         PARTITION BY entity_id
         ORDER BY occurred_at, id
       ) AS previous_status,
       status AS current_status
FROM events
ORDER BY entity_id, occurred_at, id;

Run the query to see the result.

Expected versus actual datasets

Normalize both sides to the same columns/types/grain before comparison. Then use joins or EXCEPT in both directions. Differences should be explainable: missing, extra, or mismatched.

Test-data cleanup safely

Never use broad DELETE statements simply because data is “test data.” Scope by tenant/environment/test-run identifier, inspect the candidate rows first, and respect relationships. In shared QA environments, accidental cleanup can destroy other teams' evidence.

Database assertions in automation

Database checks are valuable when they verify a behavior that cannot be observed reliably through the public interface, but overusing direct DB assertions can tightly couple tests to implementation. Prefer API/UI behavior for externally visible contracts and DB checks for persistence/integration invariants.

Practical SQL review checklist for QA

• Does the query use the same tenant/user/environment scope as the system under test?
• Is result grain explicit?
• Are NULLs handled intentionally?
• Are joins one-to-one, one-to-many, or many-to-many as expected?
• Could duplicate amplification invalidate counts/sums?
• Is ordering deterministic?
• Are timestamp/timezone boundaries correct?
• Does zero rows mean “pass,” or did the query accidentally filter everything out?

Practice: write three “should return zero rows” integrity queries against the sample database: orphan orders, captured-payment amount mismatch, and impossible/missing email rule of your choice.