💵 fiscal

U.S. federal fiscal data: the revenue side (IRS Statistics of Income — income, deductions and tax by ZIP, county, and taxpayer class; county-to-county migration of returns; the roster and financials of tax-exempt organizations) and the outlay side (USAspending obligations by awarding agency and by recipient state; SBA 7(a) and 504 loan approvals). Each row is a geography-and-period or entity-and-period observation joinable to geo (states/counties) and, for nonprofits and borrowers, by EIN/name. Together they show where federal money comes from and where it goes, comparable per county against population and income.

27 datasets · 529 columns

soi_income_by_zip · table

IRS Statistics of Income individual income tax statistics aggregated by ZIP x AGI size class (agi_stub) x tax year. The finest-grained revenue table; joins to geo ZIP crosswalks for county/CBSA rollups and to census ACS income. All dollar amounts are in THOUSANDS of dollars and may be negative. agi_stub brackets: 1=$1-25k, 2=$25-50k, 3=$50-75k, 4=$75-100k, 5=$100-200k, 6=$200k+. zip_code = 00000 rows are the state total for that state (kept; exclude in views that sum ZIPs). Source (per year): irs.gov/pub/irs-soi/{YY}zpallagi.csv.

Column	Type	Null	Description
state_fips	string	yes	2-digit state FIPS code (FK to geo.state_ref)
state_abbr	string	yes	2-letter USPS state code (FK to geo.state_ref)
zip_code	string	no	5-digit ZIP (00000 = state total)
agi_bracket	string	no	SOI AGI size class (agi_stub 1-6)
num_returns	long	yes	Number of returns (N1) ~ households
num_individuals	long	yes	Number of individuals / exemptions (N2) ~ population
num_single_returns	long	yes	Single returns (mars1)
num_joint_returns	long	yes	Joint returns (MARS2)
num_hoh_returns	long	yes	Head-of-household returns (MARS4)
num_elderly_returns	long	yes	Returns with elderly (65+) taxpayers (ELDERLY)
adjusted_gross_income	double	yes	Adjusted gross income, $1000s (A00100)
total_income_amount	double	yes	Total income, $1000s (A02650)
salaries_wages	double	yes	Salaries and wages amount, $1000s (A00200)
taxable_interest	double	yes	Taxable interest amount, $1000s (A00300)
ordinary_dividends	double	yes	Ordinary dividends amount, $1000s (A00600)
business_net_income	double	yes	Business/professional net income, $1000s (A00900)
net_capital_gain	double	yes	Net capital gain amount, $1000s (A01000)
taxable_pensions	double	yes	Taxable pensions and annuities, $1000s (A01700)
unemployment_comp	double	yes	Unemployment compensation, $1000s (A02300)
taxable_social_security	double	yes	Taxable Social Security benefits, $1000s (A02500)
total_itemized_deductions	double	yes	Total itemized deductions, $1000s (A04470)
taxable_income	double	yes	Taxable income, $1000s (A04800)
income_tax_before_credits	double	yes	Income tax before credits, $1000s (A05800)
total_income_tax	double	yes	Total income tax, $1000s (A06500)
total_tax_liability	double	yes	Total tax liability, $1000s (A10300)
eitc_amount	double	yes	Earned income tax credit amount, $1000s (A59660)
elf	long	yes	IRS SOI field ELF, as published
cprep	long	yes	IRS SOI field CPREP, as published
prep	long	yes	IRS SOI field PREP, as published
dir_dep	long	yes	IRS SOI field DIR_DEP, as published
vrtcrind	long	yes	IRS SOI field VRTCRIND, as published
total_vita	long	yes	IRS SOI field TOTAL_VITA, as published
vita	long	yes	IRS SOI field VITA, as published
tce	long	yes	IRS SOI field TCE, as published
vita_eic	long	yes	IRS SOI field VITA_EIC, as published
rac	long	yes	IRS SOI field RAC, as published
n02650	long	yes	IRS SOI field N02650, as published
n00200	long	yes	IRS SOI field N00200, as published
n00300	long	yes	IRS SOI field N00300, as published
n00400	long	yes	IRS SOI field N00400, as published
a00400	double	yes	IRS SOI field A00400, as published
n00600	long	yes	IRS SOI field N00600, as published
n00650	long	yes	IRS SOI field N00650, as published
a00650	double	yes	IRS SOI field A00650, as published
n00700	long	yes	IRS SOI field N00700, as published
a00700	double	yes	IRS SOI field A00700, as published
n00900	long	yes	IRS SOI field N00900, as published
n01000	long	yes	IRS SOI field N01000, as published
n01400	long	yes	IRS SOI field N01400, as published
a01400	double	yes	IRS SOI field A01400, as published
n01700	long	yes	IRS SOI field N01700, as published
schf	long	yes	IRS SOI field SCHF, as published
n02300	long	yes	IRS SOI field N02300, as published
n02500	long	yes	IRS SOI field N02500, as published
n26270	long	yes	IRS SOI field N26270, as published
a26270	double	yes	IRS SOI field A26270, as published
n25870	long	yes	IRS SOI field N25870, as published
a25870	double	yes	IRS SOI field A25870, as published
n02900	long	yes	IRS SOI field N02900, as published
a02900	double	yes	IRS SOI field A02900, as published
n03220	long	yes	IRS SOI field N03220, as published
a03220	double	yes	IRS SOI field A03220, as published
n03300	long	yes	IRS SOI field N03300, as published
a03300	double	yes	IRS SOI field A03300, as published
n03270	long	yes	IRS SOI field N03270, as published
a03270	double	yes	IRS SOI field A03270, as published
n03150	long	yes	IRS SOI field N03150, as published
a03150	double	yes	IRS SOI field A03150, as published
n03210	long	yes	IRS SOI field N03210, as published
a03210	double	yes	IRS SOI field A03210, as published
n04450	long	yes	IRS SOI field N04450, as published
a04450	double	yes	IRS SOI field A04450, as published
n04100	long	yes	IRS SOI field N04100, as published
a04100	double	yes	IRS SOI field A04100, as published
n04200	long	yes	IRS SOI field N04200, as published
a04200	double	yes	IRS SOI field A04200, as published
n04470	long	yes	IRS SOI field N04470, as published
a00101	double	yes	IRS SOI field A00101, as published
n17000	long	yes	IRS SOI field N17000, as published
a17000	double	yes	IRS SOI field A17000, as published
n18425	long	yes	IRS SOI field N18425, as published
a18425	double	yes	IRS SOI field A18425, as published
n18450	long	yes	IRS SOI field N18450, as published
a18450	double	yes	IRS SOI field A18450, as published
n18500	long	yes	IRS SOI field N18500, as published
a18500	double	yes	IRS SOI field A18500, as published
n18800	long	yes	IRS SOI field N18800, as published
a18800	double	yes	IRS SOI field A18800, as published
n18460	long	yes	IRS SOI field N18460, as published
a18460	double	yes	IRS SOI field A18460, as published
n18300	long	yes	IRS SOI field N18300, as published
a18300	double	yes	IRS SOI field A18300, as published
n19300	long	yes	IRS SOI field N19300, as published
a19300	double	yes	IRS SOI field A19300, as published
n19500	long	yes	IRS SOI field N19500, as published
a19500	double	yes	IRS SOI field A19500, as published
n19530	long	yes	IRS SOI field N19530, as published
a19530	double	yes	IRS SOI field A19530, as published
n19570	long	yes	IRS SOI field N19570, as published
a19570	double	yes	IRS SOI field A19570, as published
n19700	long	yes	IRS SOI field N19700, as published
a19700	double	yes	IRS SOI field A19700, as published
n20950	long	yes	IRS SOI field N20950, as published
a20950	double	yes	IRS SOI field A20950, as published
n04475	long	yes	IRS SOI field N04475, as published
a04475	double	yes	IRS SOI field A04475, as published
n04800	long	yes	IRS SOI field N04800, as published
n05800	long	yes	IRS SOI field N05800, as published
n09600	long	yes	IRS SOI field N09600, as published
a09600	double	yes	IRS SOI field A09600, as published
n05780	long	yes	IRS SOI field N05780, as published
a05780	double	yes	IRS SOI field A05780, as published
n07100	long	yes	IRS SOI field N07100, as published
a07100	double	yes	IRS SOI field A07100, as published
n07300	long	yes	IRS SOI field N07300, as published
a07300	double	yes	IRS SOI field A07300, as published
n07180	long	yes	IRS SOI field N07180, as published
a07180	double	yes	IRS SOI field A07180, as published
n07230	long	yes	IRS SOI field N07230, as published
a07230	double	yes	IRS SOI field A07230, as published
n07240	long	yes	IRS SOI field N07240, as published
a07240	double	yes	IRS SOI field A07240, as published
n07225	long	yes	IRS SOI field N07225, as published
a07225	double	yes	IRS SOI field A07225, as published
n07260	long	yes	IRS SOI field N07260, as published
a07260	double	yes	IRS SOI field A07260, as published
n09400	long	yes	IRS SOI field N09400, as published
a09400	double	yes	IRS SOI field A09400, as published
n85770	long	yes	IRS SOI field N85770, as published
a85770	double	yes	IRS SOI field A85770, as published
n85775	long	yes	IRS SOI field N85775, as published
a85775	double	yes	IRS SOI field A85775, as published
n10600	long	yes	IRS SOI field N10600, as published
a10600	double	yes	IRS SOI field A10600, as published
n59660	long	yes	IRS SOI field N59660, as published
n59661	long	yes	IRS SOI field N59661, as published
a59661	double	yes	IRS SOI field A59661, as published
n59662	long	yes	IRS SOI field N59662, as published
a59662	double	yes	IRS SOI field A59662, as published
n59663	long	yes	IRS SOI field N59663, as published
a59663	double	yes	IRS SOI field A59663, as published
n59664	long	yes	IRS SOI field N59664, as published
a59664	double	yes	IRS SOI field A59664, as published
n59720	long	yes	IRS SOI field N59720, as published
a59720	double	yes	IRS SOI field A59720, as published
n11070	long	yes	IRS SOI field N11070, as published
a11070	double	yes	IRS SOI field A11070, as published
n10960	long	yes	IRS SOI field N10960, as published
a10960	double	yes	IRS SOI field A10960, as published
n11560	long	yes	IRS SOI field N11560, as published
a11560	double	yes	IRS SOI field A11560, as published
n06500	long	yes	IRS SOI field N06500, as published
n10300	long	yes	IRS SOI field N10300, as published
n85530	long	yes	IRS SOI field N85530, as published
a85530	double	yes	IRS SOI field A85530, as published
n85300	long	yes	IRS SOI field N85300, as published
a85300	double	yes	IRS SOI field A85300, as published
n11901	long	yes	IRS SOI field N11901, as published
a11901	double	yes	IRS SOI field A11901, as published
n11900	long	yes	IRS SOI field N11900, as published
a11900	double	yes	IRS SOI field A11900, as published
n11902	long	yes	IRS SOI field N11902, as published
a11902	double	yes	IRS SOI field A11902, as published
n12000	long	yes	IRS SOI field N12000, as published
a12000	double	yes	IRS SOI field A12000, as published

soi_income_by_county · table

IRS SOI individual income tax statistics at county grain x AGI size class x tax year (coarser than ZIP, longer history). Joins directly to geo.counties via county_fips. Amounts in $1000s. county_fips ending in 000 (COUNTYFIPS = 000) is the state total (kept; exclude in views that sum counties). Source: irs.gov/pub/irs-soi/{YY}incyallagi.csv.

Column	Type	Null	Description
state_fips	string	yes	2-digit state FIPS code (FK to geo.state_ref)
state_abbr	string	yes	2-letter USPS state code (FK to geo.state_ref)
county_fips	string	no	5-digit county FIPS code (FK to geo.counties)
county_name	string	yes	County name (000 rows carry the state name)
agi_bracket	string	no	SOI AGI size class (agi_stub 1-6)
num_returns	long	yes	Number of returns (N1)
num_individuals	long	yes	Number of individuals / exemptions (N2)
adjusted_gross_income	double	yes	Adjusted gross income, $1000s (A00100)
total_income_amount	double	yes	Total income, $1000s (A02650)
salaries_wages	double	yes	Salaries and wages amount, $1000s (A00200)
business_net_income	double	yes	Business/professional net income, $1000s (A00900)
net_capital_gain	double	yes	Net capital gain amount, $1000s (A01000)
taxable_income	double	yes	Taxable income, $1000s (A04800)
total_income_tax	double	yes	Total income tax, $1000s (A06500)
total_tax_liability	double	yes	Total tax liability, $1000s (A10300)
mars1	long	yes	IRS SOI field mars1, as published
mars2	long	yes	IRS SOI field MARS2, as published
mars4	long	yes	IRS SOI field MARS4, as published
elf	long	yes	IRS SOI field ELF, as published
cprep	long	yes	IRS SOI field CPREP, as published
prep	long	yes	IRS SOI field PREP, as published
dir_dep	long	yes	IRS SOI field DIR_DEP, as published
vrtcrind	long	yes	IRS SOI field VRTCRIND, as published
total_vita	long	yes	IRS SOI field TOTAL_VITA, as published
vita	long	yes	IRS SOI field VITA, as published
tce	long	yes	IRS SOI field TCE, as published
vita_eic	long	yes	IRS SOI field VITA_EIC, as published
rac	long	yes	IRS SOI field RAC, as published
elderly	long	yes	IRS SOI field ELDERLY, as published
n02650	long	yes	IRS SOI field N02650, as published
n00200	long	yes	IRS SOI field N00200, as published
n00300	long	yes	IRS SOI field N00300, as published
a00300	double	yes	IRS SOI field A00300, as published
n00400	long	yes	IRS SOI field N00400, as published
a00400	double	yes	IRS SOI field A00400, as published
n00600	long	yes	IRS SOI field N00600, as published
a00600	double	yes	IRS SOI field A00600, as published
n00650	long	yes	IRS SOI field N00650, as published
a00650	double	yes	IRS SOI field A00650, as published
n00700	long	yes	IRS SOI field N00700, as published
a00700	double	yes	IRS SOI field A00700, as published
n00900	long	yes	IRS SOI field N00900, as published
n01000	long	yes	IRS SOI field N01000, as published
n01400	long	yes	IRS SOI field N01400, as published
a01400	double	yes	IRS SOI field A01400, as published
n01700	long	yes	IRS SOI field N01700, as published
a01700	double	yes	IRS SOI field A01700, as published
schf	long	yes	IRS SOI field SCHF, as published
n02300	long	yes	IRS SOI field N02300, as published
a02300	double	yes	IRS SOI field A02300, as published
n02500	long	yes	IRS SOI field N02500, as published
a02500	double	yes	IRS SOI field A02500, as published
n26270	long	yes	IRS SOI field N26270, as published
a26270	double	yes	IRS SOI field A26270, as published
n25870	long	yes	IRS SOI field N25870, as published
a25870	double	yes	IRS SOI field A25870, as published
n02900	long	yes	IRS SOI field N02900, as published
a02900	double	yes	IRS SOI field A02900, as published
n03220	long	yes	IRS SOI field N03220, as published
a03220	double	yes	IRS SOI field A03220, as published
n03300	long	yes	IRS SOI field N03300, as published
a03300	double	yes	IRS SOI field A03300, as published
n03270	long	yes	IRS SOI field N03270, as published
a03270	double	yes	IRS SOI field A03270, as published
n03150	long	yes	IRS SOI field N03150, as published
a03150	double	yes	IRS SOI field A03150, as published
n03210	long	yes	IRS SOI field N03210, as published
a03210	double	yes	IRS SOI field A03210, as published
n04450	long	yes	IRS SOI field N04450, as published
a04450	double	yes	IRS SOI field A04450, as published
n04100	long	yes	IRS SOI field N04100, as published
a04100	double	yes	IRS SOI field A04100, as published
n04200	long	yes	IRS SOI field N04200, as published
a04200	double	yes	IRS SOI field A04200, as published
n04470	long	yes	IRS SOI field N04470, as published
a04470	double	yes	IRS SOI field A04470, as published
a00101	double	yes	IRS SOI field A00101, as published
n17000	long	yes	IRS SOI field N17000, as published
a17000	double	yes	IRS SOI field A17000, as published
n18425	long	yes	IRS SOI field N18425, as published
a18425	double	yes	IRS SOI field A18425, as published
n18450	long	yes	IRS SOI field N18450, as published
a18450	double	yes	IRS SOI field A18450, as published
n18500	long	yes	IRS SOI field N18500, as published
a18500	double	yes	IRS SOI field A18500, as published
n18800	long	yes	IRS SOI field N18800, as published
a18800	double	yes	IRS SOI field A18800, as published
n18460	long	yes	IRS SOI field N18460, as published
a18460	double	yes	IRS SOI field A18460, as published
n18300	long	yes	IRS SOI field N18300, as published
a18300	double	yes	IRS SOI field A18300, as published
n19300	long	yes	IRS SOI field N19300, as published
a19300	double	yes	IRS SOI field A19300, as published
n19500	long	yes	IRS SOI field N19500, as published
a19500	double	yes	IRS SOI field A19500, as published
n19530	long	yes	IRS SOI field N19530, as published
a19530	double	yes	IRS SOI field A19530, as published
n19570	long	yes	IRS SOI field N19570, as published
a19570	double	yes	IRS SOI field A19570, as published
n19700	long	yes	IRS SOI field N19700, as published
a19700	double	yes	IRS SOI field A19700, as published
n20950	long	yes	IRS SOI field N20950, as published
a20950	double	yes	IRS SOI field A20950, as published
n04475	long	yes	IRS SOI field N04475, as published
a04475	double	yes	IRS SOI field A04475, as published
n04800	long	yes	IRS SOI field N04800, as published
n05800	long	yes	IRS SOI field N05800, as published
a05800	double	yes	IRS SOI field A05800, as published
n09600	long	yes	IRS SOI field N09600, as published
a09600	double	yes	IRS SOI field A09600, as published
n05780	long	yes	IRS SOI field N05780, as published
a05780	double	yes	IRS SOI field A05780, as published
n07100	long	yes	IRS SOI field N07100, as published
a07100	double	yes	IRS SOI field A07100, as published
n07300	long	yes	IRS SOI field N07300, as published
a07300	double	yes	IRS SOI field A07300, as published
n07180	long	yes	IRS SOI field N07180, as published
a07180	double	yes	IRS SOI field A07180, as published
n07230	long	yes	IRS SOI field N07230, as published
a07230	double	yes	IRS SOI field A07230, as published
n07240	long	yes	IRS SOI field N07240, as published
a07240	double	yes	IRS SOI field A07240, as published
n07225	long	yes	IRS SOI field N07225, as published
a07225	double	yes	IRS SOI field A07225, as published
n07260	long	yes	IRS SOI field N07260, as published
a07260	double	yes	IRS SOI field A07260, as published
n09400	long	yes	IRS SOI field N09400, as published
a09400	double	yes	IRS SOI field A09400, as published
n85770	long	yes	IRS SOI field N85770, as published
a85770	double	yes	IRS SOI field A85770, as published
n85775	long	yes	IRS SOI field N85775, as published
a85775	double	yes	IRS SOI field A85775, as published
n10600	long	yes	IRS SOI field N10600, as published
a10600	double	yes	IRS SOI field A10600, as published
n59660	long	yes	IRS SOI field N59660, as published
a59660	double	yes	IRS SOI field A59660, as published
n59661	long	yes	IRS SOI field N59661, as published
a59661	double	yes	IRS SOI field A59661, as published
n59662	long	yes	IRS SOI field N59662, as published
a59662	double	yes	IRS SOI field A59662, as published
n59663	long	yes	IRS SOI field N59663, as published
a59663	double	yes	IRS SOI field A59663, as published
n59664	long	yes	IRS SOI field N59664, as published
a59664	double	yes	IRS SOI field A59664, as published
n59720	long	yes	IRS SOI field N59720, as published
a59720	double	yes	IRS SOI field A59720, as published
n11070	long	yes	IRS SOI field N11070, as published
a11070	double	yes	IRS SOI field A11070, as published
n10960	long	yes	IRS SOI field N10960, as published
a10960	double	yes	IRS SOI field A10960, as published
n11560	long	yes	IRS SOI field N11560, as published
a11560	double	yes	IRS SOI field A11560, as published
n06500	long	yes	IRS SOI field N06500, as published
n10300	long	yes	IRS SOI field N10300, as published
n85530	long	yes	IRS SOI field N85530, as published
a85530	double	yes	IRS SOI field A85530, as published
n85300	long	yes	IRS SOI field N85300, as published
a85300	double	yes	IRS SOI field A85300, as published
n11901	long	yes	IRS SOI field N11901, as published
a11901	double	yes	IRS SOI field A11901, as published
n11900	long	yes	IRS SOI field N11900, as published
a11900	double	yes	IRS SOI field A11900, as published
n11902	long	yes	IRS SOI field N11902, as published
a11902	double	yes	IRS SOI field A11902, as published
n12000	long	yes	IRS SOI field N12000, as published
a12000	double	yes	IRS SOI field A12000, as published

