💵 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.

38 datasets · 776 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

irs_gross_collections_by_state_year · table

IRS SOI Table 1-5, "Gross Collections, by Type of Tax and State" — one row per state/area per fiscal year, wide across the 11 tax-collection measures the table publishes. Sourced to close the gap federal_spending_vs_income_tax_by_state_year documents explicitly: that table carries individual income tax only, so a balance-of-payments computation built from it alone makes every state look net-positive (confirmed against the NY State Comptroller's official FFY2023 report, whose Rockefeller/OMB methodology finds NJ/MA/WA as real net donors). business_income_taxes (corporate) and excise_taxes here close the biggest part of that gap. Structural IRS limitation — cannot be fixed at ETL time: IRS combines FICA with individual income tax withheld (individual_withheld_and_fica_tax), and SECA with individual income tax payments (individual_payments_and_seca_tax), in this state-level report. A clean payroll/FICA-only-by-state figure is not obtainable from any IRS SOI state-grain publication — this is the same reason federal_spending_vs_income_tax_by_state_year never carried it. The combined columns are still the correct denominator for a balance-of-payments computation (each captures both components), just not separable into payroll-tax-alone. Non-state rows are kept, not dropped, matching soi_income_by_county's kept county-total convention: 'United States, total' (the national aggregate) and three non-state buckets IRS reports separately ('U.S. Armed Service members overseas and Territories other than Puerto Rico', 'Puerto Rico', 'International', 'Undistributed') all get state_abbr NULL — filter WHERE state_abbr IS NOT NULL for a clean 50+DC sum. Source: irs.gov/pub/irs-soi/{YY}dbs01t05co.xlsx (FY2019-2024 combined tax-type workbook; sheet name and filename convention both changed for FY2025 — see the source block). Scoped to FY2019+ (this modern combined-workbook format) rather than IRS's full history back to the 1990s — pre-2019 files are the legacy .xls binary format with a plausibly different layout, unverified and out of scope here.

Column	Type	Null	Description
state_name	string	no	State/area name exactly as published (e.g. 'United States, total', 'Puerto Rico')
state_abbr	string	yes	2-letter USPS state code (FK to geo.state_ref)
fiscal_year	integer	no	Federal fiscal year
total_collections	double	yes	Total Internal Revenue collections, $1000s (includes penalties and interest)
business_income_taxes	double	yes	Corporation income tax (Form 1120 series) + tax-exempt unrelated business income (Form 990-T), $1000s
individual_employment_estate_total	double	yes	Subtotal of individual income tax, employment taxes, and estate/trust income tax, $1000s
individual_withheld_and_fica_tax	double	yes	Individual income tax withheld COMBINED with FICA payroll tax — IRS does not report these separately by state, $1000s
individual_payments_and_seca_tax	double	yes	Individual income tax payments COMBINED with SECA self-employment tax — IRS does not report these separately by state, $1000s
unemployment_insurance_tax	double	yes	Federal unemployment insurance tax (FUTA), $1000s
railroad_retirement_tax	double	yes	Railroad retirement tax, $1000s
estate_and_trust_income_tax	double	yes	Estate and trust income tax, $1000s
estate_tax	double	yes	Estate (inheritance) tax, $1000s
gift_tax	double	yes	Gift tax, $1000s
excise_taxes	double	yes	Federal excise taxes, $1000s

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. Years 2008 onward (2008 = the 2007-2008 file). Coverage is not uniform: IRS lists a county pair only above a disclosure threshold, 10 returns for 2008-2013 (about 78,000-91,000 pairs a year) and about 20 returns from 2014 (about 36,000-60,000 pairs a year), so pair counts and summed flows from 2014 on are not comparable with 2008-2013 and a pre/post comparison of small flows needs the threshold applied to both sides. 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. For full Form 990 returns (return_type 990) it also carries Part I line 15 (salaries, other compensation, employee benefits) and line 16b (fundraising expenses), the Part IX line 25 functional-expense columns (A total, B program service, C management and general, D fundraising), Part IX line 5 officer compensation, and the Part VII Section A compensation columns D/E/F summed over the listed persons; these are null on 990-EZ, 990-PF and 990-T returns, which do not report those parts. Overhead ratio is functional_expenses_mgmt_general + functional_expenses_fundraising over functional_expenses_total. 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}/. Two indexed receipt-2023 filings, 202313409349100901 (Aurora Care Foundation Inc, 990-PF) and 202301929339300110 (Northwestern Memorial Hospital, 990-T), are in no published IRS zip and are absent from the table; the load logs and counts each as known absent and fails on any other indexed filing it cannot find.

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)
salaries_comp_benefits	double	yes	Form 990 Part I line 15, current year: salaries, other compensation, employee benefits (US dollars). Null for 990-EZ, 990-PF and 990-T returns, which have no Part I line 15
fundraising_expenses_16b	double	yes	Form 990 Part I line 16b, current year: total fundraising expenses (US dollars). Null for 990-EZ, 990-PF and 990-T returns
functional_expenses_total	double	yes	Form 990 Part IX line 25 column (A): total functional expenses (US dollars); equals the sum of the three columns below and total_expenses. Null for 990-EZ, 990-PF and 990-T returns
functional_expenses_program	double	yes	Form 990 Part IX line 25 column (B): program service expenses (US dollars). Null for 990-EZ, 990-PF and 990-T returns
functional_expenses_mgmt_general	double	yes	Form 990 Part IX line 25 column (C): management and general expenses (US dollars). Null for 990-EZ, 990-PF and 990-T returns
functional_expenses_fundraising	double	yes	Form 990 Part IX line 25 column (D): fundraising expenses (US dollars). Null for 990-EZ, 990-PF and 990-T returns
officer_comp_part_ix_line5	double	yes	Form 990 Part IX line 5 column (A): compensation of current officers, directors, trustees and key employees (US dollars); the filer-reported officer pay total. Null for 990-EZ, 990-PF and 990-T returns
part_vii_persons_listed	integer	yes	Form 990 Part VII Section A: number of officers, directors, trustees, key employees and highest-compensated employees listed (continuation-sheet persons included as the XML carries them). Null for 990-EZ, 990-PF and 990-T returns
part_vii_comp_from_org	double	yes	Form 990 Part VII Section A column (D) summed over the listed persons: reportable compensation from the organization (W-2 box 5 / 1099-MISC, US dollars). Summed here because the XML has no total element; null when any listed person lacks the amount. Null for 990-EZ, 990-PF and 990-T returns
part_vii_comp_from_related_orgs	double	yes	Form 990 Part VII Section A column (E) summed over the listed persons: reportable compensation from related organizations (US dollars). Null for 990-EZ, 990-PF and 990-T returns
part_vii_other_comp	double	yes	Form 990 Part VII Section A column (F) summed over the listed persons: estimated amount of other compensation from the organization and related organizations (US dollars). Null for 990-EZ, 990-PF and 990-T returns
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. CMS-funded awards are likewise attributed to the Medicare Administrative Contractor's state (North Dakota, Minnesota and Indiana host contractors paying Medicare claims for beneficiaries nationwide), so obligated_amount and obligated_amount_excl_loans are not a measure of spending landing in the state and must not be divided by population to rank states; use obligated_amount_excl_loans_excl_cms_admin for that. 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, by place of performance. Includes loan face value and CMS-funded awards booked at the Medicare Administrative Contractor's state, so it overstates spending received in states hosting large loan servicers or Medicare contractors (ND, MN, IN); for per-capita or cross-state comparison use obligated_amount_excl_loans_excl_cms_admin.
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

