🌎 environment

U.S. environmental data spanning air quality (EPA AQS monitored concentrations and AQI), toxics and greenhouse-gas emissions (EPA TRI and GHGRP by facility), surface water (USGS streamflow and monitoring sites), public drinking-water systems (EPA SDWIS), and the EPA facility registry with compliance and enforcement history (ECHO/FRS). Each row is a place-and-period observation or a facility record joinable to geo (states/counties), sec (regulated companies), econ, and weather. Together they describe environmental quality, pollution sources, and regulatory status across the United States.

17 datasets · 177 columns

air_quality_annual · table

EPA AirData annual-summary air quality by monitor and pollutant (annual mean, peak values, observation/valid-day counts) for PM2.5, Ozone, SO2, NO2, and CO. Keyless national bulk file annual_conc_by_monitor_.zip. Moved here from the weather schema (air quality is a distinct domain). Joins geo.states via state_fips and geo.counties via county_fips.

Column	Type	Null	Description
state_fips	string	no	2-digit state FIPS code (e.g. '06' for California)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
parameter_code	string	yes	Pollutant code (88101, 44201, etc.)
parameter_name	string	yes	Pollutant name (e.g. 'PM2.5 - Local Conditions')
year	integer	no	Calendar year
arithmetic_mean	double	yes	Annual mean concentration
first_max_value	double	yes	Highest recorded value
observation_count	integer	yes	Number of observations
valid_day_count	integer	yes	Days with valid data
aqi	integer	yes	Air Quality Index

air_quality_daily · table

EPA AirData daily-summary air quality by monitoring site. One row per site per day per pollutant standard (mean, peak, AQI where applicable). Keyless national bulk files daily__.zip, one per pollutant-year. Moved here from the weather schema. Joins geo.states/geo.counties.

Column	Type	Null	Description
state_fips	string	no	2-digit state FIPS code (e.g. '06' for California)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
site_number	string	yes	Monitoring site number
parameter_code	string	yes	Pollutant code (88101, 44201, etc.)
parameter_name	string	yes	Pollutant name
year	integer	no	Calendar year
date	string	no	Sample date (YYYY-MM-DD)
sample_duration	string	yes	Sample duration (e.g. '24-HR BLK AVG', '1 HOUR')
pollutant_standard	string	yes	Pollutant standard used for AQI calculation
arithmetic_mean	double	yes	Daily mean concentration
first_max_value	double	yes	Highest recorded value for the day
aqi	integer	yes	Air Quality Index (null for non-daily-standard measurements)
observation_count	integer	yes	Number of observations
latitude	double	yes	Site latitude
longitude	double	yes	Site longitude

aqs_monitors · table

EPA AQS monitor metadata — one row per (site, parameter, POC) with location, monitor type, networks, and operating span. Keyless reference file aqs_monitors.zip. Snapshot; joins air_quality_daily by site and geo by county_fips.

Column	Type	Null	Description
state_fips	string	yes	2-digit state FIPS code (e.g. '06' for California)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
site_number	string	yes	Monitoring site number
parameter_code	string	yes	Pollutant/parameter code
parameter_name	string	yes	Parameter name
latitude	double	yes	Monitor latitude
longitude	double	yes	Monitor longitude
monitor_type	string	yes	Monitor type (e.g. SLAMS, SPM)
networks	string	yes	Monitoring networks
first_year	integer	yes	First year of data
last_sample_date	string	yes	Last sample date
local_site_name	string	yes	Local site name
city_name	string	yes	City name
cbsa_name	string	yes	Core-Based Statistical Area name

tri_releases · table

EPA Toxics Release Inventory — one row per facility × chemical × reporting form for the year, with on-site/off-site release and transfer amounts. Per-year national bulk CSV (efservice mv_tri_basic_download). Delta by year. Joins geo via state (abbr) + county name; frs_id links epa_facilities.

Column	Type	Null	Description
tri_facility_id	string	yes	TRI facility id
frs_id	string	yes	FRS registry id (FK to epa_facilities.registry_id)
facility_name	string	yes	Facility name
city	string	yes	City
county	string	yes	County name
state_abbr	string	yes	State (USPS abbr)
zip	string	yes	ZIP code
latitude	double	yes	Facility latitude
longitude	double	yes	Facility longitude
primary_naics	string	yes	Primary NAICS code
chemical	string	yes	Chemical name
cas_number	string	yes	CAS registry number
unit_of_measure	string	yes	Unit of measure for release amounts
fugitive_air	double	yes	Fugitive air release
stack_air	double	yes	Stack air release
water_release	double	yes	Surface-water discharge
on_site_release_total	double	yes	Total on-site releases
off_site_release_total	double	yes	Total off-site releases
total_releases	double	yes	Total releases (on-site + off-site)

ghg_facilities · table

EPA GHGRP facility dimension — one row per reporting facility per year with location, NAICS, parent company, and reported industry types. Envirofacts efservice full-year CSV. Delta by year. Join ghg_emissions on (facility_id, year); frs_id links epa_facilities.

Column	Type	Null	Description
facility_id	string	no	GHGRP facility id
facility_name	string	yes	Facility name
latitude	double	yes	Facility latitude
longitude	double	yes	Facility longitude
city	string	yes	City
state_abbr	string	yes	State (USPS abbr)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
county	string	yes	County name
zip	string	yes	ZIP code
naics_code	string	yes	NAICS code
frs_id	string	yes	FRS registry id (FK to epa_facilities.registry_id)
parent_company	string	yes	Parent company
reported_industry_types	string	yes	Reported industry types
facility_types	string	yes	Facility types (e.g. 'Direct Emitter')

ghg_emissions · table

EPA GHGRP emissions facts — CO2-equivalent emissions keyed by facility × year × sector × subsector × gas. Envirofacts efservice full-year CSV. Delta by year. Per-facility annual total = SUM(co2e_emission) over the facility-year; join ghg_facilities on (facility_id, year) for geography.

Column	Type	Null	Description
facility_id	string	no	GHGRP facility id (FK to ghg_facilities.facility_id)
sector_id	integer	yes	GHGRP sector id
subsector_id	integer	yes	GHGRP subsector id
gas_id	integer	yes	Gas id (1=CO2, 2=CH4, 3=N2O, ...)
co2e_emission	double	yes	Emissions in metric tons CO2-equivalent

water_sites · table

USGS NWIS surface-water monitoring sites — one row per active stream site with location, HUC, and drainage area. Fetched per state from the NWIS site service (RDB, expanded output). Snapshot. site_no joins streamflow; state_fips/county_fips join geo.

Column	Type	Null	Description
site_no	string	no	USGS site number (PK)
agency_cd	string	yes	Reporting agency code
station_name	string	yes	Station name
site_type	string	yes	Site type code (ST = stream)
latitude	double	yes	Decimal latitude
longitude	double	yes	Decimal longitude
state_fips	string	yes	2-digit state FIPS code (e.g. '06' for California)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
huc_code	string	yes	Hydrologic unit code
drainage_area_sqmi	double	yes	Drainage area (square miles)

streamflow · table

USGS NWIS daily streamflow — one row per (site, date) with discharge (cfs) and gage height (ft). Fetched per (state, year) from the NWIS daily-values service (RDB; concatenated per-site blocks with dynamically-named value columns, normalized by the transformer). Delta by year; current year is re-fetched. site_no joins water_sites.

Column	Type	Null	Description
site_no	string	no	USGS site number (FK to water_sites.site_no)
obs_date	date	no	Observation date
discharge_cfs	double	yes	Daily mean discharge (cubic feet per second)
discharge_qual	string	yes	Discharge qualifier (A=approved, P=provisional, e=estimated)
gage_height_ft	double	yes	Daily mean gage height (feet)
gage_height_qual	string	yes	Gage-height qualifier

drinking_water · table

EPA SDWIS public water systems — one row per active public water system (PWS) with type, population served, ownership, and source-water type. Envirofacts efservice full-state CSV (active systems only). Snapshot. pwsid joins drinking_water_violations; state_code joins geo.states.

Column	Type	Null	Description
pwsid	string	no	Public Water System id (PK)
pws_name	string	yes	Public water system name
pws_type_code	string	yes	PWS type (CWS, NTNCWS, TNCWS)
primacy_agency_code	string	yes	Primacy agency code
epa_region	string	yes	EPA region
population_served_count	long	yes	Population served
service_connections_count	long	yes	Service connections
owner_type_code	string	yes	Owner type code
gw_sw_code	string	yes	Ground-water / surface-water code
primary_source_code	string	yes	Primary source-water type
city_name	string	yes	City
zip_code	string	yes	ZIP code
state_abbr	string	yes	State (USPS abbr)

epa_facilities · table

EPA ECHO Exporter — one row per regulated facility (~1.5M) with registry identity, location, and a rolled-up compliance/enforcement summary (inspections, violations, penalties, per-statute compliance status). Keyless weekly-snapshot bulk zip. registry_id links TRI/GHGRP frs_id; the facility_compliance view projects the compliance subset.

Column	Type	Null	Description
registry_id	string	no	FRS registry id (PK)
facility_name	string	yes	Facility name
street	string	yes	Street address
city	string	yes	City
state_abbr	string	yes	State (USPS abbr)
zip	string	yes	ZIP code
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
latitude	double	yes	Facility latitude
longitude	double	yes	Facility longitude
naics_codes	string	yes	NAICS codes (space-separated)
sic_codes	string	yes	SIC codes (space-separated)
compliance_status	string	yes	Overall compliance status
snc_flag	string	yes	Significant non-compliance flag
inspection_count	integer	yes	Inspections/evaluations count
date_last_inspection	string	yes	Date of last inspection
formal_action_count	integer	yes	Formal enforcement actions count
total_penalties	double	yes	Total penalties (USD)
caa_compliance_status	string	yes	Clean Air Act compliance status
cwa_compliance_status	string	yes	Clean Water Act compliance status
rcra_compliance_status	string	yes	RCRA compliance status
sdwa_compliance_status	string	yes	Safe Drinking Water Act compliance status

