Capstone: Data Integration Challenge
Complete a timed, independent assignment to reconcile inconsistent customer and work-order exports in a local review dashboard. Implement traceable reconciliation, retain unresolved records for review, and handle invalid inputs safely. Evaluate your submission against the rubric before reviewing the annotated reference solution.
In this capstone, you’ll build a complete take-home solution for a real-style customer request. Here’s the brief from the customer:
“I compare the customer export with the work-order export by hand. Give me a local, read-only dashboard where I can review matched open work by customer and investigate open work that can’t be safely matched.”
Service operations coordinator
Deliverables and timing rules
Start-of-attempt notice: Opening the full brief and accompanying files starts the 240-minute active-work limit. Do not open them until you are ready to begin. Reading, implementation, testing, documentation, and recording all count toward this limit.
This fictional course assignment is separate from the other capstone and does not represent an employer's assessment. You may use standard documentation and software libraries. During the independent first attempt, do not use AI assistance for coding or writing, implementation help from others, or annotated model solutions.
Clarification is unavailable in this self-directed exercise. Document unresolved assumptions and questions without changing or overriding any explicit requirements.
Full delivery brief
Build a repeatable ingestion-and-reconciliation pipeline and a runnable local dashboard for the coordinator. Deliver:
Matched open work grouped by customer, with identifiable work-order details.
Visible unresolved and invalid records, including reasons and source references.
Complete input accounting, with customer exceptions, closed work, and suppressed duplicates still inspectable.
Focused tests and an end-to-end smoke test.
Documented prerequisites and a one-command execution route from a clean clone, without hidden local state or embedded credentials.
A README covering usage, reconciliation choices, assumptions, limitations, and safe failure behavior.
Actual time and assistance declarations, including unmet requirements.
An accessible, playable recorded walkthrough shorter than five minutes.
Both inputs are synthetic local exports. No API, live service, write-back, cloud deployment, authentication system, or LLM is required. Choose familiar tools. Charts, hosting, fuzzy matching, and additional services are outside the required MVP.
Save the brief as data_integration_brief.md and the following files under their exact names. Keep this assignment independent of earlier examples.
Input files and rules
customer_export.csv
customer_id,customer_nameC01,North Clinic" c01 "," North Clinic "C02,East ClinicC03,Hill ClinicC03,Ridge ClinicC04,,West ClinicC05,Lakes Clinic
The input file contains eight customer records. The header is on physical line 1, followed by eight records on lines 2–9. Quotation marks delimit fields and are not part of the values.
work_order_export.csv
work_order_id,customer_id,statusW101,C01,openW102," c02 "," OPEN "W103,C03,openW104,C99,openW105,,openW106,C05,closedW107,C01,openW107," c01 "," open "W108,C01,openW108,C02,openW109,C05,pendingW110,C04,open
There are twelve work-order records, occupying physical lines 2–13.
Supplied reconciliation requirements
Use these customer requirements to determine expected behavior before implementing the solution.
Normalization: Remove surrounding whitespace from identifiers, then convert them to uppercase. Trim surrounding whitespace from customer names without changing their spelling. Trim and lowercase status values. Only
openandclosedare valid statuses. ...