🚜 ag

USDA agricultural economics: crop production and livestock inventory (NASS QuickStats), farm income and wealth (ERS), and crop insurance experience (RMA Summary of Business); plus USGS estimated agricultural pesticide use by county (NAWQA Pesticide National Synthesis Project). County- and state-level, joinable to geo.counties / geo.states via state_fips_code + county_code, to disasters (drought/storm) for yield-impact analysis, and to econ.fred_indicators for commodity-price context.

9 datasets · 157 columns

nass_crop_production · table

USDA NASS QuickStats field-crop statistics — production, area planted, area harvested, yield, condition, and progress — by county and state. Tall layout — filter statisticcat_desc for a specific measure and unit_desc for its unit; reference_period_desc is annual ('YEAR') for production/yield/area but weekly ('WEEK #nn') or monthly for condition/progress measures, so filter statisticcat_desc before comparing reference_period_desc across rows. NassQuickStatsTransformer drops the in-season forecast vintages NASS also publishes for production/yield (reference_period_desc 'YEAR - AUG/SEP/OCT/NOV FORECAST') so this table carries only each year's final published annual estimate for those measures, not every revision. Joins to geo.counties via the derived county_fips column and to geo.states via state_fips_code; also joinable to disasters drought/storm tables by county+year for yield-impact analysis.

Column	Type	Null	Description
year	integer	no	Data year (partition column)
source_desc	string	yes	Program source: CENSUS (5-yearly, county-dense) or SURVEY (annual)
sector_desc	string	yes	NASS sector (CROPS or ANIMALS & PRODUCTS)
group_desc	string	yes	Commodity group (e.g. FIELD CROPS, LIVESTOCK, POULTRY)
commodity_desc	string	yes	Commodity (CORN, SOYBEANS, CATTLE, HOGS, ...)
class_desc	string	yes	Commodity class (e.g. ALL CLASSES, GRAIN)
prodn_practice_desc	string	yes	Production practice (e.g. ALL PRODUCTION PRACTICES, IRRIGATED)
util_practice_desc	string	yes	Utilization practice (e.g. GRAIN, SILAGE)
statisticcat_desc	string	yes	Statistic category (PRODUCTION, AREA HARVESTED, AREA PLANTED, YIELD, INVENTORY)
domain_desc	string	yes	Domain (TOTAL or a breakdown dimension)
domaincat_desc	string	yes	Domain category value
short_desc	string	yes	Full data-item description (the NASS "short_desc")
unit_desc	string	yes	Unit of the value (BU, LB, TONS, HEAD, ACRES, ...)
agg_level_desc	string	yes	Geographic aggregation level (COUNTY or STATE)
state_alpha	string	yes	USPS state abbreviation
state_fips_code	string	yes	2-digit state FIPS code (FK to geo.states.state_fips)
state_ansi	string	yes	State ANSI code
state_name	string	yes	State name
asd_code	string	yes	Agricultural statistics district code
asd_desc	string	yes	Agricultural statistics district name
county_ansi	string	yes	County ANSI code
county_code	string	yes	3-digit county FIPS code within state
county_fips	string	yes	5-digit county FIPS (state+county, derived) — FK to geo.counties.county_fips
county_name	string	yes	County name (empty for state-level rows)
value	double	yes	Numeric statistic value (null when NASS suppressed it; see value_flag)
value_flag	string	yes	NASS suppression marker when value is null: (D) disclosure, (Z) rounds to zero, (X), (NA)
cv_pct	double	yes	Coefficient of variation (%) of the estimate, when published
freq_desc	string	yes	Frequency (ANNUAL, ...)
reference_period_desc	string	yes	Reference period (YEAR, MARKETING YEAR, ...)
load_time	string	yes	NASS record load timestamp

nass_livestock_inventory · table

USDA NASS QuickStats livestock and poultry inventory head counts by county and state, annual — cattle, hogs, sheep, chickens, turkeys, etc. Fetched with statisticcat_desc=INVENTORY only, so production/marketings for animals & products are not in this table. Tall layout; use commodity_desc + class_desc for a specific animal class and unit_desc for the unit (HEAD). reference_period_desc cadence varies by series: most classes report one annual snapshot ('FIRST OF JAN'), but on-feed cattle classes report a separate row per month ('FIRST OF JAN' through 'FIRST OF DEC') — summing across reference_period_desc for those classes double-, triple-, or 12x-counts the same animals rather than adding distinct populations. Filter to a single reference_period_desc before aggregating. Joins to geo.counties via the derived county_fips column and to geo.states via state_fips_code.

Column	Type	Null	Description
year	integer	no	Data year (partition column)
source_desc	string	yes	Program source: CENSUS (5-yearly, county-dense) or SURVEY (annual)
sector_desc	string	yes	NASS sector (CROPS or ANIMALS & PRODUCTS)
group_desc	string	yes	Commodity group (e.g. FIELD CROPS, LIVESTOCK, POULTRY)
commodity_desc	string	yes	Commodity (CORN, SOYBEANS, CATTLE, HOGS, ...)
class_desc	string	yes	Commodity class (e.g. ALL CLASSES, GRAIN)
prodn_practice_desc	string	yes	Production practice (e.g. ALL PRODUCTION PRACTICES, IRRIGATED)
util_practice_desc	string	yes	Utilization practice (e.g. GRAIN, SILAGE)
statisticcat_desc	string	yes	Statistic category (PRODUCTION, AREA HARVESTED, AREA PLANTED, YIELD, INVENTORY)
domain_desc	string	yes	Domain (TOTAL or a breakdown dimension)
domaincat_desc	string	yes	Domain category value
short_desc	string	yes	Full data-item description (the NASS "short_desc")
unit_desc	string	yes	Unit of the value (BU, LB, TONS, HEAD, ACRES, ...)
agg_level_desc	string	yes	Geographic aggregation level (COUNTY or STATE)
state_alpha	string	yes	USPS state abbreviation
state_fips_code	string	yes	2-digit state FIPS code (FK to geo.states.state_fips)
state_ansi	string	yes	State ANSI code
state_name	string	yes	State name
asd_code	string	yes	Agricultural statistics district code
asd_desc	string	yes	Agricultural statistics district name
county_ansi	string	yes	County ANSI code
county_code	string	yes	3-digit county FIPS code within state
county_fips	string	yes	5-digit county FIPS (state+county, derived) — FK to geo.counties.county_fips
county_name	string	yes	County name (empty for state-level rows)
value	double	yes	Numeric statistic value (null when NASS suppressed it; see value_flag)
value_flag	string	yes	NASS suppression marker when value is null: (D) disclosure, (Z) rounds to zero, (X), (NA)
cv_pct	double	yes	Coefficient of variation (%) of the estimate, when published
freq_desc	string	yes	Frequency (ANNUAL, ...)
reference_period_desc	string	yes	Reference period (YEAR, MARKETING YEAR, ...)
load_time	string	yes	NASS record load timestamp

rma_crop_insurance · table

USDA RMA Summary of Business — crop insurance experience by state, county, crop, insurance plan, coverage category, delivery type, and coverage level. Policies, acres, liability, premium, subsidy, indemnity, and loss ratio. Joins to geo.counties via county_fips and to disasters storm/drought tables by county+year for loss attribution.

Column	Type	Null	Description
year	int	no	Commodity (crop insurance) year — partition column
state_fips	string	yes	2-digit state FIPS (FK to geo.states.state_fips)
state_abbr	string	yes	USPS state abbreviation
county_code	string	yes	3-digit county code within state
county_fips	string	yes	5-digit county FIPS (state+county, derived) — FK to geo.counties.county_fips
county_name	string	yes	County name
commodity_code	string	yes	RMA commodity code
commodity_name	string	yes	Insured crop name
insurance_plan_code	string	yes	Insurance plan code
insurance_plan_abbr	string	yes	Insurance plan abbreviation (YP, APH, RP, ...)
coverage_category	string	yes	Coverage category (A=Buyup, C=CAT, E=Existing, L=Limited)
delivery_type	string	yes	Delivery type (RCAT/RBUP/FCAT/FBUP)
coverage_level	double	yes	Coverage level as a decimal fraction (e.g. 0.65)
policies_sold_count	long	yes	Number of policies sold
policies_earning_prem_count	long	yes	Policies earning premium
policies_indemnified_count	long	yes	Policies indemnified
units_earning_prem_count	long	yes	Insured units earning premium
units_indemnified_count	long	yes	Insured units indemnified
quantity_type	string	yes	Unit of net_reported_quantity (Acres, Tons, Pounds, ...)
net_reported_quantity	double	yes	Net reported quantity (in quantity_type units; the net reporting level)
endorsed_companion_acres	double	yes	Endorsed/companion acres
liability_amount	double	yes	Total liability (USD)
total_premium_amount	double	yes	Total premium (USD)
subsidy_amount	double	yes	Federal premium subsidy (USD)
state_private_subsidy	double	yes	State/private subsidy (USD)
additional_subsidy	double	yes	Additional subsidy (USD)
efa_premium_discount	double	yes	EFA premium discount (USD)
indemnity_amount	double	yes	Indemnity paid (USD; signed)
loss_ratio	double	yes	Loss ratio (indemnity / total premium)

rma_cause_of_loss · table

USDA RMA Cause of Loss — crop insurance experience by state, county, crop, insurance plan, coverage category, growth stage, cause of loss (peril), and month of loss. Policies, acres, liability, premium, subsidy, indemnity, and loss ratio broken out per peril (e.g. Drought, Excess Moisture/Precip/Rain, Hail, Freeze) — the field rma_crop_insurance's Summary of Business extract does not carry. Use this table, not rma_crop_insurance, to attribute indemnities to a specific cause; sum indemnity_amount across causes for a state/county/crop/year and it reconciles to rma_crop_insurance's total. Unlike rma_crop_insurance, RMA does not publish a quantity unit (quantity_type) for this file's acreage/production fields. Joins to geo.counties via county_fips and to disasters storm/drought tables by county+year for loss attribution.

Column	Type	Null	Description
year	int	no	Commodity (crop insurance) year — partition column
state_fips	string	yes	2-digit state FIPS (FK to geo.states.state_fips)
state_abbr	string	yes	USPS state abbreviation
county_code	string	yes	3-digit county code within state
county_fips	string	yes	5-digit county FIPS (state+county, derived) — FK to geo.counties.county_fips
county_name	string	yes	County name
commodity_code	string	yes	RMA commodity code
commodity_name	string	yes	Insured crop name
insurance_plan_code	string	yes	Insurance plan code
insurance_plan_abbr	string	yes	Insurance plan abbreviation (YP, APH, RP, ...)
coverage_category	string	yes	Coverage category (A=Buyup, C=CAT, E=Existing, L=Limited)
stage_code	string	yes	Crop growth stage at time of loss (RMA Stage Code Listing). Blank/null for plans with no crop growth stage (e.g. WFRP, DO, TDO, AQDOL, and some HIP-WI commodity rows) — confirmed live, not a parsing gap.
cause_of_loss_code	string	yes	RMA code identifying the peril that caused the loss. "XX" (All Other Causes) is an annual rollup of minor perils with no specific month/year of loss -- see month_of_loss/year_of_loss.
cause_of_loss_desc	string	yes	Cause-of-loss description (e.g. Drought, Excess Moisture/Precip/Rain, Hail, Freeze)
month_of_loss	int	yes	Numeric month when the loss occurred; 0 for the cause_of_loss_code="XX" (All Other Causes) annual rollup, which carries no specific month/year of loss.
month_of_loss_name	string	yes	3-letter abbreviation of the month when the loss occurred
year_of_loss	int	yes	Calendar year the loss occurred (can differ from the crop/policy year, since the same crop-year/month-of-loss combination gets restated in later settlement years) — part of the row's natural key alongside month_of_loss. Null for the cause_of_loss_code="XX" (All Other Causes) annual rollup.
policies_earning_prem_count	long	yes	Policies earning premium (not available for the CLIP plan)
policies_indemnified_count	long	yes	Policies with a reported loss for this cause (not available for the CLIP plan)
net_planted_quantity	double	yes	Acres/tons/pounds planted after the insured's share is applied (unit not published)
net_endorsed_acres	double	yes	Acres insured under an endorsement (e.g. SCO, STAX, Margin Protection)
liability_amount	double	yes	Maximum dollar amount of insurance for the crop (USD)
total_premium_amount	double	yes	Premium before subsidy (base premium rate x liability; USD)
producer_paid_premium_amount	double	yes	Premium paid by the producer (USD)
subsidy_amount	double	yes	Federal premium subsidy (USD)
state_private_subsidy	double	yes	State/private subsidy (USD)
additional_subsidy	double	yes	Additional subsidy, e.g. Pandemic Cover Crop Program in 2021-2022 (USD)
efa_premium_discount	double	yes	EFA premium discount (USD)
net_determined_quantity	double	yes	Acres/tons/pounds lost to damage from this cause, after the insured's share (unit not published)
indemnity_amount	double	yes	Indemnity paid for this specific peril (USD; signed)
loss_ratio	double	yes	Loss ratio for this cause-of-loss row (indemnity / total premium)

ers_farm_income · table

USDA ERS Farm Income and Wealth Statistics by state and US total: cash receipts, government payments, production expenses, net farm income, and balance-sheet items. Tall — one row per year x state x variable. Values are generally thousands of USD (see unit_desc; some rows are Percent/Ratio/per farm). Joins to geo.states via the state USPS abbreviation.

Column	Type	Null	Description
year	int	no	Data year (partition column); most recent 1-2 years are ERS forecasts
state	string	yes	USPS state abbreviation, or 'US' for the national total (FK to geo.states.state_abbr)
artificial_key	string	yes	ERS stable series code embedding state + variable
variable_description_total	string	yes	Full variable label (category + subcategory)
category	string	yes	Variable category (VariableDescriptionPart1)
subcategory	string	yes	Variable subcategory (VariableDescriptionPart2)
amount	double	yes	Value in the units named by unit_desc (usually thousands of USD; can be negative)
unit_desc	string	yes	Units: $1,000 (dominant), Percent, Ratio, $1,000 per farm, 1,000 acres
publication_date	string	yes	ERS release stamp (e.g. "Data as of February 5, 2026.")
source	string	yes	ERS attribution string
gdp_deflator	double	yes	GDP chain-type deflator for the row's year (for nominal->real conversion)

