Summary
Implement a repeatable, idempotent backfill job to migrate legacy NMA_MinorTraceChemistry records into the new Ocotillo schema (Observation, Sample, Parameter, Notes tables).
Source feature spec: tests/features/nma-chemistry-minortracechemistry-refactor.feature
Requirements
Core backfill (idempotent)
- Create
Observation records from legacy NMA_MinorTraceChemistry rows, keyed by GlobalID → nma_pk_chemistryresults
- Link each
Observation to its Sample via SamplePtID → nma_pk_chemistrysample
- Re-running the job must not create duplicates
Field mapping
| Legacy Field |
Target |
Notes |
GlobalID |
Observation.nma_pk_chemistryresults |
Idempotency key |
SamplePtID |
Sample linkage via nma_pk_chemistrysample |
Also links to Thing |
Analyte |
Parameter.parameter_name (matrix = "water") |
Create Parameter if needed |
SampleValue |
Observation.value |
|
Units |
Observation.unit |
|
AnalysisDate |
Observation.observation_datetime |
|
AnalysisMethod |
Observation.analysis_method_name |
Preserve as-is (e.g. "Field analysis", "EPA 200.8") |
AnalysesAgency |
Observation.analysis_agency |
|
Uncertainty |
Observation.uncertainty |
|
Symbol = < |
Observation.detect_flag = false |
Value is detection limit, not detected concentration |
Volume |
Sample.volume |
Populated on related Sample |
VolumeUnit |
Sample.volume_unit |
Populated on related Sample |
Notes |
Notes table record |
target_table="observation", note_type="Chemistry Observation" |
Unmapped fields (ignored)
SamplePointID, OBJECTID, WCLab_ID — not persisted in new schemaVolume and VolumeUnit go to Sample, not Observation
Orphan prevention
- Skip legacy records whose
SamplePtID does not match an existing Sample
- Report count of skipped records with reason (missing Sample linkage)
Linkage integrity
- Each
Observation must link to its Sample and the Thing associated with that Sample (no orphaned observations)
Acceptance Criteria
All scenarios in the feature file pass:
Summary
Implement a repeatable, idempotent backfill job to migrate legacy
NMA_MinorTraceChemistryrecords into the new Ocotillo schema (Observation,Sample,Parameter,Notestables).Source feature spec:
tests/features/nma-chemistry-minortracechemistry-refactor.featureRequirements
Core backfill (idempotent)
Observationrecords from legacyNMA_MinorTraceChemistryrows, keyed byGlobalID→nma_pk_chemistryresultsObservationto itsSampleviaSamplePtID→nma_pk_chemistrysampleField mapping
GlobalIDObservation.nma_pk_chemistryresultsSamplePtIDnma_pk_chemistrysampleAnalyteParameter.parameter_name(matrix = "water")SampleValueObservation.valueUnitsObservation.unitAnalysisDateObservation.observation_datetimeAnalysisMethodObservation.analysis_method_nameAnalysesAgencyObservation.analysis_agencyUncertaintyObservation.uncertaintySymbol=<Observation.detect_flag = falseVolumeSample.volumeVolumeUnitSample.volume_unitNotesNotestable recordtarget_table="observation",note_type="Chemistry Observation"Unmapped fields (ignored)
SamplePointID,OBJECTID,WCLab_ID— not persisted in new schemaVolumeandVolumeUnitgo to Sample, not ObservationOrphan prevention
SamplePtIDdoes not match an existingSampleLinkage integrity
Observationmust link to itsSampleand theThingassociated with that Sample (no orphaned observations)Acceptance Criteria
All scenarios in the feature file pass:
<sets detect_flag to false