avantifellows / avantifellows/external_data_sources
Improve fk_avanti_student_id matcher: 1,295 Avanti students in dim_student left unmatched
- Dominant language
- Python
- Stars
- 1
- Forks
- 1
- Avg merge
- 7h 7m
- Merged PRs (30d)
- 32
Description
## Summary
We compared our name+DOB identity crosswalk against `fk_avanti_student_id` on the ingested external tables. We found **1,295 exam/board rows that belong to Avanti students already present in `dim_student`, but where `fk_avanti_student_id` is NULL** — the matcher is dropping students it should be linking. These are high-confidence (the proposed student's name matches the exam row's name, and for most, DOB matches too).
The goal is to fix the matcher at source so these get an fk on the next ingest — not a downstream patch.
## The file (private GCS — contains names + DOB)
```
gs://avantifellows-private-data/akshay-data-cleanup/handoff/2026-07-06/fk_links_AVANTI_students_improve_match_2026-07-06.csv
```
One row per missed link. Key columns: `exam_key`, `exam_student_name`, `exam_dob`, `proposed_fk_avanti_student_id`, `dim_student_name`, `dim_dob`, `name_jaccard`, `confidence_tier`.
Confidence tiers:
- `A_dob_confirmed` — 996 rows, name + DOB both exact. Near-certain.
- `B_name_only` — 289 rows, name matches, DOB not available to confirm.
- `C_dob_conflict` — 10 rows, name matches but DOB differs (possible namesake, review).
## Three likely causes to fix
1. **DOB format mismatch** — exam DOB is `DDMMYYYY`; `dim_student.date_of_birth` is `YYYY-MM-DD`. A literal compare never matches. Normalise both before comparing.
2. **Name formatting** — trailing/double spaces, initials abbreviated vs expanded, token order (`AVANTHIKA A M` vs `Avanthika A M`). Token-normalise before comparing.
3. **Scope** — most rows are exact name + DOB matches, so the student is plainly already in `dim_student`; worth checking the matcher isn't scoping out grades/years it should include.
Once (1) and (2) are fixed, most of tier A should auto-match.
## Source tables affected
`jnv_fact_board_results_10th`, `jnv_fact_neet_results`, `jnv_fact_jee_results`.
Contributor guide
No contributing guide indexed for this repository
Research direction
Start by locating the matcher that populates fk_avanti_student_id for jnv_fact_board_results_10th, jnv_fact_neet_results, and jnv_fact_jee_results, then inspect how DOB, names, and grade/year scope are compared. Review the private CSV by confidence tier, treating C_dob_conflict rows separately. Done means the matcher links the high-confidence missed rows on the next ingest without a downstream patch.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python
- Domain
- data-engineering
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100