fsa_commodity_payments · table

USDA FSA program and conservation payments aggregated by county, program, and program year (ARC-CO, PLC, CRP, LFP, ERP, DMC, etc.). payments is the summed disbursement (USD); farms is a distinct-payee count (proxy — FSA files carry no farm/operation id). No commodity dimension exists (program is the finest grain). year is the disbursement (vintage) year; program_year is the crop/program year and differs. Joins to geo.counties via county_fips.

Column	Type	Null	Description
year	int	no	Disbursement (vintage) year — partition column
program_year	string	yes	Crop/program year the payment applies to (differs from disbursement year)
state_fips	string	yes	2-digit state FIPS (FSA state code; FK to geo.states.state_fips)
county_fips	string	yes	5-digit county FIPS (state+county FSA code) — FK to geo.counties.county_fips
program_code	string	yes	FSA accounting program code
program_description	string	yes	FSA accounting program description (ARC-County, PLC, CRP, LFP, ...)
payments	double	yes	Summed disbursement amount (USD) for the county/program/program-year
farms	int	yes	Distinct payee count (proxy for number of farms/operations)

pesticide_use_by_county · table

USGS NAWQA Pesticide National Synthesis Project estimated annual agricultural pesticide use by county, active-ingredient compound, and year — low and high estimate methods (epest_low_kg / epest_high_kg, kilograms applied) for the conterminous United States, reflecting two different assumptions used when Crop Reporting District-level survey data was incomplete. Compiled from every county-level release the source currently publishes: final annual files for 1992-2012, the 2013-17 (ver 2.0) consolidated final release, and single-year preliminary releases thereafter. Preliminary years cover far fewer compounds than the finalized series — 2019 holds only 69 of the ~400 compounds present in 2013-2018 — because USGS calculates preliminary estimates as soon as data becomes available from its survey provider, ahead of the full compound set. A finalized 2018-2022 release restoring the full compound list is scheduled by USGS for December 2026 and will replace these preliminary partitions on reprocess; until then, filter to year <= 2018 for a compound set that is stable across years. Excludes seed-treatment pesticide application beginning in 2015 (the survey provider discontinued those estimates) and excludes the separate state-level-by-crop-group series (different grain, not in this table). Joins to geo.counties via the derived county_fips column and to geo.states via state_fips_code.

Column	Type	Null	Description
year	int	no	Data year (partition column), from the source file's own YEAR field
compound	string	yes	Pesticide active-ingredient compound name
state_fips_code	string	yes	2-digit state FIPS code (FK to geo.states.state_fips)
county_fips_code	string	yes	3-digit county FIPS code within state
county_fips	string	yes	5-digit county FIPS (state+county, derived) — FK to geo.counties.county_fips
epest_low_kg	double	yes	Low estimate of agricultural pesticide use (kilograms applied) for the compound/county/year
epest_high_kg	double	yes	High estimate of agricultural pesticide use (kilograms applied) for the compound/county/year

faostat_production · table

UN FAO FAOSTAT production, land use, and food-balance statistics per area (country) x item x element x year. Tall — the international analog of the USDA NASS/ERS tables. country_code is the UN M49 / ISO 3166-1 numeric code (FK to ref.countries.iso_numeric); FAO regional/economic-group areas are flagged is_aggregate. Fan out one normalized bulk file per FAOSTAT domain; year is emitted per row and the partition is replaced wholesale on re-run.

Column	Type	Null	Description
country_code	string	yes	UN M49 / ISO 3166-1 numeric, unpadded (FK to ref.countries.iso_numeric)
area_name	string	yes	FAO area name
item_code	string	yes	FAO item code (commodity / land class / balance line)
item_name	string	yes	Item name (Wheat, Cattle, Arable land, ...)
element_code	string	yes	FAO element code (statistic type)
element_name	string	yes	Element (Production, Area harvested, Yield, Import quantity, ...)
year	integer	no	Data year (partition column; from FAO Year)
value	double	yes	Numeric value (null when FAO suppressed / not available)
unit	string	yes	Unit (tonnes, ha, hg/ha, 1000 head, ...)
flag	string	yes	FAOSTAT observation flag (E=estimate, I=imputed, M=missing, ...)
is_aggregate	boolean	yes	True for FAO regional/economic-group areas (Area Code >= 5000)

faostat_production_enriched · view

FAOSTAT rows enriched with country identity from ref.countries (ISO alpha-3, name, M49 region). Country areas only (FAO regional/economic-group aggregates filtered out). The international complement to the U.S. NASS/ERS tables — country-level production, land use, and food balances.

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