usaspending_contract_recipients_by_district · table

Top 100 procurement-contract recipients by obligated dollar amount for each place-of-performance congressional district x fiscal year — the contracts-only counterpart to usaspending_recipients_by_district. The ranking is computed server-side by USAspending's spending_by_category/recipient endpoint over contract and IDV award types only (award_type_codes A, B, C, D, IDV_A-IDV_E; the same set as usaspending_by_district.obligated_amount_contracts), so grant, direct-payment and loan recipients never occupy a rank. Use this table, not usaspending_recipients_by_district, for "which contractors dominate a district" questions; the all-award-types table ranks universities, health systems and benefit administrators alongside contractors. Same columns, same request shape and same top-100 ceiling (the API's page-size maximum) as usaspending_recipients_by_district: a district with concentrated contract spending is fully captured, one with many small dispersed contractors below the 100th rank is not. An aggregate "MULTIPLE RECIPIENTS" row (null recipient_id/uei/duns) is USAspending's own bucket for sub-reporting-threshold transactions; it is kept as a ranked row and flagged by is_aggregate_recipient. 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. cd_fips matches geo.congressional_districts.cd_fips exactly. A district's obligated_amount here is not bounded by usaspending_by_district.obligated_amount_contracts for that district/year: the district total nets every recipient's transactions, including large downward de-obligations from recipients outside this table's top 100, while each row here is one recipient's own gross positive sum. YEAR SCOPE: defaults to the 2 most recent complete fiscal years (usaspending_recipient_district_year_range); one API call per (year, district), so GOVDATA_START_YEAR widens it at ~8 minutes per added year. 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).

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 contracts-only 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), contract and IDV award types only

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). DQ-validated 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 thousands of awards across both CFDA numbers, DQ-confirmed: 241 for 11.557 + 2,454 for 10.855) 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

usaspending_bridge_awards · table

