Building Tomorrow’s System to Meet Yesterday’s Problems

Since 2009, EPA’s Office of Ground Water and Drinking Water (OGWDW) has been trying to replace its aging and technologically obsolete SDWIS State system. Originally designed in the mid-1990s and based on a data model going back to the 1980s, the majority of the States, Territories, and EPA Regions use SDWIS State to help manage their Public Water System Supervision Programs.

As drinking water regulation is a data and labor intensive activity, it is imperative that the EPA Regions and the states, territories, and tribes responsible for implementing the drinking water regulations have effective data systems supporting them in this work. EPA and its regulatory partners can make the job of ensuring the public has water safe to drink much easier and less labor intensive by leveraging the latest data analytics and information management technologies.

Recently, OGWDW stopped work on SDWIS Prime, the system slated to replace SDWIS State, because of problems with its database design and issues with incremental deployment of development code into the production environment of a closely related system. During this latest hiatus, OGWDW reexamined the cost benefits and alternatives analysis  (the last one dating from ten years ago) to revalidate the original decision made in 2010 to replace SDWIS State with a new custom-developed system. Results of this analysis have not been shared with the public, so the future technical path of the system is a matter of conjecture.

Part of that reassessment involved prioritizing business and functional requirements of the on what was delivered so far with SDWIS Prime and what shows on the product backlog. Most of the user stories in the backlog were reverse-engineered from the  legacy SDWIS State system. Not included in the backlog were user stories supporting the scientists and engineers at EPA who review the effectiveness of the drinking water regulations, executives leading drinking water regulatory programs, those supporting improved data sharing among the states and EPA, and EPA Regions responsible for discharging their program oversight responsibilities.

There’s a very real risk that OGWDW will deliver a new system that doesn’t meet today’s requirements and those of the near future. OGWDW could be building tomorrow’s system meeting yesterday’s requirements.


Before I go on, just a reminder that the opinions expressed in this article are mine and mine alone. They do not represent those of the US Environmental Protection Agency or any of is regulatory partners and contractors.

Program Perspective

All of the states and US territories using SDWIS State have delegated primary enforcement responsibilities under SDWA. EPA provides SDWIS State to these states and territories (and some EPA Regions) at no cost. They are, however, responsible for operating and maintaining their instances of SDWIS State. Over the years, many of the states have been consolidating their information technology support across their governments to improve efficiency and realize cost savings. This has affected many of the state drinking water programs.

As important as drinking water is, it often does not have the same priority as other state programs. As a result, many state drinking water regulatory programs do not enjoy the level of IT support as they once did. Many states cannot keep their versions of SDWIS State up to date because they don’t have the necessary technical resources to manage the system and to support their end users. Instead, they rely on OGWDW contractors (often at OGWDW expense) to provide that support.

At least 55 of the 67 drinking water programs in the US are using SDWIS State and OGWDW found that the majority of them are not using the system to its fullest potential. For example, several states have developed proprietary systems in their day-to-day work and then use SDWIS State as a final “data collection point” for reporting quarterly data to EPA.

For example, there was one state categorized as a SDWIS State user that was using a collection of Microsoft Excel spreadsheets for its day-to-day work. Each quarter, the state would extract data from their spreadsheets and consolidated it into SDWIS State for their quarterly report. This state really did not have a data system for managing its drinking water program. Hopefully it’s the only one operating this way.

About eight states use their own proprietary systems instead of SDWIS State and several EPA Regions use a system programmed using Microsoft Access and developed by an EPA Region. Most of these systems have become technologically obsolete and require extensive rework, if not full-on replacement. Yet these states are leery of investing their taxpayer dollars in developing replacements for their systems, especially given the hits to state budgets because of COVID. Furthermore, considering that each drinking water program is doing pretty much the same thing, why develop 67 versions of the same system?

Now many of the “proprietary” states are banking on OGWDW and EPA to lead they way in developing and funding the SDWIS State replacement. All this while OGWDW experienced budget cuts and has had to strip funding away from other aspects of the drinking water program to fund SDWIS State replacement. OGWDW could, under SDWA, use funding from state technical grants to pay for the new system, but the states won’t agree to it because they need the money to help fill in the funding gaps in their drinking water programs.

