03
COLLEGE PREDICTOR DATA VALIDATION
Validated the ranks, counts and field mappings behind a student-facing prediction tool by recomputing every aggregate from source rather than trusting what the application had stored.
- Role
- Careers360
- Stack
- SQL · MySQL · Python
- Year
- 2026
- Status
- In production
Context
A prediction tool students use to judge where they stand. Behind one number on screen sit several layers: entries that record a usage event, per-user rank and count aggregates built from them, a mapping layer that decides which inputs feed which prediction, and an entitlement layer that decides whether this user was allowed the attempt at all.
The failure mode here is worse than an outage. An outage is obvious and someone fixes it in an hour. A rank computed from a mapping that quietly points at the wrong column does not look like a bug; it looks like an answer, and a student makes a real decision on it.
Correctness was therefore both arithmetic and access. The same attempt has to be counted once, ranked against the right population, and permitted by the entitlement that gates it, and all three have to agree.
Approach
- 01
Worked backwards from the number on screen to the exact rows that produced it, through the usage, rank and count layers in turn, so every figure had a traceable lineage rather than a plausible one.
- 02
Recomputed each aggregate independently in SQL and compared it against what the application had stored, treating a match as the only acceptable result and an explained mismatch as the next best.
- 03
Verified the field-mapping layer resolves every predictor input to a column that actually exists, including the mappings that point at columns which had since been renamed or dropped.
- 04
Cross-checked usage entries against the entitlement records that gate them, so access, attempt counts and the ranks derived from them tell the same story.
- 05
Proved each API filter with a negative case as well as a positive one, after finding that an unrecognised parameter name is silently ignored and returns the full unfiltered set, which reads exactly like a filter that works.
- 06
Probed the live API and database before drawing any conclusion, because ticket status in this codebase reflected intent rather than what was deployed.
- 07
Documented the cases where the spec and the data genuinely disagreed and escalated them as questions, instead of assuming whichever reading made the test pass.
Results
- of results rechecked from raw data
- 100%
- systems checked behind one number
- 5
- filters shipped untested
- 0
Stack
- SQL
- MySQL
- Python
- Analytics
- Data Validation
Architecture sketch: COLLEGE PREDICTOR DATA VALIDATION
AD CAMPAIGN DATA INTEGRITY
INTERNAL PORTAL & REPORTING AUTOMATION