🌀 disasters

U.S. discrete emergency-event data: FEMA disaster declarations, Public Assistance and Hazard Mitigation grant projects, NFIP flood-insurance policies and paid claims, NOAA storm events, and NIFC WFIGS wildfire perimeters. Each row is a bounded incident with a date, geographic footprint, and measurable damage — distinct from the continuous meteorological measurements in the weather schema. Enables disaster-risk analysis joined to SEC filings (state-of-incorporation exposure), econ employment, lands national forests (burned NF acreage), and FEC contributions.

11 datasets · 124 columns

disaster_declarations · table

Canonical FEMA disaster-declaration record. One row per disaster-declaration-county-program-area combination. Partitioned by fiscal year declared (fyDeclared). Joins to geo.counties via county_fips and to sec.filing_metadata by state for risk-disclosure research. Refreshed daily (declarations issued continuously within 24h of signature). Source: fema.gov/api/open/v2/DisasterDeclarationsSummaries.

Column	Type	Null	Description
disaster_number	integer	no	FEMA disaster number (e.g., 4611); join key across FEMA tables
fema_declaration_string	string	yes	Composite declaration id (e.g., 'EM-3610-PR')
declaration_type	string	yes	DR (major), EM (emergency), FM (fire mgmt), FS (fire suppression)
declaration_title	string	yes	Declaration title (e.g., 'TROPICAL STORM ERNESTO')
declaration_date	date	yes	Date of presidential declaration
incident_type	string	yes	Hurricane / Flood / Tornado / Wildfire / Severe Storm / etc.
incident_begin_date	date	yes	Start of incident period
incident_end_date	date	yes	End of incident period (null if ongoing)
state_fips	string	yes	2-digit state FIPS code (FK to geo.states)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
designated_area	string	yes	Human-readable designated area (e.g., 'Adjuntas (Municipio)')
region	integer	yes	FEMA region number (1-10)
ih_program_declared	boolean	yes	Individual and Households Program declared
ia_program_declared	boolean	yes	Individual Assistance declared
pa_program_declared	boolean	yes	Public Assistance declared
hm_program_declared	boolean	yes	Hazard Mitigation declared
tribal_request	boolean	yes	Whether the declaration was a tribal request
id	string	no	OpenFEMA row UUID (PK)

public_assistance_projects · table

FEMA Public Assistance grants to state/local governments and nonprofits for disaster recovery. Largest dollar-value FEMA dataset. One row per funded project worksheet. Partitioned by declaration year. Joins to disaster_declarations by disaster_number (many-to-one; not a declared FK) and to geo.counties via county_fips. Source: fema.gov/api/open/v2/PublicAssistanceFundedProjectsDetails.

Column	Type	Null	Description
disaster_number	integer	yes	FEMA disaster number (e.g., 4611); join key across FEMA tables
pw_number	integer	yes	Project worksheet number
application_title	string	yes	Project title
applicant_id	string	yes	FEMA applicant id
declaration_date	date	yes	Date of declaration (drives the year partition)
incident_type	string	yes	Incident type (Hurricane, Flood, etc.)
damage_category_code	string	yes	FEMA damage category code (A-G)
damage_category	string	yes	Damage category description
project_status	string	yes	Active / Closed / etc.
project_size	string	yes	Large or Small
state_fips	string	yes	2-digit state FIPS code (FK to geo.states)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
county_name	string	yes	County/parish name
state_abbr	string	yes	2-char state abbreviation
project_amount	double	yes	Approved project cost (USD)
federal_share_obligated	double	yes	Federal obligation to date (USD)
total_obligated	double	yes	Total obligated incl. non-federal share (USD)
hash	string	no	OpenFEMA row hash (PK)

hazard_mitigation_projects · table

FEMA Hazard Mitigation Assistance projects (HMGP, BRIC, FMA, PDM, LPDM). Pre- and post-disaster investment in resilience. One row per project. Partitioned by program fiscal year (programFy). Joins to geo.counties via county_fips and to disaster_declarations by disaster_number (nullable for non-disaster BRIC/FMA programs). Source (API v4): fema.gov/api/open/v4/HazardMitigationAssistanceProjects.