There’s No Free Ride

While it may seem that the states are getting a “free ride” by having OGWDW pay for the new system, that’s not the case. States will have to retrain their workforce to use the new system and they’ll have to pickup, at least, some of the cost for that. Early on, OGWDW agreed to pay for operating and maintain the new system so the states didn’t have to worry about those costs. Whether OGWDW can afford to do that in the future, however, is a different matter.

Because SDWIS State doesn’t cover all of the state’s needs, there’s a large number of specialized systems developed by the states that feed data into and out of SDWIS State. For example, SDWA mentions nothing about collecting fees from regulated water systems, but many states do just that. These billing and permitting systems use data in SDWIS State to identify the water systems and to maintain contact information.

There are other state developed systems used for scheduling and tracking water system engineering reviews, site visits and “sanitary surveys,” water system operator certifications, and other business and technical needs associated with managing a drinking water regulatory program. A large number of these systems, or “interfacing applications,” are specialized database queries and business intelligence reports. Whether these are really applications or not, it doesn’t make much difference because the states depend on them.

As states have been developing and maintaining these interfacing applications for almost as long as SDWIS State has been around, they are a significant investment. Often, business processes specific to an individual state has grown around or been influenced by its interfacing applications. Because of their reliance on these interfacing applications, they have been a significant barrier to moving off of SDWIS State and onto a more modern system.

There’s three paths to making these interfacing applications work with a new SDWIS State replacement system: modification, replacement, or consolidation. Modification means just that; changing each application so that it can communicate with the new system. If the application uses older technology that can’t withstand modification, then the next option is to replace it with a new application. If replacement isn’t possible, the last solution is to consolidate the application into the SDWIS State replacement.

The last solution would make the SDWIS State replacement system enormously complex and frightfully expensive. It also assumes that OGWDW would pick up the tab for designing and developing state specific features for the new system.

Back in March 2009, when I first joined EPA, the Office of Water (OW, the parent office of OGWDW) decided to review the cost/benefit to EPA of continuing on with SDWIS State. Included in the analysis was a comparison of several alternative solutions to the business as usual case of SDWIS State. It took over a year to complete the analysis and it became obvious that the most effective solution, cost, benefit, risk, and operational efficiency wise, was to move to a centralized system shared by all of the states and EPA Regions.

There was significant resistance by many of the states over this finding, some of it driven by the investments in the interfacing applications I spoke of earlier and the realization by EPA that these applications depended upon Open Database Connectivity (ODBC) to communicate with the SDWIS State database. States would have to rewrite these interfacing applications to switch over from ODBC to some kind of application programming interface (API) scheme that would avoid the cybersecurity risks of using ODBC across the web.

One way to address this issue is to use a Virtual Private Network (VPN) to connect the interfacing applications across the web to the new centralized system. Interfacing applications and how they would work with the centralized SDWIS State replacement system wasn’t considered in the analysis. If it was, the cost, risks, and benefits of using a VPN to further secure communications between interfacing applications and the new centralized system would have been included in the analysis. Or at least triggered a revised analysis. But that wasn’t the case.

Instead, OGWDW, following the lead of the OW Project Management Office (PMO), planned on developing an application programming interface and provide opportunities for states (but not EPA Regions) to receive grants for modifying or replacing their interfacing applications. The development team exerted considerable effort designing and demonstrating a ReST application programming interface (API) during the second attempt at building the SDWIS State replacement (that attempt used Google Web Toolkit for the front end on top of a Java/Spring framework).

Another major barrier to starting SDWIS State modernization development was the answer to the question of where this new centralized system would be hosted. Would it be EPA’s National Computer Center (NCC) or some other provider?

This was during the time when cloud hosting first arrived on the scene and the Obama administration was pushing for data center consolidation and moving workloads to the cloud. The OW PMO spent considerable time working on ways to host the new SDWIS system using a less expensive external cloud hosting provider. In the end, NCC won out. But the path to that decision consumed several years. As OGWDW didn’t know where the new system would be hosted or the architecture of the hosting site (Windows or Unix, for example), it couldn’t start writing code for the SDWIS State replacement.

