Waterboards MCP Server
Provides access to curated Databricks Gold views, allowing retrieval of public water system profiles, SAFER risk and violation status, source-water chemistry, and school lead results, all sourced from State Water Board open data.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@Waterboards MCP Server Are there any at-risk water systems near ZIP 90012?"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
Waterboards MCP Server
An MCP server that answers California drinking water questions — public water systems and their SAFER risk/violation status, source-water chemistry, and school lead results, by ZIP code or water system ID — from curated Databricks Gold views over State Water Board open data (data.ca.gov). Built with FastMCP and uv; deployed as a Databricks App.
Design decisions and the review of the original plan: req/mcp_design_review.md (local, git-ignored).
Architecture
data.ca.gov drinking water files (+ Census ZCTA centroids)
└─ sql/drinking_water/ Bronze → Silver → Gold (run in a Databricks SQL warehouse)
└─ users.suresh_kandru.v_* views (the contract in this README)
└─ Databricks SQL warehouse
└─ this server (FastMCP, Databricks Apps, /mcp over HTTP)
└─ MCP client / agentThe server is read-only and queries only <catalog>.<schema>.v_* Gold views (plus information_schema for dataset discovery). Every caller-supplied value is bound as a named SQL parameter.
Related MCP server: mcp-arcgis-sacramento
Data pipeline
scripts/download_drinking_water_data.py downloads the source files. Upload them (plus the Census ZCTA gazetteer as zcta_centroids.txt) to /Volumes/users/suresh_kandru/raw/drinking_water/current/, then run in order:
Script | Layer | Output |
| setup | volume, |
| Bronze | raw all-string tables |
| Silver | typed |
| Gold | the |
| Bronze/Silver/Gold | DDW SDWIS tap-water results and ZIP-to-service-area lookup → |
Silver turns NULL/NaN/N/A into SQL NULL, parses dates, keeps non-detects explicit (is_non_detect, result_numeric NULL, reporting_limit), and drops street addresses. Systems are located by SAFER coordinates, wells by their own coordinates, and schools by the ZIP parsed from their address.
Tools
Tool | Gold view | Purpose |
|
| What datasets are available |
|
| Water systems near a ZIP, optionally only failing/at-risk/violating |
|
| Profile, SAFER status, violations for one |
|
| Latest well chemistry near a ZIP, filter by chemical |
|
| Latest well chemistry for one |
|
| School lead sampling near a ZIP |
|
| Latest tap/treated-water sample results for the systems serving a ZIP |
All return {results, count, offset, has_more, next_offset, source, retrieved_at}, or {error, hint}. Empty results include an actionable hint.
Gold view contract
The views must exist with at least these columns (the server does SELECT * then filters/orders on them):
View | Required columns |
|
|
|
|
|
|
|
|
|
|
|
|
zip + distance_km are precomputed (pairs up to 50 km), so the server filters distance_km <= :radius_km (the tools take radius_miles, max 31, and convert to km) rather than doing geospatial math per request. Keep PII (street addresses, phone numbers) out of Gold.
Run locally
cp .env.example .env # set DATABRICKS_SQL_WAREHOUSE_ID
databricks auth login --host https://<workspace>.cloud.databricks.com
uv sync --group dev
uv run python main.py # stdioLocal auth uses the Databricks SDK's unified auth (CLI profile via DATABRICKS_CONFIG_PROFILE, or DATABRICKS_HOST + DATABRICKS_TOKEN). Connect a client with an entry like:
{
"mcpServers": {
"waterboards": {
"command": "uv",
"args": ["run", "waterboards-mcp"],
"cwd": "/absolute/path/to/this/repo"
}
}
}HTTP mode (what Databricks Apps runs): MCP_TRANSPORT=streamable-http uv run python main.py, then curl localhost:8000/health.
Verify
uv run pytest tests/ -v
uv run ruff check . && uv run ruff format --check .Tests mock the Databricks SDK; no workspace is needed.
Add a tool
One tool per file in src/waterboards_mcp/tools/, exposing register(mcp); add it to register_tools in tools/__init__.py. Use ZipCode/RadiusMiles/PaginationParams from models.py for validated inputs and call warehouse.query_gold(...) with static where/order_by SQL and Params for values. Document source, refresh cadence, and result shape in the docstring — the model reads it.
Deploy
See deploy/databricks/README.md.
Evaluate
See eval/README.md and the mcp-eval skill.
License
See LICENSE.
Available Tools
7 toolswaterboards_find_water_systems_near_zipFind public water systems near a ZIP codeARead-onlyIdempotent
Find drinking water systems serving the area near a ZIP code.
Source: Gold view v_water_systems_near_zip (California State Water
Board SDWIS inventory + SAFER risk assessment). Refreshed when the
pipeline reloads the open data; each result has data_as_of. Only
systems with coordinates in the SAFER assessment can be located.
Distance is from the ZIP centroid to the system's location, so a
nearby system does not necessarily serve that ZIP. Ordered nearest
first.
Set flagged_only to return only systems that are Failing, At-Risk,
or have an MCL, E. coli, or treatment-technique violation.
Each result has pws_id, system_name, county, city, federal_type,
total_population, distance_km, safer_status, is_failing,
violation flags/analytes, risk levels, and source_url. Use
pws_id with waterboards_get_water_system for the full profile. SAFER
status is a regulatory screening, not a tap-water safety determination.
Inputs: zip_code (required), pagination, flagged_only, radius_miles. Returns: object
waterboards_get_school_lead_near_zipGet school drinking water lead results near a ZIP codeARead-onlyIdempotent
Get lead-in-drinking-water results at K-12 schools near a ZIP code.
Source: Gold view v_school_lead_near_zip (California State Water Board
school lead sampling, 2020 onward; the data on file stops at the
newest last_observed, so say so when results are old). Distance is
between ZIP centroids. Ordered nearest first, then most recent.
exceedances_only returns only samples the state flagged as above the
lead action level.
Each result has school_name, district, school_zip, sample_location,
result_numeric and units (UG/L), reporting_limit, is_non_detect
(result_numeric is null when true), action_level_exceeded,
follow_up_action, last_observed (sample date), pws_id, and
source_url. These are point-in-time samples, not a health
determination.
Inputs: zip_code (required), pagination, radius_miles, exceedances_only. Returns: object
waterboards_get_source_chemistry_near_zipGet drinking water source chemistry near a ZIP codeARead-onlyIdempotent
Get latest source-water chemistry for wells near a ZIP code.
Source: Gold view v_source_chemistry_near_zip (DDW/GAMA drinking water
source chemistry, 2020 to present). Refreshed when the pipeline reloads
the open data; each result has last_observed. Ordered nearest well
first, then most recent.
chemical is a case-insensitive name fragment (e.g. 'arsenic',
'nitrate', 'PFOA'); omit it to see every chemical, which is large. Set
detected_only to skip non-detects.
Each result is the latest sample for one well and chemical: pws_id,
system_name, well_id, chemical_name, result_numeric, units,
reporting_limit, is_non_detect, last_observed (sample date),
sample_count, and source_url. When is_non_detect is true,
result_numeric is null and the chemical was not found above
reporting_limit. This is raw source water before treatment, not tap
water, and not a health determination; units vary by chemical (UG/L,
NG/L, MG/L, ...) so never compare across units.
Inputs: zip_code (required), chemical, pagination, radius_miles, +1 more. Returns: object
waterboards_get_system_source_chemistryGet source chemistry for a water systemARead-onlyIdempotent
Get latest source-water chemistry for one water system's wells.
Source: Gold view v_source_chemistry_latest (DDW/GAMA drinking water
source chemistry, 2020 to present). Refreshed when the pipeline reloads
the open data; each result has last_observed. Ordered by chemical,
then well. Get pws_id from waterboards_find_water_systems_near_zip.
chemical is a case-insensitive name fragment; detected_only skips
non-detects.
Each result is the latest sample for one well and chemical: pws_id,
system_name, well_id, chemical_name, result_numeric, units,
reporting_limit, is_non_detect, last_observed (sample date),
sample_count, and source_url. When is_non_detect is true,
result_numeric is null and the chemical was not found above
reporting_limit. This is raw source water before treatment, not tap
water, and not a health determination; units vary by chemical (UG/L,
NG/L, MG/L, ...) so never compare across units.
Inputs: pws_id (required), chemical, pagination, detected_only. Returns: object
waterboards_get_tap_water_for_zipGet tap water sample results for a California ZIP codeARead-onlyIdempotent
Get the latest tap-water test results for a California ZIP code.
Source: Gold view v_tap_water_for_zip (DDW SDWIS sample results, 2023 to
present, joined to water-system service-area boundaries). Refreshed when
the pipeline reloads the open data; each result has last_observed.
Systems are ordered by how much of the ZIP they serve
(pct_of_zip_covered, highest first); a ZIP can be served by several
systems, so use the largest coverage as the likely supplier.
stage is 'distribution' (samples from the pipes that reach customers,
the closest to tap water; default) or 'treated' (leaving the treatment
plant). chemical is a case-insensitive name fragment (e.g. 'lead',
'arsenic', 'pfas', 'nitrate'). detected_only (default true) hides
non-detects; exceeds_mcl_only shows only results above the legal
limit. min_coverage_pct (default 10) hides systems covering less of
the ZIP than that; lower it to 0 to see every overlapping system.
Each result: zip, pct_of_zip_covered, pws_id, system_name, total_population, water_stage, analyte_name, result_numeric, units, is_non_detect, reporting_limit, mcl, exceeds_mcl, any_exceeds_mcl, max_result_numeric, sample_count, last_observed, sampling_point, method, source_url. When is_non_detect is true, result_numeric is null and the analyte was below reporting_limit.
These are system-level samples, not results for an individual address or home. A single sample above the MCL is not a violation by itself (many limits apply to running averages), and this is not a health determination. Units vary by analyte; never compare across units.
Inputs: zip_code (required), stage, chemical, pagination, +3 more. Returns: object
waterboards_get_water_systemGet a public water system profileARead-onlyIdempotent
Get the profile and compliance/risk summary of one water system.
Source: Gold view v_water_systems (California State Water Board SDWIS
inventory + SAFER risk assessment). Refreshed when the pipeline reloads
the open data; the result carries data_as_of.
The single result has system name, county, city, type, owner, source
type, population, service connections, last sanitary survey,
safer_status, is_failing, economic_status (DAC/SDAC), violation
flags with analytes (primary/secondary MCL, E. coli, treatment
technique, monitoring), risk levels by category, and source_url.
has_risk_assessment is false when the system is not in the SAFER
file, in which case the violation/risk fields are null (unknown, not
"none"). This is a regulatory screening, not a tap-water safety
determination.
Inputs: pws_id (required). Returns: object
waterboards_list_datasetsList waterboard datasetsARead-onlyIdempotent
List the water datasets (Gold views) available through this server.
Source: Unity Catalog information_schema for the configured Gold schema. Refresh cadence: reflects the current catalog; underlying data refresh varies by dataset (see each view's comment).
Returns a pagination envelope; each result has table_name and
table_comment.
Inputs: pagination. Returns: object
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 7 tools
Each tool maps to a distinct dataset/query axis: catalog listing, systems near a ZIP, single-system profile, source chemistry by ZIP vs by system, school lead, and tap water. The only near-pair is get_source_chemistry_near_zip vs get_system_source_chemistry, which descriptions do separate by lookup key (ZIP vs pws_id). Source vs tap water vs school lead are clearly delineated in the descriptions.
All names use the same waterboards_ prefix and snake_case verb_noun form, which is highly predictable. Minor inconsistency: 'get_source_chemistry_near_zip' places the qualifier at the end while its sibling is 'get_system_source_chemistry', so the pair reads less symmetrically than the rest.
Seven tools is well-scoped for a read-only public-data server; each tool corresponds to one backing Gold view or catalog operation. No redundant or padding tools are present.
The surface covers discovery, system lookup, source chemistry (two access paths), school lead, and tap water, which is a strong read-only lifecycle for the domain. Gaps are minor: no name/county search for a water system, no way to resolve a ZIP's serving systems beyond distance-based proximity, and no violation-detail listing beyond flags in the system profile.
Maintenance
Related MCP Connectors
Query US tap-water quality from the EPA's SDWIS records — by city, system, or contaminant.
Query real-time and historical USGS water data from ~8,000 stream gages and groundwater wells.
Search EPA compliance, TRI, Superfund, drinking-water, EJScreen, and AirNow data.
California DMV MCP — registered vehicles and EV adoption by ZIP code, field offices, the official…
Related MCP Servers
- AlicenseAqualityCmaintenanceEnables querying USGS water data including real-time and historical streamflow, gage height, and water temperature from USGS gauges across the United States.3MIT
- AlicenseNot gradedqualityCmaintenanceEnables querying and exploring City of Sacramento GIS open geospatial datasets (parcels, zoning, public works) via ArcGIS Feature Services.94 npmMIT

SWATGenX MCP Serverofficial
AlicenseNot gradedqualityCmaintenanceEnables search and retrieval of calibrated SWAT+ watershed models, national groundwater lithology and PFAS inventories, plus API-key-secured ordering and downloading of custom watershed models.1MIT- AlicenseNot gradedqualityCmaintenanceEnables querying and retrieving US water-chemistry data, including station metadata, sample results, and controlled vocabularies from the Water Quality Portal.75 npmMIT