Executive Summary/Abstract

Based in the foothills of California’s Sierra Nevada Mountains, Sierra Streams Institute (SSI) uses science, research, and monitoring to protect the highly sensitive ecosystems in the Deer Creek and Bear River watersheds. Water quality monitoring is a key tenet of their work. Staff and volunteer monitors collect important water data which is used to evaluate baseline conditions, perform targeted assessments, and ensure overall watershed health. With over 20 years of data already collected, a modern solution was needed to help better manage and organize SSI’s water data in a way that would, in tandem, increase the data utility for SSI and their stakeholders while also making the data more accessible to watershed communities. To achieve this goal, SSI deployed a cloud-based software as a service (SaaS) workflow utilizing an Airtable online database linked with a publicly available water data dashboard. With this workflow, SSI streamlined its data collection and management and gave the public increased access to up-to-date water quality information that conforms to all updated FAIR data standards.

Introduction

Watersheds of the Sierra Nevada Mountains face an abundance of threats to ecosystem health including wildfires, derelict mines, and agricultural runoff. These waters support diverse and sensitive ecosystems as well as many watershed communities. Valuable resources like these necessitate the continued evaluation of local water quality. Back in the 1990s, community members identified dangerous data gaps in water data due to the diversity in watershed geography, access, and agency capacity. The response to address the deficits was to create Friends of Deer Creek in 1995 — which began collecting some of the first community-generated water data in the region. Friends of Deer Creek evolved into Sierra Streams Institute (SSI) in 2010 and throughout this evolution, they’ve become one of the leading environmental non-profit organizations in the area, dedicated to diagnosing and solving important environmental problems throughout the Sierras through research, monitoring, and watershed stewardship.

To glean the greatest amount of information from their water(shed) monitoring efforts, SSI focuses on three primary areas: quarterly baseline monitoring, targeted monitoring to evaluate the effects of extreme conditions in the watershed, and annual creek macroinvertebrate surveys. By combining all three, SSI maintains a reliable and continuous picture of watershed health while staying abreast of new and emerging threats to local streams and rivers. Unfortunately, almost thirty years of water quality data can quickly become a burden and lose its usefulness if not organized and managed in a way that allows for easy access and sharing, something SSI realized and sought to remedy through their work with The Commons.

The need for a standard in community-generated water data

FAIR (Findable, Accessible, Interoperable, Reusable) data standards has become the minimum requirement for environmental data used in everything from policy, to rulemaking, and enforcement. These standards make it so data from a myriad of different sources can be accessed, compared, and utilized to make vital up-to-the-minute decisions about our waterways. Most community-generated data, meanwhile, does not yet meet FAIR data standards. This data is important for public awareness and filling data gaps, it historically suffers from a lack of concrete standards which frequently leads to a wide spectrum of data management methods and siloed data. Public availability of water data is a cornerstone of all community water monitoring groups so improving data management strategies to increase accessibility and use is a critical investment.

Planning

Increased access to cost-effective technology allows grassroots environmental organizations to quickly modernize their data practices to more closely resemble techniques used by local, state, and federal agencies. Two capacity restraints obstruct efforts to overhaul data management strategies. First, technology and data literacy gaps invalidate inertia regarding addressing existing data management challenges. Second, financial concerns present a real barrier to new software and SaaS adoption. The Commons’ Digital Services program was created to streamline data management to meet FAIR data standards for community environmental groups while making it more approachable and cost-effective. By using readily available, industry-leading software as a service (SaaS) platforms, projects can be completed in a shorter window of time, allowing groups to remain agile and update their data management workflows with no delay in data collection or program work.

Going into this project, SSI already had a robust water quality program with an established workflow. Staff recognized the need to build on what was working well while modernizing their data collection and management methods and doing it in a way that allowed that data to be more accessible to the larger community. To achieve this, the project team agreed that SSI’s water quality data had to first be centralized and organized in a way that allowed the SSI team to easily work with the data and second, be easily integrated via application programming interfaces (APIs) to data visualization software. In the end, the cloud-based data management platform Airtable and geospatial platform ArcGIS Online were selected to create a SaaS data management workflow for the project.

Methodology

This project focused on modernizing data management systems for water quality assessments and public education. At the start, SSI had an existing data management system with some lightweight visualizations associated. They wanted to build a data management system that was more modern and robust and would enhance the overall value of their water quality program. To start, the biggest question we had was how do we improve upon something that’s not broken? A majority of data-based projects are seeking to create new data systems and build them from the ground up; but for this project, we wanted to improve upon (through modernization) the system that was already in place. The elements outlined in this section characterize the below-the-deck work undertaken and tools used to facilitate efficient collective analysis, reporting, and communication.

Assembling the software toolkit: curating a data management system

Let’s walk through the software we used to complete this project. 

Airtable