Old Requirements for a New System

While OW worked on the hosting issue, OGWDW worked with the end users to revalidate the requirements for the replacement system. This was a huge challenge because the users wanted the new system to work exactly like the legacy SDWIS State system. In fact, the user community forced OGWDW to align the system scope (or the sum total of system functionality) of the new SDWIS NextGen (as the replacement system was known at that time) to be the same as the last version of SDWIS State deployed by EPA.

Despite what the states wanted, OGWDW had a real problem with defining the new system in this manner. For one, SDWIS State was an unfinished system. Several critical SDWIS State features were never fully developed, particularly those having to do with monitoring and sampling schedules. Furthermore, the audit capabilities of SDWIS State, a requirement critical for OGWDW, Office of Enforcement and Compliance Assurance (OECA), and EPA Regional oversight, were lacking almost entirely.

Aside from system features, the SDWIS State user interface is an archaic throw-back to early web browser based applications, completely unlike the single web page applications we find today. Often, users ran dual SDWIS State sessions in separate browser windows to get around usability issues. In a way, it was strange to see how many users were so attached to SDWIS State while, at the same time, complaining about its nonintuitive user experience.

After the release of the second web-based version in the mid 2000s (replacing an earlier non-web based client/server version), SDWIS State was pretty much frozen and most new features implemented through workarounds and stand-alone applications that interacted with the SDWIS State back-end. Core SDWIS State features continued with the archaic user experience while new stand-along modules relied on tools like Adobe Flash to implement a more up to date look and feel and workflow jarringly dissimilar from the legacy core system. And yet, in the state’s eyes, this was the system that served as the baseline for SDWIS NextGen.

More importantly, it wasn’t a matter of simply tacking on a new user interface to an old system. As I mentioned earlier, SDWIS State is based on a data model stretching back to the 1980s. Since that time, the drinking water regulations have proliferated and become more complex. The underlying data model didn’t change and hasn’t been keeping up.

This data model, by the way, isn’t restricted to SDWIS State. It’s the data model used by the drinking water program. You can see part of it by looking at the SDWA 3.5 Data Exchange Template published by the Environmental Information Exchange Network. You’ll need to be proficient in reading XML (eXtensible Markup Language) schema files to understand the data that states are required to report to EPA each quarter about their drinking water programs.

OW maintains a perfunctory description of the reported data on its SDWIS Federal Reporting Services web page. You’ll learn more about the this part of the drinking water data model by diving into the SDWIS Fed Reporting Services and Drinking Water GPRA tool. More descriptive documentation about drinking water data is not readily available to the public; you’ll probably need to make a Freedom of Information Act (FOIA) request for that information.

Once you dive into the XML schema retrieved from the Environmental Information Exchange Network or manage to get a copy of the SDWA quarterly reporting data requirements, you’ll see that it’s a fairly sparse dataset. This is by design because back in the 1990s, when the reporting requirements were developed, sending large datasets into EPA was nontrivial. The internet just came into being and high speed data connections between remote systems were scarce and very expensive.

Aside from the technology limitations, the states didn’t want to be burdened preparing a potentially massive data report and OGWDW didn’t want to burden the states. That’s mainly why the dataset is sparse and reported only once per quarter. No other regulatory program at EPA has this long of a reporting lag.

A well-design, modern data system should not have any problem shipping a comprehensive dataset to EPA at frequent intervals without affecting the states’ burden. However, if a state chooses not to adequately invest in its drinking water regulatory program (and some do not for various reasons), then it may not be able to keep up with more frequent EPA reporting requirements. Thus, a new system could inadvertently identify states with underfunded and understaffed drinking water programs. Perhaps another reason for maintaining the status quo?

Getting back to requirements coverage and the 21st century, the Venn diagram below shows the “big picture” of requirements for the replacement to SDWIS State. The pale blue circle represents the user stories for developing the new system (SDWIS NextGen/ SDWIS Prime/ SDWIS Modernization). The three non-overlapping circles within the pale circle represent the user stories for three closely related legacy applications and the workflows between them.

SDWIS Modernization User Story Coverage

