7 SQL checks I run before UAT sign-off (with queries you can copy)

Prachit Parikh — Mon, 02 Nov 2026 02:45:46 +0000

The hour I trust least on any project is the one right before UAT sign-off. The testers have clicked through every screen, the defect board is mostly green, and someone is waiting for a signature. Screens can look perfect while the data underneath is wrong, though, and nobody finds out until the first month-end report goes to finance.

So before I sign anything as the business analyst, I run a short set of SQL checks against the test environment. It takes about an hour. Those checks have caught problems that weeks of manual testing walked right past: a load that silently dropped 2% of rows, a currency field that doubled on one branch, customers with orders but no customer record.

Here are the seven checks, with generic queries you can adapt. The table names are made up, so swap in your own.

Why a business analyst should write SQL at all

You don't need to be a data engineer. You need to be able to answer one question without waiting on anyone: "Is the data in the new system what the business expects?" Developers test that the code does what the spec says. A BA's job is to check that the spec, and the data it produces, matches what the business needs. SQL is the fastest way to do that yourself.

If you can write SELECT, GROUP BY, JOIN and CASE, you can run every check below.

1. Row counts: did everything arrive?

Start with the dumbest possible question. If the source had 48,210 orders for a day, does the target have 48,210?

SELECT 'source' AS side, COUNT(*) AS row_count
FROM staging.orders
WHERE load_date = '2026-10-30'
UNION ALL
SELECT 'target', COUNT(*)
FROM dw.fact_orders
WHERE load_date = '2026-10-30';

If the numbers differ, ask why before anything else. Sometimes there's a good reason (test orders filtered out on purpose). Often there isn't.

2. Totals that should tie out

Counts can match while amounts don't. Sum the money by a period the business cares about and compare.

SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(order_amount) AS total_amount
FROM dw.fact_orders
GROUP BY 1
ORDER BY 1;

Run the same query on the source or the legacy report. Put the two side by side in Excel with a difference column. Any month that doesn't tie to the cent gets a defect.

3. Duplicates on the business key

Duplicates are the classic silent killer. One order counted twice inflates revenue and nobody notices until an auditor does.

SELECT order_number, COUNT(*) AS copies
FROM dw.fact_orders
GROUP BY order_number
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Use the key the business uses, not the surrogate ID the database made up. An order number, invoice number or account number is what people will actually reconcile against.

4. Orphans: records pointing at nothing

Every order should belong to a customer that exists. Every transaction should belong to an account that exists.

SELECT o.order_number, o.customer_id
FROM dw.fact_orders o
LEFT JOIN dw.dim_customer c
       ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

Orphans usually mean the loads ran in the wrong order or a lookup failed. Reports built on an inner join will quietly drop these rows, which is how totals go missing.

5. Nulls in fields the business depends on

Go back to the requirements. Which fields did stakeholders say were mandatory? Check them.

SELECT COUNT(*) AS total_rows,
       SUM(CASE WHEN region IS NULL THEN 1 ELSE 0 END) AS missing_region,
       SUM(CASE WHEN product_code IS NULL THEN 1 ELSE 0 END) AS missing_product,
       SUM(CASE WHEN order_amount IS NULL THEN 1 ELSE 0 END) AS missing_amount
FROM dw.fact_orders;

A dashboard filtered by region will hide every row with a null region. That's a requirement failure even if no screen ever throws an error.

6. Values outside the allowed list

If the requirement says status can only be Open, Shipped, Cancelled or Returned, prove it.

SELECT status, COUNT(*) AS rows_with_status
FROM dw.fact_orders
WHERE status NOT IN ('Open', 'Shipped', 'Cancelled', 'Returned')
   OR status IS NULL
GROUP BY status;

This one finds mapping gaps fast. A legacy code like "SHP" that nobody mapped will show up here instead of in a confused user's email three weeks after go-live.

7. Trace five real records end to end

Not every check is a query. Pick five records the business knows well: a big customer, a refund, an order that changed status twice, something from the last day of a month. Follow each one from the source screen, through the tables, to the final report. Write down what you see at every step.

Queries tell you the data is consistent. Tracing real records tells you it means what people think it means.

How I log the results

I keep one simple table per test cycle:

Check	Query saved as	Expected	Actual	Pass/Fail	Defect
Row count 30 Oct	rc_orders.sql	48,210	48,210	Pass
Duplicates	dup_orders.sql	0	14	Fail	DEF-212

Each row links back to a requirement in the traceability matrix, so when someone asks "did we test that?", the answer is one click away. It also makes the sign-off conversation short: here's what we checked, here's what passed, here are the open defects and their severity.

When a check fails

Don't sign off with a known data defect and a promise to fix it later. Raise it with the exact query attached, the expected result, and the actual result. Developers can rerun it in seconds, and nobody has to argue about what "the numbers look off" means.

Then agree on severity with the business owner, not alone. A 14-row duplicate in a test table might be minor. A 14-row duplicate in a revenue report is not.

The one-page version

Before you sign UAT, confirm:

Row counts match between source and target for at least two load dates
Money totals tie out by month
No duplicates on the business key
No orphan records on any key relationship
Mandatory fields have no nulls
Coded fields only contain allowed values
Five real records traced end to end
Every failure has a defect with the query attached

That's an hour of work that protects weeks of everyone else's.

About me

I'm Prachit Parikh, a business analyst. I turn what stakeholders ask for into requirements a team can test, check the data with SQL, and see changes through UAT. I keep free templates, including these SQL checks, in my BA Toolkit on GitHub, and I make short animated explainers on YouTube. Besides Dev.to, I write on Substack, Medium, Hashnode, Blogger and WordPress, and I answer questions on Quora. You can also find me on LinkedIn, Bluesky, Kaggle, Credly, Crunchbase and Gravatar. Everything is collected on my website and my profile hub.

What check would you add to this list? I'd like to hear the one that saved you on a project.

DEV Community: Prachit Parikh