FHWA bridge-specific award-level grants from USAspending — one row per award, with recipient, dollar amount, performance period, and recipient location (county-grain via county_fips; state_abbr supports GROUP BY state totals). Covers the IIJA Bridge Formula Program (CFDA 20.263, formula funds apportioned to every state DOT for bridge replacement/rehabilitation) and the discretionary Bridge Investment Program (CFDA 20.264, competitive large -bridge awards). DQ-validated 2026-09-14: real large bridge projects (e.g. CFDA 20.263's Vincent Thomas Bridge deck replacement in Los Angeles County CA, $152.6M; CFDA 20.264's Brent Spence Bridge Corridor OH/KY, $400M). recipient_name/state_abbr/county_fips are the state DOT's registered address, not necessarily the bridge's physical location — the same convention as this table's sibling USAspending award tables (broadband_reconnect_awards, broadband_arra_awards); the award description usually names the specific bridge/corridor. Closes the program-level counterpart to usaspending_by_state (which carries only an undifferentiated per-state total with no program breakdown): filtering this table to cfda_number = '20.263' isolates Bridge Formula Program dollars per state, the gap this table was built for. 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 result set is small enough (low thousands of awards across both CFDA numbers, DQ-confirmed: 3,380 for 20.263 + 33 for 20.264) that a full refresh is cheap. The Competitive Highway Bridge Program (CFDA 20.267) and Competitive Type 3 Highway Bridge Program (20.198) exist in the CFDA catalog but had zero awarded grants as of 2026-09-14 — not included here; add them if/when they start awarding. 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 (20.263 = Bridge Formula Program, 20.264 = Bridge Investment Program)
award_id	string	no	USAspending award identifier (PK)
recipient_name	string	yes	Award recipient (state DOT for nearly all awards in both programs)
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 Transportation/FHWA for both programs)
description	string	yes	Award description as filed by the awarding agency (typically names the specific bridge/corridor)
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. FY2019 (Oct 2018-Sep 2019) carries a source-published distortion: because of the partial federal government shutdown, most February 2019 benefits were issued in January 2019, so month 1 of FY2019 shows roughly double a normal month's total_benefits_usd and month 2 shows almost none, and the per-state fiscal-year Total row (month IS NULL) averages persons and households over those two months as published, which pulls the FY2019 average persons_participating below the surrounding months.

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

omb_apportionments · table

OMB-approved apportionments (SF-132 schedules) for every federal budget account, one row per schedule line of each approval iteration, by fiscal year FY2022-present. Apportionment is the step where OMB releases appropriated budget authority to an agency by quarter or by project, so this is where withheld, deferred or footnoted-away funds show up: the free-text footnote_text column carries OMB's conditions on the money, including language withholding funds pending review and proposed-rescission or deferral notices. It is the account-level counterpart to mts_outlays_by_function and federal_outlays_by_function, which stop at function totals. Key by (tafs_iteration_id, line_number, line_split); a later iteration of the same account (iteration) restates the whole schedule, so compare iterations rather than summing them. line_number is the SF-132 line (1000 = unobligated balance brought forward, 1100/1200 = appropriations, 1920 = total budgetary resources, 6001-6004 = category A apportioned by quarter, 6190 = total budgetary resources available); approved_amount is in dollars as published. begin_poa/end_poa are the period of availability years and availability_type_code is 'X' for no-year money. A footnote no line references is kept as a row with null line_number. Covers OMB apportionment actions only: GAO Impoundment Control Act decisions and the rescission packages transmitted to Congress are separate publications and are not in this table. Source: apportionment-public.max.gov, the JSON rendition of each posted apportionment.

Column	Type	Null	Description
file_id	long	no	OMB file id of the apportionment document; several accounts can share one file
file_name	string	yes	Source file name, which embeds agency, bureau, TAFS, iteration and approval time
folder	string	yes	Agency folder the document is filed under on the site
approval_timestamp	string	yes	OMB approval time as published (YYYY-MM-DD-HH.MM.SS.micros)
approver_title	string	yes	Title of the OMB official who approved the apportionment
funds_provided_by	string	yes	The appropriation act or authority providing the funds, as published
budget_agency_title	string	yes
budget_bureau_title	string	yes
account_title	string	yes	Treasury account title
cgac_agency	string	yes	3-digit CGAC agency code of the Treasury account
cgac_account	string	yes	4-digit main account code of the Treasury account
allocation_agency_code	string	yes	Agency code of the receiving agency when the account is an allocation
allocation_subaccount	string	yes
begin_poa	string	yes	First year of availability (beginning period of availability)
end_poa	string	yes	Last year of availability (ending period of availability)
availability_type_code	string	yes	'X' for no-year (unlimited) availability; null when begin_poa/end_poa carry the period
iteration	string	yes	Apportionment iteration number for the account this fiscal year
tafs_iteration_id	long	yes	OMB id of the account-iteration; groups all lines of one schedule
line_number	string	yes	SF-132 line number, or a marker such as IterNo/RptCat/AdjAut; null on an unreferenced-footnote row
line_split	string	yes	Line qualifier (for example ME for mandatory-exempt, YES/NO flags)
line_description	string	yes
approved_amount	double	yes	Amount on the line in dollars as published; null on an unreferenced-footnote row
footnote_number	string	yes	Footnote reference on the line, or the footnote's own number on an unreferenced-footnote row
footnote_text	string	yes	OMB's full footnote text, including conditions on or withholding of the funds

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). Local water and sewer utilities: A91 water supply utility revenue (charges billed to customers) and A80 sewerage charges, with E91/E80 the matching operating expenditure and F91/F80 construction; A81/E81 are solid waste. These give what each city, county, township or special district collects and spends on water and sewer service per year (2022: ~20k units report A91) — dollar totals, not per-customer rates or tariffs. For a per-person comparison, divide by the local population (geo/census tables via county_fips; environment.drinking_water has population_served_count per water system but shares no key with government units). For the national trend in water/sewer prices see the CPI series CUUR0000SEHG02 in econ.inflation_metrics. 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. county_fips is a FIPS code for every year: the 2012-2016 files' own ID county field is a Census county sequence number, not FIPS, so GovtFinanceProvider looks up the real FIPS county per unit from the Fin_GID identifier file bundled in the same ZIP. 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; cross-validate a state-level dollar aggregate against Census's own state-level crosstab file (NNstatetypepu.txt, bundled in each year's ZIP) before relying on it. state_fips is a FIPS code for every year: the 2012-2016 files number states with Census's own alphabetical codes (01-51, District of Columbia 09), which GovtFinanceProvider translates. 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 for 2017+; looked up from the same ZIP's Fin_GID file for 2012-2016, whose ID field carries a Census county sequence number instead). 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)

usaspending_assistance · table

Every federal assistance transaction (grants, cooperative agreements, direct payments, loans, insurance) by federal fiscal year (Oct-Sep; year = the fiscal year of action_date), one row per award action (award x modification), from USAspending's award data archive (all-agency Assistance Full files, FY2007 onward; roughly 6-9 million rows per recent year, about 115 million in all). Carries the recipient (UEI, DUNS, parent, name, address, county and congressional district), the program (cfda_number = Assistance Listing / ALN), the awarding and funding agency, place of performance, period of performance and dollars. federal_action_obligation is additive across rows (sum it for obligations by year, recipient or program); total_obligated_amount and total_outlayed_amount_for_overall_award are cumulative per award and repeated on every row of that award, so take one row per assistance_award_unique_key (the latest action_date) before summing outlays. Outlays are only available as that cumulative per-award figure. Loan rows report the subsidy cost as the obligation and the face value in face_value_of_loan. recipient_uei joins to fac_audits.auditee_uei (Single Audit filings) and cfda_number to fac_federal_awards (see the view fac_awards_vs_usaspending); recipient_duns joins the pre-UEI FAC years; there is no EIN in USAspending, so EIN reaches it through fac_audits. Individuals' awards carry a masked recipient and no UEI. Contracts are not in this table. Source: files.usaspending.gov/award_data_archive (URL resolved through api.usaspending.gov/api/v2/bulk_download/list_monthly_files/).

Column	Type	Null	Description
assistance_transaction_unique_key	string	no	Transaction identifier (PK): one row per award action (award x modification)
assistance_award_unique_key	string	yes	USAspending award identifier; many transaction rows share one award
award_id_fain	string	yes	Federal Award Identification Number assigned by the awarding agency (non-aggregate awards)
modification_number	string	yes	Modification number of this action (0 = original award)
award_id_uri	string	yes	Unique Record Identifier for aggregate (per-recipient-per-period) awards
sai_number	string	yes	System for Award Information number
federal_action_obligation	double	yes	Dollars obligated by this single action (USD); additive across rows. Negative on de-obligations. For loans this is the subsidy cost, not face value
total_obligated_amount	double	yes	Cumulative obligation of the whole award as of the extract, repeated on every row of that award; do not sum across rows
total_outlayed_amount_for_overall_award	double	yes	Cumulative outlays (dollars actually paid) of the whole award as of the extract, repeated on every row of that award; do not sum across rows. Source carries no per-year outlay flow
non_federal_funding_amount	double	yes	Non-federal (recipient/cost-share) funding of this action (USD)
face_value_of_loan	double	yes	Face value of the loan for loan actions (USD)
original_loan_subsidy_cost	double	yes	Original subsidy cost of the loan (USD)
action_date	string	yes	Date of the action (YYYY-MM-DD); its federal fiscal year (Oct-Sep) is the year partition
period_of_performance_start_date	string	yes	Award period-of-performance start (YYYY-MM-DD)
period_of_performance_current_end_date	string	yes	Award current period-of-performance end (YYYY-MM-DD)
awarding_agency_code	string	yes	Awarding top-tier agency code (Treasury CGAC)
awarding_agency_name	string	yes	Awarding top-tier federal agency
awarding_sub_agency_code	string	yes	Awarding sub-tier agency code
awarding_sub_agency_name	string	yes	Awarding sub-tier agency
awarding_office_name	string	yes	Awarding office
funding_agency_code	string	yes	Funding top-tier agency code
funding_agency_name	string	yes	Funding top-tier federal agency (can differ from the awarding agency)
funding_sub_agency_name	string	yes	Funding sub-tier agency
recipient_uei	string	yes	Recipient Unique Entity ID (12 characters). Join key to fac_audits.auditee_uei; USAspending backfills UEI on older records, but individuals and masked recipients have none
recipient_duns	string	yes	Recipient DUNS number (populated FY2007-FY2021, sparse after). Join key to fac_audits_historic.duns
recipient_name	string	yes	Recipient legal name as normalised by USAspending (individuals appear as MULTIPLE RECIPIENTS or redacted)
recipient_parent_uei	string	yes	Parent organisation UEI
recipient_parent_duns	string	yes	Parent organisation DUNS
recipient_parent_name	string	yes	Parent organisation name
recipient_country_code	string	yes	Recipient country (ISO alpha-3)
recipient_city_name	string	yes	Recipient city
recipient_county_fips	string	yes	Recipient 5-digit county FIPS (FK to geo.counties)
recipient_state_code	string	yes	Recipient state (USPS abbreviation)
recipient_zip_code	string	yes	Recipient 5-digit ZIP
recipient_congressional_district	string	yes	Recipient congressional district (current)
pop_state_fips	string	yes	Place-of-performance 2-digit state FIPS
pop_state_name	string	yes	Place-of-performance state name
pop_county_fips	string	yes	Place-of-performance 5-digit county FIPS (FK to geo.counties)
pop_county_name	string	yes	Place-of-performance county name
pop_city_name	string	yes	Place-of-performance city
pop_zip4	string	yes	Place-of-performance ZIP+4
pop_congressional_district	string	yes	Place-of-performance congressional district (current)
cfda_number	string	yes	Assistance Listing (CFDA) number, e.g. 93.778; matches fac_federal_awards federal_agency_prefix || '.' || federal_award_extension
cfda_title	string	yes	Assistance Listing program title
funding_opportunity_number	string	yes	Notice of funding opportunity number
assistance_type_code	string	yes	Assistance type code (02-05 grants/cooperative agreements, 06 direct payment, 07/08 loans, 09 insurance, 10 direct payment unrestricted, 11 other)
assistance_type_description	string	yes	Assistance type description
business_types_code	string	yes	Recipient business-type codes (comma separated)
business_types_description	string	yes	Recipient business types (state/local government, nonprofit, etc.)
action_type_code	string	yes	Action type code (A new, B continuation, C revision, D adjustment to completed project)
record_type_code	string	yes	Record type (1 aggregate by recipient, 2 non-aggregate, 3 individual recipient)
transaction_description	string	yes	Free-text description of the action as filed
disaster_emergency_fund_codes	string	yes	Disaster Emergency Fund codes funding the award (COVID-19, IIJA, etc.)
usaspending_permalink	string	yes	Link to the award on usaspending.gov
last_modified_date	string	yes	When USAspending last changed this transaction

fac_audits · table

One row per Single Audit (Uniform Guidance audit of an entity that spent $750,000 or more in federal awards; $1,000,000 from FY2025) filed with the Federal Audit Clearinghouse, audit years 2016 to date, from the FAC current-data bulk file general.csv (about 418,000 audits through October 2026; roughly 37,000-48,000 per year). Carries the auditee (name, address, EIN, UEI), the audit firm, the cognizant and oversight agencies, the opinion and internal-control findings flags and total federal dollars expended. auditee_uei is the placeholder GSA_MIGRATION for audit years 2016-2021 (filed before UEIs); auditee_ein is always present, so those years reach usaspending_assistance through the EIN-to-UEI mapping in the view fac_awards_vs_usaspending. EIN joins exempt_org_990 and ref.canonical_org_entity. Filings arrive up to 9 months after fiscal year end and are amended in place (resubmission_version, resubmission_status), and the file is a full daily extract, so each monthly run replaces the table. Contact people (names, e-mail, phone) are not loaded. Pre-2016 audits are in fac_audits_historic. Source: fac.gov/data/download (app.fac.gov/dissemination/public-data/gsa/full/general.csv).

Column	Type	Null	Description
report_id	string	no	FAC report identifier (PK of fac_audits); for 2016-2021 filings it embeds the Census id
auditee_uei	string	yes	Auditee UEI; the literal GSA_MIGRATION for audit years 2016-2021 (filed before UEIs), real UEI from 2022. Use auditee_ein for those years
audit_year	integer	yes	Audit year: the fiscal year covered by the audit
auditee_name	string	yes	Auditee (recipient of federal awards) name
auditee_ein	string	yes	Auditee EIN, always present; joins exempt_org_990 and ref.canonical_org_entity
auditee_address_line_1	string	yes	Auditee street address
auditee_city	string	yes	Auditee city
auditee_state	string	yes	Auditee state (USPS)
auditee_zip	string	yes	Auditee ZIP
auditor_firm_name	string	yes	Audit firm
auditor_ein	string	yes	Audit firm EIN
auditor_city	string	yes	Audit firm city
auditor_state	string	yes	Audit firm state
auditor_zip	string	yes	Audit firm ZIP
cognizant_agency	string	yes	Cognizant federal agency prefix (2-digit)
oversight_agency	string	yes	Oversight federal agency prefix (2-digit)
date_created	string	yes	Date the submission record was created
submitted_date	string	yes	Date submitted
fac_accepted_date	string	yes	Date the FAC accepted the filing
fy_start_date	string	yes	Audited fiscal year start
fy_end_date	string	yes	Audited fiscal year end
audit_type	string	yes	single-audit, program-specific, or alternative-compliance-examination
audit_period_covered	string	yes	annual, biennial, or other
number_months	string	yes	Months covered when the period is other
gaap_results	string	yes	Auditor opinion type(s) on the financial statements
is_going_concern_included	string	yes	Going-concern paragraph included (Yes/No)
is_internal_control_deficiency_disclosed	string	yes	Internal-control deficiency disclosed (Yes/No)
is_internal_control_material_weakness_disclosed	string	yes	Material weakness disclosed (Yes/No)
is_material_noncompliance_disclosed	string	yes	Material noncompliance disclosed (Yes/No)
dollar_threshold	double	yes	Threshold separating Type A and Type B programs (USD)
is_low_risk_auditee	string	yes	Qualified as a low-risk auditee (Yes/No)
agencies_with_prior_findings	string	yes	Agency prefixes with prior-year findings (comma separated)
entity_type	string	yes	Auditee type (local, state, non-profit, higher-ed, tribal, unknown)
total_amount_expended	double	yes	Total federal awards expended in the audit year (USD)
type_audit_code	string	yes	UG uniform guidance, AL alternative compliance, ...
is_public	string	yes	Report is public (t/f)
data_source	string	yes	GSA (filed in the new system) or CENSUS (migrated)
resubmission_version	string	yes	Version number of this filing
resubmission_status	string	yes	most_recent or superseded marker
is_multiple_eins	string	yes	Filing covers multiple EINs (Yes/No)
is_additional_ueis	string	yes	Filing lists additional UEIs (Yes/No)
is_secondary_auditors	string	yes	Secondary auditors involved (Yes/No)

fac_federal_awards · table

One row per federal program (Assistance Listing) audited in a Single Audit, audit years 2016 to date (about 7.3 million rows through October 2026; 580,000-930,000 per year), from the FAC current-data bulk file federal_awards.csv. Joins fac_audits on report_id (EIN, auditee name and state live there). The ALN is federal_agency_prefix || '.' || federal_award_extension and matches usaspending_assistance.cfda_number. amount_expended is the program's federal expenditure in the audit year. is_direct = 'Y' rows are prime awards received straight from the agency and can be matched to USAspending; is_direct = 'N' rows are pass-through funds received from a state or other entity and will not match a prime award to the same UEI. Includes cluster, major-program, loan, findings and pass-through-to-subrecipient fields. Same monthly full replacement as fac_audits. Pre-2016 awards are in fac_federal_awards_historic. Source: app.fac.gov/dissemination/public-data/gsa/full/federal_awards.csv.

Column	Type	Null	Description
report_id	string	no	FAC report identifier (PK of fac_audits); for 2016-2021 filings it embeds the Census id
auditee_uei	string	yes	Auditee UEI; the literal GSA_MIGRATION for audit years 2016-2021 (filed before UEIs), real UEI from 2022. Use auditee_ein for those years
audit_year	integer	yes	Audit year: the fiscal year covered by the audit
fac_accepted_date	string	yes	Date the FAC accepted the filing
award_reference	string	no	Award line within the report (PK with report_id)
federal_agency_prefix	string	yes	Assistance Listing agency prefix (2 digits), first part of the ALN
federal_award_extension	string	yes	Assistance Listing extension (3 characters), second part of the ALN, e.g. 778 or U01 for an unnamed program
additional_award_identification	string	yes	Additional award identification supplied by the auditee
federal_program_name	string	yes	Federal program name
amount_expended	double	yes	Federal dollars expended on this program in the audit year (USD)
cluster_name	string	yes	Program cluster
other_cluster_name	string	yes	Cluster name when cluster_name is OTHER CLUSTER
state_cluster_name	string	yes	State cluster name
cluster_total	double	yes	Total expended for the cluster (USD)
federal_program_total	double	yes	Total expended for the program across its lines (USD)
is_major	string	yes	Audited as a major program (Y/N)
is_loan	string	yes	Federal loan program (Y/N)
loan_balance	string	yes	Loan balance outstanding as the auditee typed it (free text in the source: plain numbers, $ and comma formats, N/A, and the GSA_MIGRATION placeholder on migrated rows), so it is not parsed
is_direct	string	yes	Y when received directly from the federal agency (a prime award that can match USAspending), N when passed through another entity
audit_report_type	string	yes	Auditor opinion on the program (U unmodified, Q qualified, A adverse, D disclaimer)
findings_count	integer	yes	Number of audit findings for the program
is_passthrough_award	string	yes	Part of the award was passed through to subrecipients (Y/N)
passthrough_amount	double	yes	Amount passed through (USD)

fac_audits_historic · table

One row per Single Audit for audit years 1998-2015 (723,457 audits; 32,000-46,000 per year), from the Federal Audit Clearinghouse's archived Census data (ELECAUDITHEADER). Identified by (year, dbkey); year is the audit year. The Census system pre-dates UEIs, so the keys are ein (always present) and duns (blank on about 30 percent of rows); uei is empty. Carries the auditee, audit firm, cognizant and oversight agencies, opinion and internal-control flags and total federal expenditure. The files are frozen (the FAC replaced them with the GSA system for 2016 onward, see fac_audits). Contact people are not loaded. Source: app.fac.gov/dissemination/public-data/census/csv/census-.zip.

Column	Type	Null	Description
dbkey	string	no	Census database key; unique within the audit year (PK with year)
census_id	string	yes	Census id (dbkey+year)
ein	string	yes	Auditee EIN (EIN+subcode identify the auditee)
ein_subcode	string	yes	EIN sub-code
duns	string	yes	Auditee DUNS number (blank on about 30 percent of rows); join key to usaspending_assistance.recipient_duns
auditee_name	string	yes	Auditee (recipient of federal awards) name
street	string	yes	Auditee street
city	string	yes	Auditee city
state	string	yes	Auditee state
zip_code	string	yes	Auditee ZIP
cpa_firm_name	string	yes	Audit firm
cpa_city	string	yes	Audit firm city
cpa_state	string	yes	Audit firm state
cpa_zip_code	string	yes	Audit firm ZIP
auditor_ein	string	yes	Audit firm EIN
cognizant_agency	string	yes	Cognizant federal agency prefix (2-digit)
oversight_agency	string	yes	Oversight federal agency prefix (2-digit)
fy_start_date	string	yes	Audited fiscal year start
fy_end_date	string	yes	Audited fiscal year end
audit_type	string	yes	single-audit, program-specific, or alternative-compliance-examination
period_covered	string	yes	Audit period covered (A annual, B biennial, O other)
number_months	string	yes	Months covered when the period is other
report_type_fs	string	yes	Opinion type on the financial statements
material_weakness	string	yes	Material weakness disclosed
material_noncompliance	string	yes	Material noncompliance disclosed
going_concern	string	yes	Going-concern paragraph included
dollar_threshold	double	yes	Threshold separating Type A and Type B programs (USD)
low_risk	string	yes	Low-risk auditee flag
total_federal_expenditure	double	yes	Total federal awards expended (USD)
entity_type	string	yes	Auditee type (local, state, non-profit, higher-ed, tribal, unknown)
fac_accepted_date	string	yes	Date the FAC accepted the filing
uei	string	yes	UEI (empty in the Census years)

fac_federal_awards_historic · table

One row per federal program (CFDA) audited in a Single Audit, audit years 1998-2015 (about 9.4 million rows; 340,000-700,000 per year), from the archived Census data (ELECAUDITS). Joins fac_audits_historic on (year, dbkey); the EIN is repeated on each row. cfda is the Assistance Listing number (e.g. 93.569) and matches usaspending_assistance.cfda_number. amount is the program's federal expenditure in the audit year; direct = 'Y' marks funds received directly from the agency (prime awards), 'N' pass-through funds. Same frozen source as fac_audits_historic. Source: app.fac.gov/dissemination/public-data/census/csv/census-.zip.

Column	Type	Null	Description
dbkey	string	no	Census database key; unique within the audit year (PK with year)
cfda_seq_num	integer	yes	Sequence of the program within the audit
cfda	string	yes	Assistance Listing (CFDA) number, e.g. 93.569; matches usaspending_assistance.cfda_number
cfda_prefix	string	yes	Agency prefix of the CFDA number
cfda_ext	string	yes	Extension of the CFDA number
ein	string	yes	Auditee EIN (EIN+subcode identify the auditee)
federal_program_name	string	yes	Federal program name
amount	double	yes	Federal dollars expended on the program (USD)
program_total	double	yes	Total expended for the program (USD)
cluster_total	double	yes	Total expended for the cluster (USD)
major_program	string	yes	Audited as a major program (Y/N)
direct	string	yes	Y when received directly from the federal agency, N when passed through
loans	string	yes	Loan program (Y/N)
loan_balance	double	yes	Loan balance outstanding (USD)
arra	string	yes	ARRA (Recovery Act) funds (Y/N)
passthrough_award	string	yes	Passed through to subrecipients (Y/N)
passthrough_amount	double	yes	Amount passed through (USD)
cluster_name	string	yes	Program cluster
state_cluster_name	string	yes	State cluster name
award_identification	string	yes	Additional award identification
findings_count	integer	yes	Number of audit findings for the program
type_requirement	string	yes	Compliance requirement types tested
questioned_costs	string	yes	Questioned costs flag

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.

fac_awards_vs_usaspending · view

Single Audit federal awards (fac_federal_awards joined to fac_audits) beside the prime assistance obligations USAspending recorded for the same recipient UEI, Assistance Listing (ALN) and fiscal year, for audit years 2016 onward. One row per audit award line. uei is the filing's own UEI for audit years 2022 on; for 2016-2021 filings (UEI placeholder GSA_MIGRATION) it is the UEI the same EIN carries on a 2022+ filing, only where that EIN maps to exactly one UEI (otherwise null). usaspending_obligated is the sum of federal_action_obligation in that fiscal year only, so a multi-year award obligated in an earlier year shows 0 transactions here; sum usaspending_assistance across years for the lifetime figure. is_direct = 'N' lines are pass-through funds and are not expected to match a prime award to the auditee; amount_expended is the auditee's expenditure in the audit year, not an obligation, so the two amounts are comparable only in magnitude.

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

usaspending_assistance_by_county · view

Federal assistance obligations by place-of-performance county x Assistance Listing (ALN, formerly CFDA number) x federal fiscal year (Oct-Sep; year = fiscal year of action_date): the program breakdown that usaspending_by_county (one total per county, all award types) does not carry. One row per (year, county_fips, assistance_listing), aggregated from usaspending_assistance, so it covers exactly the fiscal years that table has loaded. obligated_amount is the signed sum of federal_action_obligation, so de-obligations net out and a county-listing-year can be negative. obligated_amount_excl_loans leaves out the loan assistance types (07 direct loan, 08 guaranteed/insured loan, and the FY2026-onward codes F003 direct loan and F004 loan guarantee), whose obligation is the subsidy cost rather than the face value, as usaspending_by_county does; face value is in usaspending_assistance.face_value_of_loan. county_fips is the PLACE OF PERFORMANCE county, not the recipient's: for pass-through awards (a state agency receiving a block grant and subawarding it to counties) the prime award is attributed to where USAspending records the work, which can be the recipient's own county, so a county's total need not equal what reached its residents, and recipient-county grain (usaspending_assistance. recipient_county_fips) gives different numbers. Transactions with a blank place-of- performance county or a state-level code (county part 000) are left out, never guessed into a county, so county rows do not sum to the national assistance total. The share is large and varies by year: FY2008 full file 19 percent of transactions and 24 percent of net dollars; FY2025 DQ sample 3 and 2 percent; FY2026 DQ sample 15 and 25 percent (state-level formula grants dominate the dollars); measure the current share from usaspending_assistance. transactions counts award actions (award x modification), not awards. assistance_listing_title is the title on the year's rows. Joins to geo.counties on county_fips; older fiscal years carry some retired county codes (FY2008: 12025, the former Dade County, 4.4 billion dollars) that do not join. Example, State Opioid Response (93.788) and Substance Abuse Prevention and Treatment Block Grant (93.959) obligations by county and year: SELECT c.county_name, v.year, v.assistance_listing, v.obligated_amount FROM usaspending_assistance_by_county v JOIN geo.counties c ON c.county_fips = v.county_fips WHERE v.assistance_listing IN ('93.788', '93.959') ORDER BY v.year, v.obligated_amount DESC. Reconciliation: usaspending_by_county is built from the USAspending API with the assistance award types plus contracts, and has no assistance-only column, so compare with the API's assistance-only county figures instead; for the FY2008 full file, 3,226 of 3,227 API counties matched, 72 percent to within one dollar, 99.6 percent to within 1 percent, and the national totals differ by 0.42 percent (cause of the residual not isolated).

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