county_migration_flows · table

IRS SOI county-to-county migration derived from year-over-year address changes on returns. One row per origin->destination county for the filing year (from the OUTFLOW file: Y1 = origin, Y2 = destination). Summary/foreign buckets (state FIPS >= 57; county 000) and non-migrant same-county rows are filtered out; suppressed cells (n1 < 0) dropped. Both endpoints join to geo.counties. agi in $1000s. Source: irs.gov/pub/irs-soi/countyoutflow{YYZZ}.csv.

Column	Type	Null	Description
origin_state_fips	string	no	Origin state FIPS (Y1; FK to geo.state_ref)
origin_county_fips	string	no	5-digit origin county FIPS (Y1; FK to geo.counties)
dest_state_fips	string	no	Destination state FIPS (Y2; FK to geo.state_ref)
dest_county_fips	string	no	5-digit destination county FIPS (Y2; FK to geo.counties)
dest_county_name	string	yes	Destination county name
num_returns	long	yes	Returns (households) that moved (n1)
num_individuals	long	yes	Individuals (exemptions) that moved (n2)
agi	double	yes	Aggregate AGI of movers, $1000s

exempt_org_master · table

IRS Exempt Organizations Business Master File — the roster of all registered tax-exempt entities (EIN, name, location, subsection, deductibility, NTEE, latest financials). Cumulative monthly snapshot sharded across four regional files (eo1..eo4), fetched and concatenated in one pass. subsection_code 03 = 501(c)(3); ruling_date/tax_period are YYYYMM. Anchors exempt_org_990 on EIN. Dollar amounts fold the sign into the value. Source: irs.gov/pub/irs-soi/eo{1..4}.csv. To reach this org's cross-schema identity FROM another schema rather than by name, join ref.canonical_org_entity on exempt_org_ein = ein — EIN is a second exact-match hub key alongside GLEIF's LEI (sec.filing_metadata.irs_number matches it directly), so the join is exact. Matching org_name as text finds the wrong entities and misses orgs filing under a differently-worded name.

Column	Type	Null	Description
ein	string	no	Employer Identification Number (9-digit) — PK
org_name	string	yes	Legal name
street	string	yes	Street address
city	string	yes	City
state_abbr	string	yes	2-letter USPS state code (FK to geo.state_ref)
zip_code	string	yes	ZIP code
subsection_code	string	yes	IRC 501(c) subsection (03 = 501(c)(3))
classification_code	string	yes	Classification refinement code
ruling_date	string	yes	IRS ruling date (YYYYMM)
deductibility_code	string	yes	Contribution deductibility (1=deductible, 2=not, 4=by treaty)
foundation_code	string	yes	Foundation status code
organization_code	string	yes	Organization type (1=corp, 2=trust, 3=coop, 4=partnership, 5=assoc)
exempt_status	string	yes	Exemption status (01 = unconditional)
tax_period	string	yes	Latest return tax period (YYYYMM)
asset_amount	double	yes	Total assets (USD)
income_amount	double	yes	Computed income (USD, signed)
revenue_amount	double	yes	Form 990 Part I total revenue (USD, signed)
ntee_code	string	yes	NTEE taxonomy code (first char A-Z = major group)
sort_name	string	yes	Secondary / DBA name line

exempt_org_990 · table

Nonprofit/tax-exempt financials from IRS Form 990 e-file XML — one row per filing (OBJECT_ID) for the IRS receipt year. The provider reads the per-year index CSV, then streams each TEOS XML zip, extracting header identity and Part I summary financials with version-defensive local-name matching across 990 / 990-EZ / 990-PF. NTEE is not in the XML — join to exempt_org_master on ein. Joins to ref/EIN. Source: apps.irs.gov/pub/epostcard/990/xml/{YEAR}/.

Column	Type	Null	Description
ein	string	yes	Filer EIN (9-digit; FK to exempt_org_master.ein)
org_name	string	yes	Organization name (BusinessNameLine1)
return_type	string	yes	Form type (990 / 990EZ / 990PF)
tax_period_end	string	yes	Tax period end date (YYYY-MM-DD)
tax_year	integer	yes	Tax year of the return
total_revenue	double	yes	Total revenue (Part I / current year)
total_expenses	double	yes	Total expenses (Part I / current year)
total_assets	double	yes	Total assets, end of year (Part X)
object_id	string	no	IRS filing object id — PK

usaspending_by_agency · table

Federal obligations summarized by awarding agency x fiscal year, from the USAspending Spending Explorer (type=agency, period=12 == full FY through Sep). Amounts are cumulative obligations. Joins semantically to fedregister.agencies by name. Source: api.usaspending.gov/api/v2/spending/.

Column	Type	Null	Description
agency_code	string	yes	Awarding agency code (Treasury AGENCY code)
agency_name	string	no	Awarding agency name
obligated_amount	double	yes	Total obligations for the fiscal year (USD)

usaspending_by_state · table

