Subsurface Ledger Tap Water & Groundwater Atlas

Hydrological Data Reconciliation, Unit Normalization & SHA-256 Provenance

Every statistic in Subsurface Ledger is deterministically compiled from six primary federal and California government archives (5,223,006 reconciled records, publication 3519f42f4cbb6094cdb2, status PASSED) with cryptographic SHA-256 verification and zero synthetic imputation.

1. EPA UCMR 5 Unit Scaling (ug/L to ng/L) & Strict <MRL Non-Detect Separation

Upstream EPA UCMR5_All.txt reports AnalyticalResultValue in micrograms per liter (ug/L). All 38,562 quantified PFAS detections (AnalyticalResultsSign = '=') are multiplied by 1,000.0 into nanograms per liter (ng/L, parts per trillion) for direct alignment with April 2024 EPA Maximum Contaminant Levels. All 1,934,476 observations below the laboratory Minimum Reporting Level (AnalyticalResultsSign = '<') retain quantified_value = NULL and are never substituted with zero or the MRL. Quantified Lithium results (18,939 rows) are preserved in ug/L against the 10.0 ug/L EPA Health Reference Level.

EPA UCMR 5 Source MRL to Final MCL Unit Reconciliation Proof
Compound Raw UCMR 5 MRL (ug/L) Normalized MRL (ng/L) Federal Benchmark
PFOA 0.004 4.0 4.0 ng/L
PFOS 0.004 4.0 4.0 ng/L
PFHxS 0.003 3.0 10.0 ng/L
PFNA 0.004 4.0 10.0 ng/L
HFPO-DA 0.005 5.0 10.0 ng/L
Lithium 9.000 Retained ug/L 10.0 ug/L

2. California SWRCB SAFER Small-System Rescue & Semicolon Analyte Parsing

Joining SWRCB SAFER (3,186 systems) to EPA UCMR 5 on canonical PWSID matches 680 utilities and rescues 2,506 small rural California water systems (< 3,300 population) omitted from federal UCMR 5 sampling. Rescued utilities carry in_ucmr5 = false so unmonitored utilities are never misrepresented as clean PFAS non-detects. PRIMARY_ANALYTES strings are split strictly on ; so comma-containing IUPAC names (1,2,3-TRICHLOROPROPANE, CHROMIUM, HEX) remain intact.

3. CA DWR Borehole Stratigraphy, SGMA Water Levels & Zero-PII WCR Ingestion

Across 47,658 California DWR groundwater stations and 3,310,413 SGMA-era water-level observations (2014 to present), 183,040 rows flagged for active pumping cones of depression, dry-well obstructions, or questionable QC are quarantined prior to computing ordinary-least-squares 5-year water-table slopes (ft/yr) across 10,184 wells. From 1,112,243 raw CNRA Well Completion Report rows, 100,470 newly constructed SGMA boreholes (2014 to 2026) are isolated while excluding 91,231 well destructions and 3,020 modifications; owner names, street addresses, driller license numbers, and parcel APNs are stripped at stream ingestion.

4. Cryptographic SHA-256 Primary Source Manifest

Upstream .gov Bulk Archives Verified in Publication 3519f42f4cbb6094cdb2
Source ID Capture Mode & Scope Bytes SHA-256 Digest
epa_ucmr5 full_archive
Nationwide EPA UCMR 5 Public Water System analytical occurrence results (29 PFAS + Lithium), ZIP code crosswalk, and additional data elements
13,531,598 313c88a247ab41c7...74158f2a
ca_dwr_stations full_csv
California DWR Periodic Groundwater Level monitoring well stations, coordinates, basin codes, counties, ground surface elevation, and total well depth
8,372,933 bcc8ae8f601f2f6a...9f1ae92d
ca_dwr_perforations full_csv
California DWR monitoring well borehole casing and perforated screen intake intervals (top_prf_int, bot_prf_int ft bgs)
421,936 0855effae5da28e8...e6901ecb
ca_dwr_measurements sgma_window_stream_2014_present
Modern SGMA hydrological window (msmt_date >= '2014-01-01', 2014-present) deterministically stream-captured from upstream CA DWR bulk CSV (> 350 MB threshold)
768,511,480 3987aa2f2c5b0bc1...1bf3e051
ca_swrcb_safer ckan_dynamic_sas_csv
California State Water Resources Control Board (SWRCB) SAFER Drinking Water Risk Assessment covering Failing, At-Risk, Potentially At-Risk, and Not At-Risk public water systems (including small systems < 3,300 population missed by federal UCMR 5) and primary MCL co-contaminant violations
5,841,888 5887adfd2894f4af...5cd7a15b
ca_dwr_wcr sgma_zero_pii_stream_2014_2026
Modern SGMA Well Completion Reports (2014-2026) stream-captured from CNRA CA DWR bulk CSV (408,062,594 bytes) with owner/street-address/APN PII stripped and coordinates rounded to 3 decimals
28,513,447 1af69ba0679e3df7...728c75f4

5. Interpretive Caveats

Regional co-location of public water systems and CA DWR groundwater basins provides basin-scale hydrogeologic context without asserting site-specific contaminant plume migration into individual municipal supply wells. Point-in-time UCMR 5 exceedances reflect laboratory analytical values rather than formal four-quarter running annual average SDWA compliance determinations.

6. Related Public-Data Engineering & Operational Notes

Subsurface Ledger is maintained within the Meru AI Public Data Platforms engineering program. Practitioners auditing pipeline determinism, reproducible release manifests, or physical distribution infrastructure can inspect the companion technical references on Meru AI: