01
Overview
This assignment used a simulated water-system and laboratory package to test whether a model could reconcile custody records, analytical exports, quality-control evidence, program roles, and conflicting draft conclusions. I built and validated a row-level review that kept data validity separate from threshold interpretation.
02
Challenge
The files did not agree automatically. One reference card was obsolete, one draft used the wrong percentile method, a late analysis affected some analytes but not others, an out-of-scope row appeared in the metals export, and a QC failure required qualification without inventing a corrected result.
03
Source material
- Chain-of-custody and sample-receiving CSV files
- Sampling-plan/site-role records
- Ion chromatography and ICP-MS analytical exports
- Batch QC summary and instrument calibration log
- Laboratory maintenance note
- Outdated limits reference and a superseded preliminary draft
04
My work
- Reconciled 58 in-scope sample IDs across custody, receiving, plan and analytical records.
- Expanded 46 three-analyte ion rows into 138 results and combined them with 71 metals results to produce a 209-row ledger.
- Checked collection-to-analysis holding times by analyte instead of applying one rule to an entire sample.
- Applied QC qualifications, excluded out-of-scope data, and preserved rejected results without fabricating values.
- Recomputed lead and copper 90th percentiles using the assignment's required ordinal-rank method.
- Separated valid screening observations from the subset eligible for formal decision-making.
05
Analytical approach
Reconcile identity and scopeMatch work order, sample IDs and program roles before interpreting any number.
Establish row dispositionClassify each result as valid, qualified, rejected, excluded or not a compliance result.
Verify calculationsRecompute elapsed times, units, rounding and ordinal percentiles from source values.
Resolve source authorityUse current governing criteria when a superseded draft or outdated card conflicts with primary records.
Separate evidence layersKeep data validity, screening observations and formal decision criteria distinct.
06
Key findings
Specific examples from the completed work.
Analyte-specific holding-time decision
Sample DS-33 was analyzed 70.03 hours after collection. Nitrate and nitrite exceeded their 48-hour requirement and were qualified for exclusion from formal decisions; fluoride from the same sample remained valid because its allowed holding time was 672 hours.
Percentile calculation corrected
For 10 lead sites, the required ordinal rank was ceil(0.90 × 10) = 9. The ninth ordered value was 14 µg/L (0.014 mg/L), not the draft's interpolated 0.016 mg/L. Copper's corresponding result was 0.38 mg/L; the draft had substituted the maximum, 0.52 mg/L.
Obsolete threshold rejected
A 2004 bench card listed an arsenic value of 0.05 mg/L. I did not let it override the current 0.010 mg/L criterion, which changed the interpretation of the 0.011 mg/L entry-point result and the 0.013 mg/L distribution screening result.
QC issue handled without false precision
A copper continuing-calibration verification result of 89% triggered a J qualification on 12 copper rows. I retained the reported measurements as estimated and did not invent a numerical correction.
Scope and rejection controls
OUTFALL-002 belonged to a different work order and was excluded. DS-44 was over range with no reportable value; it remained rejected rather than being converted into a guessed number.
07
Deliverable
A structured review with a complete 209-row ledger, control totals, row dispositions, calculation checks, threshold comparisons, conflicting-draft audit, and a plain-English findings summary.
08
Skills demonstrated
- Multi-file data reconciliation
- Dataset QA
- Row-level classification
- Holding-time calculation
- Percentile verification
- Unit conversion
- Quality-control interpretation
- Data lineage
- Evidence-based exception handling
Why it matters
The domain is specialized, but the transferable work is universal: establish authority, preserve lineage, verify calculations, classify every row, and prevent an appealing draft conclusion from outranking the underlying data.