solid checklist. i still would not sign off on a total that never said what one row means.
order versus order line sounds pedantic until a join quietly multiplies the sum. nulls and duplicates can all pass while the grain is wrong. naming that grain before uat, then sitting a few rows at that level next to the aggregate, caught more bad sign offs for us than another null scan did.
Agree, and I'd put the grain above all seven. One line at the top of the test plan, "one row = one order line," settles half the arguments before they start. Putting a few rows next to the total is a good habit too. I made a short video on exactly this, one join quietly inflating a sum: youtube.com/watch?v=SG-6rtZeizg
putting the grain first is exactly what i wish more plans opened with.
one line like 'one row = one order line' at the top probably settles more fights than another null scan. what worked for a teammate was forcing that sentence into the PR description before anyone looked at the seven checks. then sitting a few rows at that grain next to the total made the quiet join blowups obvious. i'll check your video on the inflated sum, that failure mode is the one i keep underestimating.
Putting it in the PR description is a smart forcing function, because it's the one place nobody skips. I'm stealing that. The same line works as a UAT entry criterion: no grain sentence, no testing starts. Hope the video is useful, and tell me if the fan-out example looks like the blowups you've seen.
Making the grain sentence a hard entry gate is sharper than leaving it as advice.
We tried the soft version first and people still skipped it under deadline. Once testing could not start without that one line in the PR, the fan-out cases showed up earlier, usually as an order-line total that quietly matched invoice count. Your video's blowup shape is the same one we keep walking into.
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?
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.
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?
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.
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?
Pre-UAT checks are most valuable when they encode the business invariants behind the tables. Alongside counts and null checks, reconcile control totals to the source system, test idempotent reruns, and sample the newest boundary records. Those catch the quiet data-shape failures that a happy-path UI run can miss.
Idempotent reruns is a great add. Load the same file twice and the totals shouldn't move. If they do, you've found the duplicate problem before a user does. And "control totals" is a phrase finance teams already use, so it lands well in a sign-off meeting.
Thanks, that's a smart shortcut. If the orphans all cluster after one load, the load order is the first suspect, and you skip checking them one by one. Adding a created_at group-by to the orphan query is an easy way to see it.
Great checklist! Data validation is just as important as application testing, especially before UAT sign-off. I'd also consider adding automated data quality checks to catch inconsistencies early.
Agreed. Once a check is a query that only returns rows when something's wrong, it's easy to schedule and alert on, so it runs on every load and not just once before sign-off.
We're a place where coders share, stay up-to-date and grow their careers.
solid checklist. i still would not sign off on a total that never said what one row means.
order versus order line sounds pedantic until a join quietly multiplies the sum. nulls and duplicates can all pass while the grain is wrong. naming that grain before uat, then sitting a few rows at that level next to the aggregate, caught more bad sign offs for us than another null scan did.
Agree, and I'd put the grain above all seven. One line at the top of the test plan, "one row = one order line," settles half the arguments before they start. Putting a few rows next to the total is a good habit too. I made a short video on exactly this, one join quietly inflating a sum: youtube.com/watch?v=SG-6rtZeizg
putting the grain first is exactly what i wish more plans opened with.
one line like 'one row = one order line' at the top probably settles more fights than another null scan. what worked for a teammate was forcing that sentence into the PR description before anyone looked at the seven checks. then sitting a few rows at that grain next to the total made the quiet join blowups obvious. i'll check your video on the inflated sum, that failure mode is the one i keep underestimating.
Putting it in the PR description is a smart forcing function, because it's the one place nobody skips. I'm stealing that. The same line works as a UAT entry criterion: no grain sentence, no testing starts. Hope the video is useful, and tell me if the fan-out example looks like the blowups you've seen.
Making the grain sentence a hard entry gate is sharper than leaving it as advice.
We tried the soft version first and people still skipped it under deadline. Once testing could not start without that one line in the PR, the fan-out cases showed up earlier, usually as an order-line total that quietly matched invoice count. Your video's blowup shape is the same one we keep walking into.
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?
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.
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?
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.
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?
Pre-UAT checks are most valuable when they encode the business invariants behind the tables. Alongside counts and null checks, reconcile control totals to the source system, test idempotent reruns, and sample the newest boundary records. Those catch the quiet data-shape failures that a happy-path UI run can miss.
Idempotent reruns is a great add. Load the same file twice and the totals shouldn't move. If they do, you've found the duplicate problem before a user does. And "control totals" is a phrase finance teams already use, so it lands well in a sign-off meeting.
running the checks before sign-off beats arguing about the numbers after. the copy-paste queries help.
That's the whole idea. A few queries before sign-off cost a lot less than a week of debating numbers after it. Glad the copy-paste versions help.
第 4 条 orphan 检查我想补一个细节：除了 LEFT JOIN 找孤儿，最好顺手看一眼孤儿记录的产生时间分布——如果都集中在某一次 load 之后，基本可以断定是那次的加载顺序错了，比逐条排查快得多。清单本身很实用，这套流程可以直接抄走用。
Thanks, that's a smart shortcut. If the orphans all cluster after one load, the load order is the first suspect, and you skip checking them one by one. Adding a created_at group-by to the orphan query is an easy way to see it.
Great checklist! Data validation is just as important as application testing, especially before UAT sign-off. I'd also consider adding automated data quality checks to catch inconsistencies early.
Agreed. Once a check is a query that only returns rows when something's wrong, it's easy to schedule and alert on, so it runs on every load and not just once before sign-off.