Executive Summary
EPA documents PWSID as the identity of a public water system, and documents a separate geographic-area concept for the geography a system serves. The established rule for counting systems is therefore to count distinct PWSID values, not rows.
This paper reports what that rule costs in one bound artifact. Across Colorado, Ohio, Oklahoma, Pennsylvania and Texas, the governed extract holds 9,672 physical county rows and 9,672 distinct PWSIDs. Row overcount is 0.000 in every state. Median, 90th-percentile and maximum county rows per system are all exactly 1. No system in the extract carries more than one county row.
The processing explanation is ruled out. The EPA-derived AREA_TYPE_CODE=CN relationship rows retained by our ingest contain one county relationship per active-community-system PWSID in these five states, and our pipeline preserved those relationships one-for-one into the governed table. Pipeline flattening is therefore ruled out. A lineage audit compared the retained upstream snapshots for these five states against the governed table on all six fields: 9,672 rows against 9,672 rows, 9,672 distinct PWSIDs each, and nothing in either direction that the other lacks. No system in the retained relationship rows carries more than one county.
The remaining limitation is narrower. Because the original EPA archive was deleted after the successful ingest, and because the EPA relationship source does not establish correspondence to every real-world service area, this analysis cannot conclude that each system actually serves only one county, nor that EPA's county relationship coverage is complete. The paper therefore reports numerical equality in this artifact and claims nothing about the completeness of the source's county-service representation.
What the result licenses is correspondingly narrow. For a count taken from this exact snapshot, with these five states and these filters, a row count and a distinct-PWSID count return the same number. That is an arithmetic fact about one artifact. It is not a validation of row counting as a method, and it does not transfer to another snapshot, another state set, another filter, or another system class.
The identity rule does not change and is the only validated rule here: count distinct PWSID.
Business Question
Question. In the governed five-state inventory snapshot, do physical-row totals differ from totals computed with the validated distinct-PWSID rule, and how large is the numerical difference?
Audience. Analysts, practitioners, journalists and researchers who need governed public-data comparisons — the professional research reader.
Publication-value mode. Reference benchmark. EPA's data dictionary defines PWSID as the unique system identifier and geographic-area records as system geography, and EPA's newer service-area product maps systems by PWSID. None of them quantifies the row-to-system distortion in represented state data.
Specific reader utility. A reader learns whether a count already taken from this exact snapshot, state set and filters needs an arithmetic correction, and retains distinct-PWSID counting as the rule to apply everywhere else.
Positive takeaway. A material row-to-system difference would give analysts an empirical correction and force distinct-PWSID counting.
Credible null takeaway, which is the result here. Physical-row and distinct-PWSID totals are numerically equal in the exact governed artifact analysed here under the represented active-community-system and five-state filters. Distinct PWSID remains the sole validated counting method; the equality does not validate physical-row counting.
Why it matters. Denominator errors in public-data work are silent. They do not raise an exception; they produce a number that is wrong by an unknown amount. Bounding that amount, even to zero, is worth stating explicitly.
Data & Scope
Source. EPA Safe Drinking Water Information System, active community water system inventory, as represented in the governed Eldhurst research data platform. EPA's population noun is the public water system, identified by PWSID; a county row is a reported county-service association.
Universe. Colorado, Ohio, Oklahoma, Pennsylvania and Texas. 9,672 physical county rows.
Grain. One distinct PWSID and one reported county row.
Period. The current governed inventory snapshot. The source declares no time field. There is no date column to bound this result with, which is why the snapshot binding is stated in the title block, the executive summary, this section and Section 8 rather than in a footnote.
Inclusion rule. Every row in the five represented states is included.
Exclusions and their counts. Zero rows were excluded for a missing or blank PWSID. Zero rows were excluded for a missing or blank county name. Zero exact duplicate rows on PWSID, state and county name were removed. Nothing was dropped, so the result is a measured property of the extract as it stands rather than an artifact of filtering. That is a statement about processing, not about whether the extract represents every county a system serves.
Coverage gaps. The governed source is an inventory. It carries the system roster, the county served and the population served, and it carries no violations, no outages, no compliance status and no water-quality measure of any kind. The governed record also does not allocate population served among county rows.
Method
For each state, two counts are computed independently: the number of physical rows, and the number of distinct PWSID values. The row overcount is the ratio of rows to distinct systems, minus one — the proportional inflation an analyst would incur by counting rows.
For each distinct PWSID, the number of distinct county names attached to it is counted, giving the distribution of counties per system and the share of systems appearing in more than one county.
A state qualifies when it holds at least 100 distinct systems; all five qualify, against a prespecified minimum of 3 qualifying states. Material distortion was defined before any outcome was computed as a state-level row overcount of at least 0.05.
Two integrity checks gate the result: PWSID must be non-null and non-blank, and county name must be non-null and non-blank; exact duplicate rows on PWSID, state and county name are removed before legitimate multiplicity is measured, and the number removed is reported. Neither check can establish that the extract represents every county a system serves; they bound the processing, not the source's completeness.
Population served is counted once per PWSID and is never summed across county rows. It appears in this paper only as context and in no computed statistic.
There is no ranking rule and no decision rule. No system, county or utility is named.
Findings
The locked evidence artifact: rows against systems
| State | Physical county rows | Distinct PWSIDs | Row overcount | Systems in more than one county |
|---|---|---|---|---|
| Colorado | 1,058 | 1,058 | 0.000 | 0.0% |
| Ohio | 1,125 | 1,125 | 0.000 | 0.0% |
| Oklahoma | 889 | 889 | 0.000 | 0.0% |
| Pennsylvania | 1,851 | 1,851 | 0.000 | 0.0% |
| Texas | 4,749 | 4,749 | 0.000 | 0.0% |
| All five | 9,672 | 9,672 | 0.000 | 0.0% |
Figure 1 renders the first two columns as paired bars, which are equal in every state.
Counties per system
| Counties per system | Systems | Share of systems |
|---|---|---|
| 1 | 9,672 | 100.0% |
The distribution has one row. Median, 90th percentile and maximum are all exactly 1. There is no tail.
Integrity checks
| Check | Value |
|---|---|
| Physical rows | 9,672 |
| Distinct PWSIDs | 9,672 |
| Rows missing PWSID | 0 |
| Rows missing county name | 0 |
| Exact duplicate PWSID–state–county rows removed | 0 |
| Systems in more than one county | 0 |
These lines establish that the equality is a property of the extract as it stands, rather than an artefact of the processing applied to it: a zero produced by silently dropping rows, by collapsing duplicates, or by a table with a broken identifier would look identical in the headline and mean nothing, and none of those occurred. They do not establish that the extract represents every county a system serves; no check available here can do that.
Interpretation, kept separate from the above
The direct finding is that physical rows and distinct PWSIDs are equal in this extract for these five states. The interpretation is deliberately narrow: within this one artifact the two counts coincide arithmetically. That equality is carried through from the source relationship rows rather than produced by our processing, which a lineage audit establishes; what it does not establish is whether the source's county relationships are complete. Section 8 states the limitation that remains. The identity rule EPA documents is unchanged either way.
Association Analysis
No inferential association test is run, and none is justified by the locked method.
The locked method specifies distinct-identifier counting against physical row counting and a multiplicity distribution. It authorises no significance test, correlation or model.
Beyond that, there is nothing here to associate. The measured quantity is exactly zero in every qualifying state, with zero variance and no distribution to model. A correlation or significance statistic computed on a constant would be meaningless, and the universe is every represented row rather than a sample of them.
The authorised check reported in Section 5 is the multiplicity distribution, which has a single row.
Association is not causation, and this paper claims neither.
Business Implications
The declared value mode is a reference benchmark, so the permitted implication is what a reader can now check and rely on.
An analyst using this source can:
- count distinct PWSID values — the only validated rule here, unchanged, and correct regardless of this result;
- know that for a count already taken from this exact snapshot, with these five states and these filters, a row count returns the same number as a distinct-PWSID count, so that particular figure needs no arithmetic correction;
- state the snapshot, the five-state scope and the filters whenever relying on that equality, because none of the three is implied by the number itself;
- re-measure rather than assume before extending any row-based count to another snapshot, state set, filter or system class.
What must not be inferred or automated. This result must not be generalised nationally. A physical row is a reported county-service association and must never be called a duplicate facility, a duplicate system or a data error. Population served must not be allocated or summed across county rows. Nothing here supports any inference about water quality, compliance, violations, outages or resilience, because the governed source carries none of them. And the zero is not timeless: it belongs to one inventory snapshot.
Limitations
What the data exclude. Violations, outages, compliance status, water quality, resilience and any system outside the five represented states. The governed source is an inventory of active community water systems and nothing more.
What the method cannot determine. Whether the same equality holds in any other state, at any other time, or for any system class outside active community water systems. On why the equality holds, one candidate explanation is now excluded and one limitation remains. The EPA-derived AREA_TYPE_CODE=CN relationship rows retained by our ingest contain one county relationship per active-community-system PWSID in these five states, and our pipeline preserved those relationships one-for-one into the governed table. Pipeline flattening is therefore ruled out.
The remaining limitation is narrower. Because the original EPA archive was deleted after the successful ingest, and because the EPA relationship source does not establish correspondence to every real-world service area, this analysis cannot conclude that each system actually serves only one county, nor that EPA's county relationship coverage is complete. That is the most important caveat in the paper.
Coverage and sensitivity weaknesses. There is no time dimension to test sensitivity against, because the source declares no time field. A single refreshed extract could change the result, and nothing in this paper would detect that.
Alternative explanations considered. The explanation that our own processing collapsed multi-county systems to one row is ruled out by lineage: the retained upstream relationship rows already carry exactly one county per active-community-system PWSID in these five states, they are written one row per EPA relationship row with no deduplication, and their six-field multiset equals the governed table exactly in both directions. What remains open is a question about the source rather than the pipeline: the EPA relationship data need not correspond to every real-world service area, and the original archive was deleted after the successful ingest, so this paper cannot establish that each system truly serves one county or that EPA's county coverage is complete. Every claim is therefore still stated as numerical equality inside this artifact.
Scope discipline on a tempting comparison. The wider physical source does contain multi-county systems in states outside the governed universe. That observation is a scoping diagnostic, not evidence, and it is deliberately not quantified in this paper. Using it would import an out-of-universe figure into a bounded benchmark.
The unanswered question that should guide follow-up. Whether the equality is a property of these states or of this extract. Answering it requires either a second snapshot or a comparison against EPA's own service-area product, which carries modelled boundaries and a different vintage and is therefore not a drop-in substitute.
References / Appendix
Authoritative sources
- U.S. Environmental Protection Agency, SDWIS Federal Reporting Services Data Dictionary — defines PWS ID as the unique public-water-system identifier and geographic-area records as system geography, which is the identity rule this paper tests the cost of.
- U.S. Environmental Protection Agency, Public Water System Service Areas — the owner's service-area product, which maps systems by PWSID and publishes its own coverage and modelled-boundary limitations; carried as prior art and not used as a substitute denominator.
- U.S. Environmental Protection Agency, Providing Safe Drinking Water in America: National Public Water Systems Compliance Report — the owner's practice of counting distinct systems in national compliance work.
All external sources were verified on 12 August 2026.
Field definitions
| Term | Definition |
|---|---|
| PWSID | EPA's unique public water system identifier; the system identity. |
| Physical county row | One reported county-service association for a system in the governed inventory. |
| Distinct system | One distinct PWSID value. |
| Row overcount | Physical rows divided by distinct systems, minus one. |
| Counties per system | The number of distinct county names attached to one PWSID. |
| Material distortion | A state-level row overcount of at least 0.05, fixed before any outcome was computed. |
| Active community water system | The system class represented in the governed inventory. |
Permitted and prohibited claims
This article states: the counts and the zero row overcount in Section 5, stated as properties of one governed inventory snapshot for five named states.
Outside what this article claims: generalising the zero nationally or using out-of-universe national figures as evidence; calling a physical row a duplicate facility, a duplicate system or a data error; allocating or summing population served across county rows; inferring water quality, compliance, outages or resilience; and presenting the benchmark as timeless rather than bound to one snapshot and five states.
Figures
Scroll chart horizontally to read all labels.
Full chart description
Grouped horizontal bar chart titled "Rows and System IDs Match in a Five-State Snapshot". For each of five states it shows two bars: the number of physical county rows in the governed active community water system inventory, and the number of distinct PWSID system identifiers. The two bars are the same length in every state. - CO: 1,058 physical county rows and 1,058 distinct PWSIDs. - OH: 1,125 physical county rows and 1,125 distinct PWSIDs. - OK: 889 physical county rows and 889 distinct PWSIDs. - PA: 1,851 physical county rows and 1,851 distinct PWSIDs. - TX: 4,749 physical county rows and 4,749 distinct PWSIDs. The two counts are numerically equal in this exact governed snapshot, for active community water systems in these five states. That equality belongs to this artifact and these filters; it is not a general identity between rows and systems, and distinct PWSID remains the only validated counting method. The figure says nothing about water quality, compliance, outages, population served, or any other state.