Data Management Protocol for the Minnesota Pollution Control Agency's Ambient Groundwater Monitoring Network

Data Management Protocol for the

Minnesota Pollution Control Agency’s

Ambient Groundwater Monitoring

Network

July 2017

Minnesota Pollution Control Agency

520 Lafayette Road North | Saint Paul, MN 55155-4194 |

651-296-6300 | 800-657-3864 | Or use your preferred relay service. | Info.pca@state.mn.us

This report is available in alternative formats upon request, and online at www.pca.state.mn.us.

Document number: wq-am1-08

Authors

Andrea Samuelson

Sharon Kroening

Contributors/acknowledgements

Bill Scruton

Editing and graphic design

PIO staff

Graphic design staff

Mary Blackstock

The MPCA is reducing printing and mailing costs

by using the Internet to distribute reports and

information to wider audience. Visit our

website for more information.

MPCA reports are printed on 100% post-

consumer recycled content paper

manufactured without chlorine or chlorine

derivatives.

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

Contents

Abbreviations and acronyms .............................................................................................................. ii

Executive summary ............................................................................................................................1

Introduction ......................................................................................................................................1

Purpose and scope ........................................................................................................................................... 1

Roles and responsibilities ...................................................................................................................3

MPCA groundwater monitoring staff ............................................................................................................... 3

MPCA data management staff ......................................................................................................................... 3

MPCA quality assurance staff ........................................................................................................................... 3

MNIT staff ......................................................................................................................................................... 3

Data management .............................................................................................................................3

Electronic data management systems ............................................................................................................. 4

EQuIS ..................................................................................................................................................................... 4

OnBase .................................................................................................................................................................. 6

Data management for the ambient groundwater monitoring network .......................................................... 6

Location, construction, and lithology data ....................................................................................................... 6

Sampling plans ................................................................................................................................................. 8

Field data recording and storage ..................................................................................................................... 9

Laboratory data storage ................................................................................................................................... 9

Sample tracking .................................................................................................................................................. 10

Data verification ................................................................................................................................................. 11

Laboratory audits ................................................................................................................................................ 12

Data dissemination .......................................................................................................................... 12

MPCA Policies and memorandums ................................................................................................... 13

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

Abbreviations and acronyms

American Society for Testing and Materials

ASTM

Contaminants of Emerging Concern CEC

Chain of Custody

COC

County Well Index

CWI

Minnesota Department of Natural Resources

DNR

Environmental Data Access

EDA

Electronic Data Deliverable

EDD

EQuIS Data Gathering Engine

EDGE

EQuIS Data Processor

EDP

U.S. Environmental Protection Agency

EPA

EQuIS

Minnesota Department of Agriculture

MDA

Minnesota Department of Health

MDH

Minnesota Geological Survey

MGS

Minnesota

Minnesota Information Technology Services

MNIT

Minnesota Pollution Control Agency

MPCA

Portable Document Format

PDF

Public Land Survey System

PLSS

Quality Assurance

Sample Planning Module

SPM

United States Geological Survey

USGS

Water Quality Exchange

WQX

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

Executive summary

The Minnesota Pollution Control Agency (MPCA) monitors its Ambient Groundwater Monitoring

Network each year to evaluate the current condition of the state’s groundwater and track any trends in

contamination. This report describes how the agency manages this information in accordance with the

requirements of Minnesota State law. Good data management allows MPCA staff to promptly analyze

and interpret the data they collect and quickly make it available to the public. The MPCA manages the

information from its Ambient Groundwater Monitoring Network throughout its lifecycle from the initial

installation of each well until the collection of the last sample from it. This report documents how MPCA

staff manage and verify the field and laboratory groundwater data in its water-quality database as well

as describes how written documents, such as access agreements for well sites and written laboratory

reports are managed in the agency’s electronic filing system.

Introduction

The Minnesota Pollution Control Agency (MPCA) maintains an Ambient Groundwater Monitoring

Network to evaluate the occurrence of non-agricultural chemicals in groundwater and quantify how the

concentrations of these chemicals change over time. The agency began building this network in 2004

and added over 100 new monitoring wells to it from 2009-2016. The network currently (2017) consists

of 266 wells that MPCA staff sample annually; 210 dedicated to monitoring, 53 to domestic purposes,

and three public water supply wells. The wells generally are shallow and located in urban areas,

completed in aquifer systems that are naturally susceptible to contamination (Figure 1).

MPCA staff collect water samples annually from each of the network wells, and these samples are

