DEV Community

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

Collapse
 
sgaggjhkjh profile image
Arjun Sharma •

Checking the business key instead of the surrogate ID is the check I would keep first, because a duplicate order number directly inflates revenue. Do you also run these checks after each data refresh during UAT, or only once before sign-off, since a late reload could reintroduce the same duplicates?

Collapse
 
prachitparikh profile image
Prachit Parikh •

Every refresh, for the cheap ones. A late reload is exactly when duplicates sneak back in, so the duplicate business-key check and the row-count reconciliation should rerun after each load. The full set is the gate before sign-off.

Collapse
 
sgaggjhkjh profile image
Arjun Sharma •

That makes sense, and treating the full set as a gate is the part I would keep. Do you also log which load each rerun covered, so a later reviewer can tell whether a clean result came from the latest data or an earlier snapshot?

Thread Thread
 
prachitparikh profile image
Prachit Parikh •

Good call, and I'd make it a column rather than a note. Each result row gets the load ID or the latest load timestamp it ran against, next to pass/fail. Then 'clean' always means 'clean as of that load', and a reviewer can see if a reload happened after the last green run. The results log in my PDF of the checks has Expected / Actual / Pass columns; I'd add 'Load covered' as the first one.

Thread Thread
 
sgaggjhkjh profile image
Arjun Sharma •

A column is stronger than a note, because it forces every rerun to state what it covered. I would also keep the previous green run visible, so a reviewer can see at a glance whether a new load changed anything or only confirmed the old result. Would you treat a load that only fixes one check as a full new sign-off, or keep it as a partial update?

Thread Thread
 
prachitparikh profile image
Prachit Parikh •

Full set, every time. A fix aimed at one check can move another, say a dedup step that also changes the row count a reconciliation check depends on. So the rerun is a full run, but the sign-off record names the check that triggered the reload and keeps the previous green run beside it, the way you described. A reviewer then sees both: everything passed, and here's what changed since last time.

Thread Thread
 
sgaggjhkjh profile image
Arjun Sharma •

That pairing of the full rerun with the triggering check named alongside the previous green run makes the audit trail much clearer. Do you also keep the diff between the two runs, or is the check name plus the two result sets enough for a reviewer to spot what changed?

Thread Thread
 
prachitparikh profile image
Prachit Parikh •

I'd keep the diff, but a small one: per check, the previous result, the new result, and how many rows moved. Two full result sets make the reviewer compare by eye, and that's where a change slips past. If a check flipped from fail to pass, the diff row names the fix that did it. If it flipped the other way, that's the first line anyone reads.