C3 · Publication Volume 13

Geochemical Data Pipeline and Audit

sample identity, chain of custody, batches, units and lineage

Learning goals

The learner should be able to define stable sample and result identities; reconcile collection, custody, preparation and analytical batches; preserve units, methods, limits and qualifiers in a normalised result model; version transformations and validation decisions; trace every map or target statement to immutable inputs; test completeness and uniqueness automatically; and assemble a release package that another reviewer can reproduce without hidden institutional context.

A data pipeline is part of the measurement system. A perfect field and laboratory programme can be invalidated by duplicate identifiers, unit conversion, row shifts, lost qualifiers, coordinate errors or undocumented exclusions. Audit begins at identifier design and ends at the decision output.

Identity, hierarchy and custody

Use a stable, project-neutral sample identifier that does not encode interpretations likely to change. Keep display labels separate. Represent parent–child relations: field sample, fraction, split, reject, pulp, solution, dilution and reanalysis. Each physical object has its own identity and links to its parent; one identifier must not be reused for different material.

Custody records connect handover events, container or shipment identity, count, seal or condition where used, responsible role, timestamp and exception. Custody does not prove sample correctness, but it constrains mix-up and loss. Reconcile expected and received identities at every transfer. Preserve authorised corrections as new records with reason and approval role.

Location, medium, horizon, depth, mass, field observations and collection time belong to the field-sample entity. Preparation and analytical batches are many-to-one process relations, not columns casually overwritten after reanalysis. The model should support one sample with multiple methods and result versions without ambiguity.

Result model, units and qualifiers

Store results in a long, typed structure or an equivalently constrained model: sample identity, analyte, value, unit, qualifier, lower bound, upper bound, method, preparation, dilution, batch, report version and validation status. Enforce uniqueness on the intended result key while allowing a new version or method. A wide table can be generated for analysis after these constraints pass.

An auditable pipeline reconciles sample identity, custody, batches, qualified results, transformations and released decisions
An auditable pipeline reconciles sample identity, custody, batches, qualified results, transformations and released decisions

Use controlled units and convert with dimension-aware rules. Parts per million on a mass basis is numerically equivalent to milligrams per kilogram, but a water result in milligrams per litre is not interchangeable without density and basis. Dry and wet mass differ. Percentage-to-mass-fraction conversions must be explicit. Preserve the original unit and create a normalised derived value.

Qualifiers are typed data. A less-than sign stripped during import changes an interval into an exact limit value. Text such as “not analysed,” “insufficient material” or “interference” must map to distinct status codes. Unknown codes stop the load for review; they must not become zero or null without meaning.

Validation, transformation and provenance

Validation tests schema, identity, referential integrity, range, unit, qualifier, batch, coordinates, control roles and reconciliation. A value can be structurally valid yet scientifically unusable because its blank failed or medium differs. Store both machine validation and scientific fitness decisions with reason and scope.

Every transformation is a versioned activity with named input version, rule, parameters, output field, execution environment, responsible role and timestamp. Examples include unit conversion, coordinate transformation, censoring scenario, log-ratio, batch exclusion, domain assignment, interpolation and target ranking. Hashes or checksums can confirm file identity; they do not explain scientific meaning.

Lineage should answer: Which field samples contribute to this map cell? Which laboratory report and method supplied each result? Which controls governed its validation? Which qualified values were transformed? Which code and parameters produced the figure? Which target decision used it? A screenshot cannot answer these questions; preserve data and executable or precisely specified transformations.

Release, reproducibility and governance

Freeze releases with a manifest: dataset identifier and version; included tables and files; schemas; row counts; hashes; coordinate reference; units; method inventory; censoring profile; quality-control summary; exclusions; transformations; known limitations; access class; and supersession relation. Release notes distinguish corrections from reinterpretations.

Use automated tests for uniqueness, required fields, parent existence, expected sample counts, valid unit-method combinations, qualifier-bound consistency, coordinate bounds, control frequency and orphan results. Human review remains necessary for geological comparability, unusual patterns and fitness for purpose. Automation catches declared rules; it does not discover every scientific error.

Access and privacy controls can restrict sensitive locations or operational notes. A public or teaching release may generalise coordinates or use synthetic data, but it must say so. Redaction creates a derived release with lineage; it must not imply that generalised coordinates are original measurements. No company or individual identity is required to teach or reproduce the method.

Worked synthetic example

A synthetic field programme records 120 routine samples and 12 quality-control samples, so 132 physical submissions are expected. The receipt table contains 131 unique identities: one routine sample is missing. The result file contains 133 distinct sample labels. Reconciliation finds the same 131 received identities, one duplicated label with suffix variation and one unknown label. The pipeline must stop; row count alone would hide both errors.

After authorised reconciliation, the missing sample remains “not received,” the suffix variant is mapped to its physical identity through a correction record, and the unknown label is confirmed as a laboratory control not present in the field table. The released model has 132 expected field identities, 131 received field identities, one missing status and one separately typed laboratory control. Nothing is invented to fill the gap.

For one analyte there are 130 routine or field-control results, including 8 left-censored and 2 right-censored values. A wide export containing 130 plain numbers is impossible without information loss. The compliant export includes value, qualifier and bounds, or a display field alongside those typed columns. A log-ratio view cites its censoring rule and dataset version.

Pipeline audit workflow

  1. Define immutable identities and parent–child material relationships.
  2. Reconcile expected, collected, dispatched, received, prepared and reported objects.
  3. Validate custody events, batches, methods, units, limits and qualifiers.
  4. Load original reports unchanged into a versioned raw layer.
  5. Normalise through declared, tested transformations without overwriting inputs.
  6. Apply quality-control findings as scoped fitness statuses and reasons.
  7. Test uniqueness, completeness, referential integrity and coordinate plausibility.
  8. Trace analytical views, maps and targets to exact input and code versions.
  9. Publish a manifest, schema, quality summary, limitations and supersession record.
  10. Reproduce the release in a clean environment and compare counts and hashes.

Practice and review

  1. Design keys for a sample analysed by two methods, one dilution and one later reanalysis.
  2. Write validation rules for qualifier, lower bound, upper bound and numeric value.
  3. Reconcile a synthetic shipment where 50 were expected, 49 received and 51 report rows contain two duplicate identities.
  4. Trace a map class backward through interpolation, transformation, validation, assay and field sample.
  5. Draft a release manifest that distinguishes original coordinates from a generalised teaching release.

Review questions: Is each physical object uniquely identified? Are expected and received counts reconciled? Were original units and qualifiers preserved? Can every derived result be reproduced? Are gaps and scientific limitations visible?

Sources and further reading