routinely analyzed for over 100 different chemicals. This includes nutrients, major ions, volatile organic

compounds, and trace metals, as well as field measurements of water pH, specific conductance,

dissolved oxygen concentrations, and water temperature. Each year the MPCA also samples

a subset of

the wells to determine the presence of over 100 contaminants of emerging concern (CEC) and

pesticides. MPCA staff collect CEC samples from about 40 of the network wells each year and also

collect 20 pesticide samples annually in collaboration with the Minnesota Department of Agriculture

(MDA).

Purpose and scope

This report describes the data management associated with the MPCA’s Ambient Groundwater

Monitoring Network. It explains how MPCA staff manage the: 1) field and laboratory data collected for

this network as well as the legal agreements that allow the agency to install wells on properties and 2)

documents that describe the well construction and development.

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

Figure 1. Locations of wells in the Minnesota Pollution Control Agency’s Ambient Groundwater Monitoring

Network, 2017

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

Roles and responsibilities

The MPCA and MNIT staff share the data management responsibilities for the Ambient Groundwater

Monitoring Network. MPCA staff actively collect water samples and other field data for the network and

ensure that this information are accurately entered into its water-quality data management system.

MNIT staff provide information technology services to the MPCA, which includes building, maintaining,

and securing the agency’s water-quality data management system. The following sections describe the

roles and responsibilities of the key staff associated with data management for the MPCA’s Ambient

Groundwater Monitoring Network.

MPCA groundwater monitoring staff

The MPCA groundwater monitoring staff are responsible for: 1) ensuring site locations and required

metadata are entered into its water-quality data management system, 2) accurately recording field

information, 3) accurately completing the Chain of Custody (COC) forms that are submitted to the

laboratory along with the samples, and 4) ensuring all collected field and sample information are

contained in electronic data deliverables (EDDs) and are entered into the agency’s water-quality data

management system.

MPCA data management staff

The MPCA data management staff are responsible for: 1) loading laboratory EDDs into the agency’s data

management system, 2) ensuring all water-quality data are stored in the database according to the

MPCA’s business practices, and 3) maintaining the MPCA’s contract with the data management system’s

software vendor.

MPCA quality assurance staff

The MPCA quality assurance (QA) staff are responsible for: 1) maintaining contracts with state and

commercial laboratories including requirements for them to provide data in electronic formats that

integrate with the agency’s data management system, 2) performing laboratory audits, and 3) providing

basic quality assurance training to agency staff.

MNIT staff

MNIT staff are responsible for: 1) maintaining the MPCA’s water-quality data management system and

the computer systems that house this database and the associated software and 2) routinely migrating

data to the U.S. Environmental Protection Agency’s (EPA) Water Quality Exchange or WQX.

Data management

The MPCA manages the information generated as part of its Ambient Groundwater Monitoring Network

according to agency policy and Minnesota State law. All government data in the state of Minnesota

must be maintained and stored according to a state law called the Minnesota Government Data

Practices Act (Minnesota Statutes, section 13). This law requires that the MPCA and all other

governmental entities preserve all information related to the agency’s activities. The information that

must be preserved by the MPCA’s Ambient Groundwater Monitoring Network includes: 1) field notes

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

and sheets prepared by the agency’s groundwater monitoring staff that contain measurements of water

temperature, pH, specific conductance, and dissolved oxygen concentration taken from the network

wells, 2) analytical results submitted to the agency in an electronic format by the contracted

laboratories, 3) written laboratory reports that further describe the analytical laboratory results and

include other sample-related information, such as the COC and Sample Condition Upon Receipt Forms,

and 4) access agreements for groundwater monitoring sites with federal, state, and local units of

government as well as private parties.

Electronic data management systems

Two electronic data management systems are used to store and manage the electronic data and reports

associated with the Ambient Groundwater Monitoring Network. MPCA staff use the EQuIS data

management system to store and manage the water-quality data collected from each sampling event.

Reports and other program documents are electronically stored and managed using an electronic filing

system called OnBase.

EQuIS

EQuIS is a commercial data management system that the MPCA uses to store environmental field

measurements and laboratory analyses. This system is comprised of a database and a set of software

that allow users to load, edit, download, and visualize the stored data.

An Oracle Database Server currently (2017) houses the MPCA’s EQuIS database. The schema or set of

related tables that make up the database is comprised of over 150 tables, including both data and

reference tables. The data tables contain the information collected during each field trip or sampling

event. The reference tables contain lists of valid values such as chemical names or unit values used to

maintain consistency in the database. The MPCA’s EQuIS schema also includes a set of tables designed

to facilitate sending the agency’s ambient ground and surface water information to the EPA’s WQX

system.

The information in the MPCA’s EQuIS database is split among several hundred facilities. “Facility” is an

EQuIS database term that corresponds to an area of interest or a group of locations. Users also are

assigned read and write access to EQuIS databases by facility. In the MPCA’s EQuIS database, each

facility corresponds to either a statewide program or an individual solid waste or remediation site. This

structure permits only selected agency staff to have write access to the database.

The water-quality data collected by the MPCA’s Ambient Groundwater Monitoring Network is contained

in the “Minnesota Pollution Control Agency – Ambient Groundwater” facility. Other ambient

groundwater data also are contained within this facility. One large notable set of historical ambient

groundwater data in this facility is from the agency’s Groundwater Monitoring and Assessment Program.

The MPCA maintained this groundwater monitoring program primarily in the 1990s and sampled over

1,000 wells for it. The “Minnesota Pollution Control Agency – Ambient Groundwater” facility also

contains information from several special studies that typically assessed the effectiveness of various

best management practices on groundwater quality.

The MPCA differentiates the data associated with all of the various projects in the “Minnesota Pollution

Control Agency – Ambient Groundwater” facility through the use of a field called the project task code

(also referred to in the database tables as Task_Code_2). The database users can use this code to

retrieve data by specific monitoring projects (Table 1). The data from the agency’s Ambient

Groundwater Monitoring Network is stored under a project task code of “PRJ07229.”

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

Table 1. Project task codes in the MPCA’s EQuIS “Minnesota Pollution Control Agency – Ambient Groundwater”

facility

PROJECT TASK CODE

DESCRIPTION

PRJ07229

Ambient Ground Water Monitoring Network

PRJ00084

Ground Water Monitoring and Assessment Program

PRJ04245

Iron/Manganese in GW Pumpout Water

PRJ04248

Bailey Lake Stormwater Infiltration Monitoring

PRJ07082

Sentinel Lakes Monitoring Project

PRJ07181

Deer Creek and Pokegama Lakes: A Diagnostic Study

PRJ07265

PCA Special Studies: wells or springs in MN ag areas

PRJ07273

PCA Special Studies: wells in MN urban areas

PRJ07855

Shoreview Pervious Pavement – Stormwater

PRJ07856

Perham Feedlot Site

PRJ07926

Southeast Minnesota Domestic Well Network

PRJ07989

Evaluating CECs in GW at Wastewater Land Application Sites

A team consisting of both MNIT and MPCA staff administer and manage the MPCA’s EQuIS data

management system. One MNIT staff person serves as the EQuIS database administrator. Besides

maintaining all of the software and tables associated with this data management system, the EQuIS

database administrator also routinely performs a series of routine maintenance checks on the system to

ensure data integrity and sends ambient surface and groundwater data that were reviewed and flagged

with an approval code of “Final” by MPCA staff to the EPA’s WQX system. One MPCA staff person serves

as the EQuIS project manager. The EQuIS project manager interacts with MPCA management, staff, and

external partners on a variety of EQuIS database related issues. This includes providing support to the

data management system users, prioritizing system enhancements, and working with MPCA contracted

laboratories and the software vendor to provide data to the agency in an EQuIS EDD or “EQuIS-ready”

format. A team comprised strictly of MPCA staff works with the project manager to manage the

information in EQuIS. This includes loading new data into the system, performing database retrievals,

assigning new users to the system and providing user assistance.

MPCA staff typically do not import and retrieve the information stored in the EQuIS database directly,

but access and load data to it through several programs, including EQuIS Professional, Enterprise, Data

Processor, Sample Planning Module (SPM), EQuIS Data Gathering Engine (EDGE), and EQuIS for ArcGIS.

The EQuIS Professional software allows advanced users to edit the reference and data tables, load new

data, create customized data retrievals, and visualize the stored information. EQuIS Enterprise is web-

based software that also allows users to retrieve and load data. Plus, users can develop dashboards that

allow other less experienced staff to visualize and export information from the EQuIS database. There

are distinct differences between the EQuIS Professional and Enterprise programs. The EQuIS

Professional software is more powerful compared to EQuIS Enterprise, but the MPCA has limited

licenses available for the EQuIS Enterprise software. An unlimited number of staff can access the EQuIS

Enterprise software. The SPM and EDGE programs are used together to plan and collect data during

sampling events. The SPM software allows users to develop sampling plans used to track progress and

prepopulate COC forms for the laboratory. The MPCA field staff use the EDGE software to record

information collected during individual sampling events.

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

Controlling user privilege settings is one way that the MPCA maintains the data quality in its EQuIS

database. The user privileges control which staff can enter, change, or delete data from EQuIS. The

EQuIS database administrator, project manager, and database team assign the user privileges to all

MPCA staff. Data editing privileges in each EQuIS facility typically are restricted to the program staff,

database administrator, project manager, and database team. Editing of the reference tables is even

more restricted and is limited to just the database administrator, project manager, and database team.

Each of the agency’s data coordinators (the lead data management staff from each agency program that

stores data in EQuIS) approve all reference table changes prior to implementation.

The MPCA structured its EQuIS data management system so the data meets the agency’s business

requirements. The database requires that: 1) all locations have coordinates, 2) the user must populate

selected site and sample-related information before data can be imported, 3) the database does not

permit entry of the duplicate data, and 4) analytes are classified using specific agency defined identifiers

that are listed in reference tables. EQuIS enforces these requirements when data is imported using the

data management system’s default error checking module. This software forces the user to correct all

discovered errors before importing the data into the database. In addition, the EQuIS database

administrator also routinely runs a series of reports that capture anything that escaped these error

checks or resulted from manual editing of the tables in the database (which is not encouraged).

OnBase

OnBase is a commercial data management system used by the MPCA to manage and store documents.

In this database, documents are stored in their native format such as Microsoft Word or PDF. The MPCA

scans physical paper documents and converts them into an image file before importing them into the

system. The MPCA classifies all files in OnBase according to an agency-defined classification system, and

these are indexed by document type, date, and a set of keywords supplied by the user.

The MPCA’s Ambient Groundwater Monitoring Program uses OnBase to store key documents that are

essential for operating and maintaining the network. These documents include: 1) access agreements

and leases to place MPCA-owned monitoring wells on property owned by other entities such as federal,

state, or local units of government or private parties, 2) field notes taken during sampling events, 3)

laboratory reports and electronic data files such as spreadsheets, and 4) reports published by the

program. All of these documents are stored according to the program’s recordkeeping plan (Minnesota

Pollution Control Agency, 2016).

Data management for the ambient groundwater monitoring network

The MPCA manages all of the information associated with the Ambient Groundwater Monitoring

Network to ensure that it is complete and accurate. This includes ensuring that sufficient metadata

about the sites is stored, along with the water level and water-quality measurements. In addition, the

agency has procedures in place to minimize data entry errors, and verify and approve the information

stored in it.

Location, construction, and lithology data

The MPCA maintains information about each network well’s location, construction, and lithology. This

information is essential for the agency staff to properly interpret the data. For example, to meet the

network’s goal of assessing temporal trends in groundwater quality, the field staff must sample the

identical wells each year. To meet this goal, the MPCA has procedures in place to ensure that staff

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

sample the correct wells for the Ambient Groundwater Monitoring Network each year and properly

identify them in the EQuIS database.

The MPCA staff use field folders that contain site information to ensure they sample the same wells

each year. Each field folder contains a photographic log of the site as well as driving directions and a

detailed site sketch showing its location relative to roads and land features. This is especially important

for old wells because these are more difficult to identify in the field. A tag placed on each registered well

that lists the Minnesota Unique Number is the best well identifier. The Minnesota Department of Health

(MDH) has assigned this identifier to all wells drilled in the state since 1972 and a substantial number of

pre-1972 wells. However, MPCA staff cannot always use the Minnesota Unique Well number to

differentiate wells during sampling events because the state did not require wells to have well tags

placed on them until 1992. Alternatively, some monitoring programs have used property owner

information to identify wells. This information, however, is not always reliable to identify wells because

it can change over time and some owners have multiple properties.

To minimize the occurrence of misidentified wells (described further in the subsequent sections of this

report), MPCA policy requires that the staff enter the site location information into its EQuIS database

before any other data can be stored, such as water levels, field measurements, or laboratory analyses.

This requirement helps point out wells whose identifiers were incorrectly recorded on the COC forms

submitted to the laboratory. The database itself also enforces this requirement, and the users cannot

enter any field or laboratory measurements into it if there is no site information present. At a minimum,

the MPCA staff must store the well’s identifier (usually the Minnesota Unique Well Number),

coordinates (in latitude/longitude), and well type (i.e. monitoring or non-public water supply) in the

water-quality database.

The MPCA’s database also includes tables that store many other well attributes other than location. This

includes the: 1) well construction, 2) lithology, 3) land surface elevation information, 4) county and basin

in which the well is located, 5) drilled depth of the well, 6) aquifer, 7) installation date, 8) disinfection

status, 9) drilling method, and 10) driller’s log. The MPCA’s database administrator imports most of this

information into the EQuIS database from another one called the County Well Index (CWI), which is the

master database of well records in the state. The MPCA staff use geographic information from the DNR

to determine the major and minor surface water drainage basin in which each network well is located.

The MPCA staff also enter some additional information about any newly installed wells for the network

into the MPCA’s EQuIS database, including the well construction, the geologist’s soil boring log

(classified using the ASTM convention), and top of casing elevation. The MPCA staff enters this

information into EQuIS by preparing an EDD from the information in the report written by the

consultant hired by the agency to oversee the well installation.

The MPCA obtains the location for its wells from several sources. For the wells that were newly installed

for the network (2010 to the present), the horizontal coordinates were taken using a field GPS unit by

the consultant hired by the MPCA to oversee the well installation. For the existing wells in the network,

the MPCA obtained the coordinates from the CWI, USGS, or historical agency records.

The CWI is the source of much of the well information stored in EQuIS. The Minnesota Geological Survey

(MGS) and MDH jointly maintain this database, which contains the most comprehensive information

about wells installed in Minnesota including their location, construction, use, and status. The MDH and

MGS provide the well’s coordinates in CWI in the Universal Transverse Mercator (UTM) projection if

MGS, MDH, or DNR staff verified the well’s location. The MPCA staff first convert these UTM coordinates

into latitude and longitude before entering them into their database. The CWI also contains the well and

boring construction record if the driller submitted the documentation to the MDH. This is the driller’s

log of the geologic materials and well construction data. MGS geologists reinterpret these logs on a

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

county-by-county basis, as part of an on-going effort to develop geologic atlases for each county in the

state. Data on old wells generally is more limited in the CWI because the drillers were not required to

report water well records to the state until 1974. Furthermore, enforcement of this standard was lax

until about 1990 (J. Blum, personal communication).

The MDH and MGS maintain the CWI’s data quality by limiting the ability to store and edit information

in the database and conducting detailed well inventories before adding new information into the

system. Currently, only MDH and MGS staff can store and edit data in CWI. All other users of the

database have read-only access to the information. MDH and MGS staff enter the information

associated with newly installed wells into the database using the Minnesota Unique Well Number as the

primary identifier. MDH staff also assign Minnesota Unique Numbers to pre-1972 wells located during

routine investigations if it has associated downhole information such as a driller’s log. However, before

the MDH and MGS staff add any new well information to the CWI, they first thoroughly review the

information associated with it, especially for the pre-1972 wells, to ensure that they do not duplicate

the same well in the database. This review includes comparing the well diameter to the one listed on the

well record (if it is available), verifying with the property owner that the well exists on that property, and

verifying the well location by property address, property owner, name on the mailbox, or site plan.

Sampling plans

Sampling plans also are an integral part of the data management process for the MPCA’s Ambient

Groundwater Monitoring Network. These plans are prepared to delegate the sampling work among the

field staff and track data entry into its water-quality database. The staff develop these plans before the

annual sampling of the network using the EQuIS SPM software. Each annual sampling plan includes the:

1) wells scheduled for sampling, 2) field staff designated to sample each well, 3) proposed sampling

date, 4) laboratory contracts associated with the network, and 5) field and laboratory methods

associated with the sampling. The software also includes several reports that help the staff prepare for

sampling by listing the laboratory analyses scheduled for each site and the number and types of bottles

required for it. In addition, the software also has reports that the MPCA staff use to track whether all of

the data planned for each well was entered into the agency’s EQuIS database.

Another key feature of the sampling plan is that the information contained in it is outputted into EDD

templates which simplifies data entry and minimizes errors on the laboratory COC forms and field data

EDDs. An EDD template is a file that can be imported into the EQuIS EDGE software that the MPCA uses

for field data entry (described further in the next section) and contains all of the information that was

provided in the sampling plan. This simplifies field data entry because the staff only need to enter the

sampling event specific information, such as the sampling date and time, weather conditions during

sampling, and the field measurements. The use of the EDD template greatly simplifies the completion of

the field EDD and COC forms which are prepared using EDGE software and reduces errors in several

ways. For example, when the MPCA field staff use the EDD template they need only to select the

identifier for the sampled well from a specified list rather than manually enter this information which

reduces transcription errors. It is also much easier for MPCA staff to accurately complete the laboratory

COC form when they use the EDGE software in conjunction with an EDD template because the software

by default only shows the truncated list of laboratory analytes and analytical methods that are planned

for the project rather than the numerous selections available in the MPCA’s database.

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

Field data recording and storage

The MPCA Ambient Groundwater Monitoring Network staff use both field notebooks and the EQuIS

EDGE software to record field data, enter it into the agency’s water-quality database, and prepare

laboratory COC forms. The EDGE software contains a GUI-based interface in which MPCA staff enters

the information collected during field-sampling events. Behind the scenes, the software formats the

entered information into an EDD that the MPCA staff import into its EQuIS database. As mentioned in

the previous section, the MPCA staff also use the SPM software to produce an EDD template that

simplifies field data entry in EDGE and the completion of the COC forms. The MPCA’s use of the EDGE

software also minimizes data entry errors because a substantial number of the required fields can be

completed using drop-down menus instead of entering the information manually. In addition, the MPCA

set up the EDGE software to automatically populate many other key fields with the correct information

including the sampling method, project code, and sampled medium.

The EDGE software also contains error checking to ensure the recorded data are of the highest quality

and meets the MPCA’s technical and business requirements. The software requires the MPCA staff to

record this information for each sampling event: 1) the well identifier which is the Minnesota Unique

Well Number, 2) sampled medium (e.g. water or air), 3) sampled matrix (e.g. groundwater or surface

water), 4) sample type (e.g. routine environmental sample, replicate sample, or trip blank), 5) sample

date and time, 6) sampler, 7) sampling method, and 8) project task code or identifier (e.g. PRJ07229).

The EDGE software returns an alert or error if any of these fields are not completed or if an incorrect

value is used. In addition, the software performs several routine calculations associated with

groundwater sampling, including calculating the water level elevation, purge volume, and stabilization

checks for the field parameters. The program also highlights when: 1) field measurements made during

well purging that have stabilized and 2) out of range field measurements such as water temperatures

over 40 degrees Celsius.

MPCA monitoring staff typically import the field data into the EQuIS database as soon as possible after

the sampling event. There are several steps involved with importing these field EDDs into EQuIS. First,

the staff process the completed field EDD using the “EDP Export” function in the EDGE program. This

step formats the field EDD into a .ZIP file comprised of several text files; this is the required EDD format

to import the data into the agency’s EQuIS database using the web-based EQuIS Enterprise program. In

addition, the “EDP Export” function only marks the last field measurement is marked as “reportable” in

the MPCA’s EQuIS database. This is notable because the primary reports used by the users of the

MPCA’s EQuIS database by default only retrieve data marked as “reportable.” Data that is marked as

“not reportable” in the MPCA’s EQuIS database include the purge measurements associated with

groundwater sampling, surrogate compounds analyzed by the contract laboratories, and data that was

reviewed and rejected. After the “EDP Export” step is completed, the staff import the resulting file into

the EQuIS database using a program called EQuIS EDP (EQuIS Data Processor). The EDD undergoes a

second round of error checking during this step to assure the data meets the MPCA’s business

requirements before it is uploaded into the database. These error checks include: 1) verifying that the

well identifiers already are present in the database and 2) informing the user if the data already are

present in the database. The MPCA staff are required by the EQuIS data management software to

correct any errors identified by the EDP program before the information is accepted into the database.

Laboratory data storage

There also are procedures in place to ensure that MPCA staff enter high-quality analytical laboratory

data into its EQuIS database. Much of this focuses on training MPCA staff to properly complete the

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

laboratory COC form and requiring the laboratories to provide data to the agency in an “EQuIS-ready”

format.

Filling out the laboratory COC form is a key step to ensure that groundwater data imports smoothly into

the agency’s EQuIS database. The COC form conveys all of the information the laboratory needs to

analyze the sample and report the data back to the agency as an EQuIS EDD that readily imports into its

water-quality database. The information on the COC form includes the analyses that the laboratory will

perform plus the EQuIS facility and project task codes.

The MPCA groundwater monitoring staff also use the EDGE software in conjunction with SPM to prepare

COC forms with minimal errors. MPCA staff must completely fill out the COC form in order for the

laboratory to prepare an EQuIS-ready EDD. Incomplete COC forms are the main reason why the

laboratories do not produce error-free EDDs. It also takes longer for the data management staff to

import EDDs with errors into EQuIS because the data management staff first must edit them to correct

the problems. The Ambient Groundwater Monitoring staff’s use of the EDGE program circumvents most

of these problems. With the EDGE EDD template, the user only selects from the anticipated wells and

lists of the chemical analyses assigned to the project, rather than entering these manually or sorting

through the hundreds of available chemical analyses present in the EQuIS database. The use of the

EDGE software also prepopulates several pieces of the project-specific information on the COC form,

such as the project manager and contact information, project name, billing code, and turnaround time.

Preparing the field EDD and laboratory COC form using the EDGE software also ensures that the

laboratory data properly merge with other sample-related data that are stored in EQuIS before the

laboratory data are received, such as sampling weather conditions or sampling method.

The MDH Environmental laboratory is the primary lab used by the Ambient Groundwater Monitoring

Network to provide analytical data. This laboratory provides information to the MPCA as both an

electronic report and as an EQuIS EDD from its laboratory information management system (LIMS), as is

required by the interagency agreement between the MPCA and the MDH laboratory. The electronic

laboratory report is stored in OnBase as previously described, and the EQuIS database team imports the

laboratory EDD into the EQuIS database. Similar to the field data storage, the EQuIS database team first

uses the EQuIS EDP software to check the laboratory EDD for any errors. If any are found, the MPCA

Ambient Groundwater Monitoring staff, EQuIS database team, and MDH laboratory work together to

resolve the errors. Typically, these are very few.

SGS AXYS Analytical Labs is another lab that provides analytical data to the MPCA. This laboratory is

located in British Columbia, Canada, and analyzes CEC samples including antibiotics, medications, DEET

and bisphenol A. This laboratory does not currently provide these data to the MPCA in the form of an

“EQuIS-ready” EDD. Currently, these data are imported into a MicroSoft Access database, and an in-

house program is used to transform this information into an EQuIS EDD that can be entered into the

agency’s database.

Sample tracking

The MPCA groundwater staff use the electronic sampling plans developed using the EQuIS SPM software

to track the entry of all of the scheduled data into the agency’s water-quality database. The staff tracks

the data by running a SPM Completeness Report from EQuIS Professional or Enterprise. The

completeness report lists all of the scheduled sampling tasks for the network, summarizes the

percentage of completed tasks, and specifically lists which, if any, wells are missing water level, field

measurement data such as water temperature and specific conductance, or laboratory analytical data.

MPCA staff periodically review the SPM completeness report for any missing data. If the staff finds any

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

missing data, they either import the missing field EDDs into the database or work with the MPCA

database team to process any missing laboratory EDDs.

Data verification

The MPCA groundwater monitoring staff must verify whether the field and laboratory information

initially entered into the water-quality database is accurate before they disseminate this information to

the public. The staff do this by following a written data verification procedure. After this is complete, the

groundwater monitoring staff change the approval codes in the agency’s

water-quality database (if approved this is set to “Final”). This is a key step because the approval code

controls how this information is disseminated to others (discussed further in the Data Dissemination

section of this report).

As part of the data verification process, the MPCA groundwater monitoring staff reviews the COC forms

before submitting them to the laboratory. This ensures that the forms do not contain any incorrect

information such as well identifiers or sampling dates. These types of errors usually result from a

typographical error. The MPCA staff correct any errors before the COC form it sent to the laboratory.

The groundwater monitoring staff also review the written analytical reports from the contracted

laboratory(ies) to ensure that no typographical errors are present in them, the quality control data

associated with the chemical analysis is within the established limits, and evaluate any data that does

not conform to these criteria. Typographical errors occasionally occur in the laboratory reports because

their current process (2017) is to have the staff transcribe the information about the samples contained

on the COC form into their LIMS by hand. The written reports received from the analytical

laboratory(ies) contain a summary of the samples received, a copy of the original COC form, a Sample

Condition Upon Receipt Form, and the QA results which usually include a method blank, laboratory

control sample, matrix spike samples, and duplicates. Organic chemistry results, such as volatile organic

compounds or CECs, also contain surrogate recovery information. The MPCA monitoring staff compares

the summary of samples received in the laboratory report to the submitted COC form to ensure that all

of the sample information, including well identifiers and sampling dates, match. If the MPCA staff notice

any discrepancies during this review, the MPCA and the laboratory both make the corrections in the

laboratory’s LIMS and MPCA’s water-quality database. It is important to update both databases with any

changes. In some rare instances, the laboratory makes a correction to their data and reissues the

information to the MPCA. Sometimes these data reissuances can occur years after the original sample

collection date, and the reissued data readily import much smoother into the EQuIS database when

there are no discrepancies between the agency’s database and the laboratory’s LIMS. The MPCA

monitoring staff also reviews the quality control data contained in the report to ensure they meet the

acceptance criteria. This review includes ensuring that any data that does not conform to the these

criteria are qualified appropriately.

In the last step of the data verification process, the MPCA monitoring staff compare the field and

laboratory data to any prior measurements collected from the well. The staff use a script developed

using the statistical package R to make these comparisons. This script runs beneath a graphical user

interface and outputs summary statistics and plots to identify any chemicals that have concentrations

outside of the normal range expected for the site (outliers). If the MPCA staff notice any outliers in this

review, they then examine the analytical data associated with the entire batch of the chemicals analyzed

with the outlier. The staff do this to determine the source of the suspect value. The outlying value could

result from a bottle switch in the field or laboratory. In rare instances, the laboratory inadvertently does

not apply a dilution factor when performing calculations. The MPCA monitoring staff work with the

laboratory to correct any of these errors if they are identified. In these cases, the laboratory issues an

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

amended data report and laboratory EDD to the MPCA. To date, data verification reviews by the MPCA

have not found any errors resulting from bottle switches. In the event that a bottle switch was found,

the data would be set with an approval code of “Rejected” unless other supporting information from the

field or laboratory justify changing information.

Laboratory audits

Staff from the MPCA’s Environmental Data Quality Unit audits the data packages completed for the

agency’s Ambient Groundwater Monitoring Network. These audits currently focus on the CEC data

packages, but these QA/QC efforts also can include reviews of project or program data quality

objectives, contract compliance, and laboratory quality assurance project plans. The MPCA uses these

audits to detect deficiencies requiring specific program or laboratory corrective actions, and are crucial

to verifying that MPCA staff and contractors follow all required protocols. The probability of errors,

undetected problems, and compromised data quality objectives increase enhancing the need to re-

sample or redesign the program when the MPCA does not conduct audits. Unresolved errors may

corrupt datasets and render data useless.

If the MPCA finds any irregularities during the data package audits, the agency may require an

explanatory narrative that fully discusses sample results and the problems encountered followed by a

corrective action. These data audits will require the laboratory to submit a variety of information such as

instrument printouts, chromatograms, raw data, sample calculations, complete project/COC records,

detection limits for current instrument studies, documentation for the preparation, analyses, and

reporting of the sample data, all of the laboratory’s internal QC information, and documentation on

source and tracking of standards. The MPCA staff use these data to verify that the data reported is

meeting the quality criteria stated in the laboratory’s Quality Assurance Manual and Standard Operating

Procedures.

Data dissemination

The approved information from the MPCA’s Ambient Groundwater Monitoring Network is available to

all staff in the agency, as well as the public. The data is available to all agency staff through either the

EQuIS Enterprise or EQuIS Professional programs. By default, the most-commonly used EQuIS reports

retrieve water-quality data that the MPCA staff set with an approval code of “Final” in the database. The

MPCA also disseminates approved data to the public online through the MPCA’s Environmental Data

Access (EDA) Portal and the EPA’s WQX. In the EDA portal, the user can download the information, view

it in a tabular format, or display it on a map. While viewing in tabular form in EDA, the user can filter the

results by the station identifier, name, purpose, and/or type as well as specify a start date, analyte

group, geographic type or a specific area within the chosen geographic type. In the map view there is

the option to display groundwater stations, and drinking water supply management areas, as well as

boundaries for cities, townships, public land survey system (PLSS), zip code, MN districts (i.e. house, and

senate), counties, and watersheds. There is also the option while viewing the map display of the data to

use tools such as zoom, pan, search, draw, measure, print, retrieve the coordinates of a location, and

change the base map.

These two data portals are available online from either the MPCA or EPA at:

https://www.pca.state.mn.us/environmental-data

https://www.epa.gov/waterdata/storage-and-retrieval-and-water-quality-exchange

Data Management Protocol • July 2017 Minnesota Pollution Control Agency

MPCA policies and memorandums

Agency policy and state law governs the management of the data from the MPCA’s Ambient

Groundwater Monitoring Network. Some of the key data management policy documents and statutes

are listed below.

Minnesota Data Practices Act. 13 Minnesota Statutes (2017).

Minnesota Pollution Control Agency. Record Retention Schedule (2010).

Minnesota Pollution Control Agency. Administrative Policy: Not Public Data Protection. (2016).

Minnesota Pollution Control Agency. Administrative Policy: Records and Data Management. (2016).

Minnesota Pollution Control Agency. Ambient Groundwater Monitoring Network Recordkeeping

Procedures (2016).