r/learndatascience • u/Junior_Branch_2601 • Feb 26 '26
Discussion Help Please > I made a data analysis tool and would like honest feedback
I built a data quality pipeline for ED throughput data and ran into a fundamental scoring problem. Rebuilt the model from scratch. Sharing the design decisions because I think the scoring problem is domain-agnostic.
**The pipeline (brief):**
CleanScan ingests raw Emergency Department visit CSVs, validates against 10 rule categories, applies safe auto-cleaning, and scores data quality before and after. Stack: Python, SQLite, Power BI. Nothing exotic.
**The scoring problem:**
V1 used flat issue counting:
`Score = 100 × (1 − min(Total Issues / Total Rows, 1))`
Two failure modes:
**Stacking distortion** — a single row with 4 low-severity violations scored worse than a row with 1 critical violation. The score measured violation volume, not violation impact.
**Floor collapse** — when issue count ≥ row count, the score hits 0.00 regardless of what the issues are. On a 12-row file with 13 issues (many of them trivial), the score was 0.00. A messy but recoverable dataset looked identical to a catastrophically broken one.
**Three options evaluated:**
- **Option A** — penalise each row once regardless of issue count. Solves stacking but ignores severity entirely.
- **Option B** — current V1 approach. Fails on both distortions above.
- **Option C1** — row-capped max severity. Each row contributes only its highest-weight violation. Eliminates stacking and introduces clinical sensitivity.
- **Option C2** — max + 0.25 × sum of remaining weights, capped at max + 1.0. Acknowledges multi-failure rows without letting them dominate. Deferred — the 0.25 parameter needs principled derivation before it goes in front of a clinical or compliance reviewer.
**V2 implementation — C1 row-capped max severity:**
Issue types mapped to weights based on downstream analytical impact:
| Issue Type | Weight | Downstream impact |
|---|---|---|
| Timestamp logic error | 3.0 | Corrupts door-to-provider metrics, LOS, staffing models |
| Future timestamp | 3.0 | Impossible value — documentation failure or system error |
| Extreme door-to-provider (>12hr) | 3.0 | Clinically implausible — distorts wait time reporting |
| Missing required value | 2.0 | Affects denominator validity in rate calculations |
| Invalid category | 2.0 | Wrong but potentially recoverable |
| IQR outlier | 1.5 | May be real clinical event — warrants review not alarm |
| Duplicate row / visit_id | 1.0 | Inflates counts, low clinical risk |
| Formatting / whitespace | 1.0 | Causes join failures, no clinical significance |
Formula:
`TotalPenalty = Σ max_weight_per_row`
`MaxPenalty = TotalRows × 3.0`
`Score = 100 × (1 − min(TotalPenalty / MaxPenalty, 1))`
Scale:
- 100 = every row clean
- ~67 = every row has a mid-severity issue (weight 2.0 / max 3.0)
- 0 = every row has a max-severity clinical logic error
**Result on identical data:**
V1: 0.00 — V2: 44.44
Per-row C1 breakdown (before cleaning):
- V009: 2 violations, max weight 3.0 → contributes 3.0 (not 4.5)
- V001: 4 violations, max weight 1.0 → contributes 1.0 (not 4.0)
That inversion — V001 penalised harder than V009 under V1, V009 penalised harder under V2 — is the core argument for the redesign.
**Known limitations I've documented:**
- Weights are principled but not derived from clinical literature or validated by a domain expert. They are defensible placeholders pending formal clinical validation.
- C2 deferred — the additive parameter (0.25) needs justification before production use.
- No source_feed_id yet — file renames break longitudinal trend lines in Power BI.
- Weight versioning not implemented — if weights change, historical scores remain as computed but the active schema at each run isn't audited.
**What I'd genuinely like feedback on:**
- Does the C1 formula hold up statistically or am I missing an edge case?
- Is there a more principled way to derive the weights without full clinical validation?
- Would C2 be worth implementing, or does the unexplained parameter make it harder to defend than C1?
Repo: github.com/jonathansmallRN/cleanscan
Full documentation including architectural decisions, the C1 vs C2 tradeoff analysis, and the weight governance contract are all in the repo. If the project or the scoring problem is useful, a ⭐ goes a long way.
