Key takeaway
A larger joined table can describe the same work repeatedly. Reconcile unique business units and relationships before presenting its row count.
Define what one row is allowed to mean
A work order, a repair attempt and a part consumption are different units. Joining them by job number can produce a tidy table whose rows represent combinations that never happened. Before counting records in a proposed package, write its grain in one sentence: one row per job, per action, or per part event. If a row means a job-action-part combination, explain why that combination is meaningful.
The pandas merge reference provides explicit key-uniqueness validation for one-to-one, one-to-many and many-to-one joins. Its many-to-many option allows the operation without those checks. It also warns that null keys match each other, unlike usual SQL behavior. The lesson for an export owner is to test the actual engine and declared relationship, rather than assume a successful join validates either.
Reconcile a small join before running the archive
In this hypothetical maintenance export, job J1 has two repair actions and three part events. Both child tables use only the job identifier as their join key. Joining the children on that key yields six action-part pairs. The source records do not say which part belongs to which action. Calling the result six completed repairs would create a new assertion.
Job J2 has one action and no part event. An inner join drops it; a left join can retain the action with missing part fields. Neither choice is inherently the correct business package. The declared question decides whether a job without parts belongs in scope, and the release notes must describe the choice.
PostgreSQL’s joined-table documentation likewise describes one row for every matching pair, and a left outer join retains unmatched left rows with null right-side fields. Check the actual query, including conditions applied after the join. A later filter on right-side fields can remove the unmatched rows that the left join initially preserved.
| Business unit | Source children | Joined result | Review decision |
|---|---|---|---|
| J1 | 2 actions; 3 parts | 6 possible pairs | Keep separate child tables; no invented action-part link |
| J2 | 1 action; no part | 0 inner-join rows | Retain job/action if within approved scope |
| Missing job key | 2 actions; 2 parts | Possible 4 null-key pairs in pandas | Quarantine unresolved keys; do not manufacture a job |
Carry separate denominators through the review
Record source jobs, source actions, source part events, distinct linked jobs, output rows and unmatched children separately. An output-row count should never silently replace a business-unit count. For J1, six rows can be technically reproducible while one unique job remains the only defensible job count. If the receiving question needs action sequences, preserve action order and the relevant identifiers instead of flattening every dimension.
An unmatched child also needs a reason code. It may be outside the approved period, refer to a deleted parent, use a changed identifier format, or have an unknown origin. Those explanations lead to different next actions. Keep the exception list internally; an initial metadata description can state the number and nature of unresolved links without disclosing individual records.
For an inner join on a shared key, predict matched rows by multiplying the left and right counts for each key and adding those products. J1 contributes 2 × 3 = 6; J2 contributes 1 × 0 = 0. Compare that prediction with the actual output. Agreement proves multiplicity is explained; it still does not prove the pairs describe real action-part relationships.
A duplicate-looking row is not permission to delete
Two events with the same job number may be legitimate repeat attempts. Removing duplicates by job alone can erase the history the recipient intended to evaluate. Define the event identity using the source system and documented event identifier where available. If identifiers are not stable, record the proposed rule and inspect collisions before applying it.
Run the uniqueness checks on both sides before the merge. Then compare the expected multiplicity with the result and review unmatched records. Where a relationship is absent, retain that absence. A later interpretation can be stored as an annotation with its own origin; it should not be disguised as an original source link.
Approve an export with counts that can be reconstructed
The release decision for this example is to keep a job table and two child tables, with documented one-to-many relationships. The package would state one J1 job, two actions and three part events, rather than advertise six independent outcomes. A future action-part link would require separate source evidence and a versioned transformation decision.
Use the inventory tool to describe the grain, period, relationship keys and exception count. Ask the technical owner to retain the reconciliation query and output internally. A named metadata introduction remains separate from approving any real table for delivery. The useful handoff is a defensible unit of evidence, not an inflated count that someone else must later unravel.