Audit a two-table match before trusting its row count

Combining two collections can lose one source record and duplicate another while leaving the total number of rows unchanged. Inspect matches before trusting that total. This is a manual review of invented records in your usual shared document. It does not use actual account data, perform a Kiguri import, or claim an integration has been verified.

State the intended matching relationship

Use three synthetic requests: R1 refers to P1, R2 to P2, and R3 to P3. A separate profile collection has P1-old, P1-current, and P2-current. Each profile has a person identifier and a version, so the two P1 profiles are distinct valid records under that composite identity.

Suppose the intended result needs one current profile per request. Write that expectation before choosing a match key. Matching only person identifier ignores the version distinction. Individually valid inputs therefore do not guarantee a useful combined result. The missing P3 profile also needs an explicit treatment rather than disappearing by accident.

Enumerate matches for each request

Under a person-only match, R1 has two counterparts: P1-old and P1-current. R2 has one, P2-current. R3 has none. Record those cardinalities as two, one, and zero beside the original request identities.

Keep ambiguous and unmatched cases visible before flattening them into output rows. Choosing the first P1 profile is not a justified correction unless an agreed rule makes that choice meaningful. Similarly, an absent match is an observation, not proof that R3 is invalid or should be removed.

Compare output identities, not only counts

An inner-style combination retains only matches. It produces R1 with the old profile, R1 with the current profile, and R2 with its current profile: three output rows. The input also had three requests, so a total-row check appears reassuring.

But the output contains only R1 and R2. R1 is duplicated and R3 has vanished. A source-preserving combination would instead retain an unmatched R3 with an explicit missing counterpart, yielding four rows under the same person-only key. Neither result automatically meets the intended one-current-profile relationship. Count agreement can conceal compensating errors; a larger output can correctly expose ambiguity.

Clarify the rule without hiding the gaps

If the responsible owner confirms that current is the required profile version, filter counterparts by that condition before matching. R1 then has one current counterpart, as does R2. R3 remains unmatched.

Keep R3 in the source-preserving result with its missing-profile condition. That gives three request rows but still requires a decision or missing input. Do not replace unknown profile details with invented values to make the output look complete. A rule change needs a fresh check across all source identities, because it can resolve one ambiguous match while excluding another valid case.

Reconcile the result back to every source

Make a ledger with request identity, matching key, candidate counterparts, selected counterpart or unresolved condition, and output occurrence count. Ask a colleague to locate all three requests from the ledger without relying on the total number of rows.

A result passes the stated relationship only when the original identities and counterpart rules are accounted for. The fixture does not establish actual database correctness or data quality in another system. Its purpose is to make duplication, omission, and unresolved matches inspectable before a tidy row count is mistaken for preserved information.