Office of Water (4607M) EPA 815-R-10-003 www.epa.gov/safewater June 2010 

## TOTAL COLIFORM RULE COMPLIANCE MONITORING DATA QUALITY AND COMPLETION REPORT

June 2010TCR Compliance Monitoring Data Quality and Completion Report

i June 2010 

### Contents

1.0 Introduction and Background ......................................................................................... 1-1 1.1 TCR Revisions and TCRDSAC/TWG......................................................................... 1-1 1.2 What Data Were Requested? ....................................................................................... 1-1 1.3 Why Were the Data Requested? .................................................................................. 1-2

2.0 TCR Data Received ........................................................................................................... 2 -1 2.1 Background .................................................................................................................. 2 -1 2.1.1 How Do States Receive and Store Data? ............................................................... 2-1 2.1.2 How Did States Provide Data to EPA? .................................................................. 2-2 2.1.3 Inventory Gaps in Data Provided by States ........................................................... 2-2 2.2 States/Primacy Data ..................................................................................................... 2-3

3.0 QA Approach ................................................................................................................... . 3-1 3.1 Use of the 50 Percent Criterion and 2005 Data ........................................................... 3-1 3.2 Primary Field Selection ................................................................................................ 3-7 3.2.1 Data Elements that Were Excluded ....................................................................... 3-9 3.3 Universal Decisions/Rules for Clean-up .................................................................... 3-10 3.3.1 Records Represent Indi vidual Assays .................................................................. 3-10 3.3.2 TCR Quality Assurance Tests .............................................................................. 3-10 3.3.3 General Observations during Clean-up Process .................................................. 3-12 3.3.4 What Records to Remove .................................................................................... 3-12 3.3.5 Cross-Cutting Issues ............................................................................................ 3-13 3.3.5.1 Summary Reporting ...................................................................................... 3-14 3.3.5.2 Incomplete Data ............................................................................................ 3-14 3.3.5.3 Representativeness of Large Systems........................................................... 3-15 3.3.5.4 Fecal/ E. coli Records .................................................................................... 3-15 3.4 Field Names ............................................................................................................... 3-1 5 3.5 Additional QA Checks ............................................................................................... 3-15

4.0 Clean-up as On-going Process ......................................................................................... 4-1

Appendices

Appendix A Summary of Data from States Usin g the Extraction Tool: State-by-State Breakout

Appendix B Summary of Data from States Not Using the Extraction Tool: State-by-State Breakout

Appendix C Summary of States Not Included in the Final Dataset: State-by-State Breakout

Appendix D State ICR Data Request LetterTCR Compliance Monitoring Data Quality and Completion Report

ii June 2010 

### EXHIBITS

Exhibit 2.1 Summary of States Su bmitting TCR Data and Its Use ............................................ 2-4 Exhibit 2.2 Summary of States Not Submitting TCR Data ........................................................ 2-5

Exhibit 3.1 Effect of Minimum ................................................................................................ ... 3-3 Exhibit 3.2 Effect of Minimum Monthly Cr iteria on Proportion of Positive Samples ............... 3-5 Exhibit 3.3 TC Percent Positiv e (Using 50% Criterion) ............................................................. 3-6 Exhibit 3.4 Data Element Fiel d Names and Definitions ............................................................. 3-7 Exhibit 3.5 Application of Rules ............................................................................................. . 3-17TCR Compliance Monitoring Data Quality and Completion Report

1-1 June 2010 

### 1.0 Introduction and Background

This document describes how To tal Coliform Rule (TCR) mon itoring data were obtained, evaluated, and modified where necessary for analyses associated with the review of the TCR. In particular, these data were cleaned for the purpo se of developing occurrence parameters for the predictive model used in the economic analysis (EA) developed for the proposed Revised TCR (RTCR), described in Chapter 5 (section 5.2 of the EA). This document describes the data requested and received, data quality issues, and m odifications to the data to make it consistent and usable for subsequent analyses. Other documents describe the analyses performed.



### 1.1 TCR Revisions and TCRDSAC/TWG

The 1996 amendments to the Safe Dri nking Water Act (SDWA) [Section 1412(b)(9)] require the Administrator of the United States Environmental Protection Agency (EPA) to review and revise, as appropria te, each national primary drinking water regulation not less than every six years. In the first Six-Year Review (Six-Year Review 1), EPA produced preliminary results six years after the 1996 SD WA Amendments were passed a nd published final results in July 2003 in the National Primary Drinking Wa ter Regulation (NPDWR) Review. EPA’s findings included a decision to revise the TCR. In response to recommendations from the Stage 2 M/DBP Federal Advisory Committee, EPA also decided to consider whether it would be appropriate to add new requireme nts to address risks associated with distribution systems.

EPA established the Total Coliform Rule Distribution System Advisory Committee (TCRDSAC) under the Federal Advisory Co mmittee Act (FACA). The purpose of the TCRDSAC is to provide advice and make reco mmendations to EPA during development of a revised TCR (RTCR). The committee will also consider what information about distribution systems is needed to better understand the public health impact from the degradation of drinking water quality in distribution systems.



### 1.2 What Data Were Requested?

EPA requested voluntary submission of mon itoring data (sample results) that were available electronically for specified chemical , radiological, and microbi ological contaminants that were collected between January 1998 a nd December 2005 (Appendix E). EPA requested data collected in compliance with the TCR regarding the presence/absence of total coliforms, E. coli , and/or fecal coliforms and also welcomed any disinfection data collected at TCR monitoring sites. Surface water systems are requi red to monitor for the pr esence of a disinfectant residual when collecting coliform samples in the distribution system.

Systems must take total coliform “routine” samples on an annual, quarterly, or monthly basis, depending on their size a nd type and state requirements. The more people served by the system, the more samples that must be taken. When samples test positive for total coliforms, systems must take “repeat” samples at and near the same location, and small systems must take at least 5 “routine” samples in the following m onth. All samples that test positive for total coliforms must also be tested for either fecal coliforms or E. coli . Violations occur if there are too high a proportion of total coliform positive sample s, or samples that test positive for fecalTCR Compliance Monitoring Data Quality and Completion Report

1-2 June 2010 coliforms or E. coli and have a related TC positive sample. Violations occur if there are too high a proportion of total coliform positive samples during a given sampling period, or if repeat samples from a total coliform positive sample test positive for fecal coliform or E. coli . Details about the rule requirements can be found in the TCR (USEPA, 1989) 1

.



### 1.3 Why Were the Data Requested?

These data are an important component in supporting EPA’s sec ond Six Year Review (Six-Year Review 2) of NPDWRs, conducted pr imarily from 2003 – 2008. During this period, EPA encouraged each Primacy Agency to submit its contaminant occurrence information for years 1998 – 2005 because these data will directly contribute to EPA’s understanding of national contaminant occurrence, populations exposed to regulated contaminants, and exposure reductions associated with the current regulations.

EPA requested the TCR monitoring results wi th the intent of conducting analyses and developing models to assess the potential impacts of changes to the rule. Models of the occurrence of total coliforms and E. coli were developed using the T CR data to assess revisions to monitoring requirements and compliance decisions for different types of systems as suggested by the stakeholders during the FACA process. The occurrence of to tal coliforms is thought to be affected by seasonal influences, and so at leas t 1 year worth of data would be needed for analyses.

1 A link to the TCR website (USEPA, 1989) can be found at: http://www.epa.gov/safewater/disinfection/tcr/pdfs/qrg_tcr_v10.pdf.TCR Compliance Monitoring Data Quality and Completion Report

2-1 June 2010 

### 2.0 TCR Data Received

2.1 Background

EPA received data from 46 States, Tribes, a nd territories (“States”) and eventually included data from 37 primacy agencies (35 states and 2 tribes) in the final dataset used for analysis. In addition, data from the Safe Dr inking Water Act Information System – Federal Version (SDWIS/Fed) database was used to comp are and, in some cases, fill in data missing from State submissions.

2.1.1 How Do States Receive and Store Data?

States collect detailed data regarding comp liance with the Safe Drinking Water Act, a summary of which must be reported to EPA and in corporated into SDWIS/Fed. Some States allow for electronic reporting of TCR sample resu lts by the labs or the systems to the State, but in States that do not have electr onic reporting, sample results have to be entered manually. The volume of data collected to determine TCR compliance is quite formidable, especially for large systems. Some States allow some systems to report total coliform-positive data in summary form rather than in detail.

The data reported by the States to SDWIS/ FED includes required inventory information (population served, type of sour ce water, public water system category) and required compliance information. It does not include sampling resu lts. EPA maintains the SDWIS/Fed database.

To assist the States, EPA developed a data system—SDWIS/State—that can serve their internal needs and make reporting of required in ventory and compliance summary information to SDWIS/Fed easier. Most States now use the SDWIS/State database system, which includes results for individual samples. Other States are transitioning to SDWIS/State, but have some of their current or older records in other systems. Some States rely on other data systems. Some States maintain some records in paper files and some maintain records in multiple offices in the State. In every case, however, summary inve ntory and compliance information is provided to SDWIS/Fed. See Exhibit 2.1 for a summary of list of States that submitted TCR data from SDWIS/State.

SDWIS/State databases were designed with input from each State; however, there are some uniform data entry requirements that apply to all users of SDWIS/State. When entering individual sample results for TCR compliance, certai n data fields are required in order to create a record.

In addition to using different databases to main tain their records, States reported data in different ways. These differences and how they were treated in subsequent multi-State analyses will be discussed in chapter 3 and in appendices A-C.TCR Compliance Monitoring Data Quality and Completion Report

2-2 June 2010 2.1.2 How Did States Provide Data to EPA?

Every six years, EPA is required to review existing rules and deci de whether revisions are necessary. As a part of the latest Six- Year Review process (S ix-Year Review 2), EPA decided to request a voluntary submission of data that would be helpful in assessing the state of the TCR, which was to be the subject of a Fede ral Advisory Committee. A letter was sent to States requesting their voluntar y participation for TCR-related data in conjunction with the collection of data for the Six-Year Review (Appendi x A). The letter requested data for six years (1999-2005) and specified particular data.

EPA prepared a tool that States using SDWIS/ State could use to easily extract data from their databases and submit it to EPA. The data extraction tool (“extrac tion tool”) was a query that collected the data elements requested in th e letter to the States. The data provided through the query tool will be discussed in detail in fo llowing sections. The States that submitted data using this tool are listed in E xhibit 2.1. Even among States using SDWIS/State and using the extraction tool, not all had records for all years or for all types of systems. There were also some differences in how data were recorded in their databases, and these differences will be described below in several sections and in Appendix A to this report.

Some States not using SDWIS/State also pr ovided data, as liste d in Exhibit 2.1 and described in Appendix B. Because these States used other databases, extra steps were often required to make the data submitted comparable with data submitted from other States. These differences and the steps needed to make them co mparable are summarized in sections below. In the case of some states, data submitted we re not used because they could not be made comparable with the combined dataset of States using the extraction tool. Appendix C describes by State the issues with datasets that were not us ed in the analysis. Generally, these issues included the following: Data were provided in formats that were internally inconsistent and could not be compiled for analysis within that st ate’s dataset nor combined with the other states that used the standard SDWIS/State format; datasets used unfamiliar codes, and the effort to clarify the codes to make the data consistent with that of other states was not always successful; some data presented extremely high occurrence rate s, indicating that the State had generally not reported negative samples; some datasets did not distinguish between routin e and repeat samples; and some datasets included data from so few systems that data were assumed not to be representative of the State.

Not all States submitted data in response to the voluntary request. In some cases there were no data in an electronic form that could be easily collected and submitted, or the effort needed to comply was beyond available State re sources. In some cases, partial data were submitted (for example, only for certain years or certain system types).

2.1.3 Inventory Gaps in Data Provided by States

In several cases EPA needed to supplement da ta provided by the States with data it had available in SDWIS/Fed, particularly for reta il population served, water source type, and water system type. The data quality issues subsection for each state section in Appendices A and B includes this information.TCR Compliance Monitoring Data Quality and Completion Report

2-3 June 2010 

### 2.2 States/Primacy Data

Exhibit 2.1 lists the States that provided at least some data on sampling results in response to the EPA request, whether the State submitted data based on the extraction tool, and whether data from the State were eventually us ed in subsequent analyses. States and other primacy agencies that did not provide any T CR data are included in Exhibit 2.2.TCR Compliance Monitoring Data Quality and Completion Report

2-4 June 2010 Exhibit 2.1 Summary of States S ubmitting TCR Data and Its Use State/Territory SDWIS Extrac tion Tool? Will Data Be Used? Alabama Y N Alaska Y Y American Samoa N N Arizona Y Y Arkansas N Y Colorado Y Y Connecticut Y Y Delaware Y Y District of Columbia N N Florida N Y Georgia Y Y Idaho Y Y Illinois N Y Indiana Y Y Iowa Y Y Kansas Y Y Kentucky Y Y Maine Y N Maryland N N Michigan N Y Minnesota N Y Missouri N Y Montana Y Y Nebraska Y Y Nevada Y Y New Jersey Y N New Mexico Y Y New York Y Y New Hampshire N Y North Carolina Y Y North Dakota Y Y Ohio N Y Oklahoma Y Y Oregon Y Y Rhode Island Y Y South Carolina N N South Dakota N N Tennessee N N Utah Y Y Vermont Y Y Virginia N Y West Virginia Y Y Wisconsin N Y Wyoming Y Y Navajo Nation N N Region 8 Tribal Y Y Region 9 Tribal N YTCR Compliance Monitoring Data Quality and Completion Report

2-5 June 2010 Exhibit 2.2 Summary of St ates Not Submitting TCR Data

State/Territory Reason No Data Submitted

California State chose not to submit data. TCR data is not stored electronically; TCR data is on paper in field offices Guam State did not respond to request for data. Hawaii State did not submit data. Louisiana State did not respond to request for data.

Massachusetts State chose not to submit data. State does not store TC, FC, EC, or residual data electronically. Mississippi State did not re spond to request for data. Northern Mariana Islands State did not respond to request for data.
Pennsylvania State chose not to submit data. Puerto Rico State did not re spond to request for data.

Texas State personnel had communicated that they would send data but it was not received. Virgin Islands State did not respond to request for data. Washington State chose not to submit data.TCR Compliance Monitoring Data Quality and Completion Report

3-1 June 2010 

### 3.0 QA Approach

3.1 Use of the 50 Percent Cr iterion and 2005 Data

For the data to be most useful in subsequent analyses, certain conditi ons were preferable. EPA sought to develop a dataset that would pr ovide information that was cleaned enough to achieve a desired level of quality while still retaining enough of the original data to maintain a reasonable level of representativeness of the data submitted. The dataset needed to be representative both in terms of the breadth of PWSs in the U.S. and of a full year of seasons.

The first condition for the data meeting certa in quality standards was that the data reported for a month of samples contained all the samples taken. In particular, the data for a month of a system’s data must contain both the sa mples that tested negative as well as those that tested positive in a month. Some States allo w some systems to report negative results in a summary format, but these summary data were not in cluded as a part of this data request. As a result, some systems only had data for positive sa mple results. Further, the data on population served came from the most recent information in SDWIS/Fed 2 at the time (2007 fourth quarter), which may have been updated since the samples were actually taken. In the most severe case, a system might have grown and now was required to take 15 samples per month rather than the 10 samples per month required at the time the samples were actually being taken.

To ensure that a system’s sampling data appeared complete during the months when TCR results were reported, a standard of having at least 50% of the nu mber of samples expected based on system size was applied to each system’s TC R compliance data on a monthly basis. The quality objective was to exclude from the comp iled dataset those system-months having only a system’s positive results or otherwise not includi ng many of a system’s samples for the month. The selection of a 50% criterion was based on consideration of the resulting proportion of overall samples, systems, and system-months retained in the compiled dataset after applying a range of percentage criteria (0%, 10%, 50%, 80%, 90%, and 100%) to eliminate system-months not having an adequate number of samples. The 50% criterion was found to retain a high proportion of the samples, systems, and system-months of data while still filtering out the worst systemmonths in terms of apparent qua lity or completeness issues.

Thus the first condition – having reasonably complete system-months of data – moved the data towards the goal of increasing quality while maintaining representativeness. The Technical Work Group of the Federal Advisory Committee agreed with this approach. The effect of setting the criterion at different leve ls on representativeness of systems, samples, system-months of data, and levels of occurrence is minimal, especially in the range of 50% or more, as shown in Exhibits 3.1 and 3.2. A highe r level, i.e., 80%, would be incrementally more inclusive, but the 50% gap allows for legitimate gaps in number of samples taken to samples expected based on population changes. Since a system’s minimum number of samples per month is based on its population served, those syst ems that grew into the next level of population between the start of the data sampling period (199 8) and the baseline PWS inventory year of the analysis (2007) were required to take more samples in 2007 (the y ear of inventory data used in

2 Further information on SDWIS/FE D can be found on EPA’s website: http://www.epa.gov/safewater/ databases/sdwis/index.html

.TCR Compliance Monitoring Data Quality and Completion Report

3-2 June 2010 the baseline) than in 1998 – 2005. EPA determined that the largest pote ntial gap caused by this change was a 50% difference in number of samp les; hence EPA focused further analysis of representativeness on the 50% criterion. This approach wa s presented and explained to the TWG, who accepted it as a reasonable balance be tween representativeness and quality in the data. EPA also mentioned use of this criter ion in updates to FACA members regarding the analysis.

The second condition was that the overall datase t resulting from the data cleaning process should encompass at least a full year of data for community water systems so that all seasons are represented. Therefore, EPA reviewed the numbe r of records available on an annual basis, to determine the year during which the largest num ber was available. The numbers of records available for the requested years were compared . Data for the year 2005 had the most records compared to other years, based on systems increa sing the amount of data they locate centrally with EPA over the years. Exhibit 3.3 contains data only for months in which a system had at least 50 percent of the number of required TCR samples. The period 1998-2001 had many fewer records available. As mentioned for Exhibits 3.1 – 3.2, results in Exhibit 3.3 are based on 28 states’ data analyzed prior to compilation of the final dataset, and served as the basis for the FACA’s decision to select the 50% criterion de scribed in section 3.0 of this report. Consideration of the addi tional states’ data to complete the 37-state dataset indicated that the same analysis applied to the final dataset would be consistent with these results.

The difference observed in the number of records available from year to year occurs because some States were not using SDWIS/State during th e whole period. Some States began using the SDWIS/State program sometime during this period, and some States that were already using the program at the start of the period began uploading more of their data at some point during the period. Based on the increased number of system s participating and the corresponding increase in data available, a decision was made to use 200 5 data exclusively in subsequent analyses. The decision to include a single data year considered the following:

• There were fewer data and systems represen ted in the years before 2005, especially 2001 and earlier, and may be not comparable with the 2005 data. The difference in the base of systems studied year-to- year might introduce an unknown bias.

• Examination of SDWIS/ FED TCR MCL violations 3 showed relatively little change in TCR MCL violation rates across the y ears 1999 – 2005, suggesting that little difference would be observed in the rate of TC occurrence across the same years if data sets of comparable levels of completene ss were available. Thus, it appears that any benefit would be minimal from includi ng the less comprehensive data sets from earlier years in estimating a national ba seline rate of TCR occurrence.

• Using only a single year of data would mean that the resulting statistics would reflect a full year of the results, which was to be the basis for future analyses, and avoid the problems of interpreting multiple years of data (each with different coverage of systems) as a single year of results.

3 Found at http://www.epa.gov/safewater/databases/pivottables.html in the summary inventory, violations, and GPRA MS Excel PivotTables® (8.3 MB ZIP File).TCR Compliance Monitoring Data Quality and Completion Report

3-3 June 2010 • Last, a single year of data would allow a singl e database to be able to hold all the data for easier manipulation in desktop applications.

Exhibit 3.1a Effect of Mi nimum Monthly Criteria (0%, 10%, 80%, and 100%) on the

Proportions Included of Samples, Systems, and System-Months by System Type (All Systems, CWS, NTNCWS, and TNCWS)

NTNCWS

90% 93% 95% 98% 100%

0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Criteria Percentage TNCWS

90% 93% 95% 98% 100%

0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Criteria Percentage

Notes: Data shown are from 2005 in the Six-Year Review 2 dataset after cleaning as de scribed in section 3.0. Results shown are based on 28 states’ data as described in section 3.1 of this report. All Systems

90% 93% 95% 98% 100%

0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100%
Criteria Percentage Number of Samples

Number of Systems

Number of System Months CWS

90% 93% 95% 98% 100%

0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Criteria PercentageTCR Compliance Monitoring Data Quality and Completion Report

3-4 June 2010 Exhibit 3.1b Effect of Minimum Monthly Criteria (0%, 10%, 80%, and 100%) on the Proportions Included of Samples, Systems, and System-Months for Systems Serving >1,000 People

Notes: Data shown are from 2005 in the Six-Year Review 2 dataset after cleaning as described in section 3.0. Results shown are based on 28 stat es’ data as described in se ction 3.1 of this report.TCR Compliance Monitoring Data Quality and Completion Report

3-5 June 2010 Exhibit 3.1c Effect of Minimum Monthly Criteria (0%, 10%, 80%, and 100%) on the Proportions Included of Samples, Systems, and System-Months by Sampling Period (pooled for 1998 through 2004 and for 2005)

Systems, 1998-2004

90% 93% 95% 98% 100%

0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Criteria Perentage Systems, 2005

90% 93% 95% 98% 100%

0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100% Criteria Perentage Notes: Data shown are from 1998 - 2005 in the Six- Year Review 2 dataset after cleaning as described in section 3.0. Results shown are based on 28 states ’ data as described in sect ion 3.1 of this report.

Exhibit 3.2 Effect of Mi nimum Monthly Criteria on Pr oportion of Positive Samples Observed by System Type and by System Size

Notes: Data shown are from 2005 in the Six-Year Review 2 dataset after cleaning as described in section 3.0. Results shown are based on 28 states’ data as described in section 3.1 of this report. Percent Positive, by System Type

0.0% 1.0% 2.0% 3.0% 4.0% 5.0% 6.0% 7.0% 8.0% 9.0% 10.0%

0% 20% 40% 60% 80% 100%

Criteria Percenta g e

All CWS NTNCWS TNCWS Percent Positive, By System Size

0.0% 1.0% 2.0% 3.0% 4.0% 5.0% 6.0% 7.0% 8.0% 9.0% 10.0%

0% 20% 40% 60% 80% 100% Criteria Percentage

All Serving 0-1,000 Serving More Than 1,000 ♦ Number of Samples

Number of Systems ▪

N umber of System Months ▲TCR Compliance Monitoring Data Quality and Completion Report

3-6 June 2010 Exhibit 3.2 Effect of Mi nimum Monthly Criteria on Pr oportion of Fecal Indicator Positive Samples by Sampling Pe riod (1998 – 2005, 2005, 1998 – 2004)

Percent Positive, B y Perio d

0.0% 1.0% 2.0% 3.0% 4.0% 5.0% 6.0% 7.0% 8.0% 9.0% 10.0%

0% 20% 40% 60% 80% 100%

Criteria Percenta ge

All 2005 1998-2004 Notes: Data shown are from the Six-Year Review 2 dataset after cleaning as described in section 3.0. Results shown are based on 28 states’ data as described in section 3.1 of this report. Exhibit 3.3 TC Percent Posi tive (Using 50% Criterion)

TC Percent Positive (Using 50% Criterion) By Type of System, 1998-2005

0.00% 1.00% 2.00% 3.00% 4.00% 5.00% 6.00% 7.00%

1998 1999 2000 2001 2002 2003 2004 2005 TNCWS
NTNCWS
All
CWS

Notes: Data shown are from 2005 in the Six- Year Review 2 dataset after cleaning as described in section 3.0. Results shown ar e based on 28 states’ data as described in section 3.1 of this report.TCR Compliance Monitoring Data Quality and Completion Report

3-7 June 2010 

### 3.2 Primary Field Selection

The extraction tool obtained data for severa l data fields that exist in SDWIS/State (Exhibit 3.4). The data fields as listed in SDWIS/State are shown in the first column of Exhibit 3.4. The data fields were given new names, lis ted in the second column, that were easier for users of the final data. Fields that were not needed are labeled as “REMOVED” and discussed in section 3.2.1. The third column of Exhibit 3.4 gives a short definition of the data field.

Exhibit 3.4 Data Element Field Names and Definitions

Original Data Element Field Name Final Data Element Field Name Data Element Field Definition

NUMBER0 PWSID_Number Uniquely identifies the water system within a specific State.

NAME PWSID_Name Name of the water system. The name can be the formal, legal, or common name that is used most generally in referring to the water system.

D_PWS_FED_TYPE_CD System_Type Coded value which classifies the water system according to federal requirements. D_POPULATION_COUNT Population_Served Count of the total population served by a water system.

D_FED_PRIM_SRC_CD Source_Water Coded value which classifies the source water type according to federal requirements.
ST_ASGN_IDENT_CD REMOVED A State-assigned value which identifies the system facility. TINWSF_NAME REMOVED The name of the water system facility.

TSASMPPT_IS_NUMBER Samp ling_Point_Number Intended to be a value which, in conjunction with at least one other attribute or relationship, uniquely identifies the entity occurrence.

TSASMPPT_TYPE_CODE Sampling_Point_Relative_Location Coded value that represents the location type of a sampling point (e.g., unit process, water system facility, distribution system).
SOURCE_TYPE_CODE Sour ce_Water_Treatment Type of water source, based on whether treatment has taken place.

IDENTIFICATION_CD Facility_ID_Number Unique code for identifying a water system facility sample point. This value must be unique within the Water System Facility. DESCRIPTION_TEXT Sampling_Point_Text Description of the sampling point.

LD_CP_TIER_LEV_TXT REMOVED If valued 1, 2, or 3 and TSASMPPT_TYPE_CODE = DS, then represents Tap. If TSASMPPT_TYPE_CODE <> DS, then probably represents Source.

LAB_ASGND_ID_NUM Lab_Sample_Number Sample identification number assigned by the laboratory for tracking the sample within the lab. COLLLECTION_END_DT Date_Sample_Taken Sample Collection Date.TCR Compliance Monitoring Data Quality and Completion Report

3-8 June 2010 Original Data Element Field Name Final Data Element Field Name Data Element Field Definition

ANALYSIS_COMPL_DT Da te_Sample_Analyzed Date the laboratory completed the analysis of a sample

COMPL_PURP_IND_CD REMOVED When selected in the sample record, the sample and its results will be used (by any automated software) in the determination of compliance for that sample. Samples not selected will not be used in any compliance determination.

TSASAMPL_TYPE_CODE Sample_Type Type of sample. This is an important element. Routine and repeat sample codes are entered as sample type designation. Other types of samples include confirmation, special, batch blanks, field blanks, performance evaluation, shipping blanks, split blanks, maximum residence, and matrix spike.

REPEAT_LOC_TYP_CD Repeat_S ample_Relative_Location Location of the repeat sample with respect to the location of the original routine sample (usually indicating the original location, upstream, downstream or other)

TSASMN_CODE Analytical_Method Code value that represents the method that was used to analyze the sample (e.g., EPA assigned method code).

TSAANLYT_NAME Analyte_Name Common name of a contaminant as used by States and EPA in the rules and other documents, such as total coliforms, fecal coliforms, or E. coli

.

TSAANLYT_CODE Analyte_Code Standard code used by EPA and the States to represent analytes in the national database. Each analyte has a unique code assigned to it for reference in compliance determination.

LESS_THAN_IND REMOVED Binary indication of whether the result is “less than” the Lab Reporting Limit or “less than” the Regulatory Minimum Reporting Limit.

LESS_THAN_CODE REMOVED When valued, indicates that the analytical result (concentration) was below the Regulatory Minimum Reporting Level or below the Laboratory Reporting Level (which in turn must be at or below the regulated MCL for the analyte).TCR Compliance Monitoring Data Quality and Completion Report

3-9 June 2010 Original Data Element Field Name Final Data Element Field Name Data Element Field Definition

DETECTN_LIMIT_NUM REMOVED The limit established by the laboratory below which scientifically reliable results cannot be achieved. In many cases, if the computed detection limit is below the Method Detection Limit, then the Method Detection Limit is used as the detection limit; if the computed detection limit is above the Method Detection Limit, then the detection limit is used.
DETECTN_LIM_UOM_CD REMOVED Standard Unit of Measure for DETECTN_LIMIT_NUM

REPORTED_MSR REMOVED The actual reported measure of a chemical/rad/water quality result. This measure is captured as text in order to preserve the trailing zeros which indicate the precision of the measure. The text value is also converted to a number and stored in the SBS_SAR Concentration_Measure attribute.

CONCENTRATION_MSR REMOVED A numeric value that represents the result obtained from a sample analysis. UOM_CODE REMOVED Standard Unit of Measure

PRESENCE_IND_CODE Presence_Absence_Result Indicates whether results of an analysis were positive (P-Presence) or negative (A-Absence). Indication of presence or absence creates an analytical result for a specific analyte.

COUNT_QTY REMOVED The number of organisms counted or estimated in a microbiological sample. Usually expressed as “# of colonies per 100 milliliter sample.”

COUNT_TYPE REMOVED Type of microbiological unit that is being counted per specified count unit. Count type varies with the microbiological organism where count has been recorded.
COUNT_UOM_CODE REMOVED The units of measure associated with the microbial analytical result count.

3.2.1 Data Elements that Were Excluded

As shown in the second column of Exhibit 3.5, some data fields were judged to not be needed and were removed from the data sets.

• ST_ASGN_IDENT_CD and TINWSF_NAME we re usually redundant of the name and PWSID number in SDWIS, and where th ey were not, this information was not available for each State and was not needed.

• LD_CP_TIER_LEV_TXT was judged to be unnecessary in subsequent analyses.TCR Compliance Monitoring Data Quality and Completion Report

3-10 June 2010 • COMPL_PURP_IND_CD was a criterion used in the extraction tool—every sample had to be a compliance sample. As a result, every record had the same data for this field and the field was not needed.

• LESS_THAN_IND, LESS_THAN_CODE, DETECTN_LIMIT_NUM, DETECTN_LIM_UOM_CD, REPORTED _MSR, CONCENTRATION_MSR, UOM_CODE, PRESENCE_IND_CODE, COUNT_QTY, and COUNT_TYPE, COUNT_UOM_CODE are either not related to information necessary for the TCR, which considers only presence/absence of total coliforms, or are not related to measurements of total coliforms.



### 3.3 Universal Decisions/Rules for Clean-up

3.3.1 Records Represent Individual Assays

Most records submitted derived from SD WIS/State and the extraction tool and represented either a total coliform test result, a fecal coliform test result, or an E. coli test result. This became the default structure where each record represents one sample event, or a follow-on test result for fecal coliforms or E. coli . Therefore, the structure of th e data tables was that a total coliform sample with a positive result that is subsequently analyzed for E. coli or fecal coliforms would be recorded as two separa te records – one record for the TC result and one record for the EC or FC result. The use of assays for FC was much less prevalent than those for EC, and EPA determined that the low number of FC assays were determined to be too underrepresented to have meaning by themselves. Because some systems used FC assays preferentially over EC assays, in order to retain that indicator inform ation EPA treated FC and EC essentially the same, as a “fecal indicator.” Therefore, both types of results were incorporated into the occurrence distribution analysis described in section 5.2 of the RTCR EA.

3.3.2 TCR Quality Assurance Tests

For each State and some of the important fi elds, initial quality assurance tests were performed. Corrections made to the data as a result of the quality assu rance tests are explained in detail in the individual State summaries foun d in Appendices A and B, and in some of the state summaries found in Appendix C, where initia l QA was performed on the dataset provided. Fields that are required in SDWIS/State are not ed by an asterisk. The quality assurance tests included the following:

• Count of records—recorded the total number of records in each original State dataset.

• *PWSID—recorded the number of unique systems in each dataset and compared against the SDWIS/Fed count; performed a data quality check for the number of characters (9) in the number, State prefix as the first two characters, and only zeroes followed the State prefix (no “O”s). Note that Utah uses the full State name rather than State acronym (e.g., UTAH12345).

• *Sample Type (routine, repeat, special purpos es, etc.)—checked for all unique values entered into data element field (inclu ding blanks); recorded the number andTCR Compliance Monitoring Data Quality and Completion Report

3-11 June 2010 calculated the percentage of each data element (including blanks) based on the Sample Type field. A high percentage of repeat samples to routine samples could indicate missing data for routine samples.

• *Date of Sample Collection — recorded the number of records with entries in this field; recorded the number of blank reco rds; recorded the range of dates; and confirmed that dates were in date format.

• Date of Sample Analysis—recorded the number of records with entries in this field; recorded the number of blank records; reco rded the range of dates; and confirmed the dates were in date format.

• Difference in Sample Collection and Sample Analysis Dates (when comparable)— compared the difference in days between data collection and lab analysis. Examined likely erroneous entries (those more than 10 days), and determined whether there was a likely typographical data entry error that could be corrected.

• *Results (analyte name, presence absence re sult)—recorded all unique values entered (including blanks); recorded the number and ca lculated the percentage of each type of code; recorded the number and calculated th e percentage of presence/absence results.

• Sampling Point Identification; Sampling Point Relative Location—checked for blanks and unusual data; checked for all unique valu es entered (including blanks); recorded the number and percentage of each type of code (including blanks).

• Repeat Sample Relative Location—checked for all unique values entered (including blanks); recorded the number and percentage of each type of code (including blanks); checked that data elements were only entered for repeat samples.

• *System Type—checked for all unique values entered (including blanks); recorded the number and percentage of each type of code (including blanks).

• *Population Served—checked and recorded the number and percentage of systems with zero values and of systems serving between 1 and 24 persons; compared the 10 largest systems in each State (regardless of absolute size) for reasonableness (compared the dataset and SDWIS/Fed invent ory). For States that did not provide population data, retrieved data from SDWIS/Fed inventory.

• Analytical Method—checked for all uniqu e values entered (including blanks); recorded the number and percentage of each type of code (including blanks).

• Unique Sampling Point—checked for the lik elihood of being able to link samples with unique sampling points within systems.TCR Compliance Monitoring Data Quality and Completion Report

3-12 June 2010 3.3.3 General Observations during Clean-up Process

During the data cleaning process, we made several general observations about the data received.

• Most of the data sets did not include good records/data for “Date Sample Analyzed” but did include good records/data for “Date Sample Collected.”

• Date differences (between sample date a nd sample analysis date) of more than 10 days appeared to be data en try errors rather than complia nce violations. For example, dates were often a month or y ear different than expected.

• Most systems serving a population of zero (0) were inactive systems. For these systems, data was considered for inclusion in the final dataset only for those systemmonths when the system was active.

• Analyte/result fields are of generally ve ry good quality and completeness. (i.e., sample records had results and results were coded in standard format (P or A)).

Some States provided summary total coliform da ta. This means that for a system with all negative sample results, the total number of samp les taken was reported in a single record rather than in individual records with information on dates taken, etc.

3.3.4 What Records to Remove

There were several circumstances where in dividual records were removed from the databases. The following summarizes these situations.

• Sampling Point Relative Location

- PC (process control) – Three States (DE, IN, and NY) used this code. Each record with this code indicated that it was associated with a treatment plant and each system had many other samples labeled as being from the distribution system. All PC records were deleted.

- SR (location at which untreated water is collected at the source) – Two States (NY and MT) used this code. In NY, each record indicated that it was associated with a well, and every system but one had many other samples labeled as from the distribution system. One system had only one other sample from an entry point that was also labeled CHLORINATOR. All SR records were deleted from NY. SR records were kept for MT because many of these samples appeared to be from the distribution system.

- RW (raw water sources) – A number of St ates used this code. In NY, RW records were deleted when there was no clea r indication that they were taken from a distribution system. All RW record s were deleted from DE because other samples within the distribution system usually existed. RW records were deleted from IN when taken before any treatment. Records coded with RW in ID wereTCR Compliance Monitoring Data Quality and Completion Report

3-13 June 2010 also coded as being wells that were not sampling locations, so ID RW records were deleted. Because all systems with RW records had other records labeled as being from the distribution system in CT, RI, MT, AK, VT, and WV, all RW records in these States were deleted. RW records were kept for AL because they appeared to be the only sa mples for many systems.

- UP (unit process point) – All records with this code in all States were deleted since they would not be considered part of a distribution system. In addition, all systems with these codes had other record s labeled as being from a distribution system.

- WS (water system facility point) – Record s with this code in all States were deleted when clearly labeled as being pa rt of a treatment plant rather than a distribution system.

• Presence/Absence Field

- Records were deleted when the presence/absence field was blank.

• Sample Type

- Any records with sample types other th an RT (routine), RP (repeat), and TR (temporary routine) were deleted. Record s with sample type TR indicate routine samples that were taken at a temporary or alternate location due to inaccessibility to the normal routine samp le location. Although it is a temporary location, TR samples are still collected/counted as routine samples in data analysis.

• Source Water Type

- Records with a blank in the source water type field that could not be filled in from other sources were deleted.

• System Type

- Records that were classified as NP (n on-public) were deleted for all States.

3.3.5 Cross-Cutting Issues

EPA identified certain issues that were common across multiple States’ datasets. Some issues were addressed by furthe r manipulation of the data, and some were reasons to exclude data, as described in sections 3.3.5.1 – 3.3.5.4 below.

The listing of samples in summary form in the case of negative results was one of several reasons identified for missing data; data not ente red, invalidated samples, samples not taken, and incorrect population served numbers corres ponding to a given data year comprise the other reasons.

In addition, many large systems have thei r own laboratory and do not use the State laboratories, and data for these large systems were not available. EPA determined that large systems’ data was not representative enough for use in the RTCR EA (USEPA, 200Xa), and instead used violations data from SDWIS for th e analysis for systems serving >4,100 people.TCR Compliance Monitoring Data Quality and Completion Report

3-14 June 2010 A last issue that EPA found in multiple states’ data was the apparent inconsistency of an EC- or FC-positive result coupled with a TC-neg ative result. EPA generally disregarded the EC- or FC-positive result unless it could be li nked by date to a TC-positive sample, although there were some exceptions, as described in section 3.3.5.4 below.

3.3.5.1 Summary Reporting

Some States allow systems to report negative results for a month in a summary listing of only the number of samples taken, rather than lis ting individual records for each assay. In some States, this only affected larger systems or certain types of systems. In other States, any system could file summary reports.

Data that was collected using the extraction tool (described in section 2.1.2) did not include summary reporting. Summary data was not part of the data query. The reporting of summary data was observed only in some of the data provided by States that did not use the extraction tool. In these cases, individual record s were generated for the database based on how many samples were taken and using the first of th e month as their collection date. This allowed these data to be compatible in form to other da ta (that is, there were individual records for each sample taken).

3.3.5.2 Incomplete Data

Another issue was systems appearing to have too few records in a month given the size of their system. The reasons for the missing data were not investigated but were assumed to be the result of the following:

• Missing records (data not entered),

• Removed records (invalidated samples),

• Samples not taken (monitoring violations), and

• Incorrect population served numbers. (In mo st cases, the data included the population of the system at the time the data were extracted, but the popul ation may have been smaller in 2005.)

The method used to address these issues was to establish a criterion for inclusion of data, as discussed in section 3.1. Specifically, the num ber of total coliform records for each system in each month was compared to the number of sample s required under the TCR. If the number of records was less than 50 percent of the required number of records, that month of data was excluded. This criterion prevented a system that reported only total coliform positive results (no total coliform negative results) from skewing the system’s percentage of total coliform positives. It also allowed for the circumstance that so me systems had grown in size since 2005 reported retail population served . The most extreme example of this is systems that in 2005 needed 10 samples per month, but then grew into the next category that requires 15 samples per month (a 50 percent increase).TCR Compliance Monitoring Data Quality and Completion Report

3-15 June 2010 3.3.5.3 Representativeness of Large Systems

In the QA check of the largest 10 systems in each State the review revealed that many larger systems were not included in the data subm itted by the States. In some cases (such as in MI), only data from the State laboratory were included. Since many large systems have their own laboratory and do not use the State laborator ies, data for these large systems were not available. Another reason for differences is that in a few cases States handled large systems with negative samples in a special way, and those data were not submitted. The end result was that the category of large systems had a lower percentage of systems represented in the State’s data than the category of smaller systems, and the data was not considered representative enough for use in further analysis. EPA th erefore did not use the larger sy stems’ data in the subsequent Economic Analysis performed under the proposed RTCR.

3.3.5.4 Fecal/ E. coli Records

In the QA check of records, it was noted th at some systems reported fecal coliforms or E. coli results even for total coliform negative assays. Fecal coliform/ E. coli records were matched up to their total coliform sample by sample date and only those with total coliform positive results were included. Since so few FC assays were performed by systems, EC and FC results were combined into a single "f ecal indicator” category, which tr eated EC+ and FC+ results as equally likely. Systems generally tested for eith er EC or FC, but if r ecords included reporting results for both, a positive result for either was considered a positive fecal indicator result.

Another issue was that some fecal coliform/ E. coli records did not have a corresponding total coliform record. In Michigan, it was concl uded after consultation with State staff that when a fecal coliform/ E. coli result is shown, there is an implied total coliform positive result. In this case, total coliform positive records were adde d to the database of useable data.



### 3.4 Field Names

For the States that did not use the SDWIS extraction tool, the data element field names did not always match the data element field names us ed in the final data sets for the States that used the Extraction Tool. Wher e appropriate, the data element fields were renamed for the final dataset. Details about the final format for individual States can be found in Appendices A and B. Only data from 2005 have been retained in the final dataset.



### 3.5 Additional QA Checks

In order to model both total coliform (TC) and E. coli (EC) occurrence in the distribution system, we needed, for each system, a count of th e total numbers of routine (NRT) samples that were tested for TC (NRTTC) and found positive fo r TC (KRTTC). We also needed the number of TC positive samples that were also tested for the fecal indicator (FI), E. coli, or fecal coliforms (NRTFI) and found positive (KRTFI). We needed the same information for repeat (RP) samples, i.e. using the same abbreviations the number of samples (N), fecal indicator (FI), and number of positives (K) for number of samples and K for number of positives the data was analyzed NRPTC, KRPTC, NRPFI, and KRPFI.TCR Compliance Monitoring Data Quality and Completion Report

3-16 June 2010 Because negative TC samples are negative for fecal indicators EC and FC, some of these negative TC samples that also produced database records for EC and/or FC were deleted (as discussed in 3.3.5.4 ). Ideally, EC and FC records would have been produced only for TCpositive samples that were then assayed for the fecal indicator. EPA also learned that some authorities (state and tribal authorities) record ed all fecal indicator re cords as FC, even though the great majority of assays were actually EC. A set of “rules” was de veloped to identify, for each system and each date that the system coll ected samples, the number of TC positive samples that were actually assayed for a fecal indicator and the number of these that were positive for the fecal indicator. When both EC and FC results were reported for the same sample, we selected the EC result.

The following are the rules that were applied:

1. If KRTTC = NRTEC, then all is well. Let NRTFI = NRTEC and let KRTFI = KRTEC.

2. If KRTTC = NRTFC, then all is well, too. We'll use the FC data: Let NRTFI = NRTFC and KRTFI = KRTFC.

Rules 1 and 2 above will apply to most routin e sample dates. The rules will probably not apply to as many repeat sample dates. The ru les below only apply to systems not addressed by 1 and 2.

3. If NRTTC = NRTEC, then the system entered EC records for TC negatives. Let NRTFI = KRTTC and KRTFI = KRTEC.

4. Similarly, for FC, if NRTTC = NRTFC, then let NRTFI = KRTTC and KRTFI = KRTFC.

Rules 3 and 4 will take care of some of the sample dates not addressed by 1 and 2. The rules below only apply to systems not addressed by 1 through 4 above.

5. If KRTTC = NRTEC+NRTFC, then each TC+ wa s assayed by either EC or FC (not both). Let NRTFI = NRTEC+NRTFC and let KRTFI = KRTEC+KRTFC.

6. If NRTTC = NRTEC+NRTFC, then each TC- produced a record for either EC or FC (not both). Let NRTFI = KRTTC and let KRTFI = KRTEC+KRTFC.

The rules that follow only apply to sample dates not addressed above.

7. If NRTEC > KRTTC, then let NRTFI = min(NRTEC, KRTTC) (let NRTFI = KRTTC because "NRTEC > KRTTC" is the pre-condition) and KRTFI = KRTEC (Check to be sure KRTFI is not greater than NRTFI.)

8. If NRTEC = 0, but NRTFC>KRTTC, then let NRTFI = min(NRTFC, KRTTC) (let NRTFI = KRTTC because "NRTEC > KRTTC" is the pre-condition) and KRTFI = KRTFC (Check to be sure KRTF I is not greater than NRTFI.)TCR Compliance Monitoring Data Quality and Completion Report

3-17 June 2010 The rules below only applies to sa mple dates not addressed above.

9. Many of those which remain will have missing EC and FC records (monitoring violations). If NRTEC> 0, then let NRTFI = NRTEC and let KRTFI = KRTEC.

10. If NRTEC = 0, but NRTFC>0, then let NRTFI = NRTFC and KRTFI = KRTFC.

Other. The remaining sample dates may be all (or mostly) cases where NRTEC=NRTFC=0. In these cases, let NRTFI=0 and KRTFI=0.

Exhibit 3.5 below shows how often each of the above rules was applied. Note that Rules 1 and 2 applied to 99.5 percent of the routin e data and 97 percent of the repeat data.

Exhibit 3.5 Application of Rules

Rule Number #RT Sys*Dates %RT Sys* Dates #RP Sys*Dates %RP Sys*Dates 1 631,276 93% 19,283 86% 2 44,191 6.5 2,512 11 3 510 0.08 292 1.3 4 121 0.018 43 0.19 5 10 0.0015 5 0.022 6 0 0 0 0 7 10 0.0015 14 0.062 8 5 0.00047 4 0.018 9 6 0.00089 10 0.044 10 798 0.12 87 0.39 Other 1,029 0.15 279 1.2 Total 677,956 100% 22,529 100%TCR Compliance Monitoring Data Quality and Completion Report

4-1 June 2010 

### 4.0 Clean-up as On-going Process

EPA used the cleaned dataset to develop a distributional analysis of TC and fecal indicator positives. Further modification of the data for the purpose of performing the distributional analysis is described in section 5.2 of the RTCR EA.

### Appendix A Summary of Data from States Using the Extraction Tool: State-by-State BreakoutTCR Compliance Monitoring Data Quality and Completion Report

A-i June 2010 

### Contents

A.1 ALASKA ........................................................................................................................ . A-4 A.2 ARIZONA........................................................................................................................ A-6 A.3 COLORADO ................................................................................................................... A- 7 A.4 CONNECTICUT ............................................................................................................. A-9 A.5 DELAWARE ................................................................................................................. A-11 A.6 GEORGIA...................................................................................................................... A -12 A.7 IDAHO ......................................................................................................................... .. A-14 A.8 INDIANA ...................................................................................................................... A-16 A.9 IOWA .......................................................................................................................... ... A-17 A.10 KANSAS ....................................................................................................................... A-19 A.11 KENTUCKY ................................................................................................................. A-21 A.12 MONTANA ................................................................................................................... A-2 2 A.13 NEBRASKA .................................................................................................................. A-2 4 A.14 NEVADA ...................................................................................................................... A -26 A.15 NEW MEXICO ............................................................................................................. A-27 A.16 NEW YORK .................................................................................................................. A-2 9 A.17 NORTH CAROLINA .................................................................................................... A-31 A.18 NORTH DAKOTA ........................................................................................................ A-32 A.19 OKLAHOMA ................................................................................................................ A-34 A.22 UTAH .......................................................................................................................... .. A-39 A.23 VERMONT .................................................................................................................... A- 41 A.24 WEST VIRGINIA ......................................................................................................... A-42 A.25 WYOMING ................................................................................................................... A-4 4 A.26 REGION 8 TRIBAL ...................................................................................................... A-46TCR Compliance Monitoring Data Quality and Completion Report

A-1 June 2010 For each State that used the SDWIS Extraction Tool, below is a description of the data, a narrative of the issues, summary of the correspo ndence with the State, inventory comparison, total number of records, and any QA issues. The inventory comparisons are based on the data in the final 37-State dataset and SDWIS-FED (4th Q, 2005). The final dataset is based on applying the 50 percent criterion and other corr ections that were made (primarily in the format of the data to ensure consistency in analysis).

This appendix looks specifically at the data quality and representativeness, primarily as compared to the data in SDWIS-FE D, but also with regard to internal consistency in the State data. Noting differences between the State data and SDWIS-FED does not imply that changes were necessarily made to the State data, but th e differences allowed EPA to gauge the coverage and completeness of the State data, their consistency with SDWIS-FED, and the internal consistency within the State data set. Where changes were made to the data, those changes are noted.

For example, the State-reported populations of the 10 largest systems in each State were compared to the data in SDWIS-FED. In some cases, EPA found that several of the largest systems were not in the State-reported data, or that the numbers appeared to differ by small or larger amounts. Differences in quantities are noted, but were not e xplored or “corrected” (without research, it was unclear which figure might be the more correct).

Before beginning the State-by-S tate summaries, a few exhibits are presented that apply to all states. The descriptive data element exhibits (Exhibits A.1 – A.3) apply to each State that used the SDWIS Extraction Tool. As discussed in Chapter 3, fields that were unnecessary in the final dataset were removed. For reference, the 14 data element fields that were unnecessary and removed are defined in the Exhibit A.1.

Exhibit A.1 Removed Data Element Fields

Removed Data Element Field Name Remo ved Data Element Field Definition ST_ASGN_IDENT_CD A State-assigned value which identifies the system facility TINWSF_NAME The name of t he water system facility

LD_CP_TIER_LEV_TXT If valued 1, 2, or 3, and TSASMPPT_TYPE_CODE = DS, then represents Tap. IF TSAS MPPT_TYPE_CODE <> DS, then probably represents Source

COMPL_PURP_IND_CD When selected in the sample record, the sample and its results will be used (by any automated so ftware) in the determination of compliance for that sample. Samples not selected will not be used in any compliance determination.

LESS_THAN_IND Binary indication of whether the result is “less than” the Lab Reporting Limit or “less than” the Regulatory Minimum Reporting Limit

LESS_THAN_CODE When valued, indicates that the analytical result (concentration) was below the Regulatory Minimum Reporting Level or below the Laboratory Reporting Level (which in turn must be at or below the regulated MCL for the analyte)TCR Compliance Monitoring Data Quality and Completion Report

A-2 June 2010 Removed Data Element Field Name Remo ved Data Element Field Definition

DETECTN_LIMIT_NUM The limit established by the laboratory below which scientifically reliable results cannot be achieved. In many cases, if the computed detect ion limit is below the Method Detection Limit, then the Method De tection Limit is used as the detection limit; if the computed detection limit is above the Method Detection Limit, then the detection limit is used. DETECTN_LIMIT_UOM_CD Standard Unit of Measure for DETECTN_LIMIT_NUM

REPORTED_MSR The actual reported measure of a chemical/rad/water quality result. This measure is captured as text in order to preserve the trailing zeros which indicate the precision of the measure. The text value is also converted to a number and stored in the SBS_SAR Concentration_Measure attribute.
CONCENTRATION_MSR A numeric value that represents the result obtained from a sample analysis UOM_CODE Standard Unit of Measure

COUNT_QTY The number of organisms counted or estimated in a microbiological sample. Usually expr essed as “# of colonies per 100 milliliter sample.”

COUNT_TYPE Type of microbiological unit that is being counted per specified count unit. Count type varies with the microbiological organism where count has been recorded
COUNT_UOM_CODE The units of measure associated with the microbial analytical result count

There were 33 original data element fields; the final dataset uses 19 data element fields that are defined in Exhibit A.2.

Exhibit A.2 Final Data Element Fields Retained for TCR Analysis

Original Data Element Field Name Data Element Field Definition

NUMBER0 Uniquely identifies the water system within a specific State. Ex. DC1234567

NAME The name of the water system. The name can be the formal, legal, or common name that is used most generally in referring to the water system
D_PWS_FED_TYPE_CD A SDWIS/State system-generated coded value which classifies the water system according to federal requirements.
D_POPULATION_COUNT A SDWIS/State system-generated count of the total population served by a water system.
D_FED_PRIM_SRC_CD A SDWIS/State system-generated coded value which classifies the water system according to federal requirements.

TSASMPPT_IS_NUMBER A SDWIS/State system-generated value which, in conjunction with at least one other attribute or relationship (State Code), uniquely identifies the entity occurrence.

TSASMPPT_TYPE_CODE The coded value that represents the location type of a sampling point. E.g. Unit Process, Wa ter System Facility, and/or Distribution System.
SOURCE_TYPE_CD The type of water source, based on whether treatment has taken place.TCR Compliance Monitoring Data Quality and Completion Report

A-3 June 2010 Original Data Element Field Name Data Element Field Definition

IDENTIFICATION_CD The unique code for identifying a water system facility sample point. This value must be unique within the Water System Facility. DESCRIPTION_TEXT A description of the sampling point.
LAB_ASSGND_ID_NUM The sample identification number assigned by the laboratory for tracking the sample within the lab. COLLLECTION_END_DT Used in most cases as the Sample Collection Date ANALYSIS_COMPL_DT The date the laboratory completed the analysis of a sample. TSASMPL_TYPE_CODE The type of sample
REPEAT_LOC_TYP_CD The location of the repeat/check/confirmation sample with respect to the location of the original sample.

TSASMN_CODE A code value that represents t he method that was used to analyze the sample (e.g., EPA a ssigned method code). Ex. EPA 525.1
TSAANLYT_NAME The common name of a contaminant as used by States and EPA in the rules and other documents.

TSAANLYT_CODE The standard code used by EPA and the States to represent analytes in the national database. Each analyte has a unique code assigned to it for reference in compliance determination.

PRESENCE_IND_CODE Indicates whether results of an analysis were positive (P – Presence) or negative (A – Absence) . Indication of presence or absence creates an analytical result for a specific analyte.

The original data field names were replaced with more “user-friendly” field names to decrease confusion and provide a more informative title for the data in the each field. Exhibit A.3 is a side-by-side comparison of the original data element field names and the final data element field names.

Exhibit A.3 Data Element Field Names – Original vs. Final

Original Data Element Field Name Final Data Element Field Name

NUMBER0 PWSID_Number NAME PWSID_Name D_PWS_FED_TYPE_CD System_Type D_POPULATION_COUNT Population_Served D_FED_PRIM_SRC_CD Source_Water TSASMPPT_IS_NUMBER Samp ling_Point_Number TSASMPPT_TYPE_CODE Sampling_Point_Relative_Location SOURCE_TYPE_CODE Sour ce_Water_Treatment IDENTIFICATION_CD Facility_ID_Number DESCRIPTION_TEXT Sa mpling_Point_Text LAB_ASGND_ID_NUM Lab_Sample_Number COLLLECTION_END_DT Date_Sample_TakenTCR Compliance Monitoring Data Quality and Completion Report

A-4 June 2010 Original Data Element Field Name Final Data Element Field Name

ANALYSIS_COMPL_DT Da te_Sample_Analyzed TSASAMPL_TYPE_CODE Sample_Type REPEAT_LOC_TYP_CD Repeat_S ample_Relative_Location TSASMN_CODE Analytical_Method TSAANLYT_NAME Analyte_Name TSAANLYT_CODE Analyte_Code PRESENCE_IND_CODE Presence_Absence_Result



### A.1 ALASKA

A.1.1 Description of Data

Crosswalk of Data Fields

Please see above (Exhibits A-1 th rough A3) for a list of kept da ta element fields, a list of removed data element fields, the definitions of da ta element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The State did not provide data for “Dat e Sample Analyzed”; no date difference comparison could be completed. The majority of “Sample Point Relative Location” data elements were not provided. The majority of th e repeat sample records had no data for “Repeat Sample Relative Location.” The ma jority of records do not have data for “Analytical Method.” The population reported in SDWIS/Fed for PWS AK 2210906 (one of the ten largest PWSs in the State) does not closely match the popul ation data in SDWIS/State.

A.1.2 System Inventory Comparison

Exhibit A.4 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the data used in the 37-State final dataset.TCR Compliance Monitoring Data Quality and Completion Report

A-5 June 2010

Exhibit A.4 Alaska – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems

Percent Difference 2005 SDWIS 37-State Dataset Total 1,526 1,469 3% <101 TNCWS 619 581 6% <101 NTNCWS 108 106 2% <101 CWS 160 152 5% 101 to 500 TNCWS 234 231 1% 101 to 500 NTNCWS 94 103 -10% 101 to 500 CWS 175 177 -1% 501 to 1000 TNCWS 9 13 -44% 501 to 1000 NTNCWS 16 15 6% 501 to 1000 CWS 58 53 9% 1001 to 4100 TNCWS 5 6 -20% 1001 to 4100 NTNCWS 6 5 17% 1001 to 4100 CWS 22 20 9% 4101 to 33,000 TNCWS 1 1 0% 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 17 6 65% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 1 0 100% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 1 0 100%

A.1.3 Number of Samples

In the final dataset for Alaska:

The total number of samples included is 12,066

. The total number of routine samples included is 11,276

. The total number of routine TC samples included is 11,091

. The total number of routine EC and FC samples included is 185

.

A.1.4 QA Issues

Records with RW and WS (when clearly labele d as being part of a treatment plant rather than a distribution system) were deleted from “S ampling Point Relative Location” data elementTCR Compliance Monitoring Data Quality and Completion Report

A-6 June 2010 field. Records that had NP (non-public) data el ements in System Type field were deleted.



### A.2 ARIZONA

A.2.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

A few of the “Date Sample Analyzed” reco rds had no data; a date difference comparison was completed for available data. Some record s in 2005 had date differences (between sample date and sample analysis date) of 10 days or more. Some of the “Analytical Method” records had no data.

A.2.2 System Inventory Comparison

Exhibit A.5 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.5 Arizona – Number of System s by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 1607 1,478 8% <101 TNCWS 350 313 11% <101 NTNCWS 87 78 10% <101 CWS 201 181 10% 101 to 500 TNCWS 210 204 3% 101 to 500 NTNCWS 75 69 8% 101 to 500 CWS 282 272 4% 501 to 1000 TNCWS 24 25 -4% 501 to 1000 NTNCWS 23 24 -4% 501 to 1000 CWS 86 96 -12% 1001 to 4100 TNCWS 17 15 12% 1001 to 4100 NTNCWS 20 19 5%TCR Compliance Monitoring Data Quality and Completion Report

A-7 June 2010 Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset 1001 to 4100 CWS 127 119 6% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 1 1 0% 4101 to 33,000 CWS 80 56 30% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 1 0 100% 33,001 to 96,000 CWS 14 2 86% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 9 4 56%

A.2.3 Number of Samples

In the final dataset for Arizona:

The total number of samples included in is 40,966

. The total number of routine samples included is 38,532

. The total number of routine TC samples included is 37,720

. The total number of routine EC and FC samples included is 812

.

A.2.4 QA Issues

Records that had data element SP in the “Sam ple Type” data element field did not fit the pattern of identification for RT (routine) or RP (repeat) samples, and represented less than 0.1% of the sample records; these were deleted. Records that had NP (non-public) data elements in the “System Type” field were also deleted.



### A.3 COLORADO

A.3.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.TCR Compliance Monitoring Data Quality and Completion Report

A-8 June 2010 Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The majority of records do not have “Dat e Sample Analyzed” data; a date difference comparison was completed with available data. Some records in 2005 had date differences (between sample date and sample analysis date) of 10 days or more. The majority of repeat sample records do not have “Repeat Sample Relative Location” data. The majority of records do not have “Analytical Method” data.

A.3.2 System Inventory Comparison

Exhibit A.6 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.6 Colorado – Number of Syst ems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 1,978 1,804 9% <101 TNCWS 519 485 7% <101 NTNCWS 55 48 13% <101 CWS 216 194 10% 101 to 500 TNCWS 372 355 5% 101 to 500 NTNCWS 76 76 0% 101 to 500 CWS 274 277 -1% 501 to 1000 TNCWS 44 40 9% 501 to 1000 NTNCWS 16 18 -13% 501 to 1000 CWS 89 88 1% 1001 to 4100 TNCWS 34 33 3% 1001 to 4100 NTNCWS 11 12 -9% 1001 to 4100 CWS 137 127 7% 4101 to 33,000 TNCWS 8 3 63% 4101 to 33,000 NTNCWS 2 1 50% 4101 to 33,000 CWS 98 45 54% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 18 2 89% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 9 0 100%TCR Compliance Monitoring Data Quality and Completion Report

A-9 June 2010 A.3.3 Number of Samples

In the final dataset for Colorado:

The total number of samples included is 24,100

. The total number of routine samples included is 23,291

. The total number of routine TC samples included is 23,075

. The total number of routine EC and FC samples included is 216

.

A.3.4 QA Issues

The records that did not have “Presence Abse nce Result” data elements were deleted. The records that had NP data elements (non- public) in “System Type” field were deleted.



### A.4 CONNECTICUT

A.4.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

Many records do not have “Date Sample An alyzed” data; a date difference comparison was completed on available data. Some records in 2005 had date differences (between sample date and sample analysis date) of 10 days or mo re. Some of repeat sample records do not have “Repeat Sample Relative Location” data. The majority of records do not have “Analytical Method” data.

A.4.2 System Inventory Comparison

Exhibit A.7 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.TCR Compliance Monitoring Data Quality and Completion Report

A-10 June 2010

Exhibit A.7 Connecticut – Number of Sy stems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 2,881 2,800 3% <101 TNCWS 1,620 1,532 5% <101 NTNCWS 401 409 -2% <101 CWS 228 218 4% 101 to 500 TNCWS 35 35 0% 101 to 500 NTNCWS 223 163 27% 101 to 500 CWS 259 240 7% 501 to 1000 TNCWS 8 7 13% 501 to 1000 NTNCWS 12 61 -408% 501 to 1000 CWS 38 30 21% 1001 to 4100 TNCWS 1 1 0% 1001 to 4100 NTNCWS 0 13 1001 to 4100 CWS 3 42 -1300% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 31 35 -13% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 17 11 35% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 5 3 40%

A.4.3 Number of Samples

In the final dataset for Connecticut:

The total number of samples included is 37,685

. The total number of routine samples included is 33,357

. The total number of routine TC samples included is 32,406

. The total number of routine EC and FC samples included is 951

.

A.4.4 QA Issues

The records that did not have “Presence Abse nce Result” data elements were deleted. Records with RW, WS (when clearly labeled as be ing part of a treatment plant rather than aTCR Compliance Monitoring Data Quality and Completion Report

A-11 June 2010 distribution system), and UP were deleted fr om “Sampling Point Relative Location” data element field. The records that had NP data elements (non-public) in “System Type” field were deleted.



### A.5 DELAWARE

A.5.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The majority of records do not have “Dat e Sample Analyzed” data; a date difference comparison was completed for available data. Some records in 2005 had date differences (between sample date and sample analysis da te) of 10 days or more . DE0000221 is not included in the 10 largest systems in the SDWIS data. Th e majority of records do not have “Analytical Method” data.

A.5.2 System Inventory Comparison

Exhibit A.8 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.8 Delaware – Number of Sy stems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 515 520 -1% <101 TNCWS 83 87 -5% <101 NTNCWS 57 60 -5% <101 CWS 59 53 10% 101 to 500 TNCWS 77 78 -1% 101 to 500 NTNCWS 35 35 0% 101 to 500 CWS 89 93 -4%TCR Compliance Monitoring Data Quality and Completion Report

A-12 June 2010 Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset 501 to 1000 TNCWS 20 20 0% 501 to 1000 NTNCWS 6 6 0% 501 to 1000 CWS 21 23 -10% 1001 to 4100 TNCWS 8 8 0% 1001 to 4100 NTNCWS 8 7 13% 1001 to 4100 CWS 28 29 -4% 4101 to 33,000 TNCWS 1 1 0% 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 17 17 0% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 3 2 33% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 3 1 67%

A.5.3 Number of Samples

In the final dataset for Delaware:

The total number of samples included is 8,049

. The total number of routine samples included is 7,523

. The total number of routine TC samples included is 7,271

. The total number of routine EC and FC samples included is 252

.

A.5.4 QA Issues

The records that did not have “Presence Ab sence Result” data were deleted. Records with RW, WS (when clearly labele d as being part of a treatment plant rather than a distribution system), and PC were deleted from “Sampling Point Relative Location” data element field. The records that had NP data elements (non-publ ic) in System Type field were deleted.



### A.6 GEORGIA

A.6.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.TCR Compliance Monitoring Data Quality and Completion Report

A-13 June 2010 Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

Incomplete data was submitted for “Date Sample Analyzed”; a date difference comparison was completed for available data. A few records in 2005 had date differences (between sample date and sample analysis date) of 10 days or more. The majority of records do not have “Analytical Method” data.

A.6.2 System Inventory Comparison

Exhibit A.9 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.9 Georgia – Number of Syst ems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 2,464 2,360 4% <101 TNCWS 327 319 2% <101 NTNCWS 96 98 -2% <101 CWS 598 583 3% 101 to 500 TNCWS 185 184 1% 101 to 500 NTNCWS 87 92 -6% 101 to 500 CWS 572 582 -2% 501 to 1000 TNCWS 29 26 10% 501 to 1000 NTNCWS 33 32 3% 501 to 1000 CWS 140 141 -1% 1001 to 4100 TNCWS 2 2 0% 1001 to 4100 NTNCWS 11 13 -18% 1001 to 4100 CWS 209 202 3% 4101 to 33,000 TNCWS 0 1 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 139 85 39% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 23 0 100% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 13 0 100%TCR Compliance Monitoring Data Quality and Completion Report

A-14 June 2010 A.6.3 Number of Samples

In the final dataset for Georgia:

The total number of samples included is 37,032. The total number of routine samples included is 35,131

. The total number of routine TC samples included is 34,646

. The total number of routine EC and FC samples included is 485

.

A.6.4 QA Issues

The records that had NP data elements (non- public) in “System Type” field were deleted.



### A.7 IDAHO

A.7.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The majority of records have do not have “D ate Sample Analyzed” data; a date difference comparison was completed for available data. No records in 2005 had date differences of 10 days or more. The majority of records do not have “Sampling Point Relative Location” data. The majority of records do not have “Analytical Method” data. A few large systems have large population discrepancies between th e State dataset and 2005 SDWIS.

A.7.2 System Inventory Comparison

Exhibit A.10 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.TCR Compliance Monitoring Data Quality and Completion Report

A-15 June 2010

Exhibit A.10 Idaho – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 2,015 1,942 4% <101 TNCWS 799 754 6% <101 NTNCWS 132 116 12% <101 CWS 362 360 1% 101 to 500 TNCWS 208 205 1% 101 to 500 NTNCWS 84 78 7% 101 to 500 CWS 229 225 2% 501 to 1000 TNCWS 18 18 0% 501 to 1000 NTNCWS 18 22 -22% 501 to 1000 CWS 60 58 3% 1001 to 4100 TNCWS 5 5 0% 1001 to 4100 NTNCWS 6 6 0% 1001 to 4100 CWS 56 56 0% 4101 to 33,000 TNCWS 1 0 100% 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 31 31 0% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 5 7 -40% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 1 1 0%

A.7.3 Number of Samples

In the final dataset for Idaho:

The total number of samples included is 30,620

. The total number of routine samples included is 28,202

. The total number of routine TC samples included is 27,489

. The total number of routine EC and FC samples included is 713

.

A.7.4 QA Issues

The records that did not have “Presence Abse nce Result” data were deleted. The records with RW, WS (when clearly labele d as being part of a treatment plant rather than a distribution system), and UP were deleted from “Sampling Point Relative Location” data element field. The records that had NP data elements (non-publ ic) in System Type field were deleted.TCR Compliance Monitoring Data Quality and Completion Report

A-16 June 2010 

### A.8 INDIANA

A.8.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

Many records do not have “Date Sample An alyzed” data; a date difference comparison was completed for available data. For dates th at could be compared, no 2005 data had date discrepancies of greater than 10 days. Many repe at sample records do not have “Repeat Sample Relative Location” data elements. The majority of records do not have “Analytical Method” data elements.

A.8.2 System Inventory Comparison

Exhibit A.11 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.11 Indiana – Number of Syst ems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 4,407 4,417 -0.2% <101 TNCWS 1,980 2,036 -3% <101 NTNCWS 300 315 -5% <101 CWS 164 175 -7% 101 to 500 TNCWS 758 720 5% 101 to 500 NTNCWS 233 241 -3% 101 to 500 CWS 151 147 3% 501 to 1000 TNCWS 131 134 -2% 501 to 1000 NTNCWS 116 115 1% 501 to 1000 CWS 132 128 3%TCR Compliance Monitoring Data Quality and Completion Report

A-17 June 2010 Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset 1001 to 4100 TNCWS 25 27 -8% 1001 to 4100 NTNCWS 26 28 -8% 1001 to 4100 CWS 213 217 -2% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 155 133 14% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 18 1 94% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 5 0 100%

A.8.3 Number of Samples

In the final dataset for Indiana:

The total number of samples included is 53,911

. The total number of routine samples included is 49,357

. The total number of routine TC samples included is 48,298

. The total number of routine EC and FC samples included is 1,059

.

A.8.4 QA Issues

The records with RW (when sample taken be fore any treatment) and PC were deleted from “Sampling Point Relative Location” data element field. The records that had NP data elements (non-public) in “System Type” field were deleted.



### A.9 IOWA

A.9.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.TCR Compliance Monitoring Data Quality and Completion Report

A-18 June 2010 Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The State did not provide data for “Dat e Sample Analyzed”; no date difference comparison could be completed. Only a few records do not have “Analytical Method” data elements.

A.9.2 System Inventory Comparison

Exhibit A.12 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.12 Iowa – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 1,991 1,999 -0.4% <101 TNCWS 510 497 3% <101 NTNCWS 63 60 5% <101 CWS 210 211 0% 101 to 500 TNCWS 176 192 -9% 101 to 500 NTNCWS 49 49 0% 101 to 500 CWS 408 410 0% 501 to 1000 TNCWS 21 19 10% 501 to 1000 NTNCWS 14 15 -7% 501 to 1000 CWS 199 200 -1% 1001 to 4100 TNCWS 6 8 -33% 1001 to 4100 NTNCWS 13 13 0% 1001 to 4100 CWS 216 220 -2% 4101 to 33,000 TNCWS 1 0 100% 4101 to 33,000 NTNCWS 0 1 4101 to 33,000 CWS 94 91 3% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 8 10 -25% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 3 3 0%TCR Compliance Monitoring Data Quality and Completion Report

A-19 June 2010 A.9.3 Number of Samples

In the final dataset for Iowa:

The total number of samples included is 53,560

. The total number of routine samples included is 51,573

. The total number of routine TC samples included is 50,740

. The total number of routine EC and FC samples included is 833

.

A.9.4 QA Issues

The records that had NP data elements (non- public) in “System Type” field were deleted.



### A.10 KANSAS

A.10.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

In January of 2008, Kansas was contacted to provide their data. Once the data had been submitted later that month, no further co rrespondence with the State was necessary.

Narrative of Issues

No significant issues.

A.10.2 System Inventory Comparison

Exhibit A.13 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.TCR Compliance Monitoring Data Quality and Completion Report

A-20 June 2010 Exhibit A.13 Kansas – Number of Syst ems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 1,055 1,010 4% <101 TNCWS 95 82 14% <101 NTNCWS 27 22 19% <101 CWS 132 116 12% 101 to 500 TNCWS 5 8 -60% 101 to 500 NTNCWS 14 17 -21% 101 to 500 CWS 347 346 0% 501 to 1000 TNCWS 0 0 501 to 1000 NTNCWS 3 3 0% 501 to 1000 CWS 144 135 6% 1001 to 4100 TNCWS 0 0 1001 to 4100 NTNCWS 5 5 0% 1001 to 4100 CWS 216 215 0% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 56 57 -2% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 6 4 33% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 5 0 100%

A.10.3 Number of Samples

In the final dataset for Kansas:

The total number of samples included is 34,774

. The total number of routine samples included is 33,977

. The total number of routine TC samples included is 33,741

. The total number of routine EC and FC samples included is 236

.

A.10.4 QA Issues

No significant issues.TCR Compliance Monitoring Data Quality and Completion Report

A-21 June 2010 

### A.11 KENTUCKY

A.11.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

State did not provide any fecal data. The ma jority of records do not have “Date Sample Analyzed” data elements; a date difference comp arison was completed for available data. The majority of records that had date differences (between sample date and sample analysis date) of 10 days or more are records from 2005. Some large systems have population discrepancies. A few repeat sample records do not have data elements for “Repeat Sample Relative Location.” A few records do not have “Analy tical Method” data elements.

A.11.2 System Inventory Comparison

Exhibit A.14 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.14 Kentucky – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 518 500 3% <101 TNCWS 37 34 8% <101 NTNCWS 17 14 18% <101 CWS 14 12 14% 101 to 500 TNCWS 25 24 4% 101 to 500 NTNCWS 15 13 13% 101 to 500 CWS 28 24 14% 501 to 1000 TNCWS 1 0 100% 501 to 1000 NTNCWS 3 4 -33%TCR Compliance Monitoring Data Quality and Completion Report

A-22 June 2010 Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset 501 to 1000 CWS 24 21 13% 1001 to 4100 TNCWS 0 0 1001 to 4100 NTNCWS 5 5 0% 1001 to 4100 CWS 132 128 3% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 199 202 -2% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 15 16 -7% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 3 3 0%

A.11.3 Number of Samples

In the final dataset for Kentucky

The total number of samples included is 56,564

. The total number of routine samples included is 55,943

. The total number of routine TC samples included is 55,744

. The total number of routine EC and FC samples included is 199

.

A.11.4 QA Issues

The records that did not have “Presence Abse nce Result” data were deleted. The records that had NP data elements (non-public) in “System Type” field were deleted.



### A.12 MONTANA

A.12.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.TCR Compliance Monitoring Data Quality and Completion Report

A-23 June 2010 Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The majority of records do not have “Analy tical Method” data elements. Only a few repeat sample records do not have “Repeat Sample Relative Location” data elements. The majority of records do not have Date Sample Analyzed data; date difference comparison was completed for available data. Very few records from 2005 had date differences (between sample date and sample analysis date) of 10 days or more.

A.12.2 System Inventory Comparison

Exhibit A.15 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.15 Montana – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 2,060 1,976 4% <101 TNCWS 768 711 7% <101 NTNCWS 97 103 -6% <101 CWS 286 262 8% 101 to 500 TNCWS 334 323 3% 101 to 500 NTNCWS 110 113 -3% 101 to 500 CWS 260 253 3% 501 to 1000 TNCWS 39 41 -5% 501 to 1000 NTNCWS 6 10 -67% 501 to 1000 CWS 52 48 8% 1001 to 4100 TNCWS 18 19 -6% 1001 to 4100 NTNCWS 6 10 -67% 1001 to 4100 CWS 55 56 -2% 4101 to 33,000 TNCWS 1 0 100% 4101 to 33,000 NTNCWS 1 1 0% 4101 to 33,000 CWS 23 22 4% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 4 4 0% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 0 0TCR Compliance Monitoring Data Quality and Completion Report

A-24 June 2010 A.12.3 Number of Samples

In the final dataset for Montana:

The total number of samples included is 30,547

. The total number of routine samples included is 28,847

. The total number of routine TC samples included is 28,192

. The total number of routine EC and FC samples included is 655

.

A.12.4 QA Issues

The records that did not have “Presence Abse nce Result” data were deleted. Unlike other States with data element SR in “Sampling Point Relative Location” field, SR data was kept for Montana. The records with RW data element in “Sampling Point Relative Location” field were deleted. The records that had NP data elem ents (non-public) in “System Type” field were deleted.



### A.13 NEBRASKA

A.13.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

Many records do not have “Date Sample An alyzed” data; a date difference comparison was completed for available data. Many reco rds from 2005 had date differences (between sample date and sample analysis date) of 10 days or more. Some records do not have “Analytical Method” data elements.

A.13.2 System Inventory Comparison

Exhibit A.16 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.TCR Compliance Monitoring Data Quality and Completion Report

A-25 June 2010

Exhibit A.16 Nebraska – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 1,344 1,390 -3% <101 TNCWS 417 458 -10% <101 NTNCWS 112 109 3% <101 CWS 81 81 0% 101 to 500 TNCWS 122 126 -3% 101 to 500 NTNCWS 55 58 -5% 101 to 500 CWS 290 289 0% 501 to 1000 TNCWS 17 18 -6% 501 to 1000 NTNCWS 6 8 -33% 501 to 1000 CWS 100 102 -2% 1001 to 4100 TNCWS 2 1 50% 1001 to 4100 NTNCWS 10 9 10% 1001 to 4100 CWS 96 96 0% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 2 2 0% 4101 to 33,000 CWS 31 31 0% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 1 1 0% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 2 1 50%

A.13.3 Number of Samples

In the final dataset for Nebraska:

The total number of samples included is 26,938

. The total number of routine samples included is 24,297

. The total number of routine TC samples included is 23,664

. The total number of routine EC and FC samples included is 633

.

A.13.4 QA Issues

The records that did not have “Presence Abse nce Result” data were deleted. The records with WS data elements in “Sampling Point Relati ve Location” field were deleted. Records thatTCR Compliance Monitoring Data Quality and Completion Report

A-26 June 2010 had NP data elements (non-public) in “System Type” field were deleted.



### A.14 NEVADA

A.14.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names

compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The State did not provide data for “Dat e Sample Analyzed”; no date difference comparison could be completed. Only a few repeat sample records do not have data elements in “Repeat Sample Relative Location” field. The majority of records do not have data elements in “Analytical Method” field.

A.14.2 System Inventory Comparison

Exhibit A.17 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.17 Nevada – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 597 501 16% <101 TNCWS 213 189 11% <101 NTNCWS 47 41 13% <101 CWS 90 63 30% 101 to 500 TNCWS 31 21 32% 101 to 500 NTNCWS 51 43 16% 101 to 500 CWS 66 56 15% 501 to 1000 TNCWS 5 5 0% 501 to 1000 NTNCWS 6 5 17%TCR Compliance Monitoring Data Quality and Completion Report

A-27 June 2010 Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset 501 to 1000 CWS 17 17 0% 1001 to 4100 TNCWS 3 2 33% 1001 to 4100 NTNCWS 2 5 -150% 1001 to 4100 CWS 34 31 9% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 1 100% 4101 to 33,000 CWS 27 22 19% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 1 0 100% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 4 0 100%

A.14.3 Number of Samples

In the final dataset for Nevada:

The total number of samples included is 8,544

. The total number of routine samples included is 8,016

. The total number of routine TC samples included is 7,822

. The total number of routine EC and FC samples included is 194

.

A.14.4 QA Issues

The records that have RW data elements were deleted from “Sampling Point Relative Location” field. The records that had NP da ta elements (non-public) in “System Type” field were deleted.



### A.15 NEW MEXICO

A.15.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.TCR Compliance Monitoring Data Quality and Completion Report

A-28 June 2010 Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The majority of records do not have data in “Date Sample Analyzed” field; a date difference comparison was completed for available data. A few records from 2005 had date differences (between sample date and sample analysis date) of 10 days or more. The majority of repeat sample records do not have data elements in “R epeat Sample Relative Location” field. Some records do not have data in the “Analytical Method” field.

A.15.2 System Inventory Comparison

Exhibit A.18 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.18 New Mexico – Number of Sy stems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 1,273 1,180 7% <101 TNCWS 320 264 18% <101 NTNCWS 83 75 10% <101 CWS 208 190 9% 101 to 500 TNCWS 129 127 2% 101 to 500 NTNCWS 48 52 -8% 101 to 500 CWS 249 245 2% 501 to 1000 TNCWS 12 10 17% 501 to 1000 NTNCWS 15 12 20% 501 to 1000 CWS 56 52 7% 1001 to 4100 TNCWS 12 11 8% 1001 to 4100 NTNCWS 7 8 -14% 1001 to 4100 CWS 79 81 -3% 4101 to 33,000 TNCWS 2 1 50% 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 47 45 4% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 5 6 -20% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 1 1 0%TCR Compliance Monitoring Data Quality and Completion Report

A-29 June 2010 A.15.3 Number of Samples

In the final dataset for New Mexico:

The total number of samples included is 26,416

. The total number of routine samples included is 24,511

. The total number of routine TC samples included is 24,053

. The total number of routine EC and FC samples included is 458

.

A.15.4 QA Issues

The records that did not have “Presence Abse nce Result” data were deleted. The records that had NP data elements (non-public) in “System Type” field were deleted.



### A.16 NEW YORK

A.16.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

Some records do not have PWSID in corre ct format (e.g., DC1234567). Most records do not have “Date Sample Analyzed” data elements; a date difference comparison was completed for available data. Of those records that could be compared, no 2005 records had date differences of 10 days or more. Some repeat sample records do not have data elements in “Repeat Sample Relative Location” field. Most records do not have data elements in “Analytical Method” field.

A.16.2 System Inventory Comparison

Exhibit A.19 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.TCR Compliance Monitoring Data Quality and Completion Report

A-30 June 2010

Exhibit A.19 New York – Number of Sy stems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 9,855 7,066 28% <101 TNCWS 4,680 3,653 22% <101 NTNCWS 331 252 24% <101 CWS 1,008 577 43% 101 to 500 TNCWS 1,437 1,076 25% 101 to 500 NTNCWS 268 206 23% 101 to 500 CWS 876 580 34% 501 to 1000 TNCWS 114 94 18% 501 to 1000 NTNCWS 101 73 28% 501 to 1000 CWS 267 195 27% 1001 to 4100 TNCWS 43 28 35% 1001 to 4100 NTNCWS 48 43 10% 1001 to 4100 CWS 380 227 40% 4101 to 33,000 TNCWS 14 3 79% 4101 to 33,000 NTNCWS 6 1 83% 4101 to 33,000 CWS 227 57 75% 33,001 to 96,000 TNCWS 1 0 100% 33,001 to 96,000 NTNCWS 1 0 100% 33,001 to 96,000 CWS 31 1 97% >96,000 TNCWS 1 0 100% >96,000 NTNCWS 0 0 >96,000 CWS 21 0 100%

A.16.3 Number of Samples

In the final dataset for New York:

The total number of samples is 59,336

. The total number of routine samples included is 56,644

. The total number of routine TC samples included is 55,532

. The total number of routine EC and FC samples included is 1,112

.

A.16.4 QA Issues

The records that had PWSID in incorrect format were either removed (i.e., test site that should not be used) or formatted correctly. Th e records that did not have “Presence AbsenceTCR Compliance Monitoring Data Quality and Completion Report

A-31 June 2010 Result” data were deleted. The records with RW (when there was no clea r indication that they were taken from a distribution system), WS (whe n clearly labeled as bei ng part of a treatment plant rather than a distribution system), PC, SR , and UP were deleted from “Sampling Point Relative Location” data element field. The reco rds that had NP data elements (non-public) in “System Type” field were deleted.



### A.17 NORTH CAROLINA

A.17.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

Most records have “Date Sample Analyzed” data elements; a date difference comparison was completed for available data. Of dates that could be compared, all date differences of 10 days or more are records from 2005. The State only submitted data for community water systems.

A.17.2 System Inventory Comparison

Exhibit A.20 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.20 North Carolina – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 6,947 2,056 70% <101 TNCWS 3,457 0 100% <101 NTNCWS 275 0 100% <101 CWS 822 773 6% 101 to 500 TNCWS 717 0 100% 101 to 500 NTNCWS 261 0 100%TCR Compliance Monitoring Data Quality and Completion Report

A-32 June 2010 Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset 101 to 500 CWS 751 773 -3% 501 to 1000 TNCWS 33 0 100% 501 to 1000 NTNCWS 20 0 100% 501 to 1000 CWS 164 165 -1% 1001 to 4100 TNCWS 5 0 100% 1001 to 4100 NTNCWS 1 0 100% 1001 to 4100 CWS 219 204 7% 4101 to 33,000 TNCWS 1 0 100% 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 190 138 27% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 20 3 85% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 11 0 100%

A.17.3 Number of Samples

In the final dataset for North Carolina:

The total number of samples included in is 12,226

. The total number of routine samples included is 12,016

. The total number of routine TC samples included is 11,944

. The total number of routine EC and FC samples included is 72

.

A.17.4 QA Issues

No QA issues were encountered for North Ca rolina data. Data was clean and complete.



### A.18 NORTH DAKOTA

A.18.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.TCR Compliance Monitoring Data Quality and Completion Report

A-33 June 2010 Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The State did not provide data for “Date Sample Analyzed”; no date difference comparison could be completed. Some records do not have “Analytical Method” data elements.

A.18.2 System Inventory Comparison

Exhibit A.21 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.21 North Dakota – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 515 519 -0.8% <101 TNCWS 141 141 0% <101 NTNCWS 14 17 -21% <101 CWS 61 61 0% 101 to 500 TNCWS 23 24 -4% 101 to 500 NTNCWS 10 9 10% 101 to 500 CWS 132 132 0% 501 to 1000 TNCWS 3 3 0% 501 to 1000 NTNCWS 1 2 -100% 501 to 1000 CWS 51 51 0% 1001 to 4100 TNCWS 0 0 1001 to 4100 NTNCWS 0 0 1001 to 4100 CWS 61 61 0% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 14 14 0% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 4 4 0% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 0 0TCR Compliance Monitoring Data Quality and Completion Report

A-34 June 2010 A.18.3 Number of Samples

In the final datase t for North Dakota:

The total number of samples included is 11,367

. The total number of routine samples included is 10,970

. The total number of routine TC samples included is 10,858

. The total number of routine EC and FC samples included is 112

.

A.18.4 QA Issues

The records that did not have “Presence Abse nce Result” data were deleted. The records that had NP data elements (non-public) in “System Type” field were deleted.



### A.19 OKLAHOMA

A.19.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

Most records had Date Sample Analyzed data elements; a date difference comparison was completed for available data. Some records from 2005 had date differences (between sample date and sample analysis date) of 10 days or more. Only a few repeat sample records do not have data elements in the “Repeat Sample Re lative Location” field. Only a few records do not have “Analytical Method” data.

A.19.2 System Inventory Comparison

Exhibit A.22 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.TCR Compliance Monitoring Data Quality and Completion Report

A-35 June 2010

Exhibit A.22 Oklahoma – Number of Syst ems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 1,608 1,469 9% <101 TNCWS 309 274 11% <101 NTNCWS 59 56 5% <101 CWS 180 145 19% 101 to 500 TNCWS 48 43 10% 101 to 500 NTNCWS 45 42 7% 101 to 500 CWS 403 353 12% 501 to 1000 TNCWS 6 6 0% 501 to 1000 NTNCWS 6 8 -33% 501 to 1000 CWS 189 154 19% 1001 to 4100 TNCWS 0 0 1001 to 4100 NTNCWS 2 2 0% 1001 to 4100 CWS 251 280 -12% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 96 94 2% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 10 10 0% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 4 2 50%

A.19.3 Number of Samples

In the final dataset for Oklahoma:

The total number of samples included is 48,694

. The total number of routine samples included is 45,552

. The total number of routine TC samples included is 44,829 The total number of routine EC and FC samples included is 723

.

A.19.4 QA Issues

The records that did not have “Presence Abse nce Result” data were deleted. The records that had NP data elements (non-public) in “System Type” field were deleted.TCR Compliance Monitoring Data Quality and Completion Report

A-36 June 2010 

### A.20 OREGON

A.20.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

Most records do not have data elements for “Date Sample Analyzed” field; date difference comparison was completed for availabl e data. Many repeat sample records do not have data elements in “Repeat Sample Relative Location” field. The State did not provide and data elements for the “Analytical Method” field.

A.20.2 System Inventory Comparison

Exhibit A.23 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.23 Oregon – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 2,644 2,602 2% <101 TNCWS 957 938 2% <101 NTNCWS 177 169 5% <101 CWS 328 316 4% 101 to 500 TNCWS 400 387 3% 101 to 500 NTNCWS 132 140 -6% 101 to 500 CWS 284 291 -2% 501 to 1000 TNCWS 54 52 4% 501 to 1000 NTNCWS 21 19 10% 501 to 1000 CWS 60 56 7% 1001 to 4100 TNCWS 19 19 0%TCR Compliance Monitoring Data Quality and Completion Report

A-37 June 2010 Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset 1001 to 4100 NTNCWS 9 9 0% 1001 to 4100 CWS 109 110 -1% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 75 76 -1% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 15 16 -7% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 4 4 0%

A.20.3 Number of Samples

In the final dataset for Oregon:

The total number of samples included is 61,033

. The total number of routine samples included is 57,882

. The total number of routine TC samples included is 57,158

. The total number of routine EC and FC samples included is 724

.

A.20.4 QA Issues

The records that had NP data elements (non- public) in “System Type” field were deleted.



### A.21 RHODE ISLAND

A.21.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence No correspondence was necessary.TCR Compliance Monitoring Data Quality and Completion Report

A-38 June 2010 Narrative of Issues

Most records do not have data elements for “Date Sample Analyzed” field; a date difference comparison was completed for availabl e data. Of those records that could be compared, no 2005 records had date differences of 10 days or more. Only a few repeat sample records do not have data elements in “Repeat Sa mple Relative Location” field. Only a few records do not have data elements in “Analyti cal Method” field. State submitted TC+, TC repeats, and Fecal and E. coli tests, but no TC- routine samples. A large system (Pawtucket) only has negative TCR samples in 2005.

A.21.2 System Inventory Comparison

Exhibit A.24 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.24 Rhode Island – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 475 459 3% <101 TNCWS 169 173 -2% <101 NTNCWS 32 35 -9% <101 CWS 21 23 -10% 101 to 500 TNCWS 133 132 1% 101 to 500 NTNCWS 31 31 0% 101 to 500 CWS 25 23 8% 501 to 1000 TNCWS 8 8 0% 501 to 1000 NTNCWS 7 7 0% 501 to 1000 CWS 4 3 25% 1001 to 4100 TNCWS 4 4 0% 1001 to 4100 NTNCWS 6 6 0% 1001 to 4100 CWS 11 8 27% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 1 0 100% 4101 to 33,000 CWS 14 6 57% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 7 0 100% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 2 0 100%TCR Compliance Monitoring Data Quality and Completion Report

A-39 June 2010 A.21.3 Number of Samples

In the final dataset for Rhode Island:

The total number of samples included is 3,980

. The total number of routine samples included is 3,462

. The total number of routine TC samples included is 3,301

. The total number of routine EC and FC samples included is 161

.

A.21.4 QA Issues

The records with RW and WS (when clearly labeled as being part of a treatment plant rather than a distribution system) were delete d from “Sampling Point Relative Location” data element field. The records that had NP data elements (non-public) in “System Type” field were deleted.



### A.22 UTAH

A.22.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The State did not provide data for “Dat e Sample Analyzed”; no date difference comparison could be completed. The State did not provide EC data.

Note that Utah’s Federal PWSIDs begin with the entire State name rather than the two letter acronym (e.g., UTAH12345).

A.22.2 System Inventory Comparison

Exhibit A.25 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.TCR Compliance Monitoring Data Quality and Completion Report

A-40 June 2010

Exhibit A.25 Utah – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 945 903 4% <101 TNCWS 253 264 -4% <101 NTNCWS 27 44 -63% <101 CWS 102 95 7% 101 to 500 TNCWS 152 154 -1% 101 to 500 NTNCWS 27 31 -15% 101 to 500 CWS 137 140 -2% 501 to 1000 TNCWS 23 26 -13% 501 to 1000 NTNCWS 3 3 0% 501 to 1000 CWS 54 53 2% 1001 to 4100 TNCWS 3 3 0% 1001 to 4100 NTNCWS 5 8 -60% 1001 to 4100 CWS 66 65 0% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 1 0 100% 4101 to 33,000 CWS 76 17 78% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 12 0 100% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 4 0 100%

A.22.3 Number of Samples

In the final dataset for Utah:

The total number of samples included is 10,759

. The total number of routine samples included is 9,572

. The total number of routine TC samples included is 9,187

. The total number of routine EC and FC samples included is 385

.

A.22.4 QA Issues

The records that had NP data elements (non- public) in “System Type” field were deleted. The records that did not have data elem ents for “System Type” were deleted.TCR Compliance Monitoring Data Quality and Completion Report

A-41 June 2010 

### A.23 VERMONT

A.23.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

Most records do not have “Date Sample Analyzed data” elements’ date difference comparison was completed for available data. Of those that could be compared, no 2005 records had date differences of 10 days or more. Most repeat sample records do not have data elements in “Repeat Sample Relative Location” field. Mo st records do not have “A nalytical Method” data elements.

A.23.2 System Inventory Comparison

Exhibit A.26 is a side-by-side comparison of the number of systems in the SDWIS/Fed inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.26 Vermont – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 1,367 1,329 3% <101 TNCWS 422 381 10% <101 NTNCWS 119 119 0% <101 CWS 144 143 1% 101 to 500 TNCWS 242 248 -2% 101 to 500 NTNCWS 105 110 -5% 101 to 500 CWS 171 174 -2% 501 to 1000 TNCWS 19 25 -32% 501 to 1000 NTNCWS 12 12 0% 501 to 1000 CWS 42 42 0% 1001 to 4100 TNCWS 7 4 43%TCR Compliance Monitoring Data Quality and Completion Report

A-42 June 2010 Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset 1001 to 4100 NTNCWS 5 3 40% 1001 to 4100 CWS 55 55 0% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 23 13 43% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 1 0 100% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 0 0

A.23.3 Number of Samples

In the final dataset for Vermont:

The total number of samples included is 15,330

. The total number of routine samples included is 13,609

. The total number of routine TC samples included is 12,962

. The total number of routine EC and FC samples included is 647

.

A.23.4 QA Issues

The records with RW were deleted from “Sampling Point Relative Location” data element field. The records that had NP data elements (non-public) in “System Type” field were deleted.



### A.24 WEST VIRGINIA

A.24.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.TCR Compliance Monitoring Data Quality and Completion Report

A-43 June 2010 Narrative of Issues

Most records do not have “Date Sample An alyzed” data elements; a date difference comparison was completed for available data. Of those that could be compared, no 2005 records had date differences of 10 days or more. Se veral large systems had population discrepancies when comparing the State dataset to 2005 SDWIS inventory. The majority of records do not have “Analytical Method” data elements.

A.24.2 System Inventory Comparison

Exhibit A.27 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.

Exhibit A.27 West Virginia – Number of Systems by Popul ation Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 1192 1200 -0.67% <101 TNCWS 461 466 -1% <101 NTNCWS 71 73 -3% <101 CWS 75 70 7% 101 to 500 TNCWS 45 45 0% 101 to 500 NTNCWS 63 67 -6% 101 to 500 CWS 204 132 35% 501 to 1000 TNCWS 2 3 -50% 501 to 1000 NTNCWS 10 9 10% 501 to 1000 CWS 185 72 61% 1001 to 4100 TNCWS 1 0 100% 1001 to 4100 NTNCWS 8 8 0% 1001 to 4100 CWS 62 188 -203% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 4 62 -1450% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 1 4 -300% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 0 1TCR Compliance Monitoring Data Quality and Completion Report

A-44 June 2010 A.24.3 Number of Samples

In the final dataset for West Virginia:

The total number of samples is 25,664

. The total number of routine samples included is 25,069

. The total number of routine TC samples included is 24,894

. The total number of routine EC and FC samples included is 175

.

A.24.4 QA Issues

The records that did not have “Presence Abse nce Result” data were deleted. The records with RW and WS (when clearly labeled as being part of a tr eatment plant rather than a distribution system) were deleted from “Sampling Point Relative Location” data element field. The records that had NP data elements (non- public) in “System Type” field were deleted.



### A.25 WYOMING

A.25.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The majority of records do not have “Date Sample Analyzed” data elements; a date difference comparison was completed for available data. Of those that could be compared, no 2005 records had date differences of 10 days or more. Many repeat sample records do not have data elements in “Repeat Sample Relative Location” field. The majority of records do not have “Analytical Method” data elements.

A.25.2 System Inventory Comparison

Exhibit A.28 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the da ta used in the 37-State final dataset.TCR Compliance Monitoring Data Quality and Completion Report

A-45 June 2010

Exhibit A.28 Wyoming – Number of Syst ems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 765 729 5% <101 TNCWS 282 259 8% <101 NTNCWS 43 41 5% <101 CWS 87 85 2% 101 to 500 TNCWS 91 86 5% 101 to 500 NTNCWS 36 35 3% 101 to 500 CWS 118 117 1% 501 to 1000 TNCWS 12 17 -42% 501 to 1000 NTNCWS 5 5 0% 501 to 1000 CWS 19 20 -5% 1001 to 4100 TNCWS 12 7 42% 1001 to 4100 NTNCWS 2 2 0% 1001 to 4100 CWS 33 32 3% 4101 to 33,000 TNCWS 3 3 0% 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 20 18 10% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 2 2 0% >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 0 0

A.25.3 Number of Samples

In the final dataset for Wyoming:

The total number of samples included is 12,749

. The total number of routine samples included is 12,123

. The total number of routine TC samples included is 11,935

. The total number of routine EC and FC samples included is 188

.

A.25.4 QA Issues

The records that did not have “Presence Abse nce Result” data were deleted. The records that had NP data elements (non-public) in “System Type” field were deleted.TCR Compliance Monitoring Data Quality and Completion Report

A-46 June 2010 

### A.26 REGION 8 TRIBAL

A.26.1 Description of Data

Crosswalk of Data Fields

Please see above for a list of kept data el ement fields, a list of removed data element fields, the definitions of data element fields, and a list of the final data element field names compared to the original data element field names.

Summary of State Correspondence

No correspondence was necessary.

Narrative of Issues

The majority of records do not have “Dat e Sample Analyzed” data elements; date difference comparison was completed for available data. Of those that could be compared, no 2005 records had date differences of 10 days or mo re. Some repeat sample records do not have data elements in “Repeat Sample Relative Location” field. Most records do not have “Analytical Method” data elements.

A.26.2 System Inventory Comparison

Exhibit A.29 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the data provided used in the 37-State final dataset.

Exhibit A.29 Region 8 Tribal – Number of Systems by Population Served and System Type

Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset Total 127 123 3% <101 TNCWS 2 2 0% <101 NTNCWS 3 3 0% <101 CWS 29 28 3% 101 to 500 TNCWS 1 0 100% 101 to 500 NTNCWS 5 4 20% 101 to 500 CWS 48 49 -2% 501 to 1000 TNCWS 0 0 501 to 1000 NTNCWS 2 0 100% 501 to 1000 CWS 13 13 0%TCR Compliance Monitoring Data Quality and Completion Report

A-47 June 2010 Population

Served System Type Number of Systems Percent Difference 2005 SDWIS 37-State Dataset 1001 to 4100 TNCWS 0 0 1001 to 4100 NTNCWS 0 2 1001 to 4100 CWS 21 20 5% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 NTNCWS 0 0 4101 to 33,000 CWS 3 2 33% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 NTNCWS 0 0 33,001 to 96,000 CWS 0 0 >96,000 TNCWS 0 0 >96,000 NTNCWS 0 0 >96,000 CWS 0 0

A.26.3 Number of Samples

In the final dataset for Region 8 Tribal:

The total number of samples included is 2,398

. The total number of routine samples included is 2,109

. The total number of routine TC samples included is 2,037

. The total number of routine EC and FC samples included is 72

.

A.26.4 QA Issues

The records that did not have “Presence Abse nce Result” data were deleted. The records that had NP data elements (non-public) in “System Type” field were deleted.

### Appendix B Summary of Data from States No t Using the Extraction Tool: State-by-State BreakoutTCR Compliance Monitoring Data Quality and Completion Report

B-i June 2010 

### Contents

B.1 ARKANSAS .................................................................................................................. .. B-3 B.2 FLORIDA ................................................................................................................... ..... B-6 B.3 ILLINOIS .................................................................................................................. ..... B-10 B.4 MICHIGAN .................................................................................................................. . B-13 B.5 MINNESOTA ................................................................................................................ B-18 B.7 NEW HAMPSHIRE ...................................................................................................... B-24 B.8 OHIO....................................................................................................................... ....... B-28 B.9 VIRGINIA .................................................................................................................. ... B-34 B.10 WISCONSIN ................................................................................................................ . B-37 B.11 REGION 9 TRIBAL ...................................................................................................... B-4 2TCR Compliance Monitoring Data Quality and Completion Report

B-1 June 2010 For each State that did not use the SDWIS Extr action Tool, below is a description of the data, crosswalk of the data fields, narrative of the issues, summary of the correspondence with the State, inventory comparison, total number of records, and any QA issues. The inventory comparisons are based on the data in the fina l 37-State dataset and SDWIS-FED (4th Q, 2005). The final dataset is based on applying the 50 pe rcent criterion and other corrections that were made to ensure consistency.

This appendix looks specifically at the data quality and representativeness, primarily as compared to the data in SDWIS-FE D, but also with regard to internal consistency in the State data. Noting differences between the State data and SDWIS-FED does not imply that changes were necessarily made to the State data, but th e differences allowed EPA to gauge the coverage and completeness of the State data, their consistency with SDWIS-FED, and the internal consistency within the State data set. Where changes were made to the data, those changes are noted.

For example, the State-reported populations of the 10 largest systems in each State were compared to the data in SDWIS-FED. In some ca ses, that identified that several of the largest systems were not in the State-reported data, or that the numbers appeared to differ by small or larger amounts. Differences in quantities are note d, but were not explored or corrected” (without research, it was unclear which fi gure might be the more correct).

Data element fields that were unnecessary in the final dataset were removed. The number of original data element fields varied fo r each State; the final data sets contain the same 19 data element fields that are used in the final data sets of the States that used the Extraction Tool. The original data field names were repl aced with more “user-friendly” field names to decrease confusion and provide a more informa tive title for the data in the each field.

General QA Issues

PWSID Numbers – Not all of the States provided the PWSID Number in the standard Federal format of seven numeric digits preceded by the two letter State acronym/code (e.g., DC1234567). Leading zeros were added in place of the missing numeric di gits; all spot-checks proved that zeros were the missing numeric digit. The two-digit State acronym/code was added where needed.

Key Data Fields – Most data element field names were in a different order and had different names from the names used by the St ates that used the Extraction Tool. Some additional data codes were used in the key data element fields that were not seen/used in the States that used the Extraction Tool. Data elem ent fields were renamed and reordered to match the data output formats generated by the Extracti on Tool. Where States did not provide a data element field, data element fields were added for consistency. For example, if no analysis date field were provided, a new column “Date Sample Analyzed” was added.

System Type – To standardize codes with the other data sources, converted “P” to “NTNC”; Converted “N” to “NC.”TCR Compliance Monitoring Data Quality and Completion Report

B-2 June 2010 Source Water – To standardize codes with the other data sources, converted “GP” to “GWP”; Converted “SP” to “SWP.”

Analyte Code – Some State submissions included an An alyte Name, but left the Analyte Code blank. Filled blank analyt e code fields (COLIFORM (T CR)=3100; FECAL COLIFORM=3013; COLIFORM, E. COLI =3014) based on data in the Analyte Name field.

Analyte Name - Removed disinfectant residual data from data tables (but kept data for potential future use).TCR Compliance Monitoring Data Quality and Completion Report

B-3 June 2010 

### B.1 ARKANSAS

B.1.1 Description of Data

Crosswalk of Data Fields

Exhibit B.1 is a side-by-side comparison of the original data element fields submitted by Arkansas to the final data element fields used in the dataset. Arkans as included data element fields that were not needed in interpreting the data and that were not used in the final 37-State dataset. These data element fields were deleted fro m the final dataset and are not included in data element fields listed in Exhibit B.1. Six fields that are included in the final dataset were not included in the data that the State submitted. These missing data element fields were added for consistency with the data submitted using the SDWIS extraction tool.

Exhibit B.1 Arkansas Data Element Fields

Original Data Element Field Names Final Data Element Field Names NUMBER0 PWSID_Number NAME PWSID_Name D_PWS_FED_TYPE_CD System_Type D_POPULATION_COUNT Population_Served source water type Source_Water IDENTIFICATION_CD Sampling_Point_Number analysis type Sampling_Point_Relative_Location - Source_Water_Treatment - Facility_ID_Number - Sampling_Point_Text LAB_ASGND_ID_NUM Lab_Sample_Number COLLLECTION_END_DT Date_Sample_Taken - Date_Sample_Analyzed TYPE_CODE Sample_Type - Repeat_Sample_Relative_Location - Analytical_Method - Analyte_Name CODE Analyte_Code REPORTED_MSR Presence_Absence_Result

Summary of State Correspondence

A number of emails and phone calls were exchanged with the State to clarify the submitted data. Clarification was received about why there seemed to be a lack of data.TCR Compliance Monitoring Data Quality and Completion Report

B-4 June 2010 Arkansas initially sent in only results for repeat samples. Results for routine samples were sent separately and clarification was received on how to interpret the sample type to distinguish between routine and repeat samples. Clarifica tion was also received on primary water source for systems that purchase water (systems were cross-checked with SDWIS-FED).

The State contact said there is no way to link up the repeat samples with the original positive routine sample, because the lab sheets fo r the repeat samples don’t have a place to put the unique lab number or some other indicato r that could link up the two samples.

Narrative of Issues

The State did not use same convention as SD WIS extraction tool for a number of data element fields. There is not a way to link rep eat samples with the original positive routine sample. Some systems were listed as purchasi ng water purchase from both ground water sources and surface water sources, but their primary water source was unclear.

B.1.2 System Inventory Comparison

Exhibit B.2 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the data provided by Arkansas.

Exhibit B.2 Arkansas – Number of Syst ems by Population Served and System Type

Population Served System Type N umber of Systems Percent Difference SDWIS 2005 37-State Dataset Total 1,105 1,019 8% <101 TNCWS 315 259 18% <101 N TNCWS 15 14 7% <101 CWS 45 34 24% 101 to 500 TNCWS 27 30 -11% 101 to 500 N TNCWS 14 13 7% 101 to 500 CWS 176 165 6% 501 to 1000 TNCWS 4 3 25% 501 to 1000 N TNCWS 4 4 0% 501 to 1000 CWS 139 129 7% 1001 to 4100 TNCWS 0 0 1001 to 4100 N TNCWS 2 2 0% 1001 to 4100 CWS 246 246 0% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 N TNCWS 0 0 4101 to 33,000 CWS 109 114 -5%TCR Compliance Monitoring Data Quality and Completion Report

B-5 June 2010 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 N TNCWS 0 0 33,001 to 96,000 CWS 8 6 25% >96,000 TNCWS 0 0 >96,000 N TNCWS 0 0 >96,000 CWS 1 0 100%

B.1.3 Number of Samples

In the final dataset for Arkansas:

The total number of samples included is 46,059

. The total number of routine samples included is 43,674

. The total number of routine TC samples included is 43,244

. The total number of routine EC and FC samples included is 430

.

B.1.4 QA Issues

Consistency with SDWIS Extraction Tool

Where appropriate, data element fields were added or renamed (see Exhibit B.1). The records that did not have “Prese nce Absence Result” data were de leted. The records labeled as SP (special) in “Sample Type” data element field are not used for compliance and were deleted. The records with NP in “System Type” were dele ted. Data elements in “Source Water” were converted to be consistent with the data elements used by the SDWIS Extraction Tool (i.e., all changed to SW, GW, GWP, or SWP). Additionally, because some systems purchase both ground water and surface water, it was necessary to cross-check primary source water for systems that purchase water with the SDWIS inventory (SDWIS-FED, 4thQ, 2005) to confirm the final dataset had the correct data.

Interpretation of Codes

All the records submitted by the State initiall y were labeled as routine even though some were repeats. After clarification from the State, the data element field “Identification code” was used to identify a record as a repeat sample. If there was a letter in the “Identification Code” column, the record was actually a repeat sample (RT converted to RP in this instance). The letters [A=original, B=upstream, C=downstream, D= alternate] are indicativ e of where the repeat sample was drawn. If some other data elemen t besides the letters listed above was in the “Identification Code” field, th e record was a routine sample.TCR Compliance Monitoring Data Quality and Completion Report

B-6 June 2010 

### B.2 FLORIDA

B.2.1 Description of Data

Crosswalk of Data Fields

Exhibit B.3 is a side-by-side comparison of the original data element fields submitted by Florida to the final data element fields used in the dataset. Florida included additional data element fields that were not need ed in interpreting the data and that were not used in the final analyses. These data element fields were deleted from the final 37-State dataset and are not included in data element fiel ds listed in Exhibit B.3.

Exhibit B.3 Florida Data Element Fields

Original Data Element Field Names Final Data Element Field Names Comments
PWS ID PWSID_Number “FL” did not precede 7-digit number. Added “FL” in final data Mailing Name PWSID_Name PWS Type System_Type Population Served Population_Served - Source_Water - Sampling_Point_Number - Sampling_Point_Relative_Location - Source_Water_Treatment - Facility_ID_Number - Sampling_Point_Text Sample # Lab_Sample_Number Sample Date Date_Sample_Taken Date_Sample_Analyzed

Sample Type Sample_Type Codes State used weren’t common – needed to change them to be consistent with other State’s data. - Repeat_Sample_Relative_Location - Analytical_Method - Analyte_Name - Analyte_Code

- Presence_Absence_Result Code combinations were used to populate this field – see below. Result - Used in combination to populate Presence Absence data element field. F/E Ind -TCR Compliance Monitoring Data Quality and Completion Report

B-7 June 2010 Summary of State Correspondence

The Florida State personnel were very he lpful. Correspondence consisted of two phone calls and several emails with Florida personnel. Clarification was receiv ed on the interpretation of Florida’s “Result” field. Clarification an d direction was received on the “Sample Type” field (i.e., delete “R” records). Clar ification and direction was receiv ed on a number of codes that Florida used in their reporting that were not standard for States using the SDWIS Extraction Tool.

Narrative of Issues

The State acronym “FL” did not precede the PW SIDs that the State submitted with their dataset.

EC and FC negative samples are not disti nguishable from each other because the data provided did not include entries specifying which analytical method was used if the result was negative. A new analyte name was used (“EC or FC ”) for these records. For positive test results, the State of Florida reported the analytical method (EC or FC). System TC records were grouped by month when all TC samples were ne gative and were converted into individual records to match the format of the 37-State da taset. This method of grouped reporting carried a single date but did not mean that samples were all taken on one day.

B.2.2 System Inventory Comparison

Exhibit B.4 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the data provided by Florida.

Exhibit B.4 Florida – Number of System s by Population Served and System Type

Population Served System Type N umber of Systems Percent Difference SDWIS 2005 37-State Dataset Total 6,137 6,125 0.2% <101 TNCWS 2,690 2,695 0% <101 N TNCWS 634 612 3% <101 CWS 448 424 5% 101 to 500 TNCWS 518 498 4% 101 to 500 N TNCWS 239 252 -5% 101 to 500 CWS 607 621 -2% 501 to 1000 TNCWS 54 58 -7% 501 to 1000 N TNCWS 67 71 -6% 501 to 1000 CWS 181 187 -3% 1001 to 4100 TNCWS 8 9 -13% 1001 to 4100 N TNCWS 28 27 4%TCR Compliance Monitoring Data Quality and Completion Report

B-8 June 2010 1001 to 4100 CWS 301 301 0% 4101 to 33,000 TNCWS 2 2 0% 4101 to 33,000 N TNCWS 5 5 100% 4101 to 33,000 CWS 242 240 1% 33,001 to 96,000 TNCWS 0 0

33,001 to 96,000 N TNCWS 0 0

33,001 to 96,000 CWS 76 83 -9%

>96,000 TNCWS 0 0 >96,000 N TNCWS 0 0 >96,000 CWS 37 40 -8%

B.2.3 Number of Samples

In the final dataset for Florida:

The total number of samples included is 287,086

. The total number of routine samples included is 281,232

. The total number of routine TC samples included is 279,658

. The total number of routine EC and FC samples included is 1,574

.

B.2.4 QA Issues

Where appropriate, data element fields were renamed for the fina l dataset (see Exhibit B.3).

PWSIDs

The State acronym “FL” was added before the 7-digit PWSIDs that the State submitted.

Sample Type

In the “Sample Type” field records with “R ” are raw water intakes before chlorination, not DS compliance samples per Federal TCR. All “R” records were deleted from the “Sample Type” data element field

Results

In FL’s “Result” data element field ther e were records with “777”, “888”, or “999”. These records were used to represent samples th at could not be read or a laboratory error. All records with “777”, “888”, or “999” in the Florida dataset “Res ult” data element field were deleted.TCR Compliance Monitoring Data Quality and Completion Report

B-9 June 2010 System TC records that were grouped by month when all TC samples were negative were converted into individual records to ma tch the format of the 37-State dataset. The separation of grouped records into individual records required re plication of the record to account for the correct number of samples that woul d have been taken by a system of this size. For the State of Florida, this process increased the number of records by about 4.5 times from the number submitted.

EC and FC negative records were not disti nguishable from each other; a new analyte name was used (“EC or FC”) for these records.

Interpretation of Codes

PWS Type – C=CWS; N=Transient; P=Non Transient

Sample Type – All T and D samples are routine compliance samples for TCR purposes (T=single tap; D=distribution system) and were made “RT” samples; C=check, i.e., Repeat and were made “RP” samples. R=raw and were deleted.

Samples Required – Pulls from system file and rela tes to a full system month, not a record. Note that FL systems are required by the State to take at least two samples per month (no single samples; no quarterly or annual samples).

Sample Date – Dates generally apply to the mont h (most are on the first) and may not reflect the actual date sample was taken (groupe d reporting does not mean that samples were all taken on one day).

Result and F/E Ind fields – see Exhibit B.5 below outlining steps taken after consulting with the State.

Exhibit B.5 Florida Data Dictionary

“Result” field “F/E Ind” field “Samples Taken” field Interpretation

[blank] or “0” [zero] [blank] [any
p ositive number] All TC-

[blank] or “0” [zero] “0” [zero] or N 1 TC-. Either an EC or FC test was not performed (an “N”), or the result was negative (“0”), but it cannot be determined whether the sample was tested for EC or FC. In either case, this sample should not be used in EC or FC analysis.TCR Compliance Monitoring Data Quality and Completion Report

B-10 June 2010 “Result” field “F/E Ind” field “Samples Taken” field Interpretation

“777”, “888”, or “999” [any] 1 DELETE, sample results could not be used (e.g., lab error, other growth obscured colonies) [any nonzero value except “777”, “888”, or“999”] E 1 TC+, EC+

[any nonzero value except “777”, “888”, or “999”] F 1 TC+, FC+

[any nonzero value except “777”, “888”, or “999”] N 1 TC+, EC or FC Sample not taken, so should not be used in EC or FC analysis.



### B.3 ILLINOIS

B.3.1 Description of Data

The initial data that Illinois sent was incomplete. More complete data was received after correspondence with the State.

Crosswalk of Data Fields

Exhibit B.6 is a side-by-side comparison of the original data element fields submitted by Illinois to the final data element fields used in the dataset. Illinois included additional data element fields that were not need ed in interpreting the data and th at were not used in the final 37- State dataset. These data element fields were de leted from the final dataset and are not included in data element fields listed in Exhibit B.6.TCR Compliance Monitoring Data Quality and Completion Report

B-11 June 2010 Exhibit B.6 Illinois Data Element Fields

Original Data Element Field Name s Final Data Element Field Names NUMBER0 PWSID_Number TINWYS_NAME PWSID_Name D_PWS_FED_TYPE_CD System_Type D_POPULATION Population_Served D_FED_PRIM_SRC_CD Source_Water - Sampling_Point_Number - Sampling_Point_Relative_Location - Source_Water_Treatment - Facility_ID_Number - Sampling_Point_Text LAB_ASSGND_ID_NUM Lab_Sample_Number COLLECTION_END_DT Date_Sample_Taken - Date_Sample_Analyzed dbo_TSASAMPL_TYPE_CODE Sample_Type - Repeat_Sample_Relative_Location - Analytical_Method dbo_TSAANLYT_NAME Analyte_Name CODE Analyte_Code PRESENCE_IND_CODE Presence_Absence_Result

Summary of State Correspondence

Initial dataset submitted by the State was incomplete and unusable. After corresponding with the State, a more complete and usable dataset was submitted.

Narrative of Issues

After Illinois resubmitted the data set, there were no significant issues.

B.3.2 System Inventory Comparison

Exhibit B.7 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the data provided by Illinois.TCR Compliance Monitoring Data Quality and Completion Report

B-12 June 2010 Exhibit B.7 Illinois – Number of System s by Population Served and System Type

Population Served System Type N umber of Systems Percent Difference SDWIS 2005 37-State Dataset Total 5,882 4,413 25% <101 TNCWS 2,845 1,769 38% <101 N TNCWS 139 70 50% <101 CWS 171 164 4% 101 to 500 TNCWS 750 537 28% 101 to 500 N TNCWS 205 146 29% 101 to 500 CWS 482 480 0% 501 to 1000 TNCWS 85 64 25% 501 to 1000 N TNCWS 48 38 21% 501 to 1000 CWS 306 305 0% 1001 to 4100 TNCWS 9 4 56% 1001 to 4100 N TNCWS 23 22 4% 1001 to 4100 CWS 436 436 0% 4101 to 33,000 TNCWS 1 0 100% 4101 to 33,000 N TNCWS 0 0 4101 to 33,000 CWS 322 322 0% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 N TNCWS 0 0 33,001 to 96,000 CWS 50 48 4% >96,000 TNCWS 0 0 >96,000 N TNCWS 0 0 >96,000 CWS 10 8 20%

B.3.3 Number of Samples

In the final dataset for Illinois:

The total number of samples included is 137,697

. The total number of routine samples included is 134,569

. The total number of routine TC samples included is 133,516

. The total number of routine EC and FC samples included is 1,053

.

B.3.4 QA Issues

Where appropriate, data element fields were renamed for the fina l dataset (see Exhibit B.6). The records with data element “NP” in the “System Type” field were deleted.TCR Compliance Monitoring Data Quality and Completion Report

B-13 June 2010 

### B.4 MICHIGAN

B.4.1 Description of Data

The system types (NTNCWS vs. TNCWS) were unclear in the initial submission from Michigan. After correspondence with the State, data on the system type designation was clarified so that sample records could be assigned to the correct system type and included in the final 37-State dataset.

Crosswalk of Data Fields

Exhibit B.8 is a side-by-side comparison of the original data element fields submitted by Michigan to the final data element fields used in the dataset. Michigan included additional data element fields that were not need ed in interpreting the data and that were not used in the final analyses. These data element fields were delete d from the final dataset and are not included in data element fields listed in Exhibit B.8.

Exhibit B.8 Michigan Data Element Fields

Original Data Element Field Names Final Data Element Field Names Comments
CWS_ID PWSID Number Added leading zeroes and State prefix.

- PWSID Name None provided. Remains unpopulated. - System Type Used State inventory to interpret system type.

- Population Served Retrieved population data from SDWIS inventory (4thQ, 2005)

- Source Water Retrieved source water data from SDWIS inventory (4thQ, 2005) Sample_id_num Sampling Point Number Sample_type Sampling Point Relative Location Mislabeled field
- Source Water Treatment - Facility ID Number - Sampling Point Text - Lab Sample Number Sample_Date Date Sample Taken - Date Sample Analyzed

Sample_purpose Sample Type Used data dictionary and interpretation clarifications to populate this data element field. - Repeat Sample Relative Location - Analytical MethodTCR Compliance Monitoring Data Quality and Completion Report

B-14 June 2010 Original Data Element Field Names Final Data Element Field Names Comments

- Analyte Name Used State clarification of missing sample results to interpret and populate field.

- Analyte Code Used State clarification of missing sample results to interpret and populate field. See Interpretation of Codes section.

- Presence Absence Result Code combinations were used to populate this field – see below. Result_value - Used in combination to populate Presence Absence data element field. Result_sign -

Summary of State Correspondence

State clarified their data element field labe led “CWS_ID.” Michigan submitted data from community systems and non-community systems sepa rately. State gave direction to delete records from systems where the PWSID read “erro r.” State clarified that the Federal PWSID could be generated if leading zeroes and the State prefix (MI) was added to the data elements in CWS_ID.

Clarification was received on apparently missing EC/FC records. Records are not missing; it is rather a function of how the lab reports the results. Rather than a separate record for EC positive, labs code total coliform results “EC POS” if it was found positive for E. coli

. State’s direction was to assume a sample was pos itive for both TC and EC when a record reads “EC POS.”

Clarification was received on missing large system data. St ate responded that only those water supplies that use the State lab and a handful of other labs are housed in the State database. Data processed at any of the othe r certified labs are tracked manua lly and are not available in the database. For the systems that were brought to the State’s attention, it is lik ely that they use their own labs or another certified la b and are therefore unavailable.

Clarification was received on Sample T ype coding and on how the State reported Presence Absence Results.

Narrative of Issues

Michigan submitted incomplete data initially. Michigan then submitted useable data in multi-file formats. State did not provide any PWS Names; this data element field was left blank for the final dataset. PWSIDs were in the incorrect format, and some data elements in PWSID field read “error.” The original submission fr om Michigan had no syst em types listed; the PWSID field was labeled CWS_ID in MI’s origin al submission. This field actually included both CWS and NCWS data. Michigan did not provi de data for “Population Served.” This data element field was populated using retail populat ion data from the SDWIS 2005 inventory. Unfamiliar codes/terms/code combinations were used in a number of data element fields thatTCR Compliance Monitoring Data Quality and Completion Report

B-15 June 2010 required clarification from the Stat e. No “Analytical Method” data elements were provided. No Analyte Name or Code data elements were provided.

B.4.2 System Inventory Comparison

Exhibit B.9 is a side-by-side comparison of the number of systems in the SDWIS inventory and the number of systems in the data provided by Michigan.

Exhibit B.9 Michigan – Number of Sy stems by Population Served and System Type

Population Served System Type N umber of Systems Percent Difference SDWIS 2005 37-State Dataset Total 11,743 8,966 24% <101 TNCWS 6,542 5,262 20% <101 N TNCWS 817 681 17% <101 CWS 343 165 52% 101 to 500 TNCWS 1990 1631 18% 101 to 500 N TNCWS 584 505 14% 101 to 500 CWS 404 207 49% 501 to 1000 TNCWS 148 138 7% 501 to 1000 N TNCWS 134 117 13% 501 to 1000 CWS 156 63 60% 1001 to 4100 TNCWS 49 37 24% 1001 to 4100 N TNCWS 23 24 -4% 1001 to 4100 CWS 298 106 64% 4101 to 33,000 TNCWS 8 0 100% 4101 to 33,000 N TNCWS 3 2 33% 4101 to 33,000 CWS 197 28 86% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 N TNCWS 0 0 33,001 to 96,000 CWS 37 0 100% >96,000 TNCWS 0 0 >96,000 N TNCWS 0 0 >96,000 CWS 10 0 100%TCR Compliance Monitoring Data Quality and Completion Report

B-16 June 2010 B.4.3 Number of Samples

In the final dataset for Michigan:

The total number of samples included is 39,610

. The total number of routine samples included is 35,915

. The total number of routine TC samples included is 34,867

. The total number of routine EC and FC samples included is 1,048

.

B.4.4 QA Issues

Where appropriate, data element fields were renamed for the fina l dataset (see Exhibit B.8).

PWSIDs

Leading zeroes and State acronym (MI) were added to generate Federal PWSID format. All records from systems where the PWSID = “error” were deleted.

System Type

The SDWIS 2005, 4thQ inventory for Michig an was used to populate the “System Type” data element field for each system since no Sy stem Type was provided with the original submission.

Interpretation of Codes

The “Presence Absence Result” data elem ent field was interpreted using the code combinations suggested by the State and are described in Exhibit B.10 below.

Exhibit B.10 Michigan Results Data Dictionary

Result-sign field Result-value field Interpretation
< blank TC-
= 0 TC- = [any nonzero value]TC+
N D TC-
POS TC+
EC POS TC+ & EC+

The State provided direction to interpret and correct the “Sample Type” data element field as shown in Exhibit B.11 below.TCR Compliance Monitoring Data Quality and Completion Report

B-17 June 2010 Exhibit B.11 Michigan Sample Type Data Dictionary

Entry in “Sample-purpose” Field General Interpretation Other Possible Exclusion/Inclusions Blank
Include as Routine Samples Records that have the following entries in the Sample Type Field are excluded: Raw Water Tap Pressure Tank/Plant Tap Untreated Private Well Treated/Softened Private Well “Compliance Monitoring

– SDWA” “Routine Monitoring”

“Repeat Sample” Include as Repeat Samples

“Real Estate Transaction”

Exclude “Repair/Construction/ N ew Well” “Water Quality Problem”TCR Compliance Monitoring Data Quality and Completion Report

B-18 June 2010 

### B.5 MINNESOTA

B.5.1 Description of Data

Crosswalk of Data Fields

Exhibit B.12 is a side-by-side comparison of the original data element fields submitted by Minnesota to the final data element fields used in the dataset. Minnesota included additional data element fields that were not needed in inte rpreting the data and that were not used in the final analyses. These data element fields were de leted from the final dataset and are not included in data element fields listed in Exhibit B.12.

Exhibit B.12 Minnesota Data Element Fields

Original Data Element Field Name s Final Data Element Field Names
PWSID PWSID_Number SysName PWSID_Name SysType System_Type Popserv Population_Served SrcType Source_Water SampID Sampling_Point_Number

- Sampling_Point_Relative_Location SampType Source_Water_Treatment SPID Facility_ID_Number

- Sampling_Point_Text

- Lab_Sample_Number Date Date_Sample_Taken

- Date_Sample_Analyzed SampPurp Sample_Type SampType Repeat_Sample_Relative_Location

- Analytical_Method

- Analyte_Name ChemID Analyte_Code Sign Presence_Absence_Result Value PWSID_Number

Summary of State Correspondence

The State provided clarificati on and an explanation for why th ere seemed to be a lack of Routine samples reported. In Minnesota, comm unity systems on a monthly monitoring schedule report to the State their TC negative samples in su mmary format, that is, only the total number ofTCR Compliance Monitoring Data Quality and Completion Report

B-19 June 2010 TC negative samples are reported, not individual TC negative samples. The State also provided a Total Routine Absent Count for all systems on monthly monitoring schedules (363,706) when requested, but this count was not compatible with the 37-State dataset format and therefore was not used.

EPA requested clarification for the “Confir mation” label that Minnesota used under the “Sample Type” data element field. Nearly all “C onfirmation” samples are investigative samples. Only six Sample IDs were non-investigative “Con firmation” samples and were repeat samples. Although confirmation and clarifi cation was received on a number of codes used by Minnesota, no clarification was need ed for the 2005 data.

Narrative of Issues

There were no coding issues in the 2005 data.

B.5.2 System Inventory Comparison

Exhibit B.13 is a side-by-side comparis on of the number of systems in the SDWIS inventory and the number of systems in the data provided by Minnesota.

Exhibit B.13 Minnesota – Number of Sy stems by Population Served and System Type

Population Served System Type N umber of Systems Percent Difference SDWIS 2005 37-State Dataset Total 7,460 6,903 7% <101 TNCWS 4,850 4,552 6% <101 N TNCWS 364 352 3% <101 CWS 177 173 2% 101 to 500 TNCWS 976 966 1% 101 to 500 N TNCWS 154 158 -3% 101 to 500 CWS 308 305 1% 501 to 1000 TNCWS 85 83 2% 501 to 1000 N TNCWS 25 21 16% 501 to 1000 CWS 153 154 -1% 1001 to 4100 TNCWS 32 33 -3% 1001 to 4100 N TNCWS 10 9 10% 1001 to 4100 CWS 187 78 58% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 N TNCWS 1 1 0% 4101 to 33,000 CWS 114 18 84% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 N TNCWS 0 0TCR Compliance Monitoring Data Quality and Completion Report

B-20 June 2010 33,001 to 96,000 CWS 22 0 100% >96,000 TNCWS 0 0 >96,000 N TNCWS 0 0 >96,000 CWS 2 0 100%

B.5.3 Number of Samples

In the final dataset for Minnesota:

The total number of samples included is 22,175

. The total number of routine samples included is 14,382

. The total number of routine TC samples included is 13,754

. The total number of routine EC and FC samples included is 628

.

B.5.4 QA Issues

Consistency with SDWIS Extraction Tool

Where appropriate, data element fields were renamed to match the 37-State dataset (see Exhibit B.13). To match the codes used in th e 37-State dataset, Mi nnesota’s “System Type” codes were changed as follows: “P” was converted to “NTNC” and “N” was converted to “NC.” The records labeled as “NP” in system type were deleted. Data elements in “Source Water” field also needed to be converted to match the SDWI S Extraction Tool format. The State’s “GP” was converted to “GWP” and “SP” to “SWP.” The State did not provide da ta in “Analyte Code” field and so used the “Analyte Name” field to populate the “Analyte Code” data element field (i.e., COLIFORM (TCR)=3100, FECA L COLIFORM=3013, COLIFORM, E. COLI =3014).

Interpretation of Codes

No interpretation of codes was needed for the 2005 data.TCR Compliance Monitoring Data Quality and Completion Report

B-21 June 2010 

### B.6 MISSOURI

B.6.1 Description of Data

Crosswalk of Data Fields

Exhibit B.14 is a side-by-side comparison of the original data element fields submitted by Missouri to the final data element fields us ed in the 37-State dataset. Missouri included additional data element fields that were not needed in interpreting the data and that were not used in the final analyses. These data element fields were deleted from the 37-State dataset and are not included in data element fiel ds listed in Exhibit B.15.

Exhibit B.14 Missouri Data Element Fields

Original Data Element Field Name s Final Data Element Field Names DNR Public Water Supply ID PWSID_Number System Name PWSID_Name System Type System_Type Population Population_Served

- Source_Water Sample ID Sampling_Point_Number Sample Source Sampling_Point_Relative_Location

- Source_Water_Treatment

- Facility_ID_Number

- Sampling_Point_Text

- Lab_Sample_Number Sample Date Date_Sample_Taken

- Date_Sample_Analyzed

- Sample_Type

- Repeat_Sample_Relative_Location Analysis Method Analytical_Method Contaminant Analyte_Name CONTAM_CODE Analyte_Code Presence_IND_CODE Presence_Absence_Result

Summary of State Correspondence

There was no correspondence with State personnel.TCR Compliance Monitoring Data Quality and Completion Report

B-22 June 2010 Narrative of Issues

The State provided very few data for “Date Sample Analyzed” field; a date difference comparison was completed for available data. No records had date differences of 10 days or more. Almost no fecal data was provided. The State did not provide “Analytical Method” data.

B.6.2 System Inventory Comparison

Exhibit B.15 is a side-by-side comparis on of the number of systems in the SDWIS inventory and the number of systems in the data provided by Missouri.

Exhibit B.15 Missouri – Number of Syst ems by Population Served and System Type

Population Served System Type N umber of Systems Percent Difference SDWIS 2005 37-State Dataset Total 2,736 ,2726 0.4% <101 TNCWS 751 759 -1% <101 N TNCWS 110 106 4% <101 CWS 412 413 0% 101 to 500 TNCWS 235 242 -3% 101 to 500 N TNCWS 94 94 0% 101 to 500 CWS 442 442 0% 501 to 1000 TNCWS 28 29 -4% 501 to 1000 N TNCWS 26 25 4% 501 to 1000 CWS 182 179 2% 1001 to 4100 TNCWS 7 6 14% 1001 to 4100 N TNCWS 14 15 -7% 1001 to 4100 CWS 278 283 -2% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 N TNCWS 0 0 4101 to 33,000 CWS 141 128 9% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 N TNCWS 0 0 33,001 to 96,000 CWS 11 5 55% >96,000 TNCWS 0 0 >96,000 N TNCWS 0 0 >96,000 CWS 5 0 100%TCR Compliance Monitoring Data Quality and Completion Report

B-23 June 2010 B.6.3 Number of Samples

In the final dataset for Missouri:

The total number of samples included is 69,982

. The total number of routine samples included is 65,404

. The total number of routine TC samples included is 63,999

. The total number of routine EC and FC samples included is 1,405

.

B.6.4 QA Issues

The records without data in “Presence Absence Result” field were deleted. The records with data element NP in “System Type” were deleted.TCR Compliance Monitoring Data Quality and Completion Report

B-24 June 2010 

### B.7 NEW HAMPSHIRE

B.7.1 Description of Data

Crosswalk of Data Fields

Exhibit B.16 is a side-by-side comparison of the original data element fields submitted by New Hampshire to the final data element fields used in the dataset. New Hampshire included additional data element fields that were not needed in interpreting the data and that were not used in the final analyses. These data element fields were deleted from the final dataset and are not included in data element fiel ds listed in Exhibit B.17.

Exhibit B.16 New Hampshire Data Element Fields

Original Data Element Field Names Final Data Element Field Names Comments

NHPWSID PWSID_Number Added leading zeroes and State acronym “NH” to submitted PWSIDs. PWSName PWSID_Name
CPN System_Type Changed codes used by State to codes used by the Extraction Tool.

- Population_Served State did not provide population data. Used SDIWS 2005 inventory to fill in this data element field.

- Source_Water State did not provide source water data. Used SDIWS 2005 inventory to fill in this data element field. SamplePointID Samp ling_Point_Number - Sampling_Point_Relative_Location - Source_Water_Treatment - Facility_ID_Number SampleDescription Sa mpling_Point_Text
SampleID Lab_Sample_Number State submitted data dictionary to clarify data element field. DateSampled Date_Sample_Taken - Date_Sample_Analyzed SampleType Sample_Type - Repeat_Sample_Relative_Location - Analytical_Method
- Analyte_Name Used Analyte Code that State provided to populate Analyte Name. Contaminant Analyte_Code

Results Presence_Absence_Result State submitted data dictionary to clarify data element field. SamplePointType - State submitted data dictionary to clarify data element field.TCR Compliance Monitoring Data Quality and Completion Report

B-25 June 2010 Summary of State Correspondence

Clarification about the codes in New Hamp shire’s “SamplePointType” and “Lab Sample Number” fields was received.

Narrative of Issues

State submitted data in two tables. The or iginal table labeled “TCR” has routine sample data, and the original table labeled “TCR1” has re peat sample data. The PWSID that the State used in submittal was not in Federal PWSID format . The State only provided an Analyte Code. Some of the codes that the State used were not common.

B.7.2 System Inventory Comparison

Exhibit B.17 is a side-by-side comparis on of the number of systems in the SDWIS inventory and the number of systems in the data provided by New Hampshire.

Exhibit B.17 New Hampshire – Number of Systems by Population Served and System Type

Population Served System Type N umber of Systems Percent Difference SDWIS 2005 37-State Dataset Total 2,377 2,262 5% <101 TNCWS 691 626 9% <101 N TNCWS 272 267 2% <101 CWS 324 328 -1% 101 to 500 TNCWS 457 423 7% 101 to 500 N TNCWS 127 129 -2% 101 to 500 CWS 255 249 2% 501 to 1000 TNCWS 53 46 13% 501 to 1000 N TNCWS 42 42 0% 501 to 1000 CWS 42 36 14% 1001 to 4100 TNCWS 17 18 -6% 1001 to 4100 N TNCWS 7 7 0% 1001 to 4100 CWS 59 61 -3% 4101 to 33,000 TNCWS 1 1 0% 4101 to 33,000 N TNCWS 1 1 0% 4101 to 33,000 CWS 25 25 0% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 N TNCWS 0 0 33,001 to 96,000 CWS 2 2 0% >96,000 TNCWS 1 0 100%TCR Compliance Monitoring Data Quality and Completion Report

B-26 June 2010 >96,000 N TNCWS 0 0 >96,000 CWS 1 1 0%

B.7.3 Number of Samples

In the final dataset for New Hampshire:

The total number of samples included is 24,136

. The total number of routine samples included is 21,881

. The total number of routine TC samples included is 21,254

. The total number of routine EC and FC samples included is 627

.

B.7.4 QA Issues

Data Format

The two original data tables that were submitted by New Hampshire were combined in order incorporate data in the final 37-State Dataset. Where appropriate, data element fields were renamed for the final dataset (see Exhibit B.16).

PWSIDs

Leading zeros (when necessary) and the St ate acronym “NH” were added to PWSIDs submitted by New Hampshire in order to generate the PWSID in the format used in SDWIS/Fed (e.g., DC1234567).

Analyte Name

“Analyte Code” data elements were used to populate the “Analyte Name” data element field (i.e., 3100=TCR; 3013=Fecal; 3014= E. coli ).

Interpretations of Codes

State provided clarification on some codes used to indicate sample point type. The codes “H” and “N” are distribution system samples. Th ese codes were changed to “DS” in order to match the SDWIS Extraction Tool codes.

The State submitted data dictionary to assist in code interpretation. Exhibit B.18 details the code interpretations for the “System Typ e” field, and Exhibit B.19 details the code interpretations for the “Presence Absence Result” field.TCR Compliance Monitoring Data Quality and Completion Report

B-27 June 2010 Exhibit B.18 New Hampshire Syst em Type Data Dictionary

N H Code Code Description Final Data Element Code C COMMUNITY SYSTEM C

N N ON-COMMUNITY TRANSIENT N C

P N ON-TRANSIENT NONCOMMUNITY N TNC

Exhibit B.19 New Hampshire Presence Absence Result Data Dictionary

N H Code Code Description Final Data Element Code *** Absent A OKK Sample Waived Deleted PEC Present E. coli P PFC Present Fecal P PNC Present Non-Coli. P PTC Present Total Coli. PTCR Compliance Monitoring Data Quality and Completion Report

B-28 June 2010 

### B.8 OHIO

B.8.1 Description of Data

Crosswalk of Data Fields

Exhibit B.20 is a side-by-side comparison of the original data element fields submitted by Ohio to the final data element fields used in the dataset. Ohio included additional data element fields that were not needed in interpreting the da ta and that were not used in the final analyses. These data element fields were deleted from the final dataset and are not included in data element fields listed in Exhibit B.20.

Exhibit B.20 Ohio Data Element Fields

Original Data Element Field Names Final Data Element Field Names Comments

PWS_ID PWSID_Number SYSNAME PWSID_Name
SYSTYPE System_Type Changed codes to be consistent with other State’s data. - Population_Served OH didn’t include population data. Used 2005 SDWIS to add data.
SOURCETYPE Source_Water Changed codes to be consistent with other State’s data. SNUMBER Sampling_Point_Number - Sampling_Point_Relative_Location - Source_Water_Treatment - Facility_ID_Number LOCATION Sampling_Point_Text - Lab_Sample_Number COLLDATE Date_Sample_Taken RECDATE Date_Sample_Analyzed

SAMTYPE Sample_Type Also used in combination with RESULT to populate Presence Absence Result field - Repeat_Sample_Relative_Location - Analytical_Method
- Analyte_Name Used data dictionary to interpret Analyte Name.

- Analyte_Code Used data dictionary to interpret Analyte Code.

- Presence_Absence_Result Used a combination of codes from SAMTYPE and RESULT fields to interpret P/A result.

RESULT - Used in combination with SAMTYPE to populate Presence Absence Result fieldTCR Compliance Monitoring Data Quality and Completion Report

B-29 June 2010 Summary of State Correspondence

Clarification was received on a number of c odes the State used in their “RESULT” and “SAMPTYPE” field. Cadmus contacted the State with a list of unknown codes and code combinations and possible interpretations for so me. The State responded with an updated list that defined or provided directi on for each code/code combination.

Narrative of Issues

Ohio used a number of unknown/uncommon codes and code combinations. The State was contacted to clarify and get a data dictionary in order to better understand the data Ohio had submitted. The data clean-up process was very i nvolved after State clarification. Ohio used different codes in “System Type” and “Source Water” data element fields. Ohio did not include data for Population Served. This data el ement field was populated using the SDWIS 2005 inventory. Ohio only entered one single result fo r every sample, e.g., although EC assays were always triggered by a TC+ result, only the EC assay result was recorded. Additional records were added to match the format of the 37-State dataset.

B.8.2 System Inventory Comparison

Exhibit B.21 is a side-by-side comparis on of the number of systems in the SDWIS inventory and the number of systems in the data provided by Ohio.

Exhibit B.21 Ohio – Number of Systems by Population Served and System Type

Population Served System Type N umber of Systems Percent Difference SDWIS 2005 37-State Dataset Total 5,420 5,031 7% <101 TNCWS 1,901 1,761 7% <101 N TNCWS 476 471 1% <101 CWS 233 227 3% 101 to 500 TNCWS 1,140 ,1081 5% 101 to 500 N TNCWS 439 345 21% 101 to 500 CWS 349 329 6% 501 to 1000 TNCWS 104 104 0% 501 to 1000 N TNCWS 23 92 -300% 501 to 1000 CWS 169 156 8% 1001 to 4100 TNCWS 23 21 9% 1001 to 4100 N TNCWS 1 22 -2100% 1001 to 4100 CWS 284 243 14% 4101 to 33,000 TNCWS 1 1 0% 4101 to 33,000 N TNCWS 0 1 -100%TCR Compliance Monitoring Data Quality and Completion Report

B-30 June 2010 Population Served System Type N umber of Systems Percent Difference SDWIS 2005 37-State Dataset 4101 to 33,000 CWS 235 155 34% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 N TNCWS 0 0 33,001 to 96,000 CWS 32 16 50% >96,000 TNCWS 0 0 >96,000 N TNCWS 0 0 >96,000 CWS 10 6 40%

B.8.3 Number of Samples

In the final dataset for Ohio:

The total number of samples included is 88,923

. The total number of routine samples included is 81,406

. The total number of routine TC samples included is 80,160

. The total number of routine EC and FC samples included is 1,246

.

B.8.4 QA Issues

Where appropriate, data element field name s were renamed for the final dataset (see Exhibit B.20). The data clean -up process was very involved af ter State clarification on unknown codes and code combinations. “System Type” data elements were changed to match the States that used the Extraction Tool. COMM=C; NT NC=no change; TNC=NC. “Source Water” data elements were changed to match rest of the dataset. GUI=GU; PGW=GWP; PSW=SWP; GW=no change; SW=no change. For RESULT: s= safe, u=unsafe, n=negative or TC-, e=EC+, i=invalid, etc.; For SAMTYPE: r=routine, c=check or repeat, s=special, i=invalid. Exhibit B.22 below details how each code and code combinati on was handled based on State clarification.

Exhibit B.22 Ohio Data Dictionary

RESULT SAMTYPE Interpretation/ Action Taken

u c

TC+ u r

u s u,cg c u,cg s u,hbc c u,hbc i u,hbc r u,hbc s u/50 sTCR Compliance Monitoring Data Quality and Completion Report

B-31 June 2010 RESULT SAMTYPE Interpretation/ Action Taken u/cg r u/cg s u/hbc c u/hbc r u/hbc s

a r

TCn c n i

n r

n s

s c s i

s r

s s s' r s # r s 3 s s 4 s

s c r s l r s 246 c s wel s s'#1 r s,bhc i s,cg i s,hbc i s/ec- c s/hbc c s/hbc r s/hbc s s` r

sa r sconf i shbc c shbc i shbc r shbcf i sphin s

ss c

ss r

ss s ssink r

su c swell rTCR Compliance Monitoring Data Quality and Completion Report

B-32 June 2010 RESULT SAMTYPE Interpretation/ Action Taken

e c

EC+ e i

e r

e s e,hbc r

e+ c

e+ r

e+ s u/e+ s u/ec+ c u/ec+ r u/ec+ s u/ec c

EC- u/ec- c u/ec- r u/ec- s u/f+ s

FC+ u/fc+ c u/fc+ r u/fc+ s u/fc- c FCu/fc- r u/fc- s (Blank) (Blank)

Delete (Blank) c (Blank) r (Blank) s i c

Delete - Invalid i r inval r invld c n/a c n/a r n/a s n/hbc c n/hbc r

na c na i

na r

na s na` r nahbc r n-cnf r hbc r Delete -TCR Compliance Monitoring Data Quality and Completion Report

B-33 June 2010 RESULT SAMTYPE Interpretation/ Action Taken i,hbc c Invalid/High Background Count i/hbc r i\hbc r

Ohio did not provide data for Population Serv ed. This data element field was populated using the SDWIS 4thQ 2005 inventory. Ohio onl y entered one single result for every sample, e.g., although EC assays were always triggere d by a TC+ result, only the EC assay result was recorded. Therefore, additional records were adde d to match the format of the 37-State dataset.TCR Compliance Monitoring Data Quality and Completion Report

B-34 June 2010 

### B.9 VIRGINIA

B.9.1 Description of Data

Crosswalk of Data Fields

Exhibit B.23 is a side-by-side comparison of the original data element fields submitted by Virginia to the final data element fields used in the dataset.

Exhibit B.23 Virginia Data Element Fields

Original Data Element Field Names Final Data Element Field Names Comments

PWSID PWSID_Number PWSName PWSID_Name

-- System_Type

-- Population_Served

-- Source_Water Sampling_Point_Number SampleID Sampling_Point_Relative_Location

-- Source_Water_Treatment

-- Facility_ID_Number Location Sampling_Point_Text LabID Lab_Sample_Number CollectDate Date_Sample_Taken

-- Date_Sample_Analyzed SDWISSampleType Sample_Type

-- Repeat_Sample_Relative_Location

-- Analytical_Method

-- Analyte_Name

-- Analyte_Code

-- Presence_Absence_Result

TCResult & FEColiResult -- Used to populate Presence A bsence, Analyte Name, and Analyte Code columns.

Summary of State Correspondence

Virginia personnel provided some clarification by telephone.TCR Compliance Monitoring Data Quality and Completion Report

B-35 June 2010 Narrative of Issues

Virginia did not include data for System Type, Population Served, and Source Water. These data element fields were populat ed using the SDWIS 2005 inventory.

B.9.2 System Inventory Comparison

Exhibit B.24 is a side-by-side comparis on of the number of systems in the SDWIS inventory and the number of systems in the data provided by Virginia.

Exhibit B.24 Virginia – Number of Sy stems by Population Served and System Type

Population Served System Type N umber of Systems Percent Difference 2005 SDWIS37-State Dataset Total 3,089 3,091 -0.06% <101 TNCWS 775 787 -2% <101 N TNCWS 233 235 -1% <101 CWS 433 441 -2% 101 to 500 TNCWS 398 397 0% 101 to 500 N TNCWS 245 253 -3% 101 to 500 CWS 402 410 -2% 501 to 1000 TNCWS 64 65 -2% 501 to 1000 N TNCWS 82 83 -1% 501 to 1000 CWS 113 116 -3% 1001 to 4100 TNCWS 9 9 0% 1001 to 4100 N TNCWS 35 35 0% 1001 to 4100 CWS 161 161 0% 4101 to 33,000 TNCWS 2 1 50% 4101 to 33,000 N TNCWS 3 2 33% 4101 to 33,000 CWS 93 79 15% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 N TNCWS 1 0 100% 33,001 to 96,000 CWS 25 11 56% >96,000 TNCWS 0 0 >96,000 N TNCWS 0 0 >96,000 CWS 15 6 60%TCR Compliance Monitoring Data Quality and Completion Report

B-36 June 2010 B.9.3 Number of Samples

In the final dataset for Virginia:

The total number of samples included is 63,447

. The total number of routine samples included is 59,874

. The total number of routine TC samples included is 58,786

. The total number of routine EC and FC samples included is 1,088

.

B.9.4 QA Issues

Interpretation of Codes

The data clean-up process was very involve d after clarification of unknown codes and code combinations. Data elements were change d according the data dictionary definitions seen in Exhibits B.25. This information was used to populate the Presence Absence field. Any data element that did not fit the descripti ons below was deleted from the dataset.

Exhibit B.25 Virginia Data Dictionary

TC & FC Result Interpretation

“<” TC-, FC- “>” TC+, FC+ Any non-zero value TC+ , FC+ “A”, “Absent”, “NA”, “ND” TC- , FC-

“P” TC+, FC+ Note: VA did only FC assays on TC+ results.TCR Compliance Monitoring Data Quality and Completion Report

B-37 June 2010 

### B.10 WISCONSIN

B.10.1 Description of Data

Crosswalk of Data Fields

Exhibit B.26 is a side-by-side comparison of the original data element fields submitted by Wisconsin to the final data element fields used in the dataset. Wisconsin included additional data element fields that were not needed in inte rpreting the data and that were not used in the final analyses. These data element fields were de leted from the final dataset and are not included in data element fields listed in Exhibit B.26.

Exhibit B.26 Wisconsin Data Element Fields

Original Data Element Field Names Final Data Element Field Names Comments

DNR Public Water Supply ID PWSID_Number Federal PWSID equals ‘WI’ prefix plus the first 7-digits of the WI 8- digit ID. System Name PWSID_Name

System Type System_Type Codes State used weren’t common – needed to change them to be consistent with other States’ data. Population Population_Served
- Source_Water Used SDWIS 2005 inventory to populate this field. Sample ID Samplin g_Point_Number Sample Source Sampling_Point_Relative_Location - Source_Water_Treatment - Facility_ID_Number - Sampling_Point_Text - Lab_Sample_Number Sample Date Date_Sample_Taken - Date_Sample_Analyzed

Sample type Sample_Type Codes State used weren’t common – needed to change them to be consistent with other States’ data. - Repeat_Sample_Relative_Location Analysis Method Analytical_Method

Contaminant Analyte_Name Also used in combination with Measured Amount and Units to populate Presence Absence Result field CONTAM_CODE Analyte_CodeTCR Compliance Monitoring Data Quality and Completion Report

B-38 June 2010

Original Data Element Field Names Final Data Element Field Names Comments

- Presence_Absence_Result Used a combination of codes to interpret P/A result. Measured Amount - Used in combination with Contaminant to populate Presence Absence Result field. Units -

Summary of State Correspondence

Clarification was received on how to interpret the PWSIDs that the State submitted.

Confirmation and clarification was received on a number of codes used by Wisconsin. Cadmus contacted the State with a list of unknown codes and code combinations and possible interpretations for some. The State responded wi th an updated list that defined or provided direction for each code/code combination. Clar ification was received on how to interpret code combinations in order to populate P/A Result data element field.

Narrative of Issues

Wisconsin did not use Federal PWSID for system identification. A number of unfamiliar codes/code combinations were used. Source Wa ter was not included in the data set submitted by the State. The State was contacted to clarify and get a data dictionary in order to better understand the data Wisconsin had submitted. The data clean-up process was very involved after State clarification.

B.10.2 System Inventory Comparison

Exhibit B.27 is a side-by-side comparis on of the number of systems in the SDWIS inventory and the number of systems in the data provided by Wisconsin.

Exhibit B.27 Wisconsin – Number of Sy stems by Population Served and System Type

Population Served System Type N umber of Systems Percent ifference 2005 SDWIS37-State Dataset Total 11,410 1,1132 2% <101 TNCWS 7,819 7,589 3% <101 N TNCWS 488 434 11% <101 CWS 333 331 1% 101 to 500 TNCWS 1,453 1,445 1% 101 to 500 N TNCWS 366 373 -2% 101 to 500 CWS 261 259 1% 501 to 1000 TNCWS 83 90 -8%TCR Compliance Monitoring Data Quality and Completion Report

B-39 June 2010 Population Served System Type N umber of Systems Percent ifference 2005 SDWIS37-State Dataset 501 to 1000 N TNCWS 67 70 -4% 501 to 1000 CWS 130 128 2% 1001 to 4100 TNCWS 27 26 4% 1001 to 4100 N TNCWS 19 21 -11% 1001 to 4100 CWS 217 218 0% 4101 to 33,000 TNCWS 1 1 0% 4101 to 33,000 N TNCWS 1 1 0% 4101 to 33,000 CWS 126 129 -2% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 N TNCWS 0 0 33,001 to 96,000 CWS 15 13 13% >96,000 TNCWS 0 0 >96,000 N TNCWS 0 0 >96,000 CWS 4 4 0%

B.10.3 Number of Samples

In the final dataset for Wisconsin:

The total number of samples included is 145,855

. The total number of routine samples included is 140,769

. The total number of routine TC samples included is 139,606

. The total number of routine EC and FC samples included is 1,163

.

B.10.4 QA Issues

PWSIDs

The PWSIDs that Wisconsin submitted were changed in order to reflect Federal PWSID format. The Federal PWSID equals a ‘WI’ prefix plus the first 7-digits of the State’s 8-digit system ID.

Source Water

Source water data was not included in the data set submitted by the State. The source water data in the SDWIS 2005 inventory was used to populate this field for the final 37-State data set.TCR Compliance Monitoring Data Quality and Completion Report

B-40 June 2010 Consistency with SDWIS Extraction Tool

Where appropriate, data element fields were renamed for the fina l dataset (see Exhibit B.26). Data elements in the “System Type” data element field were converted to be consistentwith SDWIS Extraction Tool data elements (P=NTNC; N=NC). Records with “NP” (nonpublic) in the “System Type” field were deleted. The State did not include data in “Analyte Code” field. Used the “Analyte Name” field to populate the “Analyte Code” data element field (i.e., COLIFORM (TCR)=3100, FECA L COLIFORM=3013, COLIFORM, E. COLI =3014).

Interpretation of Codes

The data clean-up process was very involve d after State clarification of unknown codes and code combinations. Data elements were changed according to the data dictionary definitions seen in Exhibits B.28 – B.31.

Exhibit B.28 Wisconsin System Type Data Dictionary

Code Interpretation System Type Used MC Municipal community CWS C N on-transient, non-community N TNCWS

OC Other than municipal, community CWS
TN Transient, non-community TNCWS

Exhibit B.29 Wisconsin Status of the Water Supply System Data Dictionary

Code Interpretation Action Taken A Active Included C Combined D Destroyed

Deleted I Inactive M On Municipal water N N ew, not yet serving public R Private, serves fewer than 25 T Temporarily closed X DuplicateTCR Compliance Monitoring Data Quality and Completion Report

B-41 June 2010 Exhibit B.30 Wisconsin Status of the Sampled Well Data Dictionary

Code Interpretation Action Taken A Active Included 1 Temporarily abandoned

Deleted

Deleted 2 Permanently abandoned 3 Temporarily out of service I Inactive N N ew, not yet serving public R Reconstructed

Exhibit B.31 Wisconsin Sample Type Data Dictionary

Code Interpretation Sample Type Used C Check Sample Repeat Sample D Compliance sample Routine Sample R Repeat (Baceriological only) Repeat Sample G Grab Sample

Excluded I Investigation N N ew, (bacteriological only) O Other

P Plant finished (bacteriological only) U ? W Raw water

The “Presence Absence Result” data element field was populat ed using the data elements and interpretations seen in Exhibit B.32 below.

Exhibit B.32 Wisconsin Presence Absence Result Data Dictionary

Measured Amount Units Contaminant Final Interpretation0 /100 ML Coliform (TCR) TCAny positive number /100 ML Coliform (TCR) TC+

0 /100 ML ENTEROCOCCI /100 ML FC0 /100 ML FECAL coliform FCAny value N EGATIVE Coliform (TCR) TCAny value N EGATIVE FECAL coliform FC-TCR Compliance Monitoring Data Quality and Completion Report

B-42 June 2010 Measured Amount Units Contaminant Final Interpretation Any value POSITIVE Coliform (TCR) TC+ Any value POSITIVE FECAL coliform FC+ Note: EC apparently was not used by WI in 2005 as an analyte; EPA therefore interpreted the enterococci analyte as an FC test, and based on having 0 measured amount, it was considered negative.



### B.11 REGION 9 TRIBAL

B.11.1 Description of Data

Crosswalk of Data Fields

Exhibit B.33 is a side-by-side comparison of the original data element fields submitted by Region 9 Tribal to the final data element fields used in the dataset. Region 9 Tribal included additional data element fields that were not needed in interpreting the data and that were not used in the final analyses. These data element fields were deleted from the final dataset and are not included in data element fiel ds listed in Exhibit B.33.

Exhibit B.33 Region 9 Tribal Data Element Fields

Original Data Element Field Names Final Data Element Field Names Comments Public Water System Identification Number (PWSID) PWSID_Number
System Name PWSID_Name
Federal Public Water System Type Code System_Type Changed data elements to match codes used by the Extraction Tool Population Served Population_Served

Source Water Type Source_Water Changed data elements to match codes used by the Extraction Tool - Sampling_Point_Number - Sampling_Point_Relative_Location

Sample Analysis Type Code Source_Water_Treatment Changed data elements to match codes used by the Extraction Tool - Facility_ID_Number Sampling Point Identification Code Sampling_Point_Text
Sample Identification Number Lab_Sample_Number Sample Collection Date Date_Sample_Taken - Date_Sample_AnalyzedTCR Compliance Monitoring Data Quality and Completion Report

B-43 June 2010 Original Data Element Field Names Final Data Element Field Names Comments

Sample Purpose Sample_Type Changed data elements to match codes used by the Extraction Tool

Sample Analysis Type Code Repeat_Sample_Relative_Location Changed data elements to match codes used by the Extraction Tool Sample Analytical Method Number Analytical_Method

- Analyte_Name Used data from “Analyte Code” field to populate. Contaminant Analyte_Code

Sample Analytical Result - Sign Presence_Absence_Result Changed data elements to match codes used by the Extraction Tool

Summary of State Correspondence

No significant corresponde nce with State personnel.

Narrative of Issues

The State did not use the same codes as the SDWIS Extraction Tool for System Type, Source Water, Source Water Treatment, Repeat Sample Relative Location, Sample Type, or Presence Absence Result. Data was not provided fo r “Analyte Name.” The State used the same data element field to provide data for “Source Water Treatment” and “Repeat Sample Relative Location.”

B.11.2 System Inventory Comparison

Exhibit B.34 is a side-by-side comparis on of the number of systems in the SDWIS inventory and the number of systems in the data provided by Region 9 Tribal.

Exhibit B.34 Region 9 Tribal – Number of Systems by Population Served and System Type

Population Served System Type N umber of Systems Percent Difference 2005 SDWIS37-State Dataset Total 485 339 30% <101 TNCWS 24 26 -8% <101 N TNCWS 13 12 8% <101 CWS 93 67 28% 101 to 500 TNCWS 13 12 8% 101 to 500 N TNCWS 17 17 0%TCR Compliance Monitoring Data Quality and Completion Report

B-44 June 2010 Population Served System Type N umber of Systems Percent Difference 2005 SDWIS37-State Dataset 101 to 500 CWS 163 111 32% 501 to 1000 TNCWS 2 2 0% 501 to 1000 N TNCWS 4 3 25% 501 to 1000 CWS 59 20 66% 1001 to 4100 TNCWS 0 0 1001 to 4100 N TNCWS 9 12 -33% 1001 to 4100 CWS 58 36 38% 4101 to 33,000 TNCWS 0 0 4101 to 33,000 N TNCWS 8 6 25% 4101 to 33,000 CWS 22 15 32% 33,001 to 96,000 TNCWS 0 0 33,001 to 96,000 N TNCWS 0 0 33,001 to 96,000 CWS 0 0 >96,000 TNCWS 0 0 >96,000 N TNCWS 0 0 >96,000 CWS 0 0

B.11.3 Number of Samples

In the final dataset for Region 9 Tribal:

The total number of samples included is 9,767

. The total number of routine samples included is 8,764

. The total number of routine TC samples included is 8,450

. The total number of routine EC and FC samples included is 314

.

B.11.4 QA Issues

Consistency with SDWIS Extraction Tool

Where appropriate, data element fields were renamed for the fina l dataset (see Exhibit B.33). Re-coded data elements in “System Type” field to match Extraction Tool data elements: Community=C; Non-Transient Non-Comm unity (NTNC)=NTNC; Transient NonCommunity=NC. Re-coded data elements in “Sour ce Water” field to match Extraction Tool data elements: Ground water=GW; Surface water=SW. Re-coded data elements in “Sample Type” field to match Extraction Tool data elements: compliance/routine=RT; repeat=RP Re-coded data elements in “Source Water Treatment” field to ma tch Extraction Tool data elements: Finished (Treated) water sample=FN. Re-coded data elements in “Repeat Sample Point Relative Location” field to match Extraction Tool data elements: Original=OR; Other=OT; Downstream=DN; Unknown=UN; Upstream=UP. Re-coded data elements in “PresenceTCR Compliance Monitoring Data Quality and Completion Report

B-45 June 2010 Absence Result” field to match Extraction Tool data elements: Absence=A; Presence=P. The federally recognized codes from “Analyte Cod e” field were used to populate “Analyte Name” data element field. The State used the same data element field to provide data for “Source Water Treatment” and “Repeat Sample Relative Locati on.” The original data element field was separated into the correct final fields.

### Appendix C Summary of States Not Included in the Final Dataset: State-by-State BreakoutTCR Compliance Monitoring Data Quality and Completion Report

C-i June 2010 

### Contents

C.1 Submitted Data Not Used in the Final Dataset ................................................................ C-1 C.1.1 ALABAMA .............................................................................................................. C- 2 C.1.2 AMERICAN SAMOA ............................................................................................. C-4 C.1.3 DISTRICT OF COLUMBIA .................................................................................... C-6 C.1.4 MAINE ................................................................................................................... .. C-8 C.1.5 MARYLAND ......................................................................................................... C-10 C.1.6 NEW JERSEY ........................................................................................................ C-12 C.1.7 SOUTH CAROLINA ............................................................................................. C-14 C.1.8 SOUTH DAKOTA ................................................................................................. C-16 C.1.9 TENNESSEE .......................................................................................................... C-18 C.1.10 NAVAJO NATION ................................................................................................ C-20 C.2 States Not Su bmitting Data ............................................................................................ C-2 2TCR Compliance Monitoring Data Quality and Completion Report

C-1 June 2010 

### C.1 Submitted Data Not Us ed in the Final Dataset

For each State that submitted data that was not included in the final dataset, a description of the data, explanation of w hy the data were not used, and QA issues will be presented.

Because the data from these States was screen ed out from the final dataset that was used, no data cleaning was conducted for most data sets and the original data contain many systems that may have been screened out during the cleani ng process. Therefore, a comparable table was not created for these States. In each State’s re spective section below is the breakout of systems from the SDWIS 2005 inventory and a total number of systems count (when available) from the data that each State submitted.TCR Compliance Monitoring Data Quality and Completion Report

C-2 June 2010 C.1.1 ALABAMA

C.1.1.1 Description of Data

Alabama used the SDWIS Extraction Tool to co llect and report data. Fifty-eight percent of the submitted data is from 2005. The remain ing data is from 2004. Many PWSs were missing from the data pull, and only 170 unique water systems had data submitted by the State. The majority (69 percent) of records were TCR repeat sample records, and only 39 percent were TCR routine samples. From the submitted data, only 18 records were routine samples that were TC negative. The submitted data were extremely incomplete, especially for TC negative data. Twenty-six percent of the submitted data were TCR positive samples. The State did not provide “Date Sample Analyzed” data elements; date diffe rence comparison could not be conducted. No “Analytical Method” data elements were submitted.

C.1.1.2 System Inventory Comparison

Because the data from Alabama was screened out from the final dataset that was created, no data cleaning was conducted and the original data contains many systems that may have been screened out during the cleaning process. Therefor e, a comparable table was not created for this State. Exhibit C.1 below has the breakout of systems from the SDWIS 2005 inventory and a total number of systems count from the dataset submitted by the State.

Exhibit C.1 Alabama System Inventory

Population

Served System Type Number of Systems
2005 SDWIS State Dataset Total 642 170 <101 TNCWS 47 <101 NTNCWS 4 <101 CWS 6
101 to 500 TNCWS 17 101 to 500 NTNCWS 12 101 to 500 CWS 31
501 to 1000 TNCWS 1 501 to 1000 NTNCWS 8 501 to 1000 CWS 55
1001 to 4100 TNCWS 0 1001 to 4100 NTNCWS 6 1001 to 4100 CWS 222 4101 to 33,000 TNCWS 0 4101 to 33,000 NTNCWS 0 4101 to 33,000 CWS 207 33,001 to 96,000 TNCWS 0 33,001 to 96,000 NTNCWS 0TCR Compliance Monitoring Data Quality and Completion Report

C-3 June 2010 Population

Served System Type Number of Systems
2005 SDWIS State Dataset 33,001 to 96,000 CWS 21 >96,000 TNCWS 0 >96,000 NTNCWS 0 >96,000 CWS 5

C.1.1.3 Narrative of Issues

The data were not representative of conditi ons in the State, even for the few individual systems for which data were submitted. The data, therefore, were not used.

C.1.1.4 Number of Records

The total number of records included in the submitted dataset for Alabama is 1,657

.

C.1.1.5 QA Issues

As noted above, many PWSs were missing from the data pull, and only 170 unique water systems had data submitted by the State. The majo rity (69 percent) of records were TCR repeat sample records, and only 39 percent were TCR routine samples. From the submitted data, only 18 records were routine samples that were TC negative. The submitted data were extremely incomplete, especially for TC negative data. Twenty-six percent of the submitted data were TCR positive samples, and most of the rest were repeat samples. The vast majority of TC negative samples were not included in the data sent. This was assumed to be a st ate reporting issue rather than an issue of a) systems not reporting data or b) excessive violations , which would result in fewer negative samples being reported.TCR Compliance Monitoring Data Quality and Completion Report

C-4 June 2010 C.1.2 AMERICAN SAMOA

C.1.2.1 Description of Data

The State submitted data in multiple formats and files.

C.1.2.2 System Inventory Comparison

Because the data from American Samoa was screened out from the fi nal dataset that was created, no data cleaning was c onducted and the original data c ontains many systems that may have been screened out during the cleaning process. Therefore, a comparable table was not created for American Samoa. Exhibit C.2 belo w has the breakout of systems from the SDWIS 2005 inventory and a total number of systems count from the dataset submitted by American Samoa.

Exhibit C.2 American Samoa System Inventory

Population

Served System Type Number of Systems
2005 SDWIS State Dataset

Total 19 ? <101 TNCWS 0 <101 NTNCWS 0 <101 CWS 3
101 to 500 TNCWS 0 101 to 500 NTNCWS 0 101 to 500 CWS 12
501 to 1000 TNCWS 0 501 to 1000 NTNCWS 0 501 to 1000 CWS 3
1001 to 4100 TNCWS 0 1001 to 4100 NTNCWS 0 1001 to 4100 CWS 0 4101 to 33,000 TNCWS 0 4101 to 33,000 NTNCWS 0 4101 to 33,000 CWS 0 33,001 to 96,000 TNCWS 0 33,001 to 96,000 NTNCWS 0 33,001 to 96,000 CWS 1 >96,000 TNCWS 0 >96,000 NTNCWS 0 >96,000 CWS 0TCR Compliance Monitoring Data Quality and Completion Report

C-5 June 2010 C.1.2.3 Narrative of Issues

Data from American Samoa included data in multiple files, were in inconsistent formats from each other and from the standard SDWI S/State format, and reflected few systems.

C.1.2.4 Number of Records

The total number of records included in the submitted dataset for American Samoa is unable to be determined.

C.1.2.5 QA Issues

See above.TCR Compliance Monitoring Data Quality and Completion Report

C-6 June 2010 C.1.3 DISTRICT OF COLUMBIA

C.1.3.1 Description of Data

The State submitted data in a number of formats (i.e., Excel, Word, PDF, etc.) and files.

C.1.3.2 System Inventory Comparison

Because the data from the District of Colu mbia was screened out from the final dataset that was created, no data cleaning was conducted and the original data contains many systems that may have been screened out during the cleani ng process. Therefore, a comparable table was not created for the District of Columbia. Exhib it C.3 below has the breakout of systems from the SDWIS 2005 inventory and a total number of sy stems count from the dataset submitted by the District of Columbia.

Exhibit C.3 District of Columbia System Inventory

Population

Served System Type Number of Systems

2005 SDWIS State Dataset Total 5 ? <101 TNCWS 0 <101 NTNCWS 0 <101 CWS 1
101 to 500 TNCWS 0 101 to 500 NTNCWS 1 101 to 500 CWS 0
501 to 1000 TNCWS 0 501 to 1000 NTNCWS 0 501 to 1000 CWS 0
1001 to 4100 TNCWS 0 1001 to 4100 NTNCWS 1 1001 to 4100 CWS 0 4101 to 33,000 TNCWS 0 4101 to 33,000 NTNCWS 0 4101 to 33,000 CWS 1 33,001 to 96,000 TNCWS 0 33,001 to 96,000 NTNCWS 0 33,001 to 96,000 CWS 0 >96,000 TNCWS 0 >96,000 NTNCWS 0 >96,000 CWS 1TCR Compliance Monitoring Data Quality and Completion Report

C-7 June 2010 C.1.3.3 Narrative of Issues

The District of Columbia submitted data from several plants/locations with sample results, but the data were in formats inconsis tent with each other and with the SDWIS/State format. Although it was judged to be good data, the e ffort to transform it into a useable form was judged disproportionate to its value, so it was not used.

C.1.3.4 Number of Records

The total number of records included in the submitted dataset for the District of Columbia is unable to be determined.

C.1.3.5 QA Issues

See above.TCR Compliance Monitoring Data Quality and Completion Report

C-8 June 2010 C.1.4 MAINE

C.1.4.1 Description of Data

The State used the SDWIS Extraction Tool to report their data. The submitted data did not distinguish between routine and repeat TC sa mple events. All TC samples are labeled “RP” (repeat sample). Region I spoke with the St ate and learned that Maine does not currently differentiate TC samples with routine vs. repeat.

C.1.4.2 System Inventory Comparison

Because the data from Maine was screened out from the final dataset that was created, no data cleaning was conducted and the original da ta contains many systems that may have been screened out during the cleaning process. Therefor e, a comparable table was not created for this State. Exhibit C.4 below has the breakout of systems from the SDWIS 2005 inventory and a total number of systems count from the dataset submitted by the State.

Exhibit C.4 Maine System Inventory

Population

Served System Type Number of Systems

2005 SDWIS State Dataset Total 1979 2483 <101 TNCWS 565 <101 NTNCWS 171 <101 CWS 186
101 to 500 TNCWS 594 101 to 500 NTNCWS 180 101 to 500 CWS 87
501 to 1000 TNCWS 43 501 to 1000 NTNCWS 22 501 to 1000 CWS 30
1001 to 4100 TNCWS 4 1001 to 4100 NTNCWS 4 1001 to 4100 CWS 64 4101 to 33,000 TNCWS 0 4101 to 33,000 NTNCWS 0 4101 to 33,000 CWS 27 33,001 to 96,000 TNCWS 0 33,001 to 96,000 NTNCWS 0 33,001 to 96,000 CWS 1 >96,000 TNCWS 0 >96,000 NTNCWS 0TCR Compliance Monitoring Data Quality and Completion Report

C-9 June 2010 >96,000 CWS 1

C.1.4.3 Narrative of Issues

The data set submitted by Maine did not dist inguish between routine and repeat sample events; therefore, the data were not used.

C.1.4.4 Number of Records

The total number of records included in the submitted dataset for Maine is 147,030.

C.1.4.5 QA Issues

See above.TCR Compliance Monitoring Data Quality and Completion Report

C-10 June 2010 C.1.5 MARYLAND

C.1.5.1 Description of Data

Maryland submitted data in a number of files. Maryland used a number of unfamiliar codes in their data submission. A number of emails were exchanged with the State in an effort to sufficiently clarify the submitted data.

C.1.5.2 System Inventory Comparison

Because the data from Maryland was screened out from the final dataset that was created, no data cleaning was conducted and the original data contains many systems that may have been screened out during the cleaning process. Therefor e, a comparable table was not created for this State. Exhibit C.5 below has the breakout of systems from the SDWIS 2005 inventory and a total number of systems count from the dataset submitted by the State.

Exhibit C.5 Maryland System Inventory

Population

Served System Type Number of Systems

2005 SDWIS State Dataset Total 3669 3879 <101 TNCWS 2250 <101 NTNCWS 289 <101 CWS 141
101 to 500 TNCWS 282 101 to 500 NTNCWS 188 101 to 500 CWS 182
501 to 1000 TNCWS 47 501 to 1000 NTNCWS 69 501 to 1000 CWS 55
1001 to 4100 TNCWS 13 1001 to 4100 NTNCWS 30 1001 to 4100 CWS 71 4101 to 33,000 TNCWS 0 4101 to 33,000 NTNCWS 1 4101 to 33,000 CWS 40 33,001 to 96,000 TNCWS 0 33,001 to 96,000 NTNCWS 0 33,001 to 96,000 CWS 6 >96,000 TNCWS 0 >96,000 NTNCWS 0 >96,000 CWS 5TCR Compliance Monitoring Data Quality and Completion Report

C-11 June 2010 C.1.5.3 Narrative of Issues

The data from Maryland was submitted in a number of files. Maryland used a number of unfamiliar codes in their data submission. A number of emails were exchanged with the State in an effort to sufficiently clarify the submitted data. Not all of Maryland’s data was stored electronically. The data showed that about 16 perc ent of the total coliform samples were positive, which indicates a highly biased data set that doe s not have complete data. The data were not used.

C.1.5.4 Number of Records

The total number of records included in the submitted dataset for Maryland is 78,522.

C.1.5.5 QA Issues

See above.TCR Compliance Monitoring Data Quality and Completion Report

C-12 June 2010 C.1.6 NEW JERSEY

C.1.6.1 Description of Data

The State used the SDWIS Extraction T ool in order to report their data.

C.1.6.2 System Inventory Comparison

Because the data from New Jersey was scre ened out from the final dataset that was created, no data cleaning was c onducted and the original data c ontains many systems that may have been screened out during the cleaning process. Therefore, a comparable table was not created for this State. Exhibit C.6 below has the breakout of systems from the SDWIS 2005 inventory and a total number of systems count from the dataset submitted by the State.

Exhibit C.6 New Jerse y System Inventory

Population

Served System Type Number of Systems

2005 SDWIS State Dataset Total 3994 698 <101 TNCWS 1608 <101 NTNCWS 418 <101 CWS 97
101 to 500 TNCWS 792 101 to 500 NTNCWS 283 101 to 500 CWS 122
501 to 1000 TNCWS 114 501 to 1000 NTNCWS 86 501 to 1000 CWS 48
1001 to 4100 TNCWS 43 1001 to 4100 NTNCWS 53 1001 to 4100 CWS 100 4101 to 33,000 TNCWS 1 4101 to 33,000 NTNCWS 5 4101 to 33,000 CWS 173 33,001 to 96,000 TNCWS 0 33,001 to 96,000 NTNCWS 0 33,001 to 96,000 CWS 38 >96,000 TNCWS 0 >96,000 NTNCWS 0 >96,000 CWS 13TCR Compliance Monitoring Data Quality and Completion Report

C-13 June 2010 C.1.6.3 Narrative of Issues

About 50 percent of the total coliform routin e samples were positive, indicating a highly biased data set. The data were not used.

C.1.6.4 Number of Records

The total number of records included in the submitted dataset for New Jersey is 9,273.

C.1.6.5 QA Issues

See above.TCR Compliance Monitoring Data Quality and Completion Report

C-14 June 2010 C.1.7 SOUTH CAROLINA

C.1.7.1 Description of Data

The State did not use the SDWIS Extracti on Tool in order to report their data.

C.1.7.2 System Inventory Comparison

Exhibit C.7 below has the breakout of sy stems from the SDWIS 2005 inventory and a total number of systems count from the dataset submitted by the State.

Exhibit C.7 South Carolina System Inventory

Population

Served System Type Number of Systems

2005 SDWIS State Dataset Total 1410 282 <101 TNCWS 526 <101 NTNCWS 76 <101 CWS 189
101 to 500 TNCWS 66 101 to 500 NTNCWS 66 101 to 500 CWS 156
501 to 1000 TNCWS 4 501 to 1000 NTNCWS 14 501 to 1000 CWS 57
1001 to 4100 TNCWS 0 1001 to 4100 NTNCWS 6 1001 to 4100 CWS 116 4101 to 33,000 TNCWS 0 4101 to 33,000 NTNCWS 1 4101 to 33,000 CWS 114 33,001 to 96,000 TNCWS 0 33,001 to 96,000 NTNCWS 0 33,001 to 96,000 CWS 15 >96,000 TNCWS 0 >96,000 NTNCWS 0 >96,000 CWS 4TCR Compliance Monitoring Data Quality and Completion Report

C-15 June 2010 C.1.7.3 Narrative of Issues

The data included in the dataset submitted by South Carolina only contained results for approximately half of the systems listed in SDWIS.

C.1.7.4 Number of Records

South Carolina submitted data in two tables. The total number of records for both tables that was included in the submitted dataset is 56,188.

C.1.7.5 QA Issues

Cleanup was conducted on the South Carolina da ta set. After cleanup was completed, South Carolina was found to only co ntain results for approximately half of the systems listed in SDWIS. Given the incompleteness of data, the d ecision was made to exclude these data from the final data set and from final analyses.TCR Compliance Monitoring Data Quality and Completion Report

C-16 June 2010 C.1.8 SOUTH DAKOTA

C.1.8.1 Description of Data

The State did not use the SDWIS Extraction T ool in order to report their data. South Dakota used a number of unfamiliar codes in their data submission, and the PWSIDs that the State used in their submission are not in Fede ral PWSID format. A number of emails were exchanged with the State in an effort to suffi ciently clarify the submitted data. Ultimately, the data from South Dakota was not included because too few systems were included in the dataset.

C.1.8.2 System Inventory Comparison

Because the data from South Dakota was scr eened out from the final dataset that was created, no data cleaning was c onducted and the original data c ontains many systems that may have been screened out during the cleaning process. Therefore, a comparable table was not created for this State. Exhibit C.8 below has the breakout of systems from the SDWIS 2005 inventory and a total number of systems count from the dataset submitted by the State.

Exhibit C.8 South Dakota System Inventory

Population

Served System Type Number of Systems

2005 SDWIS State Dataset Total 672 221 <101 TNCWS 133 <101 NTNCWS 14 <101 CWS 121
101 to 500 TNCWS 46 101 to 500 NTNCWS 11 101 to 500 CWS 180
501 to 1000 TNCWS 4 501 to 1000 NTNCWS 3 501 to 1000 CWS 63
1001 to 4100 TNCWS 4 1001 to 4100 NTNCWS 0 1001 to 4100 CWS 67 4101 to 33,000 TNCWS 0 4101 to 33,000 NTNCWS 1 4101 to 33,000 CWS 23 33,001 to 96,000 TNCWS 0 33,001 to 96,000 NTNCWS 0 33,001 to 96,000 CWS 1 >96,000 TNCWS 0TCR Compliance Monitoring Data Quality and Completion Report

C-17 June 2010 Population

Served System Type Number of Systems

2005 SDWIS State Dataset >96,000 NTNCWS 0 >96,000 CWS 1

C.1.8.3 Narrative of Issues

A number of unfamiliar codes were used in the data submission, and the PWSIDs that the State used in their submission are not in Fede ral PWSID format. The dataset had few systems and was not representative of the State.

C.1.8.4 Number of Records

The total number of records included in the submitted dataset for South Dakota is 16,383.

C.1.8.5 QA Issues

See above.TCR Compliance Monitoring Data Quality and Completion Report

C-18 June 2010 C.1.9 TENNESSEE

C.1.9.1 Description of Data

C.1.9.2 System Inventory Comparison

Because the data from Tennessee was screen ed out from the final dataset that was created, no data cleaning was c onducted and the original data c ontains many systems that may have been screened out during the cleaning process. Therefore, a comparable table was not created for this State. Exhibit C.9 below has the breakout of systems from the SDWIS 2005 inventory and a total number of systems count from the dataset submitted by the State.

Exhibit C.9 Tennessee System Inventory

Population

Served System Type Number of Systems

2005 SDWIS State Dataset Total 1129 979 <101 TNCWS 261 <101 NTNCWS 16 <101 CWS 41
101 to 500 TNCWS 123 101 to 500 NTNCWS 15 101 to 500 CWS 109
501 to 1000 TNCWS 11 501 to 1000 NTNCWS 4 501 to 1000 CWS 134
1001 to 4100 TNCWS 4 1001 to 4100 NTNCWS 6 1001 to 4100 CWS 183 4101 to 33,000 TNCWS 1 4101 to 33,000 NTNCWS 2 4101 to 33,000 CWS 192 33,001 to 96,000 TNCWS 0 33,001 to 96,000 NTNCWS 0 33,001 to 96,000 CWS 22 >96,000 TNCWS 0 >96,000 NTNCWS 0 >96,000 CWS 5TCR Compliance Monitoring Data Quality and Completion Report

C-19 June 2010 C.1.9.3 Narrative of Issues

Data were reported only as monthly summaries, and positive samples were not identified as to whether they were a routine or repeat sample.

C.1.9.4 Number of Records

The total number of records included in the submitted dataset for Tennessee is 65,168.

C.1.9.5 QA Issues

See above.TCR Compliance Monitoring Data Quality and Completion Report

C-20 June 2010 C.1.10 NAVAJO NATION

C.1.10.1 Description of Data

The State did not use the SDWIS Extraction Tool in order to report their data. Repeat and routine data were submitted in two separate tabl es. Navajo Nation used a number of unfamiliar codes and code combinations in their data repor ting. An effort was made to contact Navajo Nation in order to clarify the submitted data; no response was received from Navajo Nation.

C.1.10.2 System Inventory Comparison

Because the data from Navajo Nation was sc reened out from the final dataset that was created, no extensive data cleaning was conducted, and the original data contains many systems that may have been screened out during the cleani ng process. Therefore, a comparable table was not created for Navajo Nation. Exhibit C.9 below has the breakout of systems from the SDWIS 2005 inventory and a total number of systems count from the dataset submitted by Navajo Nation.

Exhibit C.10 Navajo Nation System Inventory

Population

Served System Type Number of Systems 2005 SDWIS State Dataset Total 159 187 <101 TNCWS 2 <101 NTNCWS 2 <101 CWS 20
101 to 500 TNCWS 2 101 to 500 NTNCWS 1 101 to 500 CWS 58
501 to 1000 TNCWS 0 501 to 1000 NTNCWS 1 501 to 1000 CWS 41
1001 to 4100 TNCWS 0 1001 to 4100 NTNCWS 1 1001 to 4100 CWS 24 4101 to 33,000 TNCWS 0 4101 to 33,000 NTNCWS 0 4101 to 33,000 CWS 7 33,001 to

96,000 TNCWS 0

33,001 to

96,000 NTNCWS 0

33,001 to

96,000 CWS 0TCR Compliance Monitoring Data Quality and Completion Report

C-21 June 2010 Population

Served System Type Number of Systems 2005 SDWIS State Dataset >96,000 TNCWS 0 >96,000 NTNCWS 0 >96,000 CWS 0

C.1.10.3 Narrative of Issues

Repeat and routine data were submitted in two separate tables. Navajo Nation used a number of unfamiliar codes and code combinations in their data reporting. An effort was made to contact the State in order to clarify the submitted data. No response was received from Navajo Nation; therefore, the data were not included in the final data analyses.

C.1.10.4 Number of Records

The total number of records included in the submitted dataset for Navajo Nation is 37,992.

C.1.10.5 QA Issues

See aboveTCR Compliance Monitoring Data Quality and Completion Report

C-22 June 2010 

### C.2 States Not Submitting Data

Some States chose not to submit data or di d not respond to the request for data. A few large States (e.g., California, Pennsylvania, Texa s, and Washington) did not submit data and are not included in any data analyses. Exhibit C.11 de tails each State that is not submitting data, the reason data were not submitted, and the number of systems for that State from the 2005 SDWIS inventory.

Exhibit C.11 States Not Submitting Data

State Not Submitting Data Reason No Data Submitted Number of Systems – 2005 SDWIS

California State chose not to submit data. TCR data is not stored electronically; TCR data is on paper in field office 7,425

Guam State did not respond to request for data. 12

Hawaii State has no TCR data. 127

Louisiana State did not respond to request for data. 1,542

Massachusetts State chose not to submit data. State does not store TC, FC, EC, or residual data electronically. 1,706

Mississippi State did not respond to request for data. 1,350

Northern Mariana Islands State did not respond to request for data. 127
Pennsylvania State chose not to submit data. 9,755

Puerto Rico State did not respond to request for data. 529
Texas State never sent TCR data. 6,541
Virgin Islands State did not respond to request for data. 302
Washington State chose not to submit data. 4,107
TOTAL 33,523

### Appendix D State ICR Data Request LetterTCR Compliance Monitoring Data Quality and Completion Report

D-1 June 2010 Mr./Ms. Xxxxx Xxxxxxx Position Office / Department Address City, State Zipcode

Dear Mr./Ms. Xxxxx Xxxxxxx,

The 1996 Safe Drinking Water Act (SDWA) Ame ndments require the U.S. Environmental Protection Agency (EPA) to review and revi se, if appropriate, existing National Primary Drinking Water Regulations (NPDWRs) at least ever y six years (i.e., the Six Year Review). The Agency completed its first Six Year Review in 2003, and the statutory deadline for completing the second Six Year Review is the summer of 2009. To support the second Six Year Review, EPA is contacting each Primacy Agency and requesting voluntary submission of its complia nce monitoring data for regulated chemical, radiological, and microbiological contaminants that were collected be tween January 1998 and December 2005. The Office of Management and Budget (OMB) has approved the information collection requirements for EPA's second Six Year Review under the provisions of the Paperwork Reduction Act , 44 U.S.C. 3501 et seq ., and has assigned OMB control number 2040- 0275. These data are an important component in supporting EPA's second Six Year Review of NPDWRs. We are encouraging each Primacy Agency to submit its occurrence information because these data will direc tly contribute to EPA's understa nding of national contaminant occurrence, populations exposed to regulated co ntaminants, and exposure reductions associated with the current regulations. Because our st atutory timeframe for completing the second Six Year Review is the summer of 2009, we are ki ndly asking for your voluntary submission by June 1, 2007. EPA is requesting only data that are curren tly stored electronically (no paper records), including both detection and non-detection results for rou tine compliance monitoring. Attachment A to this letter provides a list of the regulated contaminants for which EPA is requesting data. It also answers questions about how the data will be transferred, managed, and used. In Exhibit 2 of Attachment A, we iden tify the critical data elements needed for each sample result. To make your voluntary reporting as easy as possible, your Primacy Agency can transmit its compliance monitoring data set to EPA by whatever electronic means is most convenient (see Attachment A for the data subm ission options). Attachment A also provides some background information about why we are requesting these data

.TCR Compliance Monitoring Data Quality and Completion Report

D-2 June 2010 Through our previous work with your program, we have worked closely with Yyyy Yyyyyy
, who is copied on this request. As we move forward with collecting these drinking water occurrence data, we will be coor dinating directly with Primacy Ag encies by phone and/or email. Soon after May 1, 2007 we will begin contacting data managers. Please le t us know if you prefer we work with another staff person. Thank you for your consideration of this reque st. If you have any questions about this request or the intended uses of the data, please contact our co-Team Leads for the second Six Year Review, Karen Wirth at 202-5 64-5246 or Rajiv Khera at 202-564-4881.

Sincerely,

Cynthia C. Dougherty Director, Office of Ground Water and Drinking Water

cc: Yyyy Yyyyyyy

Enclosure: Attachment ATCR Compliance Monitoring Data Quality and Completion Report

D-3 June 2010 ATTACHMENT A

I. Details Regarding EPA's Request for Occurrence Data Request

A. What regulated contaminants are included in this request?

EPA is requesting information for chemical cont aminants that are regu lated under Phase I, II, IIb, and V Rules; the Arsenic Rule; the Lead a nd Copper Rule (tap monitoring data only), and the Radionuclides Rule. In addition, EPA is re questing data collected for the Total Coliform Rule. Exhibit 1, below, lists the specific contaminants for which EPA is requesting monitoring data. If it is easier for you to provide the elect ronic data for all regulated contaminants, EPA will extract the data needed for the second Six Year Review (Six Year Review 2).

Exhibit 1: Regulated Contaminants for Which Occurrence Data Are Requested

Chemical Contaminants (Phase I, II, IIB, and V Rules; Arsenic Rule; Lead and Copper Rule)
Acrylamide 1,1-Dichloroethylene Methoxychlor
Alachlor cis-1,2-Dichloroethylene Monochlorobenzene (Chlorobenzene)
Antimony trans-1,2-Dichloro ethylene Nitrate (as N)
Arsenic Dichloromethane (Methylene chloride) Nitrite (as N)
Asbestos 1,2-Dichloropropane Oxamyl (Vydate)
Atrazine Di(2-ethylhexyl)adip ate (DEHA) Pent achlorophenol
Barium Di(2-ethylhexyl) ph thalate (DEHP) Picloram
Benzene Dinoseb Polychlorinated biphenyls (PCBs)
Benzo[a]pyrene Diquat Selenium
Beryllium Endothall Simazine
Cadmium Endrin Styrene
Carbofuran Epichlorohydrin 2,3,7,8-TCDD (Dioxin )
Carbon tetrachloride Ethylb enzene Tetrachloroethylene
Chlordane Ethylene dibromide (EDB) Thallium
Chromium (total) Fluoride Toluene
Copper Glyphosate Toxaphene
Cyanide Heptachlor 2,4,5-TP (Silvex)
2,4-D Heptachlor epoxide 1,2,4-Trichlorobenzene
Dalapon Hexachlorobenzene 1,1,1-Trichloroethane
1,2-Dibromo-3-chloropropane (DBCP) Hexachlorocyclopentadiene 1,1,2-Trichloroethane
1,2-Dichlorobenzene ( o -Dichlorobenzene) Lead Trichloroethylene

1,4-Dichlorobenzene ( p -Dichlorobenzene) Lindane Vinyl chloride

1,2-Dichloroethane (Ethylene dichloride) Mercury (inorganic) Xylenes (total)TCR Compliance Monitoring Data Quality and Completion Report

D-4 June 2010 Exhibit 1: Regulated Contaminants for Which Occurrence Data Are Requested Radiological Contaminants
Combined Radium-226/228; and Radium-226 & Radium-228 (if available) Gross beta Tritium

Iodine-131 Uranium Gross alpha Strontium-90
Microbiological Contaminants

Total coliforms Fecal coliforms Escherichia coli (E. coli)

B. What specific data are being requested an d what timeframe should the data cover?

EPA is requesting the voluntary submission of occurrence data for regulated chemical, radiological, and microbiological contaminants (Exhibit 1) that were collected between January 1998 and December 2005. 1 This request only includes thos e data that you have stored in electronic format . The requested data include routine compliance monitoring samples (including repeat and confirmation samp les). Please include all results for both analytical detections and non-detections.

Exhibit 2 (pages A-3 and A-4) lists the data el ements that are likely to be in your compliance monitoring database. EPA is requesting that, at a minimum, you provide the nine data elements that are in bold and marked with an asterisk (t o enable dependable occurrence analyses). If you prefer to submit your data with only these nine data elements, EPA will use Public Water Supply Identification Numbers (PWSIDs) to link to addi tional system inventory data from SDWIS/Fed, adjusted as necessary using EPA's Needs Survey and the Community Water System Survey. We also encourage you to send us the compliance data even if you feel that your data set is incomplete, perhaps due to waivers and exemptions , etc. Please note th at in the following two cases, the requested compliance mo nitoring data are unique:

• Radiological contaminants: In addition to the other radi ological contaminant data, EPA is requesting data on combined radium-226/ra dium-228, and if available, on radium-226 and radium-228 separately.

• Total Coliform Rule: EPA is requesting presence/absence of total coliforms at coliform sampling sites. EPA welcomes any disinfect ion residuals data at the TCR monitoring sites, if this is convenient to provide w ith the other requested data being submitted.

Voluntary submission of your regulate d drinking water contaminant occurrence data is the most critical step in this national occurrence assessment.

1 A few States may have submitted some of this informat ion for the first Six Year Review (Six Year 1) or the Chemical Monitoring Reform (CMR). However, this is a unique request, in that several requested contaminants were not submitted previously for Six Year Review 1 or the CMR (i.e., acrylamide, arsenic, asbestos, copper, epichlorohydrin, lead, nitrate, nitrate, dioxin, and radion uclides), and the data previously submitted did not cover this full time period. Therefore, we requ est that States submit all of their relevant data, even if some data have been submitted previously.TCR Compliance Monitoring Data Quality and Completion Report

D-5 June 2010 Exhibit 2: Requested Data Elements
The 9 data elements in bold and marked with an asterisk (*) are needed to most eff ectively analyze the occurrence data; although ideally, we would like all 17 data el ements to be submitted with the occurrence data.
Data Element Description Inventory Data

* Public Water System Identification Number (PWSID) The code used to identify each PWS. The code begins with the standard 2-character postal State abbreviation or Region code; the remaining 7 numbers are unique to each PWS in the State. System Name Name of the PWS.

Federal Public Water System Type Code A code to identify whether a system is: • Community Water System; • Non-transient Non-community Water System; or • Transient Non-community Water System. Population Served Highest average daily number of people served by a PWS, when in operation.

Source Water Type Type of water at the source. Source water type can be: • Ground water; or • Surface water; or
• Ground water under the direct influence of surface water (GWUDI) (see note below)
Note : Some States may not distinguish GWUD I from surface water sources. In those States, a GWUDI source should be repo rted as a surface water source type. Sample-Specific Information

* Sampling Point Identification Code A sampling point identifier established by th e State, unique within each applicable facility, for each applicable sampling locatio n (e.g., entry point to the distribution system). This information enables occurre nce assessments that address intra-system variability. Sample Identification Number Identifier assigned by State or the laboratory that uniquely identifies a sample.

* Sample Collection Date Date the sample is collected, including month, day and year.

* Sample Purpose Indicates why the sample is being collected (e.g., compliance/routine, confirmation, duplicate, repeat, special, special duplicate, etc.).

* Sample Analysis Type Code Code for type of water sample collected. • Raw (Untreated) water sample • Finished (Treated) water sample For lead and copper only:
• Source • Tap For TCR Repeats only; indicator of sampling location relative to sample point where positive sample was originally collected: • Upstream
• Downstream • Original

* Contaminant Contaminant name, 4-digit SDWIS contamin ant identification number, or Chemical Abstracts Service (CAS) Registry Number fo r which the sample is being analyzed.

* Sample Analytical Result - Sign The sign indicates whether the sa mple analytical result was:

• (<) "less than" means the contaminant was not detected or was detected at a level "less

than" the minimum reporting level (MRL).

• (=) "equal to" means the contaminant was detected at a level "equal to" the valueTCR Compliance Monitoring Data Quality and Completion Report

D-6 June 2010 Exhibit 2: Requested Data Elements
The 9 data elements in bold and marked with an asterisk (*) are needed to most eff ectively analyze the occurrence data; although ideally, we would like all 17 data el ements to be submitted with the occurrence data.
Data Element Description reported in "Sample Analytical Result - Value." For the TCR, results will be indicated by presence/absence.

* Sample Analytical Result
- Value Actual numeric (decimal) value of the analys is for the chemical results, or the MRL if the analytical result is less than the contaminant's MRL. (Not required for TCR data)

* Sample Analytical Result
- Unit of Measure Unit of measurement for the analytical results reported (usually expressed in either u g/L or mg/L for chemicals; or pCi/l or mrem/yr for radionuclides). (Not required for TCR data) Sample Analytical Method Number EPA identification number of the analytical method used to analyze the sample for a given contaminant. Minimum Reporting Level (MRL) - Value MRL refers to the lowest concentration of an analyte that may be reported. (Not required for TCR data)

MRL - Unit of Measure Unit of measure to express the concentr ation value of a contaminant's MRL. (Not required for TCR data)

C. How can these data be submitted?

Transferring Data: We want to make this process as easy as possible for the Primacy Agencies that are volunteering to submit occurrence data. You can submit the data sets through electronic file transfer protocol (FTP) or by mailing/sh ipping CDs/DVDs (or other information storage formats). Primacy Agencies using SDWIS/State may be able to use a customized SDWIS/State query that is currently being tested.

FTP : FTP is designed specifically for transfer ring (uploading and dow nloading) files over the Internet. To ensure security of your data , each Primacy Agency will only have access to its own data on the FTP site. In addition, data sets uploaded to the FTP site will be downloaded and removed within one working day of when they are uploaded and stored on a secured computer server that is not accessible via the FTP site. For added security, you can zip the files with a password (so that they can only be unzipped with the password). If possible, please scan all files fo r viruses before uploading them.

The following information is needed to upload data set files for the Six Year Review via FTP:

FTP site address: ftp://ftp.cadmusgroup.com User Name: State Password: ?????? Directory: /Incoming/SixYearData/ State

Notes: The username and password are case sensitiv e and must be typed exactly as they are here. Enable “Passive File Transfers” to ensure compatibility with the FTP site. Once connected to the FTP site, navigate to your Pr imacy Agencies’ upload folder. From here you will be able to upload files. Following upload, y ou can scroll through the f ile listing to verify that your files were uploaded successfully. If you have any questions, or if you choose to password-protect your files, please email: SixYearData@cadmusgroup.com

.TCR Compliance Monitoring Data Quality and Completion Report

D-7 June 2010 SHIPPING : If you choose to send CDs/DVDs of your data , this can be sent via U.S. Postal Service or commercial air carrie r (such as FedEx or UPS) to:

Six Year Data Coordinator The Cadmus Group, Inc. 57 Water Street Watertown, MA 02472 Phone: 617-673-7000

QUERY : EPA is currently testing a customized query using existing SDWIS/State query tools that will pull the relevant data (specifi ed in Exhibit 2, pages A-3 to A-4) from a SDWIS/State database. Primacy Agencies that use SDWIS/State for their data storage and management can request that EPA provide them with the customized query if it becomes available during these data collection efforts. If you are interested in having EPA provide you with a data query, please contact: Karen Wirth at 202-564-5246 or wirth.karen@epa.gov ; or Rajiv Khera at 202-564-4881 or khera.rajiv@epa.gov . If your Primacy Agency has not migrated its historic drinking water data to the new SDWIS/State database, a separate data submission to include all data back to January 1998 is requested, so that the data included in the Agency’s Six Year review analysis is as complete and comparable as possible.

File Format: Virtually any electronic file format is acceptable. It would be ideal for Primacy Agencies to submit their data sets in one of the following SAS-compatible file formats: dBase TM (.dbf); Microsoft Access tables (.mdb); comma or tab delimited files (such as .cvs or .txt), or; Microsoft Excel (.xls). However, you can submit the requested data "as is," by simply copying the compliance monitoring records in whatever st ructure or condition they are currently stored in, and submitting that copy of th e electronic data to EPA.

Data Formatting and Documentation: EPA is requesting that the data set structure be horizontal , with each analytical result occupying a discrete row accompanied by the system inventory and sample-specific data items listed in Exhibit 2. EPA also re quests that your data sets include, at a minimum, a brief description of the basic format and structure of each data set, and definitions of all data elements, column/row headings, codes, acronyms, etc., used in each data set. This "data dictionary" information will reduce the amount of time needed for questions and clarification later. However, again, none of these requests are requirements. EPA's primary goal is to obtain the most complete national occu rrence data possible, and the Agency will invest the resources to reconcile data questions where needed. If only the minimum data element information is provided with your submitted da ta, EPA will extract th e additional inventory information using adjusted SDWIS/Fed informati on. However, at the very least, the PWSID number is needed with each contaminant sample result to enable linking to system inventory information.

As noted, your Primacy Agency can transmit it s compliance monitoring data set to EPA in whatever format or structure is most convenient. If it is easier for you to provide the electronic data for all regulated contaminants, EPA will extract the data needed for Six Year Review 2. If you have further questions about th is data request, you can contact SixYearData@cadmusgroup.com

.TCR Compliance Monitoring Data Quality and Completion Report

D-8 June 2010 D. When do these data need to be submitted?

To help EPA meet its Six Year Review 2 stat utory timeframe of summer 2009 and in order to allow EPA time to compile, analyze and document the results of its review, EPA is asking that you please provide the requested mon itoring data sets by June 1, 2007.

II. Background Information Regardi ng EPA's Occurrence Data Request

A. Why is EPA requesting occurrence data?

The 1996 Safe Drinking Water Act (SDWA) Amendmen ts require EPA to review and revise, if appropriate, existing National Prim ary Drinking Water Regulations (NPDWRs) at least every six years (i.e., the Six Year Review). EPA is reque sting occurrence data for regulated contaminants to support the second Six Year Review of NPDW Rs. Through the Six Year Review process, EPA reviews and assesses risks to human health posed by regulated drinking water contaminants, and drinking water occurrence data ar e critical to these assessments. Without an understanding of where and at what levels thes e contaminants are occurring in public drinking water, EPA cannot assess any poten tial risk to public health.

In addition, the 1996 SDWA Amendments require the Agency to maintain a national drinking water contaminant occurrence database (i.e., th e National Contaminant Occurrence Database or NCOD) using occurrence data for both regulate d and unregulated contaminants. Through this data collection, EPA will be fulfilling various requirements set forth by Congress in the 1996 SDWA Amendments.

B. How will these data be used?

EPA's Office of Ground Water and Drinking Water will use the data to estimate the occurrence of regulated contaminants in public drinking wate r systems and to evaluate the number of people exposed. Combined with results of other technical analyses (such as assessments of contaminant health effects), the results of th e occurrence and exposure analyses will be used to help determine whether potential revisions to the current drinking water regulations are likely to maintain or provide for the greater protection of public he alth (for those people served by public water systems) for Six Year Review 2.

Once the Agency publishes its preliminary and fi nal Federal Register Notices for Six Year Review 2, we will provide a comprehensive occu rrence report, which will be available in the docket. The procedures used to analyze these data will reflect those established for the analysis of the 16 States data used for the first Six Y ear Review. You can obtain a copy of EPA's first Six Year Review methodology report, Occurrence Estimation Me thodology and Occurrence Findings Report for the Six Year Regulatory Re view of Existing National Primary Drinking Water Regulations (EPA 815-R-03-006), by vi siting our Web site: http://www.epa.gov/safewater/review.html . This document contains all of the first Six Year Review occurrence findings and provides direct examples of the types of occurrence analyses that will be conducted using the comp liance monitoring data you submit.TCR Compliance Monitoring Data Quality and Completion Report

D-9 June 2010 C. Why is it important to submit these data?

Regulatory decisions and the public health pr otection resulting from these decisions are improved by both the quality and quan tity of the data. Each Primacy Agency that submits data can be directly represented in a ny national occurrence estimates we develop. Their data will be used in the review of existing regulations to determine whether current NPDWRs remain appropriate or if revisions shoul d be considered. All complian ce monitoring data will undergo a comprehensive quality assurance/quality contro l (QA/QC) process required for the Six Year Review 2 statistical occurrence analyses. A copy of the resulting final, QA/QC'd data sets for your Primacy Agency will be available to you upon your request.

D. What will happen once the data are submitted?

EPA will conduct initial data editing and uniform QA/QC assessments on each data set. The data will also be assessed for genera l completeness and representativeness.

Contaminant-specific analytical values will be assessed as part of the QA/QC review. For example, assessment of all analytical values for a specific contaminant will help identify possible unit errors or the presence of outliers. The data will also be checked for duplicate data entries (as defined by multiple rows of identical data elem ents) with duplicates deleted, as needed. Identified errors that do not have straight -forward solutions will be addressed through consultations with the appropriate Prim acy Agency data management staff.

Based on EPA's experience with occurrence informa tion provided by States for the first Six Year Review, the Agency will likely need to contact some States to address questions regarding the data format and content (e.g., outlier values, or missing or undefined data elements). EPA will document the QA/QC process and all edits or changes made to the raw monitoring data.

After the data have undergone QA/QC editing and fo rmatting, the data sets will be aggregated into national contaminant occurrence data sets for each contaminant. The national aggregate data sets will be used to generate statistical estimations of national occurrence. When the analyses are completed and reported, the data w ill be placed in the NCOD and in the docket to support any Six Year Review 2 decisions.