Federal spending summarized by place-of-performance state x fiscal year — the spatial counterpart to the SOI revenue tables and one level up from the county-grain usaspending_by_county. Carries obligated_amount (all award types) alongside obligated_amount_excl_loans, which drops award types '07'/'08' (loans): loans report face value at the lender/servicer's place of performance rather than the borrower's, which inflates place-of-performance totals for states with large loan servicers. For per-capita, join census.acs_population rather than a stored figure here. From USAspending spending_by_geography (geo_layer=state, all award types, FY expressed as Oct-Sep). Joins to geo.state_ref via state_abbr. Source: api.usaspending.gov/api/v2/search/spending_by_geography/.

Column	Type	Null	Description
state_abbr	string	no	2-letter USPS state code (FK to geo.state_ref)
state_name	string	yes	State name
obligated_amount	double	yes	Aggregated obligations for the fiscal year (USD), all award types
obligated_amount_excl_loans	double	yes	Aggregated obligations for the fiscal year (USD), excluding loan award types '07' (direct loans) and '08' (guaranteed/insured loans). Loans report face value at the lender/servicer's place of performance rather than the borrower's, which inflates obligated_amount for states with large loan servicers; this figure is a truer measure of spending actually landing in the state. Under the Federal Credit Reform Act, a loan award's obligated amount is its estimated subsidy cost, re-estimated annually, and a downward reestimate or negative subsidy can make a state/year's net loan-type obligation negative; when that happens, this figure is arithmetically larger than obligated_amount, since excluding a negative-valued subset raises the total rather than lowering it.
obligated_amount_excl_loans_excl_cms_admin	double	yes	obligated_amount_excl_loans further excluding CMS (Centers for Medicare and Medicaid Services) funded awards. CMS-funded awards report place of performance at the Medicare Administrative Contractor's location, not the beneficiary's — e.g. Noridian Healthcare Solutions (Fargo, ND) processes Medicare fee-for-service claims nationwide, but every claim geocodes to ND. Confirmed nationwide, not an ND-specific anomaly (other MAC-hosting states show the same effect at FY2023 scale, e.g. Minnesota $165.8B and Indiana $133.4B in CMS-funded place-of-performance alone, both exceeding California's total obligated_amount despite a fraction of the population). This is the truest measure of spending actually landing in the state for states with a large Medicare Administrative Contractor presence.

usaspending_by_county · table

Federal spending summarized by place-of-performance county x fiscal year — the county-grain counterpart to usaspending_by_state and the outlay side of soi_income_by_county. From USAspending spending_by_geography (geo_layer=county, all award types, FY expressed as Oct-Sep); ~2,800 counties per year. Joins to geo.counties via county_fips. Award types span contracts, IDVs, grants, direct payments and loans, so totals are wider than a contracts-only USAspending screen — filter in SQL if you need the A/B/C/D contract slice alone. Carries obligated_amount_excl_loans alongside obligated_amount because loans (award types '07'/'08') report face value at the lender/servicer's place of performance rather than the borrower's. For per-capita, join census.acs_population rather than using USAspending's own population reference, which is 2020 decennial. Source: api.usaspending.gov/api/v2/search/spending_by_geography/.

Column	Type	Null	Description
county_fips	string	no	5-digit county FIPS code (FK to geo.counties)
county_name	string	yes	County name as reported by USAspending (no state qualifier)
obligated_amount	double	yes	Aggregated obligations for the fiscal year (USD), all award types
obligated_amount_excl_loans	double	yes	Aggregated obligations for the fiscal year (USD), excluding loan award types '07' (direct loans) and '08' (guaranteed/insured loans). Loans report face value at the lender/servicer's place of performance rather than the borrower's, which inflates obligated_amount for counties with large loan servicers; this figure is a truer measure of spending actually landing in the county. Under the Federal Credit Reform Act, a loan award's obligated amount is its estimated subsidy cost, re-estimated annually, and a downward reestimate or negative subsidy can make a county/year's net loan-type obligation negative; when that happens, this figure is arithmetically larger than obligated_amount, since excluding a negative-valued subset raises the total rather than lowering it.
obligated_amount_excl_loans_excl_cms_admin	double	yes	obligated_amount_excl_loans further excluding CMS (Centers for Medicare and Medicaid Services) funded awards — matches usaspending_by_state's column of the same name; see that table's comment for the nationwide evidence. CMS-funded awards report place of performance at the Medicare Administrative Contractor's location, not the beneficiary's, which concentrates Medicare claims spending into the specific counties hosting MAC contractors' offices.

usaspending_by_district · table

Federal spending summarized by place-of-performance congressional district x fiscal year — the district-grain counterpart to usaspending_by_state and usaspending_by_county. From USAspending spending_by_geography (geo_layer=district, all award types, FY expressed as Oct-Sep); one call per fiscal year returns all 442 districts in a single unpaginated response (435 numbered districts, at-large single-district states as district "00", and non-voting delegate districts for DC/PR/GU/VI/AS/MP as district "98"). Carries obligated_amount (all award types) alongside obligated_amount_excl_loans and obligated_amount_excl_loans_excl_cms_admin for the same reasons as usaspending_by_state/usaspending_by_county — see those tables' comments for the nationwide evidence. obligated_amount_contracts isolates procurement contract/IDV awards specifically (D-278), since obligated_amount alone mixes contracts with grants, loans, and direct payments indistinguishably. cd_fips matches geo.congressional_districts.cd_fips exactly (state FIPS + district number), no crosswalk needed. Does NOT carry a recipient/contractor-name dimension — USAspending's award-level search (spending_by_award, already used by UsaSpendingAwardSearchProvider for the broadband_*_awards tables) is paginated per-award rather than pre-aggregated, so a full recipient-concentration table across all 442 districts is a materially heavier build, deliberately left as a separate follow-up rather than folded into this one. For per-capita, join census.acs_population rather than a stored figure here. Joins to geo.congressional_districts via cd_fips. Source: api.usaspending.gov/api/v2/search/spending_by_geography/.

Column	Type	Null	Description
cd_fips	string	no	4-digit congressional district code (state FIPS + district number), matches geo.congressional_districts.cd_fips
state_abbr	string	yes	2-letter USPS state abbreviation, decoded from USAspending's display_name
district_number	string	yes	District number as text (e.g. "04"); "00" means an at-large single-district state, "98" means a non-voting delegate district (DC, Puerto Rico, Guam, U.S. Virgin Islands, American Samoa, Northern Mariana Islands)
cd_name	string	yes	USAspending's own district label, e.g. "PA-04"
obligated_amount	double	yes	Aggregated obligations for the fiscal year (USD), all award types
obligated_amount_excl_loans	double	yes	Aggregated obligations for the fiscal year (USD), excluding loan award types '07' (direct loans) and '08' (guaranteed/insured loans). Loans report face value at the lender/servicer's place of performance rather than the borrower's, which inflates obligated_amount for districts with large loan servicers; this figure is a truer measure of spending actually landing in the district. Under the Federal Credit Reform Act, a loan award's obligated amount is its estimated subsidy cost, re-estimated annually, and a downward reestimate or negative subsidy can make a district/year's net loan-type obligation negative; when that happens, this figure is arithmetically larger than obligated_amount, since excluding a negative-valued subset raises the total rather than lowering it.
obligated_amount_excl_loans_excl_cms_admin	double	yes	obligated_amount_excl_loans further excluding CMS (Centers for Medicare and Medicaid Services) funded awards — matches usaspending_by_state/ usaspending_by_county's column of the same name; see those tables' comments for the nationwide evidence. CMS-funded awards report place of performance at the Medicare Administrative Contractor's location, not the beneficiary's, which concentrates Medicare claims spending into whichever district hosts a MAC contractor's office.
obligated_amount_contracts	double	yes	Obligations from procurement contract/IDV award types only (codes A, B, C, D, IDV_A-E) — the actual "federal contract spending" figure (D-278). obligated_amount above is ALL award types combined (contracts + grants + loans + direct payments + other assistance) and is not a contract-spending figure by itself; a caller who specifically means contracts should use this column, not filter/derive from obligated_amount.

usaspending_recipients_by_district · table

Top 100 recipients by obligated dollar amount for each place-of-performance congressional district x fiscal year — the recipient/contractor-name counterpart to usaspending_by_district, deferred from that table's build as "materially heavier" until a cheaper mechanism was confirmed live: USAspending's spending_by_category/recipient endpoint accepts a place_of_performance_locations filter shaped {country, state, district_current} and returns a server-side pre-aggregated, pre-ranked (descending by amount) recipient list in a single unpaginated call per district/year -- no per-award pagination needed, unlike UsaSpendingAwardSearchProvider's mechanism for the broadband_*_awards tables. SCOPE: exactly the top 100 recipients per district per year, matching the API's own page-size ceiling (limit=100 is the max; 500 is rejected as "above max '100'") -- not an attempt at every recipient in a district. A district with concentrated spending is fully captured; one with many small, dispersed recipients below the 100th rank is not. An aggregate "MULTIPLE RECIPIENTS" row (null recipient_id/uei/duns) appears in most districts for sub-reporting- threshold transactions USAspending itself buckets together, and is kept as a real ranked row. recipient_duns is the endpoint's "code" field (9-digit legacy DUNS-shaped identifier); recipient_uei is USAspending's own UEI when known; recipient_id is USAspending's internal recipient hash (present even when DUNS/UEI are not, except for the MULTIPLE RECIPIENTS aggregate). cd_fips matches geo.congressional_districts.cd_fips exactly, decoded the same way as usaspending_by_district. YEAR SCOPE: defaults to the 2 most recent complete fiscal years (usaspending_recipient_district_year_range), narrower than usaspending_by_district's 2018-present default -- this table makes one API call per (year, district) versus that table's 3 calls per year total, so a full 2018-present backfill is ~442x heavier; GOVDATA_START_YEAR overrides for a wider backfill when wanted. Joins to geo.congressional_districts via cd_fips. Source: api.usaspending.gov/api/v2/search/spending_by_category/recipient/ (plus one spending_by_geography call per year to enumerate current districts, matching usaspending_by_district's own district list rather than a hard-coded one).