Column	Type	Null	Description
project_identifier	string	no	HMA project identifier (PK; e.g., 'DR-1980-0019-F')
program_area	string	yes	HMGP / BRIC / FMA / PDM / LPDM
disaster_number	integer	yes	FEMA disaster number (e.g., 4611); join key across FEMA tables
region	integer	yes	FEMA region number
state_fips	string	yes	2-digit state FIPS code (FK to geo.states)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
state_name	string	yes	State name
county_name	string	yes	County name
project_type	string	yes	Mitigation project type description
status	string	yes	Project status (Approved / Closed / etc.)
recipient	string	yes	Grant recipient (state/tribe)
subrecipient	string	yes	Subrecipient (local government/entity)
date_approved	date	yes	Date approved
date_closed	date	yes	Date closed (null if open)
project_amount	double	yes	Total project cost (USD)
federal_share_obligated	double	yes	Federal share obligated (USD)
cost_share_percentage	double	yes	Federal cost-share fraction (e.g., 0.75)

nfip_claims · table

Paid NFIP flood-insurance claims. One row per claim. ~2.7M rows. Partitioned by year of loss. Key signal for disaster severity and uninsured-loss estimation. Joins to geo.counties via county_fips and to disaster_declarations / wildfire_perimeters by county+date overlap. Source: fema.gov/api/open/v2/FimaNfipClaims.

Column	Type	Null	Description
state_fips	string	yes	2-digit state FIPS code (FK to geo.states)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
state_abbr	string	yes	2-char state abbreviation
date_of_loss	date	yes	Date of flood loss
flood_zone	string	yes	FEMA rated flood zone (AE, VE, X, etc.)
cause_of_damage	string	yes	Cause-of-damage code
occupancy_type	integer	yes	Occupancy type code (1=single family, etc.)
policy_count	integer	yes	Number of policies represented by this claim record
amount_paid_building	double	yes	Amount paid on building claim (USD)
amount_paid_contents	double	yes	Amount paid on contents claim (USD)
amount_paid_icc	double	yes	Amount paid on Increased Cost of Compliance claim (USD)
total_building_coverage	double	yes	Total building insurance coverage (USD)
total_contents_coverage	double	yes	Total contents insurance coverage (USD)
census_tract	string	yes	11-digit census tract FIPS
reported_zip_code	string	yes	Reported ZIP code
latitude	double	yes	Approximate latitude (to 1 decimal per FEMA privacy rules)
longitude	double	yes	Approximate longitude
id	string	no	OpenFEMA row UUID (PK)

nfip_policies · table

NFIP flood-insurance policy transactions. One row per policy term. VERY LARGE (~74M rows all-history; several million per year). Partitioned by policy effective year. dqRowLimit caps DQ sampling; prod loads everything. Joins to geo.counties via county_fips and census_tract. Source: fema.gov/api/open/v2/FimaNfipPolicies.

Column	Type	Null	Description
state_fips	string	yes	2-digit state FIPS code (FK to geo.states)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
property_state	string	yes	2-char property state abbreviation
policy_effective_date	date	yes	Policy effective date (drives the year partition)
policy_termination_date	date	yes	Policy termination date
flood_zone	string	yes	FEMA rated flood zone
occupancy_type	integer	yes	Occupancy type code
policy_count	integer	yes	Number of policies represented
policy_cost	double	yes	Policy cost (USD)
total_insurance_premium	double	yes	Total insurance premium of the policy (USD)
total_building_coverage	double	yes	Total building insurance coverage (USD)
total_contents_coverage	double	yes	Total contents insurance coverage (USD)
federal_policy_fee	double	yes	Federal policy fee (USD)
census_tract	string	yes	11-digit census tract FIPS
reported_zip_code	string	yes	Reported ZIP code
latitude	double	yes	Approximate latitude
longitude	double	yes	Approximate longitude
id	string	no	OpenFEMA row UUID (PK)

storm_events · table

