C6 · Publication Volume 16

Drillhole Data Integration and Validation

hole identifiers, depths, intervals, units, status and version

Learning objectives

This lesson assembles the evidence chain without flattening its meaning. The learner should be able to design identifiers and relational entities, validate collar, survey, run, log, sample and assay tables, enforce interval and unit rules, model statuses and versions, preserve provenance and create an adopted release while retaining raw and superseded records.

Integration is not successful merely because tables join. A syntactically valid join can attach an assay to the wrong hole, use the wrong survey version or turn a missing result into zero. Validation must test identity, geometry, semantics, lineage and decision fitness.

Entity and relationship model

Represent program, hole, branch, collar observation, survey observation, adopted survey, drilling event, run, container, core interval, geological observation, structural feature, sample, dispatch, preparation entity, analytical batch, result, quality control, image and release as distinguishable entities. They can share keys without being one wide table.

Use persistent source identities and stable surrogate keys where needed. A hole identifier should not be inferred from a sample-name prefix. A sample belongs to an interval through an explicit relationship. A result belongs to an aliquot and method; an adopted result view selects from result versions under a rule.

Many relationships are one-to-many. One interval can have several interpretations; one sample can have several results; one raw station set can produce several adopted surveys; one image can have several derivatives. Flattening these to one row forces arbitrary selection and hides version history.

Identifier, depth and interval rules

Validate uniqueness in the correct scope. Hole IDs should be unique in the repository or namespace; sample IDs must be globally unambiguous under the stated system; sequence numbers may be unique only within a dispatch. Preserve leading zeros and case rules deliberately rather than letting spreadsheet conversion alter identities.

Depths require unit, datum along the hole and precision. Check non-negativity, from-depth below to-depth, maximum depth relative to drilled and logged length, station order, run continuity, box reconciliation and sample containment. Half-open interval logic [f,t) prevents double ownership at shared boundaries.

Overlap is not universally an error. Lithology intervals may be expected not to overlap within one adopted interpretation, while veins can overlap a lithology interval and analytical repeats can share support. Validation rules must be table- and version-specific. Gaps can be valid when their state is explicit.

Units, vocabularies and result states

Store numeric value separately from unit, analyte, method and qualifier. Normalise only through declared conversions and retain source values. Parts per million by mass may be numerically equivalent to milligrams per kilogram under specified conditions, but percentages, elemental and oxide forms, dry and wet basis and density-dependent units require explicit semantics.

Use controlled result states: valid numeric, below limit, above limit, not analysed, not received, insufficient material, invalid and pending. A null without reason is not adequate. Keep detection or reporting limit as a value with unit and method. Do not encode qualifiers inside a text number that downstream software may parse incorrectly.

Version vocabularies and mappings. If two logging schemes use different weathering or lithology concepts, create an explicit crosswalk with limitations. Do not relabel historical observations invisibly to match a new code list.

Validation, versioning and provenance

Run layered validation. Structural checks test required fields, types and references. Geometric checks test coordinates, angles, depths, intervals and trajectory plausibility. Semantic checks test units, method compatibility and allowed status combinations. Lineage checks ensure every adopted value returns to a source. Statistical checks identify unusual distributions, duplicates, copied values and batch effects. Geological review evaluates whether patterns make sense without deleting anomalies merely because they are inconvenient.

Create immutable ingestion snapshots with checksums. Transform into standardised tables using versioned code or reproducible rules. Record validation findings as data with severity, affected entity, rule, evidence, disposition and release impact. Fixing a source error should create a corrected entity or mapping, not erase the received file.

A release is a manifest of adopted versions and exclusions. It should identify collar version, survey version, log interpretations, sample and result status, validation-rule version, unresolved issues and generated products. Releasing a new version does not change what a prior model used.

Synthetic worked example

A synthetic import contains hole DH-017, sample S-1042 at 88.0–89.0\,\mathrm m, two results for element Y, and a survey file labelled “final.” Validation finds that the sample references DH017 without the hyphen, one result is in percent and one in milligrams per kilogram, and the survey file checksum differs from the version used in an earlier trace.

The process does not auto-correct by string similarity. A reviewed alias table links DH017 to DH-017 with source and reason. Units are retained and converted into a derived harmonised view only after confirming elemental basis. Both survey files become versioned entities; the adopted release states which one is used and regenerates affected coordinates.

The release report shows the original identity mismatch, conversion and survey change. A user can reproduce the adopted view or inspect the prior state.

Practice and review checklist

  • Are physical, observational, interpreted and release entities distinguished?
  • Are identifiers stable and validated in the correct scope?
  • Are alias mappings reviewed rather than guessed?
  • Do depths and intervals use declared units and boundary logic?
  • Are allowed overlaps and gaps defined per table type?
  • Are value, unit, analyte, method, qualifier and status separate?
  • Can all conversions and vocabulary mappings be reproduced?
  • Are received files immutable and checksummed?
  • Does every validation finding have disposition and release impact?
  • Can a prior release be reconstructed exactly?

Reject a “clean” table produced by overwriting source values or silently choosing one duplicate. Cleanliness without lineage is loss of evidence.

Decision implications and release

An adopted dataset is a purpose-specific view, not the only truth. It should expose the evidence used, exclusions, unresolved issues and uncertainty. Downstream users need both stable release identifiers and a route to source observations.

Before modelling or estimation, test sensitivity to survey versions, interval uncertainty, support, quality status and censored results. Regenerate derived coordinates when collar or survey changes. A release is fit when its identities, geometry, semantics, quality and provenance are all adequate for the stated decision.

Drillhole integration preserves separate entities, validates identity and intervals, and publishes a reproducible adopted release.
Drillhole integration preserves separate entities, validates identity and intervals, and publishes a reproducible adopted release.

Sources