Lessons Learned from Getting Data into STORET: A State’s Perspective

1. Lessons Learned from Getting Data into STORET:A State’s Perspective Mike Beauchene, State of Connecticut Department of Environmental Protection; Mellony Hoskinson, Dwane Young, RTI; Dave Wilcox, Gold Systems

2. STORET Success

3. Overview • Connecticut’s Data Management System • Re-formatting data to STORET specifications • Column Mapping • Crosswalk Tables • Queries • Challenges/Benefits

4. Crosswalk SIM The Process CHAOS Database Step 1. Create CT DEP Database Step 2. Populate the database Step 3. Data Analysis Update database and create crosswalks $$$ Queries Step 4. Build Queries Step 5. Load to STORET Now that the process is developed, data can be loaded to the CT DEP database, and easily migrated to STORET.

6. Connecticut - 2001 Step 1 – CT DEP creates a database to store monitoring data that meets DEP’s needs • Move data system from hard copy to electronic system • Use STORET data structure as guideline for setting up Connecticut’s database • Projects • Trips • Stations • Key to this process is implementing required STORET data elements • Analytical methods • Gear Type • Sample Collection Procedures, etc.

7. Database Elements Samples Stations Projects Trips

8. Assigned Project IDs and appropriately tagged all related data under project Project managers selected from STORET lists which methods, gear, collection procedures, etc. were used on project Projects are maintained separately, but all contain same data elements and can be imported into STORET Connecticut’s Data Management System Step 2 – Populate CT DEP Database

9. From State to STORET

10. Analysis • Identify and map CT DEP data elements to the corresponding STORET data elements • Projects • Stations • Trips • Activities • Identify missing data elements that would be required by STORET Step 3 – STORET Comparison

11. Crosswalk Tables Step 3 cont. – Build Crosswalk Tables • Match CT DEP values with STORET values • Accounts for capitalization, abbreviations, etc. • Provides a means for accounting for data elements that are required by STORET, but is not in DEP’s database • Can be easily modified to reflect changes made in current system • Crosswalk Tables are the keys to creating new STORET formatted queries

12. Example of how a characteristic in CT DEP’s Database could be broken down in a crosswalk table to provide for the necessary STORET data elements.

13. Data Select Queries Step 4 – Build Queries that use DEP database and crosswalk tables to create SIM ready files for loading into STORET • Query design • State data tables • Crosswalk tables • Queries take care of conditional situations (i.e. adding *Non-Detect when necessary) • Since the queries run directly on CT DEP database, when DEP data changes, so do the queries • Provide a means for flagging data in CT DEP database that has been loaded into STORET

16. Load the Data to STORET Step 5 – Load the data into STORET • Using SIM and the data queries, DEP can now load the data into STORET • Using these same queries, DEP then can flag all of the data (Stations, Activities) that have been loaded to STORET so that they would be excluded from future data loads

17. Challenges to Overcome • Some data elements discovered during the data analysis that are required by STORET, and therefore required changes to CT DEP’s database. • Crosswalks have to be kept up to date. If CT DEP adds a new characteristic, then that characteristic needs to be added to the crosswalk.

18. Benefits to State Maintain original data management system If there is a need to change the format for the SIM-ready files, then it’s relatively easy to change the queries. DEP doesn’t need to adopt all of the STORET codes, but rather just needs to keep the crosswalks up to date. Now that the data analysis is done, and the process is established, future data loads to STORET are relatively painless.