Column	Type	Null	Description
cd_fips	string	no	4-digit congressional district code (state FIPS + district number), matches geo.congressional_districts.cd_fips
state_abbr	string	yes	2-letter USPS state abbreviation, decoded from USAspending's display_name
district_number	string	yes	District number as text (e.g. "04"); "00" means an at-large single-district state, "98" means a non-voting delegate district (DC, Puerto Rico, Guam, U.S. Virgin Islands, American Samoa, Northern Mariana Islands)
rank	integer	no	Position (1-100) in USAspending's own descending-by-amount recipient ranking for this district/year
recipient_name	string	yes	Recipient/contractor name as reported by USAspending; "MULTIPLE RECIPIENTS" is USAspending's own aggregate bucket for sub-reporting-threshold transactions. Check is_aggregate_recipient rather than string-matching this column directly (D-280) — a naive ORDER BY obligated_amount DESC per district otherwise returns this bucket at rank 1 for the largest-total districts, reading as a single dominant contractor when it is really an aggregation artifact.
recipient_id	string	yes	USAspending's internal recipient hash identifier (e.g. "edc402f7-...-C"); null for the MULTIPLE RECIPIENTS aggregate
recipient_uei	string	yes	SAM.gov Unique Entity Identifier (12-character alphanumeric), when known
recipient_duns	string	yes	Legacy 9-digit DUNS-shaped identifier (USAspending's "code" field), when known
is_aggregate_recipient	boolean	no	True when recipient_name is USAspending's "MULTIPLE RECIPIENTS" aggregate bucket rather than a real, individually-identified recipient (D-280). Filter this out (or handle separately) before ranking recipients by obligated_amount.
obligated_amount	double	yes	Aggregated obligations for this recipient in this district/fiscal year (USD), all award types

entitlement_spending_by_state · table

Social Security and Medicare spending by place-of-performance state x fiscal year, isolated by CFDA/assistance-listing number rather than derived from usaspending_by_state's award-type totals — the entitlement-program counterpart to that table and to ssa_benefits_by_geography (which carries SSA's own county-grain administrative beneficiary/program detail but lags further due to its Wayback-archive dependency). One row per state x program x fiscal year: social_security_retirement (CFDA 96.002, SSA Old-Age and Survivors Insurance), medicare_hospital_insurance (CFDA 93.773, Medicare Part A), medicare_supplementary_medical_insurance (CFDA 93.774, Medicare Part B) — one POST per program per fiscal year against filters.program_numbers, same endpoint and geo_layer=state scope as usaspending_by_state. Social Security's per-state totals reflect the beneficiary's actual state. The two Medicare programs inherit the same place-of-performance distortion documented on usaspending_by_state's obligated_amount_excl_loans_excl_cms_admin column: CMS-funded awards report place of performance at the Medicare Administrative Contractor's location, not the beneficiary's, so states hosting a MAC (e.g. ND/Noridian, MN, IN, KY, PA, SC, CT) show implausibly large totals relative to population — confirmed live at FY2025 scale for both Medicare programs here. Not an import defect; use ssa_benefits_by_geography or a per-capita/population -normalized read for a beneficiary-state view of Medicare spending. Joins to geo.state_ref via state_abbr. Source: api.usaspending.gov/api/v2/search/spending_by_geography/.

Column	Type	Null	Description
state_abbr	string	no	2-letter USPS state code (FK to geo.state_ref)
state_name	string	yes	State name
program	string	no	social_security_retirement (CFDA 96.002), medicare_hospital_insurance (CFDA 93.773), or medicare_supplementary_medical_insurance (CFDA 93.774)
cfda_number	string	no	CFDA / assistance-listing number the row's program_numbers filter used
obligated_amount	double	yes	Aggregated obligations for the fiscal year (USD) for this program, place of performance. See table comment for the Medicare Administrative Contractor place-of-performance caveat.

sba_loan_approvals · table

Small Business Administration 7(a) and 504 loan approvals (FOIA public data), one row per approved loan — federal credit activity by borrower geography, industry, lender, and approval fiscal year. Fanned out by program; each program is split across several year-range CSVs on the source site (year is a data column, not a partition), all streamed and concatenated by SbaLoansProvider. Reissued quarterly, so the whole program partition is overwritten each run. Joins to geo.state_ref via borrower_state. Source: data.sba.gov 7(a)-504 FOIA. borrower_name and lender_name are unstructured — this table carries no EIN column, so there is no exact hub-key join. ref.canonical_org_entity carries each as an already-resolved identity (sba_borrower_name, sba_lender_name, each with an LEI when matched); join there on the name rather than re-running a fuzzy match against gleif_entities.legal_name by hand.

Column	Type	Null	Description
borrower_name	string	yes	Borrower business name — resolved identity at ref.canonical_org_entity.sba_borrower_name
borrower_city	string	yes	Borrower city
borrower_state	string	yes	Borrower state (USPS abbr; FK to geo.state_ref)
borrower_zip	string	yes	Borrower ZIP
gross_approval	double	yes	Gross approved amount (USD)
sba_guaranteed	double	yes	SBA-guaranteed portion (USD)
approval_date	string	yes	Approval date
year	integer	yes	Approval fiscal year (ApprovalFY)
delivery_method	string	yes	SBA delivery/processing method (ProcessingMethod)
naics_code	string	yes	Borrower NAICS industry code
naics_description	string	yes	NAICS industry description
project_county	string	yes	Project county name
project_state	string	yes	Project state (USPS abbr)
business_type	string	yes	Business type (corporation / individual / partnership)
loan_status	string	yes	Loan status (approved / paid in full / charged off / ...)
jobs_supported	integer	yes	Reported jobs supported
lender_name	string	yes	Approving lender (bank for 7(a); third-party lender for 504)

broadband_reconnect_awards · table

USDA ReConnect (CFDA 10.752) award-level broadband infrastructure grants and loan-grant combinations — one row per award, with recipient, dollar amount, performance period, and recipient location (county-grain via county_fips). A full crawl each run (not year-partitioned): the award-search endpoint has no reliable date-filter parameter that matches how award records are dated (obligation vs. performance-start vary by award), and the whole result set is small enough (low hundreds of awards) that a full refresh is cheap. Closes the ReConnect third of the "federal broadband award geography" sourcing gap — BEAD is the sibling broadband_bead_state_allocations table (state-grain only); RDOF/CAF-II are FCC/USAC-administered, not CFDA awards, so they aren't reachable through this endpoint at all (CAF-II location data is instead sourced from USAC's own open-data platform, see broadband_caf_deployment_locations). Source: api.usaspending.gov/api/v2/search/spending_by_award/ (program_numbers filter, award types 02/03/04/05 — grants only, no contracts/loans in this program).

Column	Type	Null	Description
cfda_number	string	no	CFDA/Assistance Listing number (10.752 = USDA ReConnect)
award_id	string	no	USAspending award identifier (PK)
recipient_name	string	yes	Award recipient (utility, cooperative, municipality, tribal entity, etc.)
award_amount	double	yes	Award amount (USD)
start_date	string	yes	Period-of-performance start date (YYYY-MM-DD)
end_date	string	yes	Period-of-performance end date (YYYY-MM-DD)
awarding_agency	string	yes	Awarding federal agency (USDA Rural Utilities Service)
description	string	yes	Award description as filed by the awarding agency
state_abbr	string	yes	2-letter USPS state code (FK to geo.state_ref)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
county_name	string	yes	Recipient county name as reported by USAspending
city_name	string	yes	Recipient city
zip5	string	yes	Recipient 5-digit ZIP

broadband_arra_awards · table

ARRA (American Recovery and Reinvestment Act, 2009) broadband stimulus award-level grants — one row per award, with recipient, dollar amount, performance period, and recipient location (county-grain via county_fips). Covers both ARRA broadband programs: NTIA's Broadband Technology Opportunities Program (BTOP, CFDA 11.557, middle-mile/institutional/public-computing infrastructure) and RUS's Broadband Initiatives Program (BIP, CFDA 10.855, last-mile rural infrastructure). Verified live 2026-09-11: real awards under both programs (e.g. BTOP's EAGLE-NET ALLIANCE, $100.6M, Broomfield County CO; BIP awards to rural school districts and health clinics). Closes the ARRA third of the "federal broadband award geography" sourcing gap alongside broadband_reconnect_awards (ReConnect) and broadband_bead_state_allocations (BEAD, state-grain only); RDOF/CAF-II remain FCC/USAC-administered, not CFDA awards — see broadband_high_cost_disbursements (carrier/state grain — RDOF has no address-level source publicly available) and broadband_caf_deployment_locations. A one-time, closed-out 2009-2011 program: full crawl each run (not year-partitioned), whole result set small enough (low hundreds of awards across both CFDA numbers) that a full refresh is cheap. Source: api.usaspending.gov/api/v2/search/spending_by_award/ (program_numbers filter, award types 02/03/04/05 — grants only, no contracts/loans in either program).

Column	Type	Null	Description
cfda_number	string	no	CFDA/Assistance Listing number (11.557 = NTIA BTOP, 10.855 = RUS BIP)
award_id	string	no	USAspending award identifier (PK)
recipient_name	string	yes	Award recipient (utility, cooperative, municipality, tribal entity, health/education institution, etc.)
award_amount	double	yes	Award amount (USD)
start_date	string	yes	Period-of-performance start date (YYYY-MM-DD)
end_date	string	yes	Period-of-performance end date (YYYY-MM-DD)
awarding_agency	string	yes	Awarding federal agency (Department of Commerce/NTIA for BTOP, USDA Rural Utilities Service for BIP)
description	string	yes	Award description as filed by the awarding agency
state_abbr	string	yes	2-letter USPS state code (FK to geo.state_ref)
county_fips	string	yes	5-digit county FIPS code (FK to geo.counties)
county_name	string	yes	Recipient county name as reported by USAspending
city_name	string	yes	Recipient city
zip5	string	yes	Recipient 5-digit ZIP

broadband_bead_state_allocations · table

NTIA Broadband Equity, Access, and Deployment program (CFDA 11.035) prime grant awards — one row per state/territory broadband office, since BEAD's federal award record is state-grain only: each state gets one lump-sum grant which it then sub-awards to individual ISPs by county/project area, and those sub-awards are not themselves federal-award records reachable from this endpoint (confirmed live: this table has on the order of 56 rows, one per state/territory/DC, not the thousands a county-level table would have). Useful for state-level BEAD allocation totals; does NOT close the county-grain half of the "federal broadband award geography" sourcing gap — see broadband_reconnect_awards and broadband_caf_deployment_locations for the two programs with real sub-state grain. Source: api.usaspending.gov/api/v2/search/spending_by_award/ (program_numbers filter, award types 02/03/04/05).

Column	Type	Null	Description
cfda_number	string	no	CFDA/Assistance Listing number (11.035 = NTIA BEAD)
award_id	string	no	USAspending award identifier (PK)
recipient_name	string	yes	Award recipient (state/territory broadband office or commission)
award_amount	double	yes	Award amount (USD)
start_date	string	yes	Period-of-performance start date (YYYY-MM-DD)
end_date	string	yes	Period-of-performance end date (YYYY-MM-DD)
awarding_agency	string	yes	Awarding federal agency (NTIA)
description	string	yes	Award description as filed by NTIA
state_abbr	string	yes	2-letter USPS state code (FK to geo.state_ref)
city_name	string	yes	Recipient (state broadband office) city
zip5	string	yes	Recipient 5-digit ZIP

broadband_caf_deployment_locations · table

FCC Connect America Fund Phase II (CAF-II) subsidized broadband buildout — one row per deployed location (near-address grain: lat/long, street address, and 15-digit census_block, whose first 5 digits are the county FIPS), filed annually by the carrier as it completes buildout milestones. Sourced from USAC's own open-data platform (opendata.usac.org, dataset r59r-rpip — the same underlying "CAF Map" data usaspending itself can't reach, since CAF-II is an FCC/USAC Universal Service Fund disbursement, not a CFDA federal-assistance award), filtered to fund_type IN ('CAF II', 'CAF II Auc') — this dataset also carries ACAM/ACAM II/CAF-BLS/RBE/AK Plan rows for other high-cost programs, out of scope here. RDOF rows do not yet appear in this feed (RDOF winners are still mid-buildout under a 10-year plan; only carrier-level disbursement/location-count totals exist for RDOF today, not individual addresses) — closes the CAF-II third of the "federal broadband award geography" sourcing gap, not RDOF. ~4.8M rows across 2015-2025 (2016-2021 the heaviest reporting years). Joins to geo.counties by taking the first 5 digits of census_block.

Column	Type	Null	Description
fund_type	string	yes	'CAF II' (annual model-based support) or 'CAF II Auc' (Auction 903 support)
study_area_code	string	yes	Carrier's FCC study area code (operating territory identifier)
holding_company	string	yes	Parent holding company
carrier	string	yes	Reporting carrier/operating company name
latitude	double	yes	Deployed location latitude
longitude	double	yes	Deployed location longitude
deployment_address	string	yes	Street address of the deployed location
deployment_city	string	yes
deployment_state	string	yes	2-letter USPS state code (FK to geo.state_ref)
deployment_zip_code	string	yes
deployment_date	string	yes	Date the location was reported as built out (may be a filing-period marker, not a literal construction date)
census_block	string	yes	15-digit Census block GEOID; first 5 digits are the county FIPS (FK to geo.counties)
locations_deployed	integer	yes	Location count for this row (almost always 1; occasionally >1 for a multi-unit filing)
speed_tier	string	yes	Reported speed tier (e.g. '25 Mbps/3 Mbps')
technology	string	yes
filing_year	integer	yes	Year the carrier filed this deployment record

broadband_high_cost_disbursements · table

Federal Universal Service Fund high-cost-program disbursements at Eligible Telecommunication Carrier (ETC) x month grain, from USAC's own open-data platform (opendata.usac.org, dataset w6qn-gx72). Covers every current-generation high-cost program's dollar flows to individual carriers by month: RDOF, CAF-II (annual + Auction 903), ACAM/ACAM-II, CAF-BLS (Broadband Loop Support), the Alaska Plan, RBE (Rural Broadband Experiments), and legacy programs (HCL, HCM, ICLS, IAS, Mobility I, LSS, SNA) still paying out on prior obligations. Together with fiscal.broadband_caf_deployment_locations (CAF-II served addresses), fiscal.broadband_reconnect_awards (USDA ReConnect), and fiscal.broadband_bead_state_allocations (NTIA BEAD) this is the fourth broadband-program table — carrier/state grain, not the county/tract grain the "federal broadband award geography" gap originally asked for (RDOF has no address-level source publicly available today; winners are still mid-buildout under a 10-year plan). Joins to sibling broadband tables by study_area_code. Source: opendata.usac.org/resource/w6qn-gx72.json.

Column	Type	Null	Description
form_498_id	string	yes	Carrier's USAC Form 498 filer identifier (payee identifier). NULL on genuine zero-disbursement rows for a study area with no filed Form 498 that period (amount_disbursed=0 on every such row) — study_area_code, not this column, is the identifier guaranteed present on every row.
study_area_code	string	yes	FCC study area code (join key to broadband_caf_deployment_locations.study_area_code)
study_area_name	string	yes	Study area / operating company name
state	string	yes	2-letter USPS state code (FK to geo.state_ref)
disbursement_year	integer	yes	Calendar year of disbursement (matches partition year; kept as a real column for filtering across years)
disbursement_month	integer	yes	Calendar month of disbursement (1-12)
fund_type	string	yes	USF high-cost program code. Current-generation broadband programs: RDOF (Rural Digital Opportunity Fund), CAFII (CAF Phase II annual model-based), CAFII AUC (CAF Phase II Auction 903), ACAM / ACAMII / EACAM (Alternative Connect America Cost Model), CAF-BLS/BLS (Broadband Loop Support), AK PLAN (Alaska Plan), RBE (Rural Broadband Experiments). Legacy programs still paying out: HCL/HCM/ICC/ICLS/IAS/IS/LSS/Mobility I/SNA/SVS/FHCS/CACM. Also PR Fixed/PR Mobile/USVI Fixed/USVI Mobile for the territories.
amount_disbursed	double	yes	Monthly disbursement to this carrier under this program (USD)

ssa_benefits_by_geography · table