I’ve already discussed SDWIS State, so I won’t repeat that here. SDWIS FedRep is a separate application used by the states for reporting data to EPA. FedRep extracts the necessary data from the SDWIS State database and formats it into the XML files specified by the SDWA 3.5 data exchange discussed earlier. Like SDWIS State, each state maintains its own instance of FedRep. FedRep is rendered redundant in a centralized system as is the current practice of generating XML reporting files and sending them, via the EPA’s Central Data Exchange (CDX). The entire quarterly reporting data flow could be replaced with an Extract-Transform-Load (ETL) process. (This process could also execute on demand in certain situations for certain states.)

The third legacy application shown in the Venn diagram is the Compliance Monitoring Data Portal (CMDP) which you can find out more about here. CMDP is a good example of what happens when people having no business designing a complex business system are placed in charge of doing just that. It’s also an example of a solution looking for a problem, which is why few states adopted the system for so long. Not only does CMDP pull scarce EPA IT resources away from SDWIS modernization, but the EPA bears the entire cost of operating and maintaining CMDP while receiving little benefit.

Practically all CMDP functionality could be subsumed by a combination of a properly designed SDWIS State replacement system and existing EPA services (such as the Central Data Exchange, which CMDP duplicates, and Shared CROMERR Services). It would also help to work with the Exchange Network to design a new data exchange template for monthly operating reports (submitted by water suppliers), something that is sorely lacking with CMDP.

More importantly, what the Venn diagram is trying to show is that the drinking water regulatory community’s laser-like focus on SDWIS State leaves out user stories that go beyond CMDP and FedRep. For example, the new system should track the evolving relationship between a water supplier and the various facility components within a water system over time. This would allow the states and EPA to track consolidation of water systems over time, much like the Federal Financial Institutions Examination Council’s National Information Center.

Other user stories would describe new workflows, such as the process for accepting/rejecting candidate violations and rescinding accepted violations, replacing ETL-based reporting process discussed above, and merging water system data for tracking water supplier consolidation. Some other user stories could include improving data sharing between coregulators by supporting real-time access to data and issuing alerts and notifications to water systems and laboratories using text messaging and other communications channels.

An area that several drinking water administrators expressed great interest was business activity monitoring (BAM) and the use of audit data to measure the efficiency of drinking water regulatory processes. Using BAM data, administrators could identify bottlenecks in their processes and adjust resources accordingly. Furthermore, administrators could use BAM data to help justify budget increases to their drinking water programs by tracking work not getting done.

There are many other user stories in the light blue area concerning business process and data model improvements that never saw the light of day, like those supporting the scientists and engineers and OW and their information needs for evaluating the effectiveness of the drinking water regulations. However, there were some built into the model prototypes built when SDWIS Prime development went on hiatus to accommodate CMDP development.

For example, the prototyping team in the summer of 2016 developed a scheduling model based on generally accepted task based scheduling concepts, which was a radical departure from the scheduling approach used by the legacy SDWIS State system. OGWDW identified a commercial off-the shelf tool the development contractor could use in conjunction with the Google Web Toolkit (discussed above). Using this off the shelf toolkit would have accelerated development and improved the chances of delivering the initial production release of SDWIS Prime (as the replacement system was known as at that time) by March 30, 2018.

Now OGWDW is embarking on the fourth incarnation of SDWIS modernization. Whether that incarnation will involve moving to a commercial off the shelf solution, continuing on with SDWIS Prime development while fixing the problems programmed into the system, or doing something completely different is known only to OGWDW and its regulatory partners. But with the turnover in EPA and contractor staff on the SDWIS modernization project and in the drinking water program in general, it’s likely the “light blue” user stories are going to slip through the “cracks.”

As OGWDW and its regulatory partners embarked on the second decade of working on a replacement to the legacy SDWIS State system, they should carefully consider if it’s building a new system meeting requirements developed in the 1990s and geared toward the data model of that time, or if it is really building a system for the future leveraging the technology of today and tomorrow.

OGWDW and state stakeholders, especially those who are users of this new system, need to step outside of their comfort zones and have those light-blue zone conversations. Doing so would help ensure that OGWDW and its partners get it right the fourth time.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: