Electronic Annual Report (EAR) Data from Public Drinking Water Systems
Database File Specifications
The Division of Drinking Water makes available the files for the Electronic Annual Report (EAR) database following the collection and processing period for each Reporting Year (RY).
In 2020, the new EAR web application was designed and released to California Drinking Water Systems for 2020 Reporting Year (2020RY) on March 15, 2021. The new platform consists of new database organization resulting in a new dataset release format.
2020RY EAR Platform Data Format (https://ear.waterboards.ca.gov/)
- Change to Identification Numbers for Survey and Questions
- New ShortName designed for Machine Readable processing
- Old ShortName stored as a Primary Key to prior Reporting Years
- Addition of the “Order” column
- Templates vary Section to Section rather than two types (more information below)
Historical EAR Platform Data Format (https://drinc.ca.gov/ear/home.aspx)
- Primary Key assigned includes Section Name and Question Identification
- Survey Identification Number provided to relate information over reporting years
- ShortName may be used for Primary Key across Reporting Years
- Two Templates, large water systems (LWS) and small water systems (SWS)
Instructions for Use
The historical EAR data are available to download in tab-delimited text files divided across Reporting Years. Alongside each Reporting Year dataset is the Template or Templates for reference. As an end-user of the machine readable data, please refer to the supplemental DDW materials for relating the EAR data including:
- Water System Inventory (Excel / CSV)
- Data source is Safe Drinking Water Information System (SDWIS), database of repository for DDW, and available for display by water system on Public Drinking Water Watch (PDWW).
- Data Dictionary 2020RY Platform (Excel)
- Included in the New Data Dictionary is the Question-Answer indicators and assignment values for water system EAR display for the 2020RY.
- Data Dictionary Historical Platform (Excel / CSV)
- All Question-Answers used in the EAR between 2013RY and 2019RY are provided as a grouped list including question-answer shortname’s initial and last year asked.
- Results with Import Errors (Excel)
- These results include the compiled importing errors as they exist.
EAR Data Library
*The 2020RY template includes all question-answers while the resultset applies the design logic used for displaying the EAR to each water system based on various criteria. In an effort to communicate the question-answer list that each water system will see, the following relationships may be made between WaterSystemInventory and DataDictionary2020RY.
- Community or NonCommunity water system
- ColumnName EARType: CWS = CWS, or All and Default
- Large or Small water system by the definition of Greater than 1,000 Service Connections or 3,300 population and the inverse respectively.
- Columnname NewEARAssignment: SWS = SWS, LWS = LWS, or Both and Null
- SDWIS Fee Code Indicator’s Wholesaler (WH), Disadvantaged (DAC), and Not Billed (DD)
- ColumnName SDWISIndicator: DAC = DAC, WH = WH, Any or Null and DD
- Urban Water Supplier (UWS) or nonUWS as defined byCA Water Code Section 10617 by greater than 3,000 Acre-Ft production or 3,000 customers annually.
- ColumnName EARIndicator: UWS = UWS, Any or NonUWS
- 2021RY database files to be released in line with updated collection deadlines.
- 2009 – 2012RY data require additional internal resolution for release in 2022.
Information for Public Water Systems
EAR End Uses of Data
Please contact us if you have any questions or comments about the data.