‍Airtable is a cloud-based data management platform that allows users to configure, input, store, and organize large datasets in user-friendly ways. For this project, Airtable serves as the centralized database to aggregate all of SSI’s past and future water quality data. Additionally, Airtable’s open and accessible format allows for multiple integrations with various third-party applications, setting the project up for success for public visualizations and interactive data exploration. We configured Airtable to push hosted data to an ArcGIS Online feature service that can then power a public-facing data visualization. Further, Airtable’s flexibility related to scripting and formulas enabled SSI to have built in quality control calculations for key parameters. 

ArcGIS Online 

‍ArcGIS Online is a cloud-based geospatial mapping and analysis service offered by ESRI as a part of their larger geographic information system (GIS) services. With ArcGIS Online users can produce maps, dashboards, story maps, and various other public-facing tools to better share their data with the public at large. For this project, ArcGIS Online was used to produce a map and data dashboard to publicly share discrete and continuous water quality data with SSI stakeholders and the larger watershed community.

Building an effective data management system

Step 1. Build a standard data model and template

With over 20 years of water data on hand, one of the last things you want to do from a data side is to totally teardown existing data models. A key first step in enhancing SSI’s data management system was to start with their pre-existing data model and retool it in a way that would allow for it to seamlessly be imported into Airtable’s database software while maintaining its core structure. Legacy water data was then migrated into Airtable within the new data model.

Changes were made which allowed for on-the-fly quality control calculations for key parameters and the addition of important contextual fields for data visualization like “Watershed” and “Monitoring Program”. Data models can differ depending on the intended use case for the data, so for this project, an emphasis on data quality and visualization influenced the construction and format of the model.

Maintaining a similar data workflow to the one SSI had previously established was important to help with adoption and decrease the time it would take to train staff on the new system. Once the modified data model was established in the database, we used Airtable’s “Forms” feature to create an easy-to-use data input form mapped to the specific data model we created, allowing SSI staff to easily input new data digitally as they would on a field or datasheet. With a fully functional Form for data input, SSI can now easily add new samples to their water quality database in Airtable.  

Step 2. Get all data into a standard, structured format

An exceptional feature of SSI’s water quality dataset is its longevity, with samples dating as far back as 2000. With over 23 years of data, we wanted to ensure that all legacy data could be retained and moved into the new data model built into Airtable. Airtable has a host of different integrations and extensions that allow administrators to work with their data in a variety of different ways from field mapping to third-party software connection. To ensure all of SSI’s legacy water data was moved quickly and without error into the new database we used a data import extension that enables the upload of pre-filled CSV files with the option for field mapping, append, and merge. With this tool, legacy data could simply be uploaded into Airtable and fields mapped to their appropriate counterparts in the new data model.

Once all legacy data was uploaded and mapped to the new data model in Airtable, SSI’s data collection could resume as normal with all of their water data now centralized in a single database. Additionally, with all data in a structured, machine-readable format it could be connected to ArcGIS Online for POST and PATCH operations for subsequent display in an AGOL configurable dashboard.

Step 3. Connect Airtable database with ArcGIS Online

A substantial benefit to using a SaaS workflow for your data management system is the built-in integrations already available to users. These allow users to connect to third-party applications that can expand the utility or optionality of the central data and workflows. Airtable provides fully documented and open application programming interfaces (APIs) built on users' established databases. For SSI, a public-facing data dashboard is essential for community education and awareness surrounding the health of their local waters, so having the ability to manage their data in one place and simultaneously visualize it was a key feature of our project. We leaned on integrations to make this happen.

Airtable and ArcGIS Online allow for data exchange between the two platforms. Speed, automation, and data refresh intervals were important issues that needed to be addressed in their connection. The Commons was able to solve these issues through the use of the third-party API connection platform Pipedream, which allows the seamless connection of APIs with minimal coding and an almost instantaneous data refresh. Thanks to this service, whenever data is updated or added to SSI’s database it is instantly updated and refreshed in the connected ArcGIS Online Feature Service and then pushed to their visualization. Our connection includes commands to push edits, additions, and deletions made in the Airtable base directly to the data hosted in the Feature Service. This anchors data management to Airtable and reduces the opportunity to introduce errors to the data powering the AGOL dashboard. This connection is an understated boon to the public who can now get up-to-the-minute water quality updates on SSI’s dashboard the same day that water quality testing results are inputted into the database.

Step 4. Create an ArcGIS Online dashboard to power an interactive application 

To build a public-facing dashboard that requires minimal human oversight and intervention, an Airtable-hosted database is a perfect backend-as-a-service solution to easily integrate data with third-party systems. Our team created an ArcGIS Online (AGOL) Feature Service using SSI’s Airtable base. If you’re familiar with ArcGIS Online feature services, you know that managing data in AGOL can be complex and limited when compared to the fluid editing environment offered by AirTable. Most often, someone is required to routinely update and edit the data within the feature service within ArcGIS online. This creates issues of version control and introduces opportunities for human error and stale data to be passed off to the public as the authoritative source. Our team was able to solve this through Pipedream, allowing SSI to manage/edit all data in Airtable removing the need for in-depth and lengthy edits in ESRI’s Feature Services.

