CHAPTER 6: DATA MANAGEMENT AND
RECORDKEEPING
PART OF THE SOURCE CONTROL (BUSINESS/SITE)
INSPECTION PROGRAM GUIDANCE MANUAL
Prepared for
2606 West Pioneer
Puyallup, Washington 98371
Funding Provided by
Stormwater Action Monitoring (SAM)
Prepared by
Herrera Environmental Consultants, Inc.
2200 Sixth Avenue, Suite 1100
Seattle, Washington 98121
Telephone: 206-441-9080
April 29, 2022
Note:
Some pages in this document have been purposely skipped or blank pages inserted
so that this document will print correctly when duplexed.
Table of Contents
cc_20-07522-000_scmanual_ch6_datamgmt_20220428.docx
CONTENTS
Data Management and Recordkeeping ………………………………………………………………………………………….. 1
6.1. Permit Requirements ………………………………………………………………………………………………………………….. 1
6.2. Developing and Maintaining a Centralized Database …………………………………………………………… 2
6.3. Annual Reporting to Ecology ……………………………………………………………………………………………………. 5
6.4. Data Collection and Updates …………………………………………………………………………………………………….. 6
6.5. Source Control Inventory Updates …………………………………………………………………………………………… 6
TABLES
Table 6.1. Source Control Inventory Database Options. ………………………………………………………………………. 3
Chapter 6: Data Management and Recordkeeping
April 2022
Source Control (Business/Site) Inspection Program Guidance Manual
6. DATA MANAGEMENT AND
RECORDKEEPING
This chapter summarizes the National Pollutant Discharge Elimination System (NPDES)
Municipal Stormwater permit requirements for data management and recordkeeping and
provides recommendations for developing and maintaining a centralized database, annual
reporting requirements for the Washington State Department of Ecology (Ecology), data
collection and updates, and source control inventory updates. Supplemental resources to
support this chapter can be found in the Source Control Online Resource Library (SCORL)
for Chapter 6.
6.1. PERMIT REQUIREMENTS
This chapter provides detail on processes to maintain a source control inspection program
database over time, and to efficiently document compliance with the NPDES Municipal
Stormwater permit requirements. The following recordkeeping requirements are specified in the
NPDES Municipal Stormwater permit for a progressive enforcement policy (Western Washington
2019-2024 Phase II Permit, S5.C.8.b.iv):
(c) Each Permittee shall maintain records, including documentation of each site
visit, inspection reports, warning letters, notices of violations, and other
enforcement records, demonstrating an effort to bring sites into compliance.
Each Permittee shall also maintain records of sites that are not inspected
because the property owner denies entry.
See Chapter 4: Developing a Business/Site Inspection Program and Chapter 5: Conducting
Business/Site Inspections for more information on developing an inspection program and
conducting inspections for business/sites.
The NPDES Municipal Stormwater permit also requires recordkeeping for staff training activities
(Western Washington 2019-2024 Phase II Permit, S5.C.8.b.v):
(v) …Permittees shall document and maintain records of the training provided
and the staff trained.
See Chapter 8: Training for more information about required training activities.
Chapter 6: Data Management and Recordkeeping
April 2022
Source Control (Business/Site) Inspection Program Guidance Manual
6.2. DEVELOPING AND MAINTAINING A CENTRALIZED
DATABASE
Each jurisdiction will have unique needs for data management to support internal tracking and
processes. A centralized database is generally recommended to house the source control
inventory and to track activities associated with each business/site over time. For more
information on how to develop an initial source control inventory, see Chapter 3: Source Control
Inventory Development, Updates, and Prioritization.
Within a centralized database, each business/site has a unique ID and associated “record” that
can be used to track several attributes about the business/site. Ideally, related files (such as
photos, inspection documents, or correspondence) can then be attached or linked to the
primary record. Note: More than one business could be located on a parcel, so using a parcel
number may not result in a unique ID for each business, but is still useful to record as a
business/site attribute.
The NPDES Municipal Stormwater permit does not specify a format or approach for
recordkeeping. If a jurisdiction is already using a data management system, it may be most
efficient to fold the source control inspection documentation into the existing system to enable
consistent processes across the organization.
If a new system is needed, the most basic set-up for the source control inventory is a Microsoft
Excel list of businesses/sites that can be matched (using a unique ID, hyperlinks, etc.) to an
individual folder containing all related files for that site. While this set-up may be the most
readily accessible and quickest to implement, there are several limitations to consider for
inspector workflows and integration with other software. For this reason, jurisdictions may want
to consider implementation of a more advanced system. See Table 6.1 for advantages and
disadvantages of various database options. Note that options listed in this manual do not
constitute a requirement or recommendation for proprietary software.
A GIS or other map-based approach is recommended because there are advantages and
efficiencies that can be attained using spatial information. A map-based inventory supports the
following inspection program activities:
● Locating businesses
● Planning business inspections around clusters of businesses
● Identifying priority areas (e.g., proximity to receiving waters)
● Identifying pollution problems within a specific geographic area or subbasin, if
applicable in the jurisdiction’s prioritization process – see Chapter 3: Source Control
Inventory Development, Updates, and Prioritization
Chapter 6: Data Management and Recordkeeping
April 2022
Source Control (Business/Site) Inspection Program Guidance Manual
Table 6.1. Source Control Inventory Database Options.
Data Storage
Platform for
Sites/Businesses
Advantages
Disadvantages
Existing System
Existing Data
Management
System
● Already configured
● Maintain consistent processes across
programs/departments
● Current staff may already be familiar with
system interface and processes
● Information Technology (IT) is familiar with
existing software
● May constrain or limit specific needs
for the source control inspection
program if these do not fit within
the existing system structure/
processes
New System Basic Option – Limited Functionality
Microsoft Excel
(or similar
spreadsheet
software)
● Already available to most municipalities
● Readily accessible, easy to use for most staff,
and easy to share/export/summarize
● Capabilities (via Mail Merge) for auto-
generating correspondence/letters
● Limited spatial integration for
tracking business/sites in map
format
● Limited integration with online/
mobile application workflows for
field inspections and dynamic edits
New System Advanced Options
ESRI File
Geodatabasea
(with ArcGIS
Online Hosted
Feature Service)
Alternate Option:
Open-Source GIS
software (QGIS)a
● Full spatial integration to view and track
businesses/sites in map format that can be
shared with non-GIS users
● Integration with online/mobile application
workflows for field inspections and dynamic
edits
● Web interface is accessible for non-GIS users
or those without a desktop license
● Can be exported to Excel/comma separate
value (CSV) format for reporting
● Capabilities for generating batched
correspondence/letters based on database
inputs
● Can be integrated with Off-the-Shelf
Providers for hybrid workflow
● Requires knowledgeable GIS staff for
setup and support
● High license cost for jurisdictions
that don’t already have a GIS license
● Limited desktop access for
unlicensed users (*see Advantages
for web interface vs. desktop license)
● Custom configuration is required to
launch a full inspection program;
workflows are not built-in (e.g., for
managing correspondence with
responsible parties)
Proprietary
System (off-the-
shelf providers)b
● *Subject to specific capabilities of the provider
● Pre-built workflows for tracking inspection
status, often customizable
● Pre-built workflows for managing
correspondence, notifications, and follow-up
● Can often be integrated with GIS
applications to take advantage of spatial
functionality and mobile applications
● Additional cost of implementation
fees and annual licensing, in
addition to staff training
● IT support/approval for new
software package; must confirm
compatibility with other platforms
already in use
● Customization capabilities may be
constrained, depending on provider
a Open-source geographic information system (GIS) software (QGIS) is also available for jurisdictions without a desktop
Environmental Systems Research Institute (ESRI) license. QGIS capabilities (e.g., for hosting data online or access to mobile apps)
were not explored in detail and do not necessarily meet all listed advantages.
b Detailed review of available proprietary systems and features is outside the scope of this manual. Advantages are assumed based
on available demonstrations for various proprietary system options.
Chapter 6: Data Management and Recordkeeping
April 2022
Source Control (Business/Site) Inspection Program Guidance Manual
A variety of proprietary systems are available, including but not limited to NPDES program
management software, inspection tracking systems, asset management systems, complaint/
request management systems, and other work order or task management systems. Different
systems have various strengths and may have differing levels of compatibility with other
programs. Based on feedback collected from Phase I permittees, asset-based configuration can
be difficult for implementing a source control inspection program focused on entire sites, rather
than individual assets.
For the Business Inspection Group (BIG) Report, four vendors provided demonstrations of
proprietary system options for implementation and data management of a source control
inspection program. Those presentations are included as SCORL Supplemental Resources 6A
through 6D.
Jurisdictions should consider data management systems used successfully by other
programs/departments to determine if existing software can support the source control
inspection program. For example, some jurisdictions may already have an Illicit Discharge
Detection and Elimination (IDDE) data management system in place that could support the
needs of the source control inspection program, since both NPDES Municipal Stormwater permit
requirements include inspection and enforcement elements. When deciding to develop a system
internally or when comparing potential vendors for proprietary systems, the following functions
and/or features should be considered:
● Customizable workflows and terminology adaptable to the local jurisdiction
● Compatibility for incorporating multiple NPDES Municipal Stormwater permit
requirements (e.g., illicit discharge field screening, outfall inspections, etc.)
● Customizable staff access to meet various needs (i.e., administrative roles versus
inspector roles)
● Capability for connecting or importing data from existing programs or other sources (for
example, existing maintenance data, existing list of sites or site contacts in spreadsheets,
complementary inspections such as Fats, Oils, and Grease (FOG) or IDDE that have
occurred at the business/site, or data from other software applications)
● Integration with ESRI ArcGIS (or other geospatial software) for map utilization/display,
data access, and mobile application utilization
● Mobile app access or integration with partner mobile applications to facilitate field data
collection and online synced database revisions based on inspector dynamic inputs, or
offline data entry that can be synced when internet connection is available
● Correspondence automation and tracking (e.g., auto-generation of correspondence
based on inspection outcomes, notices sent/received to responsible party, etc.)
Chapter 6: Data Management and Recordkeeping
April 2022
Source Control (Business/Site) Inspection Program Guidance Manual
● Graphical dashboard interface for high-level tracking of progress and goals (e.g., percent
of annual site inspections completed)
● User-friendly report generation, both site-specific and year-end reporting compatible
with Ecology requirements
● Support for system launch and setup, including training for staff and implementation/
data integration services, plus ongoing technical support
6.3. ANNUAL REPORTING TO ECOLOGY
Source control inventory development and data management should support efficient annual
reporting. The following questions are currently included in annual reporting to Ecology related
to the source control inspection program (Western Washington 2019-2024 Phase II Permit,
Appendix 3).
Annual
Report
Question #
2019–2024
Phase II
Permit
Reference
Question
Answer
Format
Supported by
Data
Management
System
S.5.C.8.b.i
Adopted ordinance(s), or other enforceable documents,
requiring the application of source control BMPs for
pollutant generating sources associated with existing land
uses and activities per S.5.C.8.b.i. Cite ordinance in
Comments field. (Required by August 1, 2022)
Yes/No
*Cite
ordinance
S5.C.8.b.ii
Established an inventory per S5.C.8.b.ii. (Required by
August 1, 2022)
Yes/No
74a
S5.C.8.b.ii
Number of total sites identified for the inventory
Number
(i.e., 1500)
S5.C.8.b.iii Implemented an inspection program per S5.C.8.b.iii.
(Required by January 1, 2023)
Yes/No
S5.C.8.b.iv Implemented a progressive enforcement policy per
S5.C.8.b.iv. (Required by January 1, 2023)
Yes/No
S5.C.8.b.iii
and
S5.C.8.b.iv
Attach a summary of actions taken to implement the
source control program per S5.C.8.b.iii and S5.C.8.b.iv.
Attachment
S5.C.8.b.iii Attach a list of inspections, per S5.C.8.b.iii, organized by the
business category, noting the number of times each
business was inspected and if enforcement actions were
taken.
Attachment
S5.C.8.b.v
Implemented an ongoing source control staff training
program per S5.C.8.b.v?
Yes/No
Chapter 6: Data Management and Recordkeeping
April 2022
Source Control (Business/Site) Inspection Program Guidance Manual
One additional question is included in the annual report for Phase I jurisdictions (2019-2024
Phase I Permit, Appendix 3).
Annual
Report
Question #
2019–2024
Phase…