Transcript SWAMP (Surface Water Ambient Monitoring Program): California`s

California’s Surface Water Ambient Monitoring Program
Data Management Systems
Cassandra Lamerdin
SWAMP Data Management Team
Marine Pollution Studies Laboratory
Moss Landing Marine Laboratories
Moss Landing, California
Presentation Outline
 SWAMP Database Goals
 Start Up Principles
 Data Elements
 Database Systems
 Questions
SWAMP
Data Management Vision and Goals
 Provide data of known & documented quality
 Create & document systems which ensure data
comparability
 Make information available to all stakeholders in a
timely manner
 Facilitate the use of data for decision making
processes
Guiding Principles
 Capture analytical QC information in the
SWAMP database
 Do not store calculated values where possible
 All samples will have unique geo-references
and information about how samples were
collected
 Data enters database as “draft” and becomes
public after SWAMP procedures are completed
Challenges We Faced
 Varied study designs
 Varied waterbody types
 Multiple external sources of data
 Analytical laboratories use performance-based
methods
 Multiple end-user groups and purposes
SWAMP Data Elements
Discrete
Field
Measures
Time
Series
Chemistry
“SWAMP”
Tissue
Toxicity
Biological
Assessment
Responsibilities of
SWAMP Database Management Team
 Data Entry Training/Facilitation
 Field data
 Analytical data
 Database Management/Quality Assurance
 Work with each Regional Board individually
 Comparability checking
 Verification of Data Quality Indicators (QAMP)
 Compliance Coding
 Transfer data from Temporary to Permanent
 Data Training/Accessibility
 Regional boards
 State board
 Outside SWAMP groups
Data Facilitation
Logistics Coordination
Contracts/Work Orders
Data Management
Regional Water Boards
State Water Boards
Reporting/QC
Analyte/Stations Maintenance
Laboratories
Sampling Crews
DFG/Contractors
Regional Water Boards
Data Flow Overview
Samples Created
in Field
Lab
Field Data
Submission
Lab
Lab
Analytical Data
Submission
Lab
Toxicity Data
Submission
Batch Loaded
Data Entry Forms
Batch Loaded
Database
(Temp side)
SWAMP Field Methods Course
For Copies
Contact George Nichol
916-341-5504 or
[email protected]
Example SWAMP Data Sheet
SWAMP Data Entry Forms
Analytical Data Facilitation
 Use Standard Formats/Training Manual
 Lab Liaison develops a rapport with labs
 Results in data being reported more efficiently
 Ongoing training
 Many labs do not have LIMS systems
 Provide converters to assist with formatting SWAMP
data
 Upload to Database using Data Loading
Program
 Data Checkers (Batch Level and Global level)
 More Automation of SWAMP Systems
Online Templates and Documentation
http://mpsl.mlml.calstate.edu/swamp.htm
SWAMP Data Systems SOPs
Data Flow Overview
Samples Created
in Field
Data
Verification
by Lab
Lab
Field Data
Submission
Lab
Lab
Analytical Data
Submission
Lab
Toxicity Data
Submission
Batch Loaded
Data Entry Forms
Data
Verification
by Field Crew
Batch Loaded
Database
(Temp side)
Preliminary
Verification
by DMT
Responsibilities of
SWAMP Database Management Team
 Training/Facilitate Data Entry
 Field Data
 Analytical Data
 Database Management/Quality Assurance
 Work with each Regional Board individually
 Comparability checking
 Verification of Data Quality Indicators (QAMP)
 Compliance Coding
 Transfer data from Temporary to Permanent
 Data Training/Accessibility
 Regional boards
 State board
 Groups outside of SWAMP
Regional Board Liaisons
 Consult on integration of sampling designs into
information management
 Modify field data sheets
 Assist with data entry training
 Completeness checking
 Follow-up/track down any missing data
 Coordinate database distribution
 Data access training
 Status of data reporting
DMT Data/QA Process Overview
Temporary
Side
Transfer to
Permanent
Side
Export to CEDEN
Export to CEDEN
Data available
to the public
(Web)
DMT Data/QA Process Overview
Temporary
Side
Transfer to
Verify
Transfer
Global
Checks
Export to CEDEN
Export to CEDEN
Contractual Project Completeness
Verify MQOs
Compliance Codes
Permanent
Side
Verify Export
Data available
to the public
(Web)
SWAMP Data Verification Process
 Verification against project MQO’s per batch per Project
 Analytical data
 Toxicity data
 Field data
 Verification of QA codes applied on both a batch and result level
 Original codes applied appropriately
 Apply any additional codes if necessary
 Compliance codes applied based on QA code combinations
 Compliant, Estimated, Historical, Rejected
 Global verification performed on both permanent and temporary
side
 Business rules
 Completeness
 Consistency
 Verified batches transferred from temporary side to permanent side
SWAMP Data Comparability Examples
Analytes, Matrices and Units
SWAMP Business Rules Examples
Responsibilities of SWAMP Database
Management Team
 Data Entry Training/Facilitation
 Field data
 Analytical data
 Database Management/Quality Assurance
 Work with each Regional Board individually
 Comparability checking
 Verification of Data Quality Indicators (QAMP)
 Compliance Coding
 Transfer data from Temporary to Permanent
 Data Training/Accessibility
 Regional boards
 State board
 Groups outside of SWAMP
Data Training / Communication
 Creation and maintenance of documentation
 Field crews
 Laboratories
 Data users
 Agencies required to be SWAMP comparable
 Initial and ongoing training
 1st Priority: SWAMP Participants
 2nd “Non-SWAMP” parties required to be comparable
 SWAMP User Group
 Information exchange
 Maintain comparability in data reporting
Data Accessibility

Data access on temporary and permanent side
 User has ability to build new queries
Data Distribution on WWW
http://baydelta.ca.gov/
Successes
 SWAMP has created information management
standards and documented processes to meet our
goals
 All 9 Regional Boards are using SWAMP Database,
although RB use varies: simple data access to data
loading
 Continue to collaborate with other agencies (SCCWRP,
Bay Foundation, DWR) for system automation
 Partnership with DWR allows more time and effort
spent on science and less time on technology
development
Questions?