Managing water quality sampling data and lab electronic deliverables

Reference article · 5 min read

Water quality data crosses several organizations before it is usable. A field crew collects a sample and signs it over on a chain of custody form. A contract laboratory runs the analyses and returns results as a spreadsheet or formatted text file. Someone at the agency must then load those results somewhere they can be queried, compared against permit limits, and reported to a state or federal program. That last step is where the chain breaks: files pile up in a shared folder and staff retype numbers at reporting time.

The fix is specific rather than complicated: each step has to produce data the next step can read without human interpretation.

Sample identifiers and chain of custody

The sample identifier is the join key for everything downstream. Assign it in the field, print it on the label, write it on the custody form, and require the lab to echo it back unchanged alongside its own internal identifier. Keep location identifiers separate: a station or outfall keeps its identifier for decades, while a sample event is unique to one visit.

The custody record should capture collection date and time with time zone, collector, container, preservation, and requested analyses. Field measurements such as temperature, pH, conductivity, and flow belong in the same record.

What the electronic deliverable has to contain

An electronic data deliverable, or EDD, is lab results in a defined structure rather than a free-form report. Every result row should carry the client and lab sample identifiers, analyte name with a standard chemical identifier, method, result value, units, a detect flag, reporting and detection limits, dilution factor, analysis date, qualifier codes, and the lab batch identifier.

Pick a specification and name it in the solicitation. Many states publish a format for their own programs, and several commercial formats are in wide use. Which one matters less than having one, published, with valid value lists the agency controls.

Check files before loading: a format checker confirms that required fields are present, that analytes, units, methods, and qualifiers match the reference lists, and that identifiers, dates, and numbers are valid. A file that fails goes back to the lab with a specific error report.

Detection limits, qualifiers, and QC samples

Store the result, the reporting limit, and the detection limit as separate numeric fields alongside a detect flag. Never put text such as "ND" or "<0.005" in a numeric column. Terminology varies by method and program, so require the lab to state which limit each field holds.

• Dilution raises limits. A diluted sample can return a reporting limit above the permit limit, where a non-detect demonstrates nothing about compliance.
• Qualifier codes are not universal. Keep a table of accepted codes and require the lab to use it or map to it.
• Blind field duplicates. The lab should not know which sample is a duplicate, but the database must: carry a QC type field so duplicates and blanks are identifiable at load time.
• Load laboratory QC too. Method blanks, control samples, and spike recoveries are what you need when a result is challenged.

Flagging exceedances against permit limits

Treat limits as data rather than logic buried in a spreadsheet formula. A limits table holds parameter, outfall or station, limit value, units, statistical basis such as daily maximum or monthly average, and effective dates, because permits are reissued and limits change.

Comparison happens in a view over stored results, not by editing them. Convert units first, and confirm the fraction matches, since total and dissolved results are not interchangeable. Exclude QC samples so a field duplicate does not count as a second exceedance, and when a reviewer overrides a flag, keep the original value and the reason.

A laboratory system or a plain database

A laboratory information management system is built around running a lab: sample login, scheduling, instrument integration, custody generation. An agency that only sends samples out uses a fraction of it. A receiving agency needs a structured store, validated import, controlled valid value lists, QC association, limits comparison, audit history, and export.

A relational database with import tooling can meet that need, but it risks becoming one person's private project. Document the schema, keep import code in version control, and host it where IT already backs up systems. Spreadsheets fail as the system of record: no referential integrity, no audit history, and silent damage to identifiers.

Publishing and reporting without retyping

A public dashboard should read from the same database, with a review status flag so provisional results are not shown as final. Regulatory submission should work the same way, as an export mapping rather than retyping. Discharge monitoring reports under a National Pollutant Discharge Elimination System permit are commonly filed electronically, and ambient results are often shared through the federal water quality data exchange. Submission rules and formats change, so confirm current requirements with the permitting authority.

What to require from the lab and the vendor

1. Name the EDD specification and version in the contract, attach the valid value lists, and state when deliverables are due.
2. Require the lab to correct and redeliver files that fail format checking at no additional charge, as a full replacement rather than a patch.
3. Ask vendors to load your actual lab files during evaluation, including the error report for a file you deliberately broke.
4. Confirm that staff can change limits, valid values, and export mappings without a vendor work order, and that you can export all data, including QC records and audit history, in an open format.

HQT builds and integrates data systems for state and local government, including environmental data import, validation, and public reporting work of this kind.

Frequently asked questions