The July roll return: cleaning 14 spreadsheets in nine days
Te Kura o te Awa is a Year 7–13 school with 1,140 students. Its enrolment data lives in 14 linked spreadsheets maintained by three staff. A student can appear in the current-roll sheet, the option-subject sheet and the bus-route sheet under slightly different names (‘Ana Ngata’ versus ‘Ana N. Ngata’), and 61 records have no caregiver email. The school must submit its July roll return in nine school days. The board has approved moving to a hosted relational database, but the vendor's import tool rejects any row that fails a foreign key to the caregiver table. The business manager wants to ‘import what we have and clean it up later’, while the deputy principal worries the roll return will be wrong. There is no budget for a data-cleaning contractor, and the three staff still have their normal duties.
1. Sort the data-quality problems in this dataset into distinct types, and recommend an order of operations for the nine days. Justify the order rather than just listing steps.
Read the worked answer
Three different problems are being treated as one.
- Entity resolution: one student appearing three times under three spellings is not a typo problem, it is the absence of a stable key.
- Missing attribute: 61 rows with no caregiver email is a completeness gap, not a duplicate.
- Constraint mismatch: the vendor's caregiver foreign key is a rule in the target schema that the source data has never had to satisfy.
Order, and why that order. (1) Freeze a read-only copy and profile all 14 sheets against one student identifier (a school or Ministry ID if one exists). You cannot clean what you cannot count, and every later step depends on the true roll. (2) Resolve identities with deterministic rules first (ID match, then exact name plus date of birth), and send only the shortlist to human review - likely tens of rows, not hundreds. (3) Only then decide the caregiver schema question.
Why not start with caregiver emails? The roll return reports student identity and status, not caregiver contact details. Spending the scarcest resource (three staff with full teaching loads) on the wrong risk is how the return gets missed anyway.
Why not import first? Merging duplicate people inside the system of record cascades into attendance, options and results. Deduplicating in a spreadsheet is cheap and reversible; unwinding a bad merge in a database is neither.
2. Evaluate the business manager's proposal to ‘import what we have and clean it up later’. Reach a recommendation.
Read the worked answer
Separate reversible risk from irreversible risk, then check what the deadline actually requires.
Irreversible. A merged student record inside a live database touches attendance, option subjects and results; unpicking it is expensive and may not be possible if the source rows are gone. A fabricated ‘Unknown Caregiver’ row created to satisfy the foreign key becomes a permanent entity that will be mailed, counted in reporting, and linked to unrelated students by any privacy request.
Reversible. Staging imports, flags, spreadsheet-level cleaning, a second pass in a fortnight.
The hidden flaw in the proposal. The import tool rejects rows failing the caregiver key, so ‘import what we have’ is not actually available. The only way to execute it is to invent caregiver rows - which means the proposal silently makes the data wrong rather than merely late.
But blanket refusal carries its own risk: a late July return has funding and audit consequences.
Recommendation - a staged middle path. Import into staging tables with no foreign key enforcement, run identity resolution there, then promote clean rows to production. If the vendor will not allow staging, import only the student table this month and leave caregiver, options and bus data in spreadsheets until cleaned. Decision rule: import what the deadline requires, into a place where bad data cannot cascade.
3. What should happen to the 61 records with no caregiver email? Give a decision rule, and explain why the obvious alternatives are worse.
Read the worked answer
First split the 61 into two genuinely different cases: the caregiver is truly absent (deceased, student independent, court order, protected contact) or merely unrecorded (the details exist somewhere else). Checking the emergency-contact sheet and the original enrolment forms probably moves a large share of the 61 into the second bucket, which means the problem is smaller and more tractable than it looks.
Decision rule. (a) Search other sources first. (b) Where genuinely absent, store NULL and make the foreign key nullable from day one. (c) Attach a status flag, an owner and a due date to each record. (d) Collect the missing details through the normal enrolment-update process, not under deadline pressure.
Why not a placeholder caregiver row? Sixty-one distinct people collapse into one entity. If a real caregiver has two children at the school, that person is then linked to sixty unrelated students, and every mailout, report and access request leaks.
Why not simply leave the field blank and move on? The school needs a contact for attendance and emergencies, so the gap is real - it just should not be solved by inventing data. The schema permitting NULL is the precondition: choose it now, or the placeholder becomes permanent.