SSA OASDI and SSI beneficiary counts and total benefit dollars by county x program x benefit type x year — the largest federal mandatory-spending stream, geo-keyed and comparable per county against soi_income_by_county and usaspending_by_state. SSA publishes only multi-sheet XLSX reports on a WAF-protected host that 403s non-browser clients, so the provider fetches the Wayback-archived workbook (oasdi_sc / ssi_sc) and parses the per-state county tables (Table 4 counts + Table 5 dollars for OASDI; Table 3 for SSI). The ANSI Code column carries the county FIPS directly. beneficiary_type is a mutually-exclusive published category (OASDI leaves sum to 'total'). Amounts are the December total monthly benefits, in whole USD; avg_monthly is per beneficiary. SSI per-category dollars are not published (only the total).

Column	Type	Null	Description
state_fips	string	no	2-digit state FIPS code (FK to geo.state_ref)
state_name	string	yes	State name (from the workbook tab)
county_fips	string	no	5-digit county FIPS code (FK to geo.counties)
county_name	string	yes	County name
program	string	no	OASDI (Old-Age/Survivors/Disability) or SSI
beneficiary_type	string	no	Published category — OASDI: total, retired_workers, retired_spouses, retired_children, survivors_widows_parents, survivors_children, disabled_workers, disabled_spouses, disabled_children (leaves sum to total); SSI: total, aged, blind_and_disabled
num_beneficiaries	long	yes	Number of beneficiaries in current-payment status (December)
total_benefits_usd	double	yes	Total December monthly benefits, USD (null for SSI non-total types)
avg_monthly_benefit_usd	double	yes	Average monthly benefit per beneficiary, USD (derived)

ssa_benefits_by_geography_acs · table

County-level Social Security and SSI recipiency and dollars DERIVED from the Census ACS 5-year estimates — a fully open-API, WAF-free complement to the Wayback-sourced ssa_benefits_by_geography (which alone carries the SSA administrative beneficiary-type/program detail). One row per county x ACS vintage. These are SURVEY ESTIMATES on a HOUSEHOLD basis (not SSA administrative person counts): they cannot split OASI vs DI or beneficiary type, but give the geographic distribution of Social Security / SSI receipt and aggregate dollars with no archive.org dependency. Aggregate-dollar figures are survey-reported and run below SSA administrative totals. Source: api.census.gov/data/{year}/acs/acs5 (B19055/B19056/B19065/B19066).

Column	Type	Null	Description
state_fips	string	no	2-digit state FIPS (FK to geo.state_ref)
county_fips	string	no	5-digit county FIPS (FK to geo.counties)
county_name	string	yes	County name (ACS NAME field)
total_households	long	yes	Total households (ACS B19055_001E)
households_with_social_security	long	yes	Households with Social Security income (ACS B19055_002E)
households_with_ssi	long	yes	Households with Supplemental Security Income (ACS B19056_002E)
aggregate_social_security_usd	double	yes	Aggregate Social Security income, USD (ACS B19065_001E)
aggregate_ssi_usd	double	yes	Aggregate Supplemental Security Income, USD (ACS B19066_001E)

snap_benefits_by_geography · table

SNAP (Supplemental Nutrition Assistance Program) persons/households participating and total/average monthly benefits, by state x year x month — the nutrition- assistance counterpart to ssa_benefits_by_geography's income-benefits data, both geo-keyed and comparable against usaspending_by_state. USDA FNA publishes one zip covering FY1989-present (one workbook per fiscal year inside), not a per-year URL, so this table has no year fetch dimension — the provider parses the full history from one download and materialize partitions by the year column found in each row, same as fiscal's other single-workbook bulk sources. Excludes P-EBT (Pandemic EBT) figures, which USDA reports separately.

Column	Type	Null	Description
state_fips	string	no	2-digit state FIPS code (FK to geo.state_ref)
state_abbr	string	yes	2-letter USPS state code (FK to geo.state_ref)
state_name	string	yes	State name, exactly as the workbook's block-header row spells it
year	integer	no	Federal fiscal year (Oct-Sep)
month	integer	yes	Calendar month (1-12); null for the per-state fiscal-year Total row
persons_participating	long	yes	Average monthly persons receiving SNAP benefits
households_participating	long	yes	Average monthly households receiving SNAP benefits
total_benefits_usd	double	yes	Total SNAP benefits issued, USD
avg_monthly_benefit_per_person_usd	double	yes	Average monthly benefit per person, USD
avg_monthly_benefit_per_household_usd	double	yes	Average monthly benefit per household, USD

govt_finance_by_unit · table

