The task and original case
Audit a join that attaches department names to employees without silently shrinking the workforce. The key result is the unresolved employee, not a perfectly filled table. This exercise produces a repair request that tells a data owner exactly what is missing.
| Case element | Value or requirement |
|---|---|
| Inputs | Employees E 1:D 1 and E 2:D 2; department dictionary contains only D 1. |
| Build | A join audit with unmatched keys, row counts and a repair request. |
This is an original invented exercise. It is independent practice, with no employer assessment or open-vacancy claim. Download the inputs, attempt your own result, then open the separate answer key.
Data and worked inputs
Build a result another person can follow
There are two employee records: E1 belongs to D1 and E2 to D2. The department dictionary contains only D1. Declare one employee per employee row and one department per dictionary row. Check those identities before merging.
Use employees as the left side of a left join. pandas documents left joins and indicator output; retain a match status or equivalent flag. Count employees before and after the merge, list unmatched department keys and check dictionary uniqueness. The dictionary supplies names, not permission to discard employees with incomplete labels.
Sources[1]
Check the worked result
The correct enriched table still has two employees. E1 matches D1; E2 remains with department ID D2 and an unresolved name. One of two employee records is unmatched. An inner join returns only E1, falsely making the population appear to contain one employee.
Your repair request should state the missing key D2, the affected employee count one, the source file and the expected dictionary schema. It should not invent a department name to make the report look complete.
Make the failure visible
Duplicate D1 in the dictionary. A left join can now increase rows even while it preserves unmatched employees; cardinality must be checked independently of join type. Add a blank department ID too, and treat it as a missing-key issue rather than an ordinary unknown code. Avoid collapsing all exceptions into “Other” unless that is a deliberately defined reporting category.
Revise it and ask someone to reproduce it
Have a peer add D2 to a revised dictionary and reproduce the merge. The employee population remains two while unresolved records fall to zero. Then ask them to introduce a duplicate D1 and confirm that the run flags it. Keep the original exception register beside the repaired result to show the actual change.
- Employee counts reconcile before and after enrichment.
- Unmatched keys remain traceable to source records.
- Dictionary duplicates are detected before row multiplication.
Describe the work accurately
After completing your own version, you could adapt this project bullet: “Audited an invented employee-to-department join, preserved the full population and documented missing and duplicate dictionary keys.”
Use Projects or Independent practice as the heading. Keep the input files, result and revision together. Describe only your own completed work; the invented numbers are model outputs, not employer results or measured business impact.