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.
| 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
| 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:
- Checksummed Public-Data Reconciliation: deterministic unit scaling, explicit non-detect separation, and SHA-256 manifest verification across federal and state archives.
- Supply-Chain & Environmental Entity Resolution: reproducible facility, utility, and subsurface basin linkage across federal and state registries.
- Distribution Operations Case Study: operational analytics and execution workflows across physical distribution systems.
- Meru AI Public Data Platforms: overview of sibling civic datasets, schema governance, and public-interest verification standards.
- AI Automation Consulting: custom agentic workflows, hydrological data pipelines, and operational automation by Meru AI.
- Meru AI Data Partnerships: custom municipal water, PFAS cost-recovery, and SGMA groundwater data inquiries.