NOAA NCEI Storm Events Database — one row per storm event detail record (tornadoes, floods, hurricanes, hail, winter storms, wildfires, etc.). Streamed from the per-year gzip CSV (StormEvents_details-ftp_v1.0_d{year}_c{procdate}.csv.gz); the processing date suffix varies, so the streaming transformer lists the NCEI directory to resolve the current file for the requested year. Damage figures are converted from K/M/B notation to USD doubles. Joins to geo.counties and to disaster_declarations by county+date overlap.

Column	Type	Null	Description
event_id	string	no	NCEI event id (PK)
episode_id	string	yes	Episode id (groups related events)
begin_date	date	yes	Event begin date
end_date	date	yes	Event end date
state_fips	string	yes	2-digit state FIPS code (FK to geo.states)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
state_name	string	yes	State name (as published by NCEI)
event_type	string	yes	Tornado / Flash Flood / Hurricane / Blizzard / etc.
cz_type	string	yes	C (county), Z (NWS zone), M (marine)
magnitude	double	yes	Event magnitude (wind speed, hail size; type-dependent)
magnitude_type	string	yes	EG/ES/MS/MG magnitude type
injuries_direct	integer	yes	Direct injuries
injuries_indirect	integer	yes	Indirect injuries
deaths_direct	integer	yes	Direct fatalities
deaths_indirect	integer	yes	Indirect fatalities
damage_property	double	yes	Property damage USD (converted from K/M/B notation)
damage_crops	double	yes	Crop damage USD (converted from K/M/B notation)
begin_lat	double	yes	Event begin latitude
begin_lon	double	yes	Event begin longitude

wildfire_perimeters · table

NIFC WFIGS Interagency wildfire perimeters. One row per fire perimeter (~39k features). Snapshot table (overwritePartitions) refreshed on cadence. The streaming transformer pages the FeatureServer via resultOffset (the layer exceeds the ArcGIS transfer cap) and converts each Esri ring polygon to simplified WKT via JTS (EsriGeometryConverter). fire_year is derived from the fire discovery date. Joins to geo.counties via county_fips (from point-of-origin FIPS) and to nfip_claims for post-fire flood exposure. Source: WFIGS_Interagency_Perimeters/FeatureServer/0.

Column	Type	Null	Description
incident_id	string	no	WFIGS IRWIN id (PK)
incident_name	string	yes	Fire incident name
unique_fire_identifier	string	yes	NIFC unique fire identifier (year-unit-localnumber)
fire_year	integer	yes	Year derived from fire discovery date
discovery_date	date	yes	Fire discovery datetime (date part)
containment_date	date	yes	Containment datetime (date part; null if active)
state_fips	string	yes	2-digit state FIPS code (FK to geo.states)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
poo_state	string	yes	Point-of-origin state (2-char abbreviation)
fire_cause	string	yes	Human / Natural / Undetermined
incident_type_category	string	yes	WF (wildfire), RX (prescribed), etc.
gis_acres	double	yes	GIS-calculated perimeter area in acres
final_acres	double	yes	Reported final fire size in acres
centroid_lat	double	yes	Perimeter centroid latitude (WGS84)
centroid_lon	double	yes	Perimeter centroid longitude (WGS84)
geometry_wkt	string	yes	Simplified WGS84 perimeter polygon as WKT (display/spatial join)

disasters_declarations_by_state_year · view

Annual disaster-declaration counts by state and incident type. Aggregates the county-program rows to distinct disasters per state/year. Ready to join sec.filing_metadata on state for risk-disclosure research.

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

disasters_pa_obligated_by_county · view

Total Public Assistance federal obligations by county and declaration year. Joins to geo.counties for mapping; the next natural join is to econ.bls_employment for disaster-affected employment analysis.

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

disasters_nfip_claims_by_county · view

Paid NFIP flood claims aggregated by county and year of loss. Key measure of insured flood loss; joins to disaster_declarations and wildfire_perimeters by county for post-event exposure analysis.

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

disasters_storm_impact_by_county · view

Storm-event human and economic impact aggregated by county and year. Joins to disaster_declarations by county to link storm episodes to FEMA declarations, and to econ employment for economic-impact research.

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