State & local government finance from Census's annual "Individual Unit File" — one row per government unit x finance item code x year (long/tall layout: amount_thousands is the single fact, item_code the dimension). The landing page (census.gov/data/datasets/{year}/econ/local/public-use-datasets.html) is read by GovtFinanceProvider, which discovers and streams the actual ZIP link (filename is neither year-templatable nor stable — confirmed live: 2023_Individual_Unit_Files.zip vs 2021_Individual_Unit_File.zip, singular/plural and casing both vary), then streams the fixed-width data file inside it (records are 32 chars for 2017+ and 34 for 2012-2016, whose ID field is two characters wider). item_code follows the Census government finance classification manual (2006_classification_manual.pdf); notable codes for capital-vs-operating comparisons: E12/F12 (elementary- secondary education current operations / construction), E36/F36 (hospitals current operations / construction), E61/F61 (parks & recreation current operations / construction). There is no single item code for "Total Taxes" — T01 is Property Tax specifically (the classification manual's largest single tax category, not an aggregate); a total-taxes figure for a unit or state requires summing every T-prefixed row (T01, T09-T16, T19-T29, T40, T41, T50, T51, T53, T99) grouped by (state_fips, year), or by (state_fips, gov_type_code, year) for a level breakdown — summing T01 alone understates total tax revenue by an amount that depends entirely on how heavily that state's governments rely on property tax versus sales/income/other taxes. gov_type_code: 0=state, 1=county, 2=city, 3=township, 4=special district, 5=independent school district (3rd digit of the source ID field). unit_id is that government's unique identifier within (state_fips, gov_type_code, county_fips) — not globally unique alone. county_fips is the county OR county-type area the unit is located in; for state-level rows (gov_type_code=0) this is not a valid geo.counties FIPS. amount_thousands is in THOUSANDS of dollars and may be negative (debt retirements, etc.). This is a stratified sample survey of government units (large governments fully enumerated, smaller ones sampled) even in Census-of-Governments years (2012/2017/2022) — a raw per-state sum over the units present in this table is not guaranteed to equal that state's true total, and for at least some small states this file's own item-code totals (verified against Census's own bundled state-level crosstab file, e.g. NNstatetypepu.txt) are implausible against independently published state totals; treat any state-level dollar aggregate from this table as needing cross-validation against an external published figure before use. Source (per year): census.gov/data/datasets/{year}/econ/local/public-use-datasets.html.

Column	Type	Null	Description
state_fips	string	no	2-digit state FIPS code (FK to geo.state_ref)
gov_type_code	string	no	3rd digit of source ID (0=state,1=county,2=city,3=township,4=special district,5=school district)
gov_type_name	string	yes	Decoded gov_type_code label
county_fips	string	no	County or county-type area FIPS the unit is located in (source ID positions 4-6). NOT a valid geo.counties key for state-level rows (gov_type_code=0); see the FK comment below.
unit_id	string	no	Government unit identifier (source ID positions 7-12), unique within (state_fips, gov_type_code, county_fips)
item_code	string	no	Finance item code (Census government finance classification manual), e.g. E12/F12 education, E36/F36 hospitals, E61/F61 parks & recreation
amount_thousands	long	yes	Amount for this item code, in thousands of dollars (may be negative)
imputation_flag	string	yes	Imputation type / item data flag (source position 32)

state_corporate_income_tax_collections · table

Census Bureau Annual Survey of State Government Tax Collections (STC), ITEM_CODE=T41 ("Corporation Net Income Taxes") only — do not confuse with T40 (individual income tax; confirmed live, CA 2024 T40 = $123.1B vs T41 = $41.4B). One row per state x tax year, 50 states + DC (no territories — confirmed live: for=state:72 (Puerto Rico) returns no data). collections_thousands is in THOUSANDS of dollars and is NULL (not zero) for a state with no general corporate net income tax that year — confirmed live for 2024: Texas, Nevada, Washington, and Wyoming are NULL; South Dakota has no broad corporate income tax but its financial-institution franchise tax IS captured under T41 and returns a real value ($60.7M for 2024), so "NULL" means "no T41-classified tax," not "no state tax revenue at all." Source (per state per year): api.census.gov/data/timeseries/govsstatetax — this endpoint's for=state: wildcard is unsupported (confirmed live: every combination of ITEM_CODE filter, get= field list, and for=state: returned HTTP 204 with no body across the published 2016-2025 range), so each state is fetched with an explicit FIPS code. GOVTYPE and AGG_DESC are requested in the URL but not materialized as columns: both are constant for this item code (GOVTYPE=002 state government, AGG_DESC=STC017) and the API silently 204s the whole query if either is dropped from get= alongside an ITEM_CODE filter (confirmed live) — they stay in the URL as required request parameters only.

Column	Type	Null	Description
state_fips	string	no	2-digit state FIPS code (FK to geo.state_ref)
state_name	string	yes	State name, as published (Census STC NAME field)
geo_id	string	yes	Census geography identifier, e.g. 0400000US06 for California
collections_thousands	long	yes	Corporation net income tax (ITEM_CODE=T41) collections for the fiscal year, in THOUSANDS of dollars (Census STC AMOUNT field, label "Amount ($1,000)"). NULL for a state with no T41-classified tax that year (see table comment), not a missing observation.

state_minimum_wage_history · table

DOL Wage and Hour Division's published "Changes in Basic Minimum Wages in Non-Farm Employment Under State Law" table (selected years, 1968 to present) — one row per jurisdiction x published year. The single page (dol.gov/agencies/whd/state/minimum-wage/history) holds six HTML tables spanning disjoint "selected years" (1968-1981, 1988-1998, then every year 2000-present); years NOT shown on the page (e.g. 1969, 1973-1975, 1982-1987) are simply absent from this table, not a gap in ETL coverage. 55 jurisdictions per year: "Federal (FLSA)" (jurisdiction_type=FEDERAL), the 50 states (STATE), District of Columbia (DISTRICT), and Guam/Puerto Rico/U.S. Virgin Islands (TERRITORY — not in geo.state_ref, so state_fips is null for these three and for the FEDERAL row). raw_wage_text preserves DOL's own cell text verbatim, including its inconsistencies ("N.A." vs "NA", inconsistent "$" prefixing, a stray source typo like "4..65(g,,j)") — wage_amount (a clean hourly USD figure) is populated ONLY for the unambiguous single-value case (value_type=SINGLE); ranges (value_type=RANGE, e.g. a tipped-vs-standard spread), dual-track values (value_type=DUAL_TRACK, e.g. the pre-1978 multi-track FLSA system or a state's two-tier law), "..." (NOT_APPLICABLE — no separate jurisdiction law that year), "N.A."/"NA" (NOT_AVAILABLE), and anything else unparseable (OTHER, e.g. a non-hourly "/day" or "/wk" rate) are left null in wage_amount by design, never guessed — see raw_wage_text for the ground truth in every case. increased_from_prior_column reflects DOL's own bolding convention (its key states: "wage rates in bold indicate an increase over the previous year's rate" — the previous YEAR SHOWN, which is not always the previous calendar year before 2000). footnote_codes carries the raw trailing footnote marker(s) from inside a SINGLE cell's "[ ]"/"( )" (DOL's own lettered key on the source page defines them; not reproduced here since the definitions are not per-row data). dol.gov 403s every direct fetch from this build environment (a domain-wide Akamai edge block, confirmed even at the site root) — the same failure mode already solved in this schema for ssa_benefits_by_geography, so StateMinWageTransformer follows that precedent and fetches the page via the Wayback Machine (FiscalHttp.fetchViaWayback) rather than dol.gov directly; source.url below is the nominal/documentary URL, not what is actually requested. DOL updates this page roughly annually (a new year column appended each January); dataset_type=snapshot re-fetches and replaces the whole table on every run since the fetch is a single small page.

Column	Type	Null	Description
year	INTEGER	no
jurisdiction_name	string	no	Raw jurisdiction label as published: "Federal (FLSA)", a state name, "District of Columbia", "Guam", "Puerto Rico", or "U.S. Virgin Islands"
jurisdiction_type	string	no	FEDERAL | STATE | DISTRICT | TERRITORY
state_fips	string	yes	2-digit Census state FIPS (FK to geo.state_ref); populated only for jurisdiction_type STATE/DISTRICT — null for FEDERAL and TERRITORY rows (Guam/Puerto Rico/U.S. Virgin Islands are not in geo.state_ref)
raw_wage_text	string	no	Exact published cell text for this jurisdiction x year, verbatim (see table comment)
wage_amount	double	yes	Parsed hourly wage in USD; populated ONLY when value_type=SINGLE — null for RANGE/DUAL_TRACK/NOT_APPLICABLE/NOT_AVAILABLE/OTHER (never a guessed or truncated number — see raw_wage_text)
value_type	string	no	SINGLE | RANGE | DUAL_TRACK | NOT_APPLICABLE | NOT_AVAILABLE | OTHER — classification of raw_wage_text
increased_from_prior_column	boolean	no	True when DOL bolded this cell (its own "increase over the previous year's rate" indicator)
footnote_codes	string	yes	Raw footnote code(s) from inside a SINGLE cell's trailing "[ ]"/"( )", verbatim (comma-separated when multiple); null otherwise. See DOL's published Key section for definitions (not reproduced here)

soi_income_by_county_year · view

County-level revenue rollup: return counts, individual/exemption counts, adjusted gross income, and total income tax by county and tax year, summed across the six SOI AGI brackets (excludes the county_fips '...000' state-total rows carried in the source soi_income_by_county table).

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

federal_spending_vs_income_tax_by_state_year · view

Places each usaspending_by_state row (state, fiscal year, obligated_amount) beside its same-state, same-year SOI total-income-tax figure summed from the soi_income_by_county state-total ('...000') rows. federal_obligations_usd is TOTAL USAspending obligations across ALL agencies and programs combined — it is NOT broken out by awarding agency, CFDA/program, or budget function, so agency-specific federal spending (e.g. Department of Education, defense) cannot be isolated from this table. soi_income_tax_thousands is IRS SOI INDIVIDUAL INCOME TAX ONLY — it excludes payroll/FICA, corporate, and excise tax paid by the state's residents and businesses (no state-level payroll/FICA or excise tax table exists anywhere in this warehouse yet; state_corporate_income_tax_collections carries corporate income tax but is not yet part of this join). This view is NOT a federal balance-of-payments (total taxes paid vs. total spending received) comparison — subtracting soi_income_tax_thousands from federal_obligations_usd computes spending against one tax type only, not spending against total federal receipts, and will overstate every state's apparent net position.

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

nonprofit_financials · view

One row per Form 990 filing: exempt_org_990's total_revenue, total_expenses, total_assets, return_type (990/990-EZ/990-PF), and tax_year, left-joined to exempt_org_master's org_name (fallback to the filing's own name), state_abbr, ntee_code, and subsection_code on EIN — the nonprofit sector view.

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

state_social_infrastructure_index · view

Social Infrastructure Investment Index (SIII), per state per year, on a 0-100 scale: 100 * (0.30pct_capex + 0.20pct_opex + 0.15pct_beds + 0.15pct_pupil_teacher_inv + 0.10pct_library + 0.10pct_access_inv), where each pct_* is that metric's PERCENT_RANK() among the 50 states + DC for that year (0=lowest, 1=highest) — a relative ranking, not an absolute scale, so a state's score can move year over year purely from other states' movement even if its own raw metric is flat. Weights favor the two direct government fiscal-investment flows (capex+opex, 50% combined) over capacity/access proxies (50% combined), consistent with this being a FISCAL-policy index first. Components (all per-capita against census.acs_population, geography=state): - capex/opex: fiscal.govt_finance_by_unit, item codes F12/F36/F61 (elementary-secondary/hospital/parks construction = capital outlay) and E12/E36/E61 (same three functions, current operations = opex). Summed across ALL gov_type_code (state+county+city+township+special district+school district) within the state — this is a whole-state total, not one government level. - beds: health.cms_pos_facilities, provider_category_code='01' (hospitals only) bed_count summed per state. KNOWN LIMITATION: this source is a CURRENT-QUARTER SNAPSHOT with no year dimension — the same beds-per-capita figure is broadcast across every year of the view rather than reflecting that year's actual bed count. Treat pct_beds as "current hospital capacity relative to other states," not a historical trend. - pupil_teacher_inv: inverted (1 - percent_rank) SUM(enrollment) / SUM(teachers_total_fte) from edu.ccd_districts — inverted because a LOWER pupil-teacher ratio is the better outcome. - library: edu.library_outlets total square_feet per state per year. - access_inv: inverted (1 - percent_rank) average of (no_internet_households / total_households) from census.acs_internet and (no_vehicle_households / total_households) from census.acs_vehicle_access — inverted because a LOWER no-access rate is the better outcome. Both ACS components only exist from their respective minYear forward (acs_internet: 2019+); years before that get a NULL access_gap_pct and therefore NULL pct_access_inv, which COALESCEs out of the weighted sum (see raw_score below) rather than zeroing the state's score. NULL-COMPONENT HANDLING: components missing for a given state/year (COALESCE to NULL, not 0) are dropped from the weighted average, and the denominator is renormalized to the weights of the components that ARE present — a state missing only access data is scored on capex/opex/beds/ pupil_teacher alone (renormalized to sum to 100), not penalized to a near-zero score by treating the missing metric as a 0.

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