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

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:

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 write longer pieces on UAT planning and requirements on my Substack and practical BA notes on my blog. Everything else, including my background, is on my site. You can also find me on GitHub, Kaggle and LinkedIn.

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