The resulting dashboard built with the SSI Feature Service delivers incredible information to decision-makers across the Sierras and California. The public around Nevada City or as far as Sacramento can easily navigate around the dashboard, having the ability to filter water data by watershed, type of monitoring, and specific sites. With this dashboard established as a go-to place for water quality information surrounding Nevada City, the Sierras communities now have a trusted and reliable source for data that can inform everything from recreation decisions to local or state policy decisions. 

Results

Sierra Streams Institute started as a small clean water advocacy group (formerly Friends of Deer Creek) concerned with the local water quality in Deer Creek and has since grown to one of the leading environmental non-profits in the Nevada City area of California. With one of the longest-standing baseline water monitoring datasets in the Sierras, they’ve formed themselves into local leaders for watershed science and leadership.

“Sierra Streams Institute was founded by volunteers, and community members still play a vital role in our organization. The dashboard that The Commons has built gives us a way to share our data with our community in real-time, which is especially important for the volunteers collecting the data, but also helpful for anyone interested in checking on how the creek is doing. The database behind the dashboard also gives us a single place to store our data, which is both accessible for staff and flexible enough that we can make updates as needed. We are very excited about the results of this partnership - thank you to everyone at The Commons for being so great at solving data challenges!” - Helen Fitanides, Science Director, SSI

Outputs

This project produced a centralized water quality database and user-friendly data management workflow for the collection and storage of baseline, targeted, and stream survey monitoring data collected by SSI. Using the cloud-based data manager, Airtable, allows SSI to perform quality assurance calculations, calculate summary statistics, and intuitively manage their data, making it easier for them to keep a close watch on water quality trends throughout the Sierras. Pairing intuitive data management with maps and visualizations via ArcGIS Online advances the goal of greater public awareness surrounding local water data along with increased stewardship of these waters. With an agile workflow developed by The Commons, outputs produced by this project can easily scale depending on the quantity of data as well as the geographical scope of SSI’s work, allowing them to add sites on the fly as needed while continuing to grow their water quality program.

Outcomes

Modernized data infrastructure

‍While community-based water monitoring is a mainstay of organizations across the clean water sector, many groups lack the data infrastructure and data democratization to achieve the greatest utility from their data. Unfortunately, most groups don’t have the resources or technical expertise to achieve a modernized data management workflow that meets FAIR data standards. Through this project, we were able to merge The Commons’ years of technical experience in environmental data systems with SSI’s robust water quality program to refine their data workflow into one that meets FAIR data standards while maintaining their original data model and structure. Using both an integrated Airtable database and an ArcGIS Online Dashboard, SSI’s water data workflow is now standardized, centralized, and providing valuable knowledge to the community at large via an API-connected database.

Increased access to water data for the Nevada City community

‍Publicly available water quality data can be scant and increasingly difficult to access depending on the publisher. Local, state, and federal agencies collect a wealth of water data that often remains siloed from public eyes, which has caused local community groups to step in and fill that public service by collecting comparable water data and sharing it with the public at large. With water quality data and education as a cornerstone of Sierra Stream Institute’s work, they’ve worked for years to collect high-quality water data and share it with the extended Nevada City community to allow the public to better track the health of their local streams and rivers. With their new public-facing ESRI Dashboard and data visualizations completed in tandem with modernizing their data infrastructure, SSI can now rely on a fully integrated water quality workflow from sample collection to data publishing. 

Impact

Facing wildfires, increased development, and lower annual snowpacks, the Sierra Nevada Mountains are being assailed by a myriad of threats that only seem to grow in number each year. By providing almost half of the state of California’s water supply, the Sierras and the health of its watersheds have a major impact on millions of people across the western reaches of the United States. However, due to their size, geography, and rugged nature of the terrain, the Sierras can be a difficult area to maintain consistent records in for things like water quality and ecosystem health. It's only through the tireless work of a combination of government agencies, universities, and community groups like SSI that watershed health, and the threats facing it, are consistently known throughout the region.

Monitoring 31 sites with data dating back to 2000, SSI has built one of the most long-standing and comprehensive water quality programs in the Sierra region. Enduring, regularly collected water data like this are invaluable, especially in an area whose watersheds simultaneously support so many people while also being at a higher risk for threats like pollution and climate-induced changes. This data loses a lot of its power if it is siloed from the outside world, making modern data management and public accessibility so important. The long-term commitment of SSI to modernize its data systems and share local water data will prove massively beneficial to maintaining the high level of integrity associated with its water programs. Highly successful community-based water quality programs can collect data and get it into the hands of decision-makers and community members alike in a rapid and efficient amount of time to make the data useful for acute and long-term analysis — a  fact that remains constant today as it did at the passing of the Clean Water Act. Moving into the future, with a dedicated and easy-to-manage SaaS workflow, SSI can continue to lead from the front in the Sierra region, collecting high-quality water data and disseminating it to the public at large in a way that's both palatable and beneficial to the greater Sierra ecosystem.

‍

Author’s note: No AI was involved in the writing of this use case. All thoughts and errors were created by a human.