drinking_water_violations · table

EPA SDWIS drinking-water violations — one row per violation per public water system (health-based & monitoring violations: contaminants, lead/ copper, treatment). Envirofacts efservice full-state CSV, filtered by primacy_agency_code (the state column; the literal state column silently no-ops). Snapshot. pwsid joins drinking_water.

Column	Type	Null	Description
pwsid	string	no	Public Water System id (FK to drinking_water.pwsid)
violation_id	string	no	Violation id (PK component)
violation_code	string	yes	Violation type code
violation_category_code	string	yes	Violation category (MR=monitoring, MCL, TT, ...)
is_health_based	string	yes	Health-based violation indicator (Y/N)
contaminant_code	string	yes	Contaminant code
compliance_status_code	string	yes	Compliance/return-to-compliance status
is_major_violation	string	yes	Major violation indicator (Y/N)
viol_measure	double	yes	Violation measurement value
unit_of_measure	string	yes	Unit of measure
compl_per_begin_date	string	yes	Compliance period begin date
compl_per_end_date	string	yes	Compliance period end date
rule_code	string	yes	Regulatory rule code
pws_type_code	string	yes	PWS type (CWS, NTNCWS, TNCWS)

superfund_sites · table

EPA SEMS Superfund sites — one row per site with National Priorities List status and location. Envirofacts efservice full table (national, ~55K). Snapshot. npl_status_code 'F' = Final NPL. Joins geo via fips_code (county) / state; lat/lon populated for NPL sites.

Column	Type	Null	Description
site_id	string	no	SEMS site id (PK)
epa_id	string	yes	EPA site id (e.g. CTD000604546)
site_name	string	yes	Site name
npl_status_code	string	yes	NPL status code (F=Final NPL, N=not on NPL)
npl_status_name	string	yes	NPL status description
street	string	yes	Street address
city	string	yes	City
state_abbr	string	yes	State (USPS abbr)
county_name	string	yes	County name
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
latitude	double	yes	Site latitude
longitude	double	yes	Site longitude
federal_facility	string	yes	Federal facility indicator

rcra_facilities · table

EPA RCRAInfo hazardous-waste handlers — one row per current handler (generators, transporters, treatment/storage/disposal). Envirofacts efservice full-state CSV filtered to current_record='Y' (the table is multi-cycle history). Snapshot. Joins geo via location_state + county_code.

Column	Type	Null	Description
handler_id	string	no	EPA RCRA handler id (PK)
handler_name	string	yes	Handler name
location_city	string	yes	City
state_abbr	string	yes	State (USPS abbr)
location_zip	string	yes	ZIP code
county_code	string	yes	County code
fed_waste_generator	string	yes	Federal hazardous-waste generator status (1/2/3/N)
transporter	string	yes	Transporter activity flag
tsd_activity	string	yes	Treatment/storage/disposal activity flag
recycler_activity	string	yes	Recycler activity flag
latitude	double	yes	Handler latitude
longitude	double	yes	Handler longitude

water_quality_samples · table

USGS/EPA Water Quality Portal — measured water-quality sample results (nitrate, metals, bacteria, pH, etc.) at monitoring locations. Fetched per (state FIPS, year) from the Result service. Delta by year; large/slow, so DQ samples via dqRowLimit. monitoring_location_id joins USGS/STORET sites.

Column	Type	Null	Description
organization_id	string	yes	Reporting organization id
monitoring_location_id	string	yes	Monitoring location id
activity_id	string	yes	Sampling activity id
activity_start_date	string	yes	Sample date (YYYY-MM-DD)
activity_media	string	yes	Sample medium (Water, ...)
characteristic_name	string	yes	Measured characteristic (e.g. Nitrate, pH, Lead)
sample_fraction	string	yes	Sample fraction (Total, Dissolved, ...)
result_value	double	yes	Measured result value
result_unit	string	yes	Result unit of measure
usgs_pcode	string	yes	USGS parameter code
result_status	string	yes	Result status (Accepted, Preliminary, ...)

ghg_facility_annual · view

Per-facility annual greenhouse-gas emissions (total metric tons CO2e) with geography, joining the emissions facts to the facility dimension.

View — columns are resolved by the query engine at runtime.

facility_compliance · view

Compliance/enforcement projection of epa_facilities — identity plus the inspection, penalty, and per-statute compliance-status columns.

View — columns are resolved by the query engine at runtime.

air_quality_annual_by_county · view

County-year average of the annual mean concentration and peak AQI by pollutant — the headline air-quality rollup, joinable to geo.counties.

View — columns are resolved by the query engine at runtime.