← All datasets

# curated.water_system_violation
EPA SDWIS drinking-water violations, one row per violation (all years on file) (63,232 rows; category: Environment; no geometry)
Notes: is_health_based_ind = true for exceeding a contaminant limit (MCL) or treatment-technique failure; most other rows are monitoring/reporting lapses (violation_category_code 'MR'), which mean a test wasn't done or reported, not that the water was bad. Always split the two when ranking systems. Decode with curated.water_system_code (value_type 'CONTAMINANT_CODE', 'VIOLATION_CODE', 'RULE_CODE'). Use compl_per_begin_date for the year. violation_status 'Unaddressed' / 'Addressed' are still open.

Fields (expression | type | filled/distinct in a sample | values or examples):
- pwsid | text | 5000/1578 | e.g.: TX1011459; TX1700032; TX1460188
- violation_id | text | 5000/2818 | e.g.: 9; 7; 1
- facility_id | text | 1879/582 | e.g.: 52576; 143161; 49160
- compl_per_begin_date | date | 5000/715 | e.g.: 2021-01-01; 2020-01-01; 2024-01-01
- compl_per_end_date | date | 3493/380 | e.g.: 2024-12-31; 2022-12-31; 2020-12-31
- violation_code | text | 5000/37 | e.g.: 03; 75; 27
- violation_category_code | text | 5000/6 | values: MR; Other; MON; MCL; TT; RPT
- is_health_based_ind | boolean | 5000/2 | values: false; true
- contaminant_code | text | 4997/81 | e.g.: 7500; 3100; 0999
- viol_measure | double precision | 537/115 | e.g.: 0; 0.011; 0.083
- unit_of_measure | text | 147/3 | values: MG/L; PCI/L; UG/L
- federal_mcl | text | 4587/36 | e.g.:  ; 5% of samples; 4.0 mg/L
- state_mcl | text | 481/11 | values: 0; .08; .01; 15; 5; 1.6; 10; .06; .002; 30; .1
- violation_status | text | 5000/4 | values: Resolved; Archived; Unaddressed; Addressed
- rule_code | text | 5000/20 | e.g.: 410; 110; 310
- rule_group_code | text | 5000/4 | values: 300; 100; 400; 200
- rule_family_code | text | 5000/14 | e.g.: 110; 410; 310
- public_notification_tier | text | 5000/3 | values: 3; 2; 1
- calculated_rtc_date | date | 4870/1184 | e.g.: 2024-12-31; 2022-12-31; 2020-12-31
- enforcement_count | integer | 5000/10 | values: 4; 3; 2; 1; 5; 0; 6; 7; 10; 9
- last_enforcement_date | date | 4693/1788 | e.g.: 2011-01-20; 2022-10-14; 2011-01-24
- last_enforcement_action_type_code | text | 4693/16 | e.g.: SOX; SIF; SIA