Protocol
Reconcile what is missing and what should never have arrived
Everything expected can be present while the result is still wrong.
When it fits
- A source-to-target comparison reports no missing records, but the target may contain extras.
When to avoid it
- A target may legitimately contain records outside this run. Define scope before treating all unmatched target rows as defects.
Why it matters
Compare the expected and actual populations in both directions. Expected minus actual reveals omissions; actual minus expected reveals unexpected additions. Use the same identity definition and cutoff in both comparisons, then inspect duplicates separately when repeated occurrences matter.
Steps
- Freeze the expected population and the matching target scope.
- Produce separate lists of missing and unexpected identities.
- Compare occurrence counts or duplicate-sensitive rows when identity membership alone is insufficient.
An example
All 80 intended partners arrived, but 12 unintended partners arrived too. A missing-only check would have declared success.
Check your result
The reconciliation reports omissions, extras and relevant multiplicity differences, not one reassuring percentage.
Keep this limit in mind
- A target may legitimately contain records outside this run. Define scope before treating all unmatched target rows as defects.
Connected ideas
Useful withMake every selected record end in an explicit status bucket
Evidence and sources
EXCEPT returns rows in the first query but not the second, and removes duplicates unless ALL is specified.
A one-direction difference cannot also identify unexpected target rows; distinct-set comparisons can hide repeated occurrences.
Combining Queries: UNION, INTERSECT, EXCEPT · Section 7.4