📈 econ¶
U.S. economic indicators from Federal Reserve (FRED), Bureau of Labor Statistics (BLS), Bureau of Economic Analysis (BEA), and Treasury Department. Includes employment statistics, inflation metrics, GDP components, interest rates, regional economic data, and over 800,000 time series from FRED. Enables macroeconomic analysis, economic forecasting, policy research, and cross-domain correlation with geographic and demographic data. Uses DuckDB quackformers extension for native embedding generation.
72 datasets · 377 columns
fred_indicators_enriched · table¶
FRED time series observations (series, date, value) enriched with series metadata: series_name (title), units, frequency, seasonal_adjustment, last_updated, observation_start/observation_end coverage window, and popularity — joins fred_indicators to econ_reference.fred_series so callers don't need a manual lookup.
View — columns are resolved by the query engine at runtime.
national_accounts_enriched · table¶
National accounts (NIPA) data enriched with table metadata. Columns: table_id (BEA table code), line_number (line within table), line_description (component description), series_code (BEA series code), year, value (millions of dollars), units, frequency (A=Annual/Q=Quarterly), table_description (full table description from BEA). Enables semantic queries without manual joins. (The nipa_tables reference catalog exposes only table_name/description from the BEA API; its former section categorization was never populated — the partition was changed section->type in commit a2c400aaa — so no section column is emitted.) To discover available table_id values, query national_accounts_tables instead of SELECT DISTINCT table_id FROM national_accounts_enriched — the latter forces a full scan of the (large) national_accounts fact table plus this join before the DISTINCT can apply.
View — columns are resolved by the query engine at runtime.
national_accounts_tables · table¶
Lightweight catalog of available national_accounts/national_accounts_enriched table_id values: a plain scan over the small nipa_tables reference table, with no join against the full national_accounts fact table. Use this (not national_accounts_enriched) to discover table_id values — e.g. SELECT DISTINCT table_id FROM national_accounts_enriched pays for a full fact-table scan and join before the DISTINCT can apply, since nothing declares nipa_tables.table_name unique. Columns: table_id (BEA table code, aliased from nipa_tables.table_name), description (full table description from BEA).
View — columns are resolved by the query engine at runtime.
regional_income_enriched · table¶
Regional income data enriched with LineCode descriptions from the reference catalog. Columns include all regional_income fields plus line_code_description (what the BEA line code represents). Enables semantic queries without needing to know specific LineCode values. (The regional_linecodes reference catalog exposes only key/desc from the BEA API, so table_prefix/data_category/geography_level/table_frequency are not available.)
View — columns are resolved by the query engine at runtime.
interest_rate_spreads · table¶
Full yield curve with spreads and breakeven inflation. Columns: date, three_month_yield, two_year_yield, five_year_yield, ten_year_yield, thirty_year_yield (all Treasury constant maturity rates %), term_spread_10y_2y (canonical recession signal, negative historically precedes downturns), term_spread_10y_3m, breakeven_inflation_10y (market-implied 10Y inflation expectations %), fed_funds_rate (FOMC policy rate %), corporate_spread_bps (investment-grade spread, widening signals credit stress). Critical for recession forecasting, monetary policy analysis, and credit market assessment.
View — columns are resolved by the query engine at runtime.
housing_indicators · table¶
Comprehensive housing market health dashboard. Columns: date, housing_starts (total new housing units started in thousands, annualized), single_family_starts (single-family housing starts subset in thousands), building_permits (authorized building permits in thousands, leading indicator), existing_sales (existing home sales in millions annualized), case_shiller_index (S&P CoreLogic Case-Shiller U.S. National Home Price Index), median_sale_price (median sales price of houses sold in USD), mortgage_rate_30y (30-year fixed rate mortgage average %), rental_vacancy_rate (percentage of rental units vacant). Essential for real estate analysis, construction sector tracking, and housing affordability assessment.
View — columns are resolved by the query engine at runtime.
monetary_aggregates · table¶
Federal Reserve monetary aggregates and velocity analysis. Columns: date, m1_money_supply (narrow money supply including currency and demand deposits in billions), m2_money_supply (broad money supply including M1 plus savings deposits and money market funds in billions), monetary_base (currency in circulation plus bank reserves in billions), nominal_gdp (gross domestic product in billions), m2_velocity (calculated as GDP/M2, measures money turnover rate, declining velocity may indicate liquidity hoarding). Critical for Federal Reserve policy analysis, inflation expectations, and monetary transmission mechanism assessment.
View — columns are resolved by the query engine at runtime.
business_indicators · table¶
Business cycle and economic activity dashboard. Columns: date, industrial_production_index (index of manufacturing and industrial output, 2017=100), capacity_utilization_pct (percentage of total industrial capacity in use, high values indicate potential inflation pressure), retail_sales_millions (advance retail sales in millions USD, excludes services, leading consumer spending indicator), bank_credit_billions (total credit extended by banks in billions, measure of lending activity), lending_standards_net_pct (net percentage of banks tightening lending standards, positive values indicate credit tightening). Essential for business cycle analysis, recession/expansion identification, and credit conditions monitoring.
View — columns are resolved by the query engine at runtime.
trade_balance_summary · table¶
U.S. international trade balance aggregated by category from BEA. Columns: year (calendar year), quarter (Q1-Q4 for quarterly data, NULL for annual), line_description (trade category such as 'Goods', 'Services', 'Foods feeds and beverages', etc.), total_exports (sum of exports in millions USD), total_imports (sum of imports in millions USD), net_trade_balance (exports minus imports, negative indicates trade deficit). Derived from BEA NIPA Table 4.2.5B (Foreign Transactions section), enables analysis of trade composition, bilateral balances, and sectoral trade patterns. Critical for understanding U.S. competitiveness, currency impacts, and GDP contribution from net exports.
View — columns are resolved by the query engine at runtime.
trade_statistics · table¶
U.S. international trade statistics from BEA NIPA Table 4.2.5B (Foreign Transactions section). Provides detailed breakdown of exports and imports of goods and services with computed columns: quarter (1-4 extracted from time_period for quarterly data, NULL for annual), trade_type (exports/imports/trade_balance derived from line_description). This is a convenience view filtering national_accounts for trade-specific data. For enriched version with section metadata, query national_accounts_enriched WHERE table_id = 'T40205B'.
View — columns are resolved by the query engine at runtime.
real_gdp_growth · table¶
Real GDP (chained 2017 dollars) with quarter-over-quarter growth rate. Columns: date, real_gdp_billions (real GDP in billions of chained 2017 dollars), prior_quarter_gdp, qoq_growth_pct (percentage change from prior quarter). Use GDPC1 instead of GDP for growth analysis to remove inflation distortion. Two consecutive negative qoq_growth_pct values is the informal recession definition.
View — columns are resolved by the query engine at runtime.
metro_cpi_inflation · table¶
Metro CPI with month-over-month and year-over-year inflation computed per metro. Columns: date, series, area_code, area_name, cpi_index (CPI-U all items, NSA, 1982-84=100), percent_change_month, percent_change_year (both percent). Replaces the former percent_change_month/percent_change_year columns on metro_cpi, which no ingestion path ever populated. Changes are NULL where the prior month (or same month last year) is missing from the loaded years, so a partition gap reads as unknown rather than as a wrong number — expect NULL percent_change_year for the first loaded year. Series are not seasonally adjusted, so month-over-month carries seasonal noise; prefer percent_change_year for trend.
View — columns are resolved by the query engine at runtime.
regional_cpi_inflation · table¶
Census-region CPI with month-over-month and year-over-year inflation computed per region. Columns: date, series, area_code (0100 Northeast, 0200 Midwest, 0300 South, 0400 West), area_name, cpi_index (CPI-U all items, NSA, 1982-84=100), percent_change_month, percent_change_year (both percent). Replaces the former percent_change_* columns on regional_cpi, which no ingestion path ever populated. Changes are NULL where the comparison period is missing from the loaded years. Pairs with metro_cpi_inflation for region-versus-metro cost-of-living divergence.
View — columns are resolved by the query engine at runtime.
pce_inflation · table¶
PCE inflation dashboard — the Federal Reserve's preferred inflation framework. Columns: date, pce_index (Personal Consumption Expenditures price index, headline), core_pce_index (PCE excluding food and energy — the FOMC's primary 2% inflation target). Complements CPI-based views; the Fed explicitly targets Core PCE, making this view critical for monetary policy analysis.
View — columns are resolved by the query engine at runtime.
consumer_spending · table¶
Consumer spending, income, and saving dashboard. Columns: date, personal_consumption_billions (PCE in billions USD — largest single component of GDP at ~70%), personal_income_billions (total personal income in billions USD), saving_rate_pct (personal saving as % of disposable income, low values indicate consumer stress, high values can signal precautionary behavior). Essential for consumer health assessment, GDP forecasting, and tracking post-shock household balance sheet recovery.
View — columns are resolved by the query engine at runtime.
labor_market_conditions · table¶
Comprehensive labor market conditions dashboard. Columns: date, unemployment_rate_u3 (official U-3 unemployment rate %), unemployment_rate_u6 (broad U-6 rate including underemployed and marginally attached %), labor_slack_gap (U6 minus U3, higher values indicate hidden slack), initial_claims (weekly initial jobless claims — most timely leading labor indicator), continuing_claims (workers still collecting unemployment benefits — measures labor market absorption), avg_weekly_hours_mfg (average weekly hours in manufacturing, a Conference Board leading indicator — employers cut hours before cutting workers). Essential for assessing true labor market health beyond the headline unemployment rate.
View — columns are resolved by the query engine at runtime.
fed_policy_dashboard · table¶
Federal Reserve policy stance dashboard. Columns: date, fed_funds_rate (FOMC policy rate %), core_pce (Fed primary inflation target — PCE ex-food-and-energy %), real_fed_funds_rate (nominal rate minus Core PCE — negative values indicate accommodative policy, positive values restrictive), fed_balance_sheet_billions (Fed total assets in billions — tracks QE/QT cycles), in_recession (NBER recession indicator, 1=recession, 0=expansion). Directly maps to the Fed's dual mandate framework (price stability + maximum employment) and balance sheet normalization tracking.
View — columns are resolved by the query engine at runtime.
global_conditions · table¶
Global commodity and currency conditions. Columns: date, wti_crude_price (West Texas Intermediate crude oil spot price in USD/barrel — leading input cost and inflation driver), usd_broad_index (trade-weighted US dollar index against broad basket of trading partners, 2006=100 — strong dollar suppresses exports and commodity prices, weak dollar does the opposite). Useful for cost-push inflation analysis, export competitiveness, and macro regime identification.
View — columns are resolved by the query engine at runtime.
employment_statistics · table¶
U.S. employment and unemployment statistics from BLS including national unemployment rate, labor force participation, job openings, and employment by sector. Also includes CPS unemployment rate, labor force participation rate, and employment-population ratio broken out by educational attainment for ages 25 and over (seasonally adjusted), and CPS labor force/employment/unemployment level and unemployment rate broken out by veteran status (total veterans, nonveterans, Gulf War-era II veterans; not seasonally adjusted). Updated monthly with seasonal adjustments.
| Column | Type | Null | Description |
|---|---|---|---|
series |
string | yes | BLS series identifier. This table is long-format: one row per (series, period), so ALWAYS filter on series — mixing series in an aggregate mixes incompatible units (percent, thousands of persons, weeks, hours). The 35 series loaded are: LNS14000000 unemployment rate, 16+ (percent); LNS11300000 labor force participation rate, 16+ (percent); LNS12300000 employment-population ratio, 16+ (percent); LNS13327709 U-6 total labor underutilization rate (percent); LNS11000000 civilian labor force level (thousands); LNS12000000 employment level, CPS (thousands); LNS13000000 unemployment level (thousands); LNS12032194 employed part time for economic reasons (thousands); LNS13008636 unemployed 27 weeks and over (thousands); LNS13025703 unemployed 27 weeks and over as percent of unemployed (percent); LNS13008275 average weeks unemployed; LNS13008276 median weeks unemployed; LNS14000003 unemployment rate, White; LNS14000006 Black or African American; LNS14000009 Hispanic or Latino; LNS14032183 Asian; LNS14000025 men 20+; LNS14000026 women 20+; LNS14000012 ages 16-19 (all percent); unemployment rate by educational attainment, 25+ — LNS14027659 less than a high school diploma, LNS14027660 high school graduates no college, LNS14027689 some college or associate degree, LNS14027662 bachelor's degree and higher; labor force participation rate by educational attainment, 25+ — LNS11327659, LNS11327660, LNS11327689, LNS11327662 (same four education groups, in order); employment-population ratio by educational attainment, 25+ — LNS12327659, LNS12327660, LNS12327689, LNS12327662 (same four education groups, in order); CES0000000001 total nonfarm employment (thousands); CES0500000001 total private employment (thousands); CES0500000002 average weekly hours, all employees, total private; CES0500000007 average weekly hours, production and nonsupervisory employees, total private; CPS labor force level / employment level / unemployment level (thousands) / unemployment rate (percent) by veteran status, 18+, not seasonally adjusted (BLS publishes no seasonally adjusted veteran breakout) — LNU01049526/LNU02049526/LNU03049526/LNU04049526 total veterans, LNU01049601/LNU02049601/LNU03049601/LNU04049601 nonveterans, LNU01066408/LNU02066408/LNU03066408/LNU04066408 Gulf War-era II veterans. All LNS (CPS) series are seasonally adjusted; the LNU veteran-status series are not. Use SELECT DISTINCT series to see what a given partition actually contains. |
year |
integer | yes | Data year from BLS API |
period |
string | yes | BLS period identifier (M01-M12 for monthly, Q01-Q04 for quarterly) |
periodName |
string | yes | Human-readable period name (e.g., 'January', 'February') |
value |
double | yes | Employment statistic value (e.g., unemployment rate as percentage) |
latest |
boolean | yes | Whether this is the latest data point in the series |
footnotes |
string | yes | Any footnotes or qualifiers for the data point |
inflation_metrics · table¶
National monthly CPI-U (all items, core less food and energy, and water/sewerage maintenance) plus PPI final demand finished goods — 4 index series (base period 1982-84=100), national level only, not rates. For sub-national CPI breakdowns see regional_cpi (4 Census regions) and metro_cpi (20 metro areas).
| Column | Type | Null | Description |
|---|---|---|---|
series |
string | yes | BLS series identifier. This table is long-format: one row per (series, period), and it mixes CPI with PPI, so ALWAYS filter on series. The 4 series loaded are: CUUR0000SA0 CPI-U, U.S. city average, all items, not seasonally adjusted (1982-84=100); CUUR0000SA0L1E CPI-U core, all items less food and energy, not seasonally adjusted; CUUR0000SEHG02 CPI-U Water and sewerage maintenance, U.S. city average, NSA; WPUFD49207 PPI final demand, finished goods. All are index levels, not rates — compute inflation as a period-over-period change. The four series do not share one start date at the source: CUUR0000SA0 (the series adjust_inflation uses) reaches back to 1913, CUUR0000SA0L1E and WPUFD49207 to the 1950s-70s, and CUUR0000SEHG02 not until 1983 (confirmed live 2026-09-10) — a year before a series' own start simply has no row for it, not an error. |
year |
integer | yes | Data year from BLS API |
period |
string | yes | BLS period identifier (M01-M12 for monthly) |
periodName |
string | yes | Human-readable period name (e.g., 'January', 'February') |
value |
double | yes | Price index value (typically base period = 100) |
latest |
boolean | yes | Whether this is the latest data point in the series |
footnotes |
string | yes | Any footnotes or qualifiers for the data point |
regional_cpi · table¶
Monthly CPI-U all items index level (not seasonally adjusted, base 1982-84=100) for the 4 U.S. Census regions (Northeast, Midwest, South, West). For metro-area-level CPI instead of regional, see metro_cpi (20 metro areas).
| Column | Type | Null | Description |
|---|---|---|---|
date |
date | yes | Observation date, first day of the month, derived from the BLS year and period fields (period M01-M12). BlsResponseTransformer emits year and period rather than a date. |
series |
string | yes | BLS series identifier. This table is long-format: one row per (series, period), so ALWAYS filter on series. The 4 series loaded are CPI-U all items, not seasonally adjusted, for the four Census regions: CUUR0100SA0 Northeast, CUUR0200SA0 Midwest, CUUR0300SA0 South, CUUR0400SA0 West. Series ID format is CUUR{area}SA0, so the embedded 4 characters are the Census region code. As with metro_cpi, BlsResponseTransformer emits only series, year, period, periodName, value, latest and footnotes — the area_code and area_name columns are not populated by the current ingest. Index levels (1982-84=100), not rates. |
area_ |
string | yes | Census region code, derived from series positions 5-8 (CUUR{area}SA0): 0100 Northeast, 0200 Midwest, 0300 South, 0400 West. |
area_ |
string | yes | Census region name for the area code, matching econ_reference.bls_geographies.geo_name and the censusRegions map in /bls/bls-constants.yaml. |
value |
double | yes | CPI index level for the area (base period 1982-84 = 100), not a rate. Period-over-period inflation is computed in the metro_cpi_inflation / regional_cpi_inflation views. |
metro_cpi · table¶
Monthly CPI-U all items index level (not seasonally adjusted, base 1982-84=100) for 20 major U.S. metropolitan areas (NYC, LA, Chicago, Houston, Phoenix, and others). For 4-region-level CPI instead of metro, see regional_cpi.
| Column | Type | Null | Description |
|---|---|---|---|
date |
date | yes | Observation date, first day of the month, derived from the BLS year and period fields (period M01-M12). BlsResponseTransformer emits year and period rather than a date. |
series |
string | yes | BLS series identifier, format CUUR{area}SA0 — the embedded 4 characters are the BLS CPI area code. Filter on series: BlsResponseTransformer emits only series, year, period, periodName, value, latest and footnotes, so the area_code and area_name columns are not populated by the current ingest (SUBSTRING(series, 5, 4) recovers the area code). This table is long-format, one row per (series, period). All 20 series are CPI-U all items, not seasonally adjusted (index, base period = 100), one per metro area: CUURS12ASA0 New York-Newark-Jersey City NY-NJ-PA; CUURS12BSA0 Philadelphia-Camden-Wilmington PA-NJ-DE-MD; CUURS23ASA0 Chicago-Naperville-Elgin IL-IN-WI; CUURS23BSA0 Detroit-Warren-Dearborn MI; CUURS24ASA0 Minneapolis-St.Paul-Bloomington MN-WI; CUURS24BSA0 St. Louis MO-IL; CUURS35ASA0 Washington-Arlington-Alexandria DC-VA-MD-WV; CUURS35BSA0 Miami-Fort Lauderdale-West Palm Beach FL; CUURS35CSA0 Atlanta-Sandy Springs-Roswell GA; CUURS35DSA0 Tampa-St. Petersburg-Clearwater FL; CUURS35ESA0 Baltimore-Columbia-Towson MD; CUURS37BSA0 Houston-The Woodlands-Sugar Land TX; CUURS48ASA0 Phoenix-Mesa-Scottsdale AZ; CUURS48BSA0 Denver-Aurora-Lakewood CO; CUURS49ASA0 Los Angeles-Long Beach-Anaheim CA; CUURS49BSA0 San Francisco-Oakland-Hayward CA; CUURS49CSA0 Riverside-San Bernardino-Ontario CA; CUURS49DSA0 Seattle-Tacoma-Bellevue WA; CUURS49ESA0 San Diego-Carlsbad CA; CUURS49GSA0 Urban Alaska. Names follow the authoritative BLS decoder (https://download.bls.gov/pub/time.series/cu/cu.area). Boston (S11A), Dallas-Fort Worth (S37A), and Urban Hawaii (S49F) publish metro CPI but are NOT loaded here. Two loaded codes have no row in econ_reference.bls_geographies, so the area_code foreign key does not resolve for them: S49B (San Francisco, absent from the metro reference list) and S49G (Urban Alaska, a statewide urban area rather than a metro). |
area_ |
string | yes | BLS CPI area code, derived from series positions 5-8 (CUUR{area}SA0). Join to econ_reference.bls_geographies (metro_cpi_area_code) for CBSA and publication codes. |
area_ |
string | yes | Metro area name for the CPI area code. Spellings follow the BLS CU decoder (https://download.bls.gov/pub/time.series/cu/cu.area), which differs cosmetically from econ_reference.bls_geographies.geo_name in places (e.g. 'St.Paul' vs 'St. Paul') — join bls_geographies on area_code, never on the name. |
value |
double | yes | CPI index level for the area (base period 1982-84 = 100), not a rate. Period-over-period inflation is computed in the metro_cpi_inflation / regional_cpi_inflation views. |
food_cpi · table¶
Monthly CPI-U Food at Home (SAF11 + its 6 subcomponents: cereals/bakery, meats/poultry/ fish/eggs, dairy, fruits/vegetables, nonalcoholic beverages, other) and Food Away From Home (SEFV), U.S. city average only (area 0000), not seasonally adjusted (base 1982-84=100). NATIONAL ONLY — confirmed against BLS's cu.series catalog, which also publishes SAF11 (but not its subcomponents or SEFV) for the 4 Census regions, several size classes, and ~23 individual metro areas; those live in BLS's region-grouped bulk files (cu.data.3-10, not cu.data.11.USFoodBeverage) and are not loaded here. For all-items regional/metro CPI see regional_cpi/metro_cpi.
| Column | Type | Null | Description |
|---|---|---|---|
series |
string | yes | BLS CPI series identifier. This table is long-format: one row per (series, period), so ALWAYS filter on series. The 8 series loaded are: CUUR0000SAF11 (Food at home), CUUR0000SAF111 (Cereals and bakery products), CUUR0000SAF112 (Meats, poultry, fish, and eggs), CUUR0000SAF113 (Dairy and related products), CUUR0000SAF114 (Fruits and vegetables), CUUR0000SAF115 (Nonalcoholic beverages), CUUR0000SAF116 (Other food at home), CUUR0000SEFV (Food away from home). All are index levels, not rates — compute inflation as a period-over-period change. |
year |
integer | yes | Data year |
period |
string | yes | BLS period identifier (M01-M12 for monthly) |
periodName |
string | yes | Human-readable period name derived from period |
value |
double | yes | Price index value (base period = 100) |
footnotes |
string | yes | BLS footnote codes for the data point |
regional_food_cpi · table¶
Monthly CPI-U Food at Home (SAF11) index level (not seasonally adjusted, base 1982-84=100) for the 4 U.S. Census regions (Northeast, Midwest, South, West). Sibling to regional_cpi (all items) — same 4 region area codes, item SAF11 instead of SA0. Food at Home subcomponents and Food Away From Home are not published sub-nationally by BLS (confirmed against cu.series — only the top-level SAF11 aggregate has region/metro rows); for those, see food_cpi (national only). For metro-area food CPI, see metro_food_cpi.
| Column | Type | Null | Description |
|---|---|---|---|
date |
date | yes | Observation date, first day of the month, derived from the BLS year and period fields (period M01-M12). BlsResponseTransformer emits year and period rather than a date. |
series |
string | yes | BLS series identifier. This table is long-format: one row per (series, period), so ALWAYS filter on series. The 4 series loaded are CPI-U Food at Home, not seasonally adjusted, for the four Census regions: CUUR0100SAF11 Northeast, CUUR0200SAF11 Midwest, CUUR0300SAF11 South, CUUR0400SAF11 West. Series ID format is CUUR{area}SAF11, so the embedded 4 characters are the Census region code. BlsResponseTransformer emits only series, year, period, periodName, value, latest and footnotes — the area_code and area_name columns are not populated by the current ingest. Index levels (1982-84=100), not rates. |
area_ |
string | yes | Census region code, derived from series positions 5-8 (CUUR{area}SAF11): 0100 Northeast, 0200 Midwest, 0300 South, 0400 West. |
area_ |
string | yes | Census region name for the area code, matching econ_reference.bls_geographies.geo_name and the censusRegions map in /bls/bls-constants.yaml. |
value |
double | yes | CPI Food at Home index level for the region (base period 1982-84 = 100), not a rate. Compute period-over-period inflation as a change over this index. |
metro_food_cpi · table¶
Monthly CPI-U Food at Home (SAF11) index level (not seasonally adjusted, base 1982-84=100) for the same 20 major U.S. metropolitan areas as metro_cpi (all items) — item SAF11 instead of SA0. Confirmed against BLS's cu.series catalog: every metro metro_cpi loads also publishes SAF11. For 4-region-level food CPI instead of metro, see regional_food_cpi. For national food CPI (with subcomponents + food away from home), see food_cpi.
| Column | Type | Null | Description |
|---|---|---|---|
date |
date | yes | Observation date, first day of the month, derived from the BLS year and period fields (period M01-M12). BlsResponseTransformer emits year and period rather than a date. |
series |
string | yes | BLS series identifier, format CUUR{area}SAF11 — the embedded 4 characters are the BLS CPI area code. Filter on series: BlsResponseTransformer emits only series, year, period, periodName, value, latest and footnotes, so the area_code and area_name columns are not populated by the current ingest (SUBSTRING(series, 5, 4) recovers the area code). This table is long-format, one row per (series, period). All 20 series are CPI-U Food at Home, not seasonally adjusted (index, base period = 100), one per metro area — the identical 20-metro set as metro_cpi (see that table's comment for the full area code list). Names follow the authoritative BLS decoder (https://download.bls.gov/pub/time.series/cu/cu.area). |
area_ |
string | yes | BLS CPI area code, derived from series positions 5-8 (CUUR{area}SAF11). Join to econ_reference.bls_geographies (metro_cpi_area_code) for CBSA and publication codes. |
area_ |
string | yes | Metro area name for the CPI area code. Spellings follow the BLS CU decoder (https://download.bls.gov/pub/time.series/cu/cu.area), which differs cosmetically from econ_reference.bls_geographies.geo_name in places (e.g. 'St.Paul' vs 'St. Paul') — join bls_geographies on area_code, never on the name. |
value |
double | yes | CPI Food at Home index level for the area (base period 1982-84 = 100), not a rate. |
state_industry · table¶
Monthly employment by industry (22 NAICS supersectors: manufacturing, construction, retail, healthcare, government, and others) for all 53 U.S. jurisdictions (50 states + DC, Puerto Rico, Virgin Islands), from BLS SM series (not seasonally adjusted, statewide total nonfarm all-employees series). For the metro-area version of the same series, see metro_industry (27 major metros).
| Column | Type | Null | Description |
|---|---|---|---|
series |
string | yes | BLS State and Metro Area Employment (SM) series identifier, 20 characters: SMU or SMS + state FIPS(2) + area(5) + supersector(2) + industry(6) + data type(2). SMU = not seasonally adjusted, SMS = seasonally adjusted. Data type 01 = all employees in thousands, 02 = average weekly hours, 03 = average hourly earnings, 11 = average weekly earnings. Decode the supersector with econ_reference.naics_sectors. This table is long-format: one row per (series, period), so ALWAYS filter on series or the decoded code columns. Rows here are the statewide all-employees slice: series matching ^SMU\d{2}00000\d{2}00000001 — area 00000 (statewide), industry 000000 (supersector totals), data type 01, not seasonally adjusted. That is 51 jurisdictions x 22 supersectors = 1,122 series; e.g. SMU01000000000000001 is Alabama, statewide, total nonfarm, all employees. Metro-area rows live in metro_industry and seasonally adjusted (SMS) rows are not loaded. |
year |
integer | yes | Data year |
period |
string | yes | BLS period identifier (M01-M12 monthly, M13 annual average) |
periodName |
string | yes | Human-readable period name derived from period |
value |
double | yes | Employment in thousands |
footnotes |
string | yes | BLS footnote codes for the data point |
state_occupation_employment · table¶
BLS Occupational Employment and Wage Statistics (OEWS) state-level employment headcount for science/engineering occupations, cross-industry (not seasonally adjusted). Covers 3 SOC codes: 17-0000 (Architecture and Engineering Occupations), 19-0000 (Life, Physical, and Social Science Occupations), and 15-1252 (Software Developers, the one Computer Occupations code that fits "scientist/engineer" — Computer Occupations as a whole (15-1xxx) is not, since it includes many non-engineering roles). Closes the gap left by patents.patent_grants (inventor location only, excludes non-inventing R&D staff and academic researchers) and research.nsf_herd_by_institution (dollars, not headcount): this is real state-level occupation-count data, the true denominator a research-workforce-concentration question needs. One row per (state, occupation) per published reference year; small state x occupation cells may be suppressed by BLS (row absent, not zero).
| Column | Type | Null | Description |
|---|---|---|---|
series |
string | no | BLS OEWS series identifier (PK), 25 characters: OEUS + area_code(7, state FIPS padded to 7 digits, e.g. 0100000 for Alabama) + industry_code(6, always 000000 here) + occupation_code(6) + datatype_code(2, always 01 here). Decode with state_fips/occupation_code/occupation_name below rather than re-parsing this. |
year |
integer | yes | OEWS reference year (single most-recently-published year; see table comment) |
state_ |
string | yes | 2-digit state FIPS code, derived from series positions 5-6 |
occupation_ |
string | yes | 6-digit SOC code, derived from series positions 18-23 |
occupation_ |
string | yes | SOC occupation name for occupation_code |
employment |
double | yes | Total employment headcount for the occupation in the state, across all industries (BLS OEWS datatype 01, not seasonally adjusted — OEWS has no seasonal-adjustment concept, it is an annual survey). |
footnotes |
string | yes | BLS footnote codes for the data point |
state_wages · table¶
Annual establishment counts, employment levels, total wages, and average weekly wage for all 51 U.S. jurisdictions (50 states + DC), from BLS QCEW (Quarterly Census of Employment and Wages, released with ~1-year lag). Broken out by NAICS industry (industry_code) AND ownership sector (own_code), so this is the catalog's source for sector- or industry-specific pay by state (e.g. teacher pay, public-sector pay, healthcare-sector pay). Worked example — public K-12 teacher-sector pay: industry_code='611' (educational services), own_code='3' (local government), agglvl_code='55'; use own_code='2' for state government (public universities), '5' for private. This is industry x ownership average pay, NOT occupation-level (SOC), so an industry figure covers every worker in the sector (teachers plus aides/administrators/support), not the occupation alone. For the metro-area version, see metro_wages (27 metros, which also carries average annual pay).
| Column | Type | Null | Description |
|---|---|---|---|
area_ |
string | yes | State FIPS code with 000 suffix (e.g., 01000 for Alabama) |
own_ |
string | yes | Ownership sector: 0=total (all ownerships), 1=federal government, 2=state government, 3=local government, 5=private. Public K-12 schools are 3 (local government); public universities are mostly 2 (state government). |
industry_ |
string | yes | NAICS industry code; '10'=total, all industries. Select a sector for industry-specific pay — e.g. '611'=educational services (schools), '622'=hospitals, '722'=food services & drinking places. Pair with the matching agglvl_code. |
agglvl_ |
string | yes | QCEW aggregation level. '55'=statewide, by 3-digit NAICS subsector, by ownership sector — the grain at which industry-specific pay (e.g. educational services, NAICS 611) resolves to one row per state per ownership; coarser codes roll up to sector or total-all-industries. Any industry-specific query must pin agglvl_code together with industry_code and own_code. |
annual_ |
int | yes | Average annual establishment count. BLS discloses this even for a cell whose employment/wage figures are suppressed (disclosure_code = 'N') — see annual_avg_emplvl. |
annual_ |
int | yes | Average annual employment level. NULL when BLS disclosure_code = 'N' — a single-employer or near-single-employer cell BLS will not publish, not a real 0. Same suppression as county_qcew, sourced from the same QCEW file — see that table's comment for the confirmed live example. Never SUM/AVG without excluding NULLs; treating a suppressed cell as 0 understates the total. |
total_ |
long | yes | Total annual wages in dollars. NULL when BLS disclosure_code = 'N' — see annual_avg_emplvl. |
annual_ |
int | yes | Average weekly wage in dollars. NULL when BLS disclosure_code = 'N' — see annual_avg_emplvl. |
disclosure_ |
string | yes | BLS suppression flag, verbatim from the source. 'N' means annual_avg_emplvl, total_annual_wages and annual_avg_wkly_wage are suppressed to NULL in this table; annual_avg_estabs is disclosed regardless. Blank means no suppression. |
state_ |
string | yes | 2-digit state FIPS code derived from area_fips |
state_ |
string | yes | State name derived from FIPS code |
metro_industry · table¶
Monthly employment by industry (22 NAICS supersectors: manufacturing, retail, healthcare, financial activities, government, and others) for 27 major U.S. metropolitan areas (NYC, LA, Chicago, Houston, and others), from BLS SM series (not seasonally adjusted, all-employees series). For the state-level version of the same series, see state_industry (53 jurisdictions).
| Column | Type | Null | Description |
|---|---|---|---|
series |
string | yes | BLS State and Metro Area Employment (SM) series identifier, 20 characters: SMU or SMS + state FIPS(2) + area(5) + supersector(2) + industry(6) + data type(2). SMU = not seasonally adjusted, SMS = seasonally adjusted. Data type 01 = all employees in thousands, 02 = average weekly hours, 03 = average hourly earnings, 11 = average weekly earnings. Decode the supersector with econ_reference.naics_sectors. This table is long-format: one row per (series, period), so ALWAYS filter on series or the decoded code columns. Rows here are the metro all-employees slice: series matching ^SMU\d{2}(CBSA)\d{2}00000001 for the 27 published metro CBSA codes at positions 6-10, industry 000000 (supersector totals), data type 01, not seasonally adjusted — 27 metros x 22 supersectors; e.g. SMU36935610000000001 is the New York metro, total nonfarm, all employees. Join the embedded CBSA code to econ_reference.bls_geographies (cbsa_code) for metro names. Statewide rows live in state_industry. |
year |
integer | yes | Data year |
period |
string | yes | BLS period identifier (M01-M12 monthly, M13 annual average) |
periodName |
string | yes | Human-readable period name derived from period |
value |
double | yes | Employment in thousands |
footnotes |
string | yes | BLS footnote codes for the data point |
metro_wages · table¶
Annual establishment counts, employment levels, total wages, average weekly wage, and average annual pay for 27 major U.S. metropolitan areas, from BLS QCEW (Quarterly Census of Employment and Wages, released with ~1-year lag). For the state-level version, see state_wages (51 jurisdictions).
| Column | Type | Null | Description |
|---|---|---|---|
area_ |
string | yes | Metro/CSA area FIPS code |
own_ |
string | yes | Ownership code (0=all, 5=private) |
industry_ |
string | yes | NAICS industry code (10=total) |
agglvl_ |
string | yes | Aggregation level code |
annual_ |
int | yes | Average annual establishment count. BLS discloses this even for a cell whose employment/wage figures are suppressed (disclosure_code = 'N') — see annual_avg_emplvl. |
annual_ |
int | yes | Average annual employment level. NULL when BLS disclosure_code = 'N' — a single-employer or near-single-employer cell BLS will not publish, not a real 0. Same suppression as county_qcew, sourced from the same QCEW file — see that table's comment for the confirmed live example. Never SUM/AVG without excluding NULLs; treating a suppressed cell as 0 understates the total. |
total_ |
long | yes | Total annual wages in dollars. NULL when BLS disclosure_code = 'N' — see annual_avg_emplvl. |
annual_ |
int | yes | Average weekly wage in dollars. NULL when BLS disclosure_code = 'N' — see annual_avg_emplvl. |
avg_ |
int | yes | Average annual pay in dollars. NULL when BLS disclosure_code = 'N' — see annual_avg_emplvl. |
disclosure_ |
string | yes | BLS suppression flag, verbatim from the source. 'N' means annual_avg_emplvl, total_annual_wages, annual_avg_wkly_wage and avg_annual_pay are suppressed to NULL in this table; annual_avg_estabs is disclosed regardless. Blank means no suppression. |
metro_ |
string | yes | Metro area publication code (e.g., A419 for Atlanta) |
metro_ |
string | yes | Metro area name |
county_qcew · table¶
County-level establishment counts, employment levels, total annual wages, and average weekly wage from BLS QCEW (Quarterly Census of Employment and Wages), broken out by NAICS industry code and ownership type (private, federal, state, local government), for all ~3,142 U.S. counties. Annual data released with ~1-year lag. See also county_wages, which sources the same county-level QCEW feed.
| Column | Type | Null | Description |
|---|---|---|---|
area_ |
string | yes | County FIPS code |
own_ |
string | yes | Ownership code (private, federal, state, local government) |
industry_ |
string | yes | NAICS industry code |
agglvl_ |
string | yes | Aggregation level code |
annual_ |
int | yes | Average annual establishment count. BLS discloses this even for a cell whose employment/wage figures are suppressed (disclosure_code = 'N') — a non-zero count here with NULL employment does not mean 0 establishments reported 0 workers, it means the count itself was never suppressed. See disclosure_code. |
annual_ |
int | yes | Average annual employment level. NULL when BLS disclosure_code = 'N' — a single-employer or near-single-employer cell whose employment BLS will not publish, not a real 0. Confirmed live: Muhlenberg County KY (FIPS 21177), NAICS 2211, 2015-2022 all carry disclosure_code 'N' with annual_avg_estabs 2-3 alongside this NULL — the plant did not go to zero employment, BLS suppressed the figure. Never SUM/AVG this column without excluding NULLs (the default SQL behavior); treating a suppressed cell as 0 understates the true total. |
total_ |
long | yes | Total annual wages in dollars. NULL when BLS disclosure_code = 'N' — see annual_avg_emplvl, which is suppressed by the same flag and for the same reason. |
annual_ |
int | yes | Average weekly wage in dollars. NULL when BLS disclosure_code = 'N' — see annual_avg_emplvl. |
disclosure_ |
string | yes | BLS suppression flag, verbatim from the source. 'N' means annual_avg_emplvl, total_annual_wages and annual_avg_wkly_wage are suppressed to NULL in this table (see those columns) to protect the identity of a single dominant employer; annual_avg_estabs is disclosed regardless. Blank means no suppression. Kept as its own column, not collapsed into the NULLs, so a caller can distinguish "BLS would not publish this" from any other reason a measure might be NULL. |
county_wages · table¶
County-level establishment counts, employment levels, total annual wages, and average weekly wage from BLS QCEW (Quarterly Census of Employment and Wages), for all 6,038 county-level area codes — the most granular geography in this schema. Annual data released with ~1-year lag. See also county_qcew, which sources the same county-level QCEW feed.
| Column | Type | Null | Description |
|---|---|---|---|
area_ |
string | yes | County FIPS code (5 digits) |
own_ |
string | yes | Ownership code (0=all, 5=private) |
industry_ |
string | yes | NAICS industry code (10=total) |
agglvl_ |
string | yes | Aggregation level code |
annual_ |
int | yes | Average annual establishment count. BLS discloses this even for a cell whose employment/wage figures are suppressed (disclosure_code = 'N') — see annual_avg_emplvl. |
annual_ |
int | yes | Average annual employment level. NULL when BLS disclosure_code = 'N' — a single-employer or near-single-employer cell BLS will not publish, not a real 0. Same suppression as county_qcew, sourced from the same QCEW file — see that table's comment for the confirmed live example (Muhlenberg County KY, NAICS 2211). Never SUM/AVG without excluding NULLs; treating a suppressed cell as 0 understates the total. |
total_ |
long | yes | Total annual wages in dollars. NULL when BLS disclosure_code = 'N' — see annual_avg_emplvl. |
annual_ |
int | yes | Average weekly wage in dollars. NULL when BLS disclosure_code = 'N' — see annual_avg_emplvl. |
disclosure_ |
string | yes | BLS suppression flag, verbatim from the source. 'N' means annual_avg_emplvl, total_annual_wages and annual_avg_wkly_wage are suppressed to NULL in this table; annual_avg_estabs is disclosed regardless. Blank means no suppression. |
county_ |
string | yes | County FIPS code alias |
state_ |
string | yes | State FIPS code derived from county FIPS |
jolts_regional · table¶
Monthly JOLTS (Job Openings and Labor Turnover Survey) job openings, hires, total separations, quits, and layoffs/discharges — rates and levels — for the U.S. national total plus the 4 Census regions (Northeast, Midwest, South, West). For national industry-level detail see jolts_industry; for state-level detail see jolts_state.
| Column | Type | Null | Description |
|---|---|---|---|
date |
date | yes | Observation date (ISO 8601 format) |
series |
string | yes | BLS JOLTS series identifier, 21 characters: JTS + industry(6, positions 4-9) + state or region(2, positions 10-11) + area(5, positions 12-16) + size class(2, positions 17-18) + data element(2, positions 19-20) + rate/level(1, position 21). Data element codes: JO job openings, HI hires, TS total separations, QU quits, LD layoffs and discharges, OS other separations, UO unemployed persons per job opening, R1/R2 survey response rates. Rate/level: L = level in thousands, R = rate (percent of employment; job openings rate uses employment plus openings as the base). Decode industry with econ_reference.jolts_industries and the data element with econ_reference.jolts_dataelements. This table is long-format: one row per (series, period), so ALWAYS filter on series (or the decoded code columns) — never aggregate across data elements or across L and R together. Rows here are the national and 4-region slice: series matching ^JTS000000(00|NE|MW|SO|WE) — industry is always 000000 (total nonfarm) and positions 10-11 hold 00 for the U.S. or NE/MW/SO/WE for the Census region. |
region_ |
string | yes | Census region code (00=US, NE/MW/SO/WE for regions) |
region_ |
string | yes | Census region name |
metric_ |
string | yes | JOLTS metric (hires, separations, openings, quits, layoffs) |
value |
double | yes | JOLTS metric value (levels in thousands, rates as percentage) |
jolts_industry · table¶
Monthly JOLTS (Job Openings and Labor Turnover Survey) job openings, hires, total separations, quits, and layoffs/discharges — rates and levels — broken out by industry sector, national level only. For the regional/national-total version see jolts_regional; for state-level detail see jolts_state.
| Column | Type | Null | Description |
|---|---|---|---|
industry_ |
string | yes | JOLTS industry code at series_id positions 4-9 (000000 = total nonfarm, excluded here) |
year |
int | yes | Year |
date |
string | yes | Observation date - period field (M01, M02, etc.) |
series |
string | yes | BLS JOLTS series identifier, 21 characters: JTS + industry(6, positions 4-9) + state or region(2, positions 10-11) + area(5, positions 12-16) + size class(2, positions 17-18) + data element(2, positions 19-20) + rate/level(1, position 21). Data element codes: JO job openings, HI hires, TS total separations, QU quits, LD layoffs and discharges, OS other separations, UO unemployed persons per job opening, R1/R2 survey response rates. Rate/level: L = level in thousands, R = rate. Decode industry with econ_reference.jolts_industries and the data element with econ_reference.jolts_dataelements. This table is long-format: one row per (series, period), so ALWAYS filter on series (or the decoded code columns) — never aggregate across data elements or across L and R together. Rows here are the national by-industry slice: series matching ^JTS[1-9][0-9]{5}00 — a non-total industry at positions 4-9 with national geography (00) at 10-11. |
metric_ |
string | yes | JOLTS metric code (last 3 chars - element + rate/level, e.g. JOR, HIR, HIL) |
value |
double | yes | JOLTS metric value (levels in thousands, rates as percentage) |
jolts_state · table¶
Monthly JOLTS (Job Openings and Labor Turnover Survey) job openings, hires, total separations, quits, and layoffs/discharges — rates and levels — for all 51 U.S. jurisdictions (50 states + DC), total nonfarm only. For the national-total/regional version see jolts_regional; for national industry-level detail see jolts_industry.
| Column | Type | Null | Description |
|---|---|---|---|
state_ |
string | yes | State FIPS code at series_id positions 10-11 (positions 4-9 are the industry field) |
state_ |
string | yes | State name from the state FIPS at series_id positions 10-11; unmapped codes captured as-is |
year |
int | yes | Year |
date |
string | yes | Observation date - period field (M01, M02, etc.) |
series |
string | yes | BLS JOLTS series identifier, 21 characters: JTS + industry(6, positions 4-9) + state or region(2, positions 10-11) + area(5, positions 12-16) + size class(2, positions 17-18) + data element(2, positions 19-20) + rate/level(1, position 21). Data element codes: JO job openings, HI hires, TS total separations, QU quits, LD layoffs and discharges, OS other separations, UO unemployed persons per job opening, R1/R2 survey response rates. Rate/level: L = level in thousands, R = rate. Decode industry with econ_reference.jolts_industries and the data element with econ_reference.jolts_dataelements. This table is long-format: one row per (series, period), so ALWAYS filter on series (or the decoded code columns) — never aggregate across data elements or across L and R together. Rows here are the by-state slice: series matching ^JTS000000(0[1-9]|[1-9][0-9]) — industry is always 000000 (total nonfarm) and positions 10-11 hold the state FIPS code. |
metric_ |
string | yes | JOLTS metric code (last 3 chars - JOR, HIR, TSR, QUR, LDR) |
value |
double | yes | JOLTS metric value (levels in thousands, rates as percentage) |
wage_growth · table¶
Monthly average hourly earnings (all employees, and production/nonsupervisory employees separately) and average weekly earnings, nominal dollars, for total private employees nationally — from BLS Current Employment Statistics (CES). Not inflation-adjusted; deflate with inflation_metrics for real wage growth.
| Column | Type | Null | Description |
|---|---|---|---|
date |
date | yes | Observation date, first day of the month, derived from the BLS year and period fields (period M01-M12). BlsResponseTransformer emits year and period rather than a date. |
series |
string | yes | BLS CES series identifier. This table is long-format: one row per (series, period), and it mixes hourly with weekly earnings, so ALWAYS filter on series. The 3 series loaded are: CES0500000003 average hourly earnings, all employees, total private (dollars per hour); CES0500000008 average hourly earnings, production and nonsupervisory employees, total private (dollars per hour); CES0500000011 average weekly earnings, all employees, total private (dollars per week). Nominal dollars — deflate with inflation_metrics for real wage growth. |
value |
double | yes | Earnings value (hourly or weekly depending on series) |
labor_productivity · table¶
BLS Major Sector Productivity and Costs (PR survey) labor productivity index — output per hour of all persons, chained index (2017=100), seasonally adjusted, quarterly — for 6 major-sector series: business, nonfarm business, manufacturing, manufacturing durable goods, manufacturing nondurable goods, and nonfinancial corporations. National grain only; BLS does not publish this series by state. api.bls.gov itself carries business/nonfarm business/nonfinancial corporations back to 1947 and manufacturing (plus its durable/ nondurable subsectors) back to 1987 (not a uniform start) — but this table's own observedCoverage below is the source of truth for what's actually loaded here, uniformly, across all 6 series; check it rather than assuming it matches BLS's full range. Only the productivity (output-per-hour) index is loaded — BLS's companion unit-labor-cost and compensation series from the same PR survey (measure codes 06, 11, etc.) are not included. Long-format: one row per (series, period) — ALWAYS filter on series.
| Column | Type | Null | Description |
|---|---|---|---|
date |
date | yes | Observation date, first day of the quarter, derived from the BLS year and period fields (period Q01-Q04). BlsResponseTransformer emits year and period rather than a date. |
series |
string | yes | BLS PR series identifier. This table is long-format: one row per (series, period), so ALWAYS filter on series. The 6 series loaded are all output-per-hour index (measure 09, duration 3 = index level, 2017=100): PRS85006093 nonfarm business; PRS84006093 business; PRS30006093 manufacturing; PRS31006093 manufacturing, durable goods; PRS32006093 manufacturing, nondurable goods; PRS88003093 nonfinancial corporations. |
value |
double | yes | Labor productivity index value (output per hour of all persons, 2017=100) |
regional_employment · table¶
Monthly state-level unemployment rate, unemployment level, employment level, and civilian labor force level (persons) for all 51 U.S. jurisdictions, from BLS Local Area Unemployment Statistics (LAUS). This is labor force size, not the labor force participation rate (see employment_statistics for the national participation rate).
| Column | Type | Null | Description |
|---|---|---|---|
series |
string | yes | BLS Local Area Unemployment Statistics (LAUS) series identifier, format LASST{state FIPS(2)}{area(10)}{measure(2)} — e.g. LASST010000000000003 is the Alabama statewide unemployment rate. The trailing measure code selects the statistic: 03 unemployment rate (percent), 04 unemployment level, 05 employment level, 06 civilian labor force level (levels in persons). 204 series are loaded = 51 jurisdictions x those 4 measures; the full list is in /bls/regional-employment-series.json. This table is long-format: one row per (series, period) and it mixes a percent with three person counts, so ALWAYS filter on series or the derived measure column before aggregating. |
year |
integer | yes | Observation year |
period |
string | yes | BLS period identifier (M01-M12 for monthly) |
value |
double | yes | Employment statistic value |
state_ |
string | yes | 2-digit state FIPS code derived from series ID (LASST{fips}...) |
measure |
string | yes | Employment measure type derived from series ID |
treasury_yields · table¶
Average interest rates on U.S. Treasury securities (Treasury Bills, Notes, Bonds, TIPS, and other security types) by security_type_desc/security_desc and record_date, from the Treasury Fiscal Data "Average Interest Rates" API. This is one average rate per security description, not a full yield curve broken out by specific maturity (1-month through 30-year).
| Column | Type | Null | Description |
|---|---|---|---|
record_ |
date | yes | Date of the interest rate record (YYYY-MM-DD format) |
security_ |
string | yes | Type of Treasury security (e.g., 'Treasury Bills', 'Treasury Notes') |
security_ |
string | yes | Description of the security |
avg_ |
double | yes | Average interest rate as a percentage |
src_ |
string | yes | Source line number identifier |
federal_debt · table¶
U.S. federal debt statistics including total public debt, debt held by public, and intragovernmental holdings. Tracks debt levels, composition, and trends. Source: Treasury Fiscal Data API.
| Column | Type | Null | Description |
|---|---|---|---|
record_ |
date | yes | Date of the debt record (YYYY-MM-DD format) |
debt_ |
double | yes | Debt held by the public in dollars |
intragov_ |
double | yes | Intragovernmental holdings in dollars |
tot_ |
double | yes | Total public debt outstanding in dollars |
src_ |
string | yes | Source line number identifier |
federal_outlays_by_function · table¶
OMB Historical Table 3.2 ("Outlays by Function and Subfunction") — federal outlays broken out by OMB's 20 canonical budget-function codes (050 National Defense, 570 Medicare, 650 Social Security, 900 Net Interest, etc.), FY1962-2031 (recent years are OMB's own budget-cycle actuals; later years are the President's Budget estimates, see is_estimate). Closes the gap left by fiscal.usaspending_by_state/county/district (place-of-performance geography, not function) and this schema's own federal_debt/ treasury_yields (debt levels and borrowing cost, not spending composition) — none of those answer "how much of federal spending is Social Security vs. defense vs. net interest." One row per (year, function); each function's total is OMB's own explicit "Total,
| Column | Type | Null | Description |
|---|---|---|---|
year |
integer | no | Federal fiscal year |
is_ |
boolean | no | True for years beyond the budget cycle's own actuals (OMB's "estimate"-suffixed columns — the President's Budget projection, not a measured outlay) |
function_ |
string | no | 3-digit OMB budget-function code (e.g. "050", "570", "650", "900") |
function_ |
string | no | OMB's own function name (e.g. "National Defense", "Medicare", "Social Security") |
outlays_ |
double | yes | Outlays in current-dollar millions for this function/year; null where OMB's own cell is a placeholder ("..........") rather than a figure, not coerced to zero |
ces_revision_vintages · table¶
BLS's own CES revision-vintage product (cesvinall.zip: "as originally published" vintages for every monthly Employment Situation release since May 2003) — the revision/vintage history that employment_statistics does not carry (each row there is only the CURRENT, most-revised value; this table lets a caller see what a series looked like as first published vs. after subsequent revisions). One row per (series, release, reference_period): release_year/release_month identify which monthly Employment Situation release this vintage snapshot is from; reference_year/ reference_month identify which data month the value describes; value is that release's published estimate for that data month (thousands of employees, CES convention). Comparing rows with the same series_id + reference_period across different release_period values shows the revision path for that data month — including the annual February benchmark revision, which is simply a jump in value for prior-year reference months between the January and February releases; no second QCEW source is needed to see it. PARTIAL/SCOPED BUILD BY DESIGN, in two ways: (1) series scope — the source ZIP carries 113 CES industry codes (226 SA/NSA files); only 4 are loaded — total nonfarm (CES/CEU0000000001), total private (CES/CEU0500000001), goods-producing (CES/CEU0600000001), and service-providing (CES/CEU0700000001) — not the 58+ deeper industry-detail series. (2) reference- period window — BLS's own file melts to the FULL 1939-present history in every release row (1000+ columns), but reference months more than a few years old are permanently frozen (never revised again) by the time they age out of BLS's own benchmark-revision reach, so repeating them in every subsequent vintage is pure duplication with zero revision signal; each release here keeps only its most recent 73 reference months (~6 years) — see CesRevisionVintagesTransformer javadoc for the exact windowing logic. Widening either scope later only requires editing that transformer's target-code set / window size, not re-deriving the source. series_id is reconstructed from the ZIP's filename industry_code (see column comment) since the file itself never spells out the full BLS series ID.
| Column | Type | Null | Description |
|---|---|---|---|
series_ |
string | no | Reconstructed BLS series ID: CES (seasonally adjusted) or CEU (not seasonally adjusted) + 6-digit industry_code + data-type suffix "0001" (all employees, thousands). One of CES0000000001/CEU0000000001 (total nonfarm), CES0500000001/CEU0500000001 (total private), CES0600000001/CEU0600000001 (goods-producing), CES0700000001/CEU0700000001 (service-providing). |
seasonally_ |
boolean | no | True for the CES-prefixed (SA) series, false for the CEU-prefixed (NSA) series |
industry_ |
string | no | Raw 6-digit CES industry/supersector code from the source ZIP's filename (e.g. "000000" total nonfarm, "050000" total private) — the input series_id was reconstructed from. |
release_ |
integer | no | Year of the monthly Employment Situation release this vintage snapshot is from |
release_ |
integer | no | Month (1-12) of the monthly Employment Situation release this vintage snapshot is from |
release_ |
date | yes | release_year/release_month as a first-of-month date, for convenient range filtering/ordering |
reference_ |
integer | no | Year of the data (reference) month this value describes |
reference_ |
integer | no | Month (1-12) of the data (reference) month this value describes |
reference_ |
date | yes | reference_year/reference_month as a first-of-month date, for convenient range filtering/ordering |
value |
double | yes | As-published employment level for this vintage (thousands of employees, CES convention — same units as employment_statistics' CES0000000001/CES0500000001). BLS's own source file uses -1 as a "not yet available" sentinel for a handful of rows (confirmed live: every series' Sep/Oct 2025 reference months, as first published in the Oct 2025 release, consistent with a federal shutdown) — that -1 is preserved verbatim (not nulled or guessed), matching every other value in this column being exactly BLS's own published figure. Filter WHERE value > -1 (or use a later release_period, where BLS replaces it with a real estimate) for arithmetic use. |
world_indicators · table¶
International economic indicators from World Bank for all countries. Includes GDP, inflation, unemployment, government debt, and population statistics. Uses bulk download with response partitioning: one API call per indicator returns all countries/years, then partitioned on write.
| Column | Type | Null | Description |
|---|---|---|---|
countryiso3code |
string | yes | ISO 3-letter country code (e.g., 'USA', 'CHN') |
date |
integer | yes | Year of observation (e.g., 2022) |
value |
double | yes | Indicator value for the given country and year |
unit |
string | yes | Unit of measurement (e.g., 'current US$', 'percent') |
obs_ |
string | yes | Observation status flag |
decimal |
int | yes | Decimal precision indicator |
fred_indicators · table¶
Federal Reserve Economic Data (FRED) time series raw observations. For metadata (series name, units, frequency, etc.), JOIN with reference_fred_series or use fred_indicators_enriched view.
| Column | Type | Null | Description |
|---|---|---|---|
series |
string | yes | FRED series identifier (foreign key to reference_fred_series, which carries the title, units, frequency, and seasonal adjustment for each). This table is long-format: one row per (series, date), so ALWAYS filter on series — units differ per series (percent, index, billions of dollars, thousands of units). The 50 curated series are: rates and monetary policy — DFF effective federal funds rate, FEDFUNDS federal funds target rate, DGS3MO / DGS2 / DGS5 / DGS10 / DGS30 Treasury yields at those maturities, T10Y2Y 10Y-2Y yield spread, T10YIE 10-year breakeven inflation, BAMLC0A0CM investment-grade corporate bond spread; money and Fed balance sheet — M1SL M1, M2SL M2, BOGMBASE monetary base, WALCL Fed total assets; national accounts — GDP nominal GDP, GDPC1 real GDP (chained 2017 dollars); labor — UNRATE unemployment rate, U6RATE U-6 underutilization, PAYEMS nonfarm payrolls, CIVPART labor force participation rate, ICSA initial jobless claims (weekly), CCSA continuing claims, AWHMAN average weekly hours in manufacturing; inflation — CPIAUCSL CPI all items SA, CPIAUCNS CPI all items NSA, CPILFESL core CPI ex food and energy SA, WPUFD49207 PPI finished goods, PCEPI PCE price index, PCEPILFE core PCE price index (the Fed's primary target); consumer — PCE personal consumption expenditures, PI personal income, PSAVERT personal saving rate; production and credit — INDPRO industrial production, TCU capacity utilization, RSXFS advance retail sales, TOTBKCR total bank credit, DRTSCILM senior loan officer survey lending standards; retail trade — RETAILIMSA retailers inventories SA (Census MTIS via FRED, a different quantity from RSXFS sales), RETAILIRSA retailers inventories-to-sales ratio SA; housing — HOUST housing starts, HOUST1F single-family starts, PERMIT building permits, EXHOSLUSM495S existing home sales, CSUSHPISA Case-Shiller national home price index, MSPUS median sales price of houses sold, RRVRUSQ156N rental vacancy rate, MORTGAGE30US 30-year fixed mortgage rate; commodities and FX — DCOILWTICO WTI crude oil price, DTWEXBGS broad trade-weighted dollar index; business cycle — USREC NBER recession indicator (1=recession, 0=expansion). |
date |
date | yes | Observation date (ISO 8601 format) |
value |
double | yes | Observed value for this date |
national_accounts · table¶
National Income and Product Accounts (NIPA) data from BEA covering comprehensive economic statistics. Includes ALL NIPA tables across 8 sections: 1=Domestic Product & Income (GDP, national income), 2=Personal Income & Outlays (wages, consumer spending), 3=Government (receipts, expenditures), 4=Foreign Transactions (exports, imports), 5=Saving & Investment (capital formation), 6=Industry (sectoral data), 7=Supplemental (per capita, additional details), 8=Not Seasonally Adjusted. Tables dynamically discovered from BEA reference catalog.
| Column | Type | Null | Description |
|---|---|---|---|
table_ |
string | yes | BEA table identifier (e.g., 'T10101') |
line_ |
string | yes | Line number within the BEA table (raw string) |
line_ |
string | yes | Description of the GDP component |
series_ |
string | yes | BEA series code for this component |
time_ |
string | yes | BEA time period (e.g., '2023' for annual, '2023Q1' for quarterly) |
data_ |
string | yes | Raw data value from BEA API |
cl_ |
string | yes | Units of measurement (e.g., 'Millions of Dollars') |
unit_ |
string | yes | Unit multiplier |
note_ |
string | yes | Note reference |
table_ |
yes | BEA table identifier for GDP components | |
line_ |
yes | Line number within the BEA table | |
year |
yes | Year extracted from time period | |
value |
yes | Component value in millions of dollars (NULL for unavailable data) | |
units |
yes | Units of measurement | |
frequency |
yes | Data frequency (A=Annual, Q=Quarterly) |
regional_price_parities · table¶
BEA Regional Price Parities (RPP): the standard federal cost-of-living / price-level index by state (search terms: cost of living, COLA, price parity, RPP) — by state and Metropolitan Statistical Area (MSA) relative to the national average (100.000), annual since 2008. This is the table to divide nominal wages/income by (index/100) to get real, cost-of-living-adjusted dollars comparable across states. Verified live against apps.bea.gov/api/data (dataset Regional, tables SARPP/MARPP): coverage 2008-2024 at verification time. tablename selects the geography: SARPP = state (includes a GeoFips=00000 "United States" national baseline row, always rpp_index=100.000, plus all 50 states + DC); MARPP = Metropolitan Statistical Areas only (no non-metro/rural, micropolitan, or port-area coverage — BEA's port-level PARPP table is not loaded here). line_code selects the price component: 1=All items, 2=Goods, 3=Services: Rents, 4=Services: Utilities, 5=Services: Other. An rpp_index of e.g. 88.901 means costs are 11.1% below the national average for that component/geography/year. (BEA's Regional dataset also lists SARPP/MARPP among the 63 tables regional_income iterates, but that table's line_code resolver only has reference data for SAINC/SQINC/CAINC/SAGDP/SQGDP/CAGDP series, so regional_income never actually fetches RPP rows — this table is the source of that data.)
| Column | Type | Null | Description |
|---|---|---|---|
geo_ |
string | yes | 5-digit FIPS (state) or CBSA (MSA) code; '00000' = United States national baseline |
geo_ |
string | yes | State name (tablename=SARPP) or MSA name (tablename=MARPP) |
year |
int | yes | Year of the RPP observation |
line_ |
string | yes | BEA RPP line code — dimension-iterated per fetch, not present in the raw API row itself: 1=All items, 2=Goods, 3=Services: Rents, 4=Services: Utilities, 5=Services: Other. |
rpp_ |
double | yes | Regional Price Parity index; 100.000 = national average cost of living for that component (e.g. 88.901 means costs 11.1% below the national average) |
units |
string | yes | Always 'Index' (BEA CL_UNIT field) |
state_personal_income · table¶
State-level personal income and its components — total personal income, population, per capita income, wages and salaries, transfer receipts, and more — from all 12 active BEA SAINC tables, for all 50 states + DC. BEA publishes this series annually back to 1929 through the present; this table's own observedCoverage below is the source of truth for what's actually loaded — check it rather than assuming it matches BEA's full range. For county/MSA-level income (and all other BEA regional topics: GDP, consumption, wages by industry), see regional_income.
| Column | Type | Null | Description |
|---|---|---|---|
geo_ |
string | yes | FIPS code for the geographic area (e.g., '01000' for Alabama) |
geo_ |
string | yes | Name of the geographic area (state name) |
region |
string | yes | Census region |
table_ |
string | yes | BEA table identifier (e.g., SAINC1, SAINC30) |
line_ |
string | yes | BEA line code — the row identifier within a BEA regional table. A line_code is only unique within a table, so ALWAYS filter or join on (table_name, line_code): the same line_code means different things in different BEA tables. Each row already carries the line's own label in the description column and its unit in the unit column; for the full catalog, join (table_name, line_code) to econ_reference.regional_linecodes (tablename, key). This table is long-format — one row per (geo_fips, table_name, line_code, year) — so never sum value across line codes: they mix dollars, index numbers, and counts, and many lines are subtotals of other lines. |
industry_ |
string | yes | Industry classification code |
description |
string | yes | Description of the metric |
unit |
string | yes | Unit of measurement |
year |
integer | yes | Year (unpivoted from column headers) |
data_ |
double | yes | Value for this year (unpivoted from wide format) |
state_gdp · table¶
State-level GDP — summary, by-industry breakdowns, and chain-type quantity/price indexes — from all 10 active BEA SAGDP tables, annual, for all 50 states + DC. For county/MSA-level GDP see regional_income; for quarterly state GDP see state_quarterly_gdp.
| Column | Type | Null | Description |
|---|---|---|---|
geo_ |
string | yes | FIPS code for the geographic area |
geo_ |
string | yes | Name of the geographic area (state name) |
region |
string | yes | Census region |
table_ |
string | yes | BEA table identifier (e.g., SAGDP1, SAGDP2) |
line_ |
string | yes | BEA line code — the row identifier within a BEA regional table. A line_code is only unique within a table, so ALWAYS filter or join on (table_name, line_code): the same line_code means different things in different BEA tables. Each row already carries the line's own label in the description column and its unit in the unit column; for the full catalog, join (table_name, line_code) to econ_reference.regional_linecodes (tablename, key). This table is long-format — one row per (geo_fips, table_name, line_code, year) — so never sum value across line codes: they mix dollars, index numbers, and counts, and many lines are subtotals of other lines. |
industry_ |
string | yes | Industry classification code |
description |
string | yes | Description of the metric |
unit |
string | yes | Unit of measurement |
year |
integer | yes | Year (unpivoted from column headers) |
data_ |
double | yes | Value for this year (unpivoted from wide format) |
state_quarterly_income · table¶
State-level personal income and its components, at quarterly cadence, from all 6 active BEA SQINC tables, for all 50 states + DC. For the annual version see state_personal_income; for county/MSA-level income see regional_income.
| Column | Type | Null | Description |
|---|---|---|---|
geo_ |
string | yes | FIPS code for the geographic area |
geo_ |
string | yes | Name of the geographic area (state name) |
region |
string | yes | Census region |
table_ |
string | yes | BEA table identifier (e.g., SQINC1, SQINC35) |
line_ |
string | yes | BEA line code — the row identifier within a BEA regional table. A line_code is only unique within a table, so ALWAYS filter or join on (table_name, line_code): the same line_code means different things in different BEA tables. Each row already carries the line's own label in the description column and its unit in the unit column; for the full catalog, join (table_name, line_code) to econ_reference.regional_linecodes (tablename, key). This table is long-format — one row per (geo_fips, table_name, line_code, year) — so never sum value across line codes: they mix dollars, index numbers, and counts, and many lines are subtotals of other lines. |
industry_ |
string | yes | Industry classification code |
description |
string | yes | Description of the metric |
unit |
string | yes | Unit of measurement |
year |
string | yes | Year/Quarter (unpivoted from column headers, e.g., 2020Q1) |
data_ |
double | yes | Value for this period (unpivoted from wide format) |
state_quarterly_gdp · table¶
State-level GDP, at quarterly cadence, from all 5 active BEA SQGDP tables, for all 50 states + DC. For the annual version see state_gdp; for county/MSA-level GDP see regional_income.
| Column | Type | Null | Description |
|---|---|---|---|
geo_ |
string | yes | FIPS code for the geographic area |
geo_ |
string | yes | Name of the geographic area (state name) |
region |
string | yes | Census region |
table_ |
string | yes | BEA table identifier (e.g., SQGDP1, SQGDP2) |
line_ |
string | yes | BEA line code — the row identifier within a BEA regional table. A line_code is only unique within a table, so ALWAYS filter or join on (table_name, line_code): the same line_code means different things in different BEA tables. Each row already carries the line's own label in the description column and its unit in the unit column; for the full catalog, join (table_name, line_code) to econ_reference.regional_linecodes (tablename, key). This table is long-format — one row per (geo_fips, table_name, line_code, year) — so never sum value across line codes: they mix dollars, index numbers, and counts, and many lines are subtotals of other lines. |
industry_ |
string | yes | Industry classification code |
description |
string | yes | Description of the metric |
unit |
string | yes | Unit of measurement |
year |
string | yes | Year/Quarter (unpivoted from column headers, e.g., 2020Q1) |
data_ |
double | yes | Value for this period (unpivoted from wide format) |
state_consumption · table¶
State-level personal consumption expenditures (PCE) by category, from all 4 active BEA SAPCE tables, annual, for all 50 states + DC. For county/MSA-level BEA data see regional_income.
| Column | Type | Null | Description |
|---|---|---|---|
geo_ |
string | yes | FIPS code for the geographic area |
geo_ |
string | yes | Name of the geographic area (state name) |
region |
string | yes | Census region |
table_ |
string | yes | BEA table identifier (e.g., SAPCE1, SAPCE2) |
line_ |
string | yes | BEA line code — the row identifier within a BEA regional table. A line_code is only unique within a table, so ALWAYS filter or join on (table_name, line_code): the same line_code means different things in different BEA tables. Each row already carries the line's own label in the description column and its unit in the unit column; for the full catalog, join (table_name, line_code) to econ_reference.regional_linecodes (tablename, key). This table is long-format — one row per (geo_fips, table_name, line_code, year) — so never sum value across line codes: they mix dollars, index numbers, and counts, and many lines are subtotals of other lines. |
industry_ |
string | yes | Industry classification code |
description |
string | yes | Description of the metric |
unit |
string | yes | Unit of measurement |
year |
integer | yes | Year (unpivoted from column headers) |
data_ |
double | yes | Value for this year (unpivoted from wide format) |
regional_income · table¶
State, county, MSA, and other sub-national (micropolitan, port, metro division, CSA) personal income, GDP, consumption, wages, arts & culture, and outdoor recreation statistics — all 63 BEA Regional Economic Accounts tables. The only source of county/MSA-level BEA income and GDP in this schema; slower per-API-call ingestion than the state-only bulk-download tables (state_personal_income, state_gdp, state_quarterly_income, state_quarterly_gdp, state_consumption), which cover the same state-level topics faster.
| Column | Type | Null | Description |
|---|---|---|---|
geo_ |
string | yes | FIPS code for the geographic area (e.g., '01000' for Alabama) |
geo_ |
string | yes | Name of the geographic area |
time_ |
string | yes | Year or Quarter - e.g. 2020 or 2023Q1 |
cl_ |
string | yes | Income metric type (e.g., 'Personal Income', 'Per Capita Income') |
unit_ |
string | yes | Unit multiplier (e.g., thousands, millions) |
data_ |
double | yes | Income value in thousands of dollars or dollars (depending on metric) |
year |
string | yes | Year partition from dimension iteration |
geo_ |
string | yes | Geographic level - STATE, COUNTY, MSA, etc. |
tablename |
string | yes | BEA table identifier (e.g., SAGDP1, SAINC1) |
line_ |
string | yes | BEA line code — the row identifier within a BEA regional table, iterated per BEA table during ingestion. A line_code is only unique within a table, so ALWAYS pair it with the tablename column when filtering or joining; join (tablename, line_code) to econ_reference.regional_linecodes (tablename, key) for the line description. Units come from cl_unit and unit_mult, and they differ by line. This table is long-format — one row per (geo_fips, tablename, line_code, time_period) — so never sum data_value across line codes: they mix dollars, counts, and index numbers, and many lines are subtotals. |
ita_data · table¶
International Transactions Accounts (ITA) from BEA: annual, national-level balance of payments — goods/services trade balances, exports and imports of goods and services, current account, capital account, and primary/secondary income balances.
| Column | Type | Null | Description |
|---|---|---|---|
data_ |
double | yes | Transaction value |
area_ |
string | yes | Geographic area or country name for the transaction |
time_ |
string | yes | Description of the time series |
series_ |
string | yes | Unique identifier for the time series |
time_ |
string | yes | Time period for the data point (e.g., '2022', '2022Q1') |
cl_ |
string | yes | Classification unit (e.g., 'USD') |
unit_ |
string | yes | Unit multiplier (e.g., '6' for millions) |
metric_ |
string | yes | Type of metric (e.g., 'Current Dollars') |
frequency |
string | yes | Data frequency ('A' for Annual, 'Q' for Quarterly) |
note_ |
string | yes | Reference to explanatory notes |
fdi_direct_investment · table¶
BEA Direct Investment (DI) statistics from the Multinational Enterprises (MNE) dataset. Balance-of-payments direct investment: position (stock), income, financial transactions, equity, debt instruments, and reinvested earnings. Inward (foreign investment in the U.S.) and outward (U.S. investment abroad), by partner country or by industry. Tall layout: one row per statistic (series_id) x area/industry (row_code) x year.
| Column | Type | Null | Description |
|---|---|---|---|
series_ |
string | yes | BEA MNE series identifier. The MNE dataset is a long-format cube: one row per (series_id, row_code, column_code, time_period), and each row carries its own label in series_name, so read series_name rather than memorizing codes — there is no separate series reference table. Ingestion requests SeriesID=all, so the loaded domain is whatever BEA publishes for the requested direction and classification; enumerate it with SELECT DISTINCT series_id, series_name. Common values: 30 = U.S. direct investment position abroad, 22 = foreign direct investment position in the U.S., 27 = direct investment income. ALWAYS filter on series_id before aggregating — positions (stocks), income, and financial flows share this column and are not additive. Scale comes from table_scale, not from the raw data_value. |
series_ |
string | yes | Human-readable statistic name |
row_ |
string | yes | Country or industry code (matches the classification partition) |
row_ |
string | yes | Country or industry name |
column_ |
string | yes | Statistic sub-breakdown code |
column_ |
string | yes | Statistic sub-breakdown name |
data_ |
double | yes | Statistic value in the units given by table_scale (null when BEA suppressed the value) |
table_ |
string | yes | Units for data_value (e.g. 'Millions of Dollars', 'Thousands of Employees') |
time_ |
string | yes | Data year (YYYY) |
fdi_activities · table¶
BEA Activities of Multinational Enterprises (AMNE) statistics from the MNE dataset, majority-owned affiliates (bank and nonbank). Operations of U.S. affiliates of foreign firms (inward) and foreign affiliates of U.S. firms (outward): employment, total sales, total assets, value added, net income, capital expenditures, R&D expenditures, and affiliate trade in goods. By partner country or by industry. Same tall layout as fdi_direct_investment; note table_scale mixes dollars and headcounts.
| Column | Type | Null | Description |
|---|---|---|---|
series_ |
string | yes | BEA MNE series identifier. The MNE dataset is a long-format cube: one row per (series_id, row_code, column_code, time_period), and each row carries its own label in series_name, so read series_name rather than memorizing codes — there is no separate series reference table. Ingestion requests SeriesID=all, so the loaded domain is whatever BEA publishes for the requested direction and classification; enumerate it with SELECT DISTINCT series_id, series_name. Common values: 30 = U.S. direct investment position abroad, 22 = foreign direct investment position in the U.S., 27 = direct investment income. ALWAYS filter on series_id before aggregating — positions (stocks), income, and financial flows share this column and are not additive. Scale comes from table_scale, not from the raw data_value. |
series_ |
string | yes | Human-readable statistic name |
row_ |
string | yes | Country or industry code (matches the classification partition) |
row_ |
string | yes | Country or industry name |
column_ |
string | yes | Statistic sub-breakdown code |
column_ |
string | yes | Statistic sub-breakdown name |
data_ |
double | yes | Statistic value in the units given by table_scale (null when BEA suppressed the value) |
table_ |
string | yes | Units for data_value (e.g. 'Millions of Dollars', 'Thousands of Employees') |
time_ |
string | yes | Data year (YYYY) |
iip_positions · table¶
BEA International Investment Position (IIP): U.S. external assets and liabilities by type of investment, reporting position (stock) and the change in position decomposed by driver (transactions, price changes, exchange-rate changes, other). Tall layout: one row per type_of_investment x component x period.
| Column | Type | Null | Description |
|---|---|---|---|
type_ |
string | yes | Asset/liability category code (e.g. CurrAndDepAssets, DirectInvAssets) |
component |
string | yes | Measure code (Pos=position; ChgPos*=change in position by driver) |
time_ |
string | yes | BEA series identifier (e.g. TSI_IipCurrAndDepAssetsChgPos_A) |
time_ |
string | yes | Human-readable series description |
time_ |
string | yes | Data period ('2022' annual, '2022Q1' quarterly) |
data_ |
double | yes | Position or change value in the units given by cl_unit/unit_mult (null when BEA suppressed) |
cl_ |
string | yes | Classification unit (e.g. 'USD') |
unit_ |
string | yes | Unit multiplier (e.g. '6' for millions) |
frequency |
string | yes | Data frequency ('A' for Annual) |
note_ |
string | yes | Reference to explanatory notes |
gdp_statistics · table¶
National GDP growth rates, nominal and real GDP levels, and other line items from BEA NIPA table T10101, annual and quarterly. Sources the same T10101 table that's also included in the broader national_accounts table (which covers 8 additional NIPA sections); for industry-level GDP breakdowns see industry_gdp.
| Column | Type | Null | Description |
|---|---|---|---|
year |
int | yes | Year of the GDP observation (extracted from TimePeriod) |
quarter |
int | yes | Quarter number (1-4) for quarterly data, null for annual data |
metric |
string | yes | GDP metric name (e.g., 'Nominal GDP', 'Real GDP', 'Personal Consumption') |
value |
double | yes | GDP value in millions of dollars (BEA returns comma-formatted numbers) |
percent_ |
double | yes | Percent change from previous period (not available in raw NIPA data) |
seasonally_ |
boolean | yes | Whether the data is seasonally adjusted |
industry_gdp · table¶
GDP by industry from the BEA GDPbyIndustry dataset, national, annual and quarterly, across the 22 BEA summary industry groups (11 agriculture through 81 other services, plus GFG/GFE/ GSLG government). TableID=ALL is requested, so all 39 BEA tables land here and table_id selects the measure: 1 value added in millions of dollars, 5 value added as a percent of GDP, 6 components of value added, 7 those components as a percent of value added, 8 and 11 chain-type quantity and price indexes, 10 real value added. Units are a property of table_id, not of the row — the units column is always null because GDPbyIndustry returns no per-row unit. On the component tables (6 and 7) industry_description carries the COMPONENT name rather than the industry name: 'Gross operating surplus', 'Compensation of employees', 'Taxes on production and imports less subsidies', plus one row named for the industry that totals to 100 percent (table 7) or to total value added (table 6). Gross operating surplus is BEA's profit measure, so table 7 gives an industry profit margin directly — filter industry_description = 'Gross operating surplus' and read value as percent of value added. Use industry_code to pick the industry on those tables, not industry_description.
| Column | Type | Null | Description |
|---|---|---|---|
table_ |
string | yes | BEA table identifier for industry GDP data |
quarter |
string | yes | Quarter identifier (e.g., 'Q1', 'Q2') or empty for annual data |
industry_ |
string | yes | NAICS industry code |
industry_ |
string | yes | Full description of the industry |
value |
double | yes | GDP value for the industry in millions of dollars |
units |
string | yes | Units of measurement (e.g., 'Millions of Dollars') |
note_ |
string | yes | Reference to footnotes or data quality notes |
trade_exports · table¶
Monthly U.S. goods exports by HS-6 commodity and destination country (Census International Trade timeseries, exports/hs endpoint). Tall layout: Census returns individual-country rows interleaved with its own built-in world-total and trade-bloc/continental aggregate rows in the same response, with no discriminator of its own — filter geo_level = 'country' before summing value_usd, or the total silently multiplies by every bloc/continent a country also belongs to (plus the world total). No quantity column: Census's QTY_1_MO/ UNIT_QY1 fields are unpopulated (always "0"/"-") at COMM_LVL=HS6, confirmed live across all 364,588 rows of a full month — quantity is only standardized at the HS10 grain, which this table doesn't fetch. trade_by_state's QTY_1_MO IS populated (state-level statehs endpoint reports it differently); use that table for quantity.
| Column | Type | Null | Description |
|---|---|---|---|
country_ |
string | yes | Destination country code |
country_ |
string | yes | Destination country name |
geo_ |
string | no | Row granularity, derived from Census's own CTY_CODE conventions (verified live against the full 252-code domain): 'world' for CTY_CODE='-' (TOTAL FOR ALL COUNTRIES), 'bloc' for the 0xxx-range trade-organization aggregates (EU, USMCA, OECD, NATO, etc.), 'continent' for the NXXX regional aggregates (1XXX North America, 2XXX Central America, ... 7XXX Africa), 'country' for every individual country/territory row. Census returns all four granularities interleaved in one response with no discriminator column of its own — summing value_usd without a WHERE geo_level = 'country' filter double(-plus)-counts every shipment once per bloc/continent it also belongs to, in addition to the world total. |
hs6 |
string | yes | HS 6-digit commodity code |
hs2 |
string | yes | HS 2-digit chapter (derived from hs6) |
value_ |
long | yes | Total export value for the month (USD) |
trade_imports · table¶
Monthly U.S. goods imports by HS-6 commodity and origin country, including CIF charges (Census International Trade timeseries, imports/hs endpoint). Tall layout: Census returns individual-country rows interleaved with its own built-in world-total and trade-bloc/continental aggregate rows in the same response, with no discriminator of its own — filter geo_level = 'country' before summing value_usd, or the total silently multiplies by every bloc/continent a country also belongs to (plus the world total). No quantity column: Census's GEN_QY1_MO/ UNIT_QY1 fields are unpopulated (always "0"/"-") at COMM_LVL=HS6, confirmed live across all 239,647 rows of a full month — quantity is only standardized at the HS10 grain, which this table doesn't fetch. trade_by_state's QTY_1_MO IS populated (state-level statehs endpoint reports it differently); use that table for quantity.
| Column | Type | Null | Description |
|---|---|---|---|
country_ |
string | yes | Origin country code |
country_ |
string | yes | Origin country name |
geo_ |
string | no | Row granularity, derived from Census's own CTY_CODE conventions (verified live against the full 252-code domain): 'world' for CTY_CODE='-' (TOTAL FOR ALL COUNTRIES), 'bloc' for the 0xxx-range trade-organization aggregates (EU, USMCA, OECD, NATO, etc.), 'continent' for the NXXX regional aggregates (1XXX North America, 2XXX Central America, ... 7XXX Africa), 'country' for every individual country/territory row. Census returns all four granularities interleaved in one response with no discriminator column of its own — summing value_usd without a WHERE geo_level = 'country' filter double(-plus)-counts every shipment once per bloc/continent it also belongs to, in addition to the world total. |
hs6 |
string | yes | HS 6-digit commodity code |
hs2 |
string | yes | HS 2-digit chapter (derived from hs6) |
value_ |
long | yes | General customs import value for the month (USD) |
cif_ |
long | yes | Cost, insurance, and freight charges for the month (USD) |
trade_by_state · table¶
Monthly U.S. goods exports by origin state, HS-6 commodity, and destination country (Census International Trade timeseries, statehs endpoint). state='-' rows are U.S. totals; country_code='-' rows are all-countries totals. Also interleaves trade-bloc and continental aggregate rows on the country axis (see geo_level) — filter state != '-' AND geo_level = 'country' for true state-by-country detail.
| Column | Type | Null | Description |
|---|---|---|---|
state |
string | yes | Origin state (USPS 2-char abbreviation); '-' = U.S. total roll-up |
country_ |
string | yes | Destination country code (4-char Census); '-' = all-countries roll-up |
country_ |
string | yes | Destination country name |
geo_ |
string | no | Row granularity, derived from Census's own CTY_CODE conventions (verified live against the full 251-code domain returned by this endpoint): 'world' for CTY_CODE='-' (TOTAL FOR ALL COUNTRIES), 'bloc' for the 0xxx-range trade-organization aggregates (EU, USMCA, OECD, NATO, etc.), 'continent' for the NXXX regional aggregates (1XXX North America, 2XXX Central America, ... 7XXX Africa), 'country' for every individual country/territory row. Combined with the existing state='-' U.S.-total roll-up, a caller must filter both state != '-' AND geo_level = 'country' to get true state-by-country detail rows — summing without both filters double(-plus)-counts. |
hs6 |
string | yes | HS 6-digit commodity code |
hs2 |
string | yes | HS 2-digit chapter (derived from hs6) |
commodity_ |
string | yes | Short HS commodity description |
value_ |
long | yes | Total export value for the month, all transport modes (USD) |
vessel_ |
long | yes | Export value shipped by vessel for the month (USD) |
air_ |
long | yes | Export value shipped by air for the month (USD) |
container_ |
long | yes | Export value shipped in containers for the month (USD) |
comtrade_flows · table¶
UN Comtrade bilateral merchandise trade: reported value and quantity per reporter country x partner country x HS-6 commodity x flow x year. Tall. reporter_code and partner_code are ISO alpha-3 (FK to ref.countries); partner_code='WLD' and other non-country codes are world/aggregate roll-ups (is_aggregate=true). Complements the one-sided U.S. Census trade_exports/ trade_imports with the partner side of every flow. Fan-out is a curated major-reporter list (comtrade/comtrade-reporters.json) x flow x year: one (reporter, flow, year) fetch returns all partners x HS-6, accumulated into the year partition and replaced wholesale on re-run (overwritePartitions).
| Column | Type | Null | Description |
|---|---|---|---|
reporter_ |
string | yes | Reporting country ISO alpha-3 (FK to ref.countries.iso_alpha3) |
partner_ |
string | yes | Partner country ISO alpha-3 (FK to ref.countries.iso_alpha3; 'WLD'=world total) |
flow_ |
string | yes | Trade flow (Import / Export / Re-import / Re-export) |
hs6 |
string | yes | HS 6-digit commodity code |
hs2 |
string | yes | HS 2-digit chapter (derived from hs6 by the transformer) |
hs_ |
string | yes | Commodity description |
hs_ |
string | yes | HS classification vintage of the reported code (H0..H6) |
trade_ |
double | yes | Reported trade value (USD) |
net_ |
double | yes | Net weight (kg) when reported |
quantity |
double | yes | Quantity in quantity_unit when reported |
quantity_ |
string | yes | Supplementary quantity unit |
is_ |
boolean | yes | True for world/aggregate partner roll-ups (exclude from partner-level joins) |
ilostat_indicators · table¶
UN ILO ILOSTAT labor indicators: value per country x indicator x sex x classification x year. Tall — the international analog of the econ BLS tables (employment_statistics, wages, jolts). country_code is ISO alpha-3 (FK to ref.countries); ILO regional/income aggregates are flagged is_aggregate. Fan out one bulk CSV per indicator (each carries all countries/years); year is split on write (responsePartitioning) and replaced wholesale (overwritePartitions).
| Column | Type | Null | Description |
|---|---|---|---|
indicator_ |
string | yes | ILOSTAT indicator id (the fan-out key) |
indicator_ |
string | yes | Indicator label |
country_ |
string | yes | Country ISO alpha-3 (FK to ref.countries.iso_alpha3; from ILO ref_area) |
sex |
string | yes | Sex classification (Total / Male / Female) |
classif1 |
string | yes | Primary classification value (age band, sector, ...); "Total" when none |
classif2 |
string | yes | Secondary classification value when present |
year |
integer | no | Reference year (partition column; emitted from the ILO time field) |
value |
double | yes | Indicator value (unit implied by indicator; null when unreported) |
unit |
string | yes | Unit / measure (rate %, thousands, local currency, ...) |
obs_ |
string | yes | ILO observation status flag |
is_ |
boolean | yes | True for ILO regional/income-group ref_area rows (exclude from country joins) |
usitc_tariffs · table¶
USITC DataWeb imports-for-consumption with the duty breakout: one row per HTS-8 x partner country x year. The tariff counterpart to econ.trade_imports — hs6 (first 6 of hts8) is the join key back to the Census HS-6 import flows. Carries customs value, calculated duties, and the effective duty rate (calculated_duties / customs_value). Country is the DataWeb country NAME (the runReport grid reports names, not codes) — join hts_description via econ.usitc_tariff_schedule. Because a single DataWeb runReport is hard-capped at 20,000 rows AND a broad all-commodities year query is too slow to compute, the transformer CHUNKS BY HTS CHAPTER: one query per chapter (01..99, ex-77) at HTS-8 x country, merged into the year partition (replaced wholesale on re-run, overwritePartitions). Zero-trade rows are suppressed; requests are paced for DataWeb's rate limit. Requires a free DataWeb token (TRADE_USITC_API_TOKEN). NOTE: a single very large chapter can still exceed the 20k cap (the transformer WARNs and that chapter is a top slice); dutiable value, first-unit quantity, and import-program breakout are additional DataWeb measures not yet wired (their dataToReport codes require a DataWeb-UI network capture to confirm).
| Column | Type | Null | Description |
|---|---|---|---|
hts8 |
string | yes | HTS 8-digit commodity code (U.S. tariff-line detail) |
hs6 |
string | yes | HS-6 prefix (first 6 digits of hts8) — join key to econ.trade_imports.hs6 |
country_ |
string | yes | Partner country name (DataWeb reports country names, not codes) |
customs_ |
double | yes | Customs (import) value for consumption imports (USD) |
calculated_ |
double | yes | Calculated duties collected (USD) |
ave_ |
double | yes | Effective duty rate = calculated_duties / customs_value; null when customs_value is 0/absent (not fabricated). Denominator is customs value (DataWeb dutiable-value measure not yet wired). |
usitc_tariff_schedule · table¶
USITC statutory Harmonized Tariff Schedule rate lines (the published general/special/column-2 rates, as opposed to duties actually collected in usitc_tariffs): one row per HTS-8 tariff line of the current HTS revision. hs6 is the first 6 digits of hts8 (join key). general_ave (ad-valorem equivalent) and effective_date are left null when not derivable from the export — never fabricated. Slowly-changing; the whole schedule is one fetch, stamped with the current revision year and overwritten each January.
| Column | Type | Null | Description |
|---|---|---|---|
hts8 |
string | yes | HTS 8-digit commodity code |
hs6 |
string | yes | HS-6 prefix (first 6 digits of hts8) — join key |
description |
string | yes | Tariff-line description |
unit_ |
string | yes | Statutory unit(s) of quantity (comma-joined when multiple) |
general_ |
string | yes | Column-1 General (MFN / normal trade relations) rate text |
general_ |
double | yes | Ad-valorem equivalent of the general rate; null for non-ad-valorem or complex rates (not fabricated) and null in the current export |
special_ |
string | yes | Column-1 Special (FTA / preference) rate text |
column2_ |
string | yes | Column-2 (non-NTR) rate text |
effective_ |
date | yes | Effective date of this rate line (null when not published in the export) |
year |
integer | no | HTS revision year (partition column; stamped by the transformer) |
usgs_mineral_commodity_stats · table¶
USGS Mineral Commodity Summaries (MCS) per-commodity salient statistics, long-format: one row per (release, commodity, country, statistic, statistic_detail, data_year). Covers production, imports, exports, consumption, price, reserves, capacity, employment, stocks, and net import reliance (statistic = 'Net import reliance') for ~127 mineral commodities — verified live 2026-09-11 against MCS 2026 (Copper NIR 38/44/41/41/45% for 2020-2024 matches the published USGS Copper chapter; Gallium NIR 100% every year matches zero US primary production). Joins to trade_imports/ trade_exports via commodity (name-based; no shared commodity code exists between USGS and Census HS classifications). year is the MCS release year (partition column); data_year/data_year_text is the calendar year (or, for a handful of multi-year aggregate rows, a text range like "2021-24") the statistic describes. value_num is null for a non-numeric annotation (USGS's own withheld/not-available markers) — the original text is preserved in value_text, never fabricated as a number. Hosted on ScienceBase (USGS's own data-release platform, not a third-party mirror); each release's item id is not derivable from its year, so this table is built by ScienceBase catalog discovery rather than a templated URL — see the provider class comment for the discovery mechanism.
| Column | Type | Null | Description |
|---|---|---|---|
mcs_ |
string | yes | USGS MCS chapter heading this row's commodity is published under |
section |
string | yes | Section within the commodity's chapter (e.g. "Salient Statistics-United States", "Import Sources", "Recycling") |
commodity |
string | yes | Commodity name as published by USGS (e.g. "Copper", "Gallium", "Aluminum") — join key to trade_imports/trade_exports' commodity-name columns; no shared code exists between USGS and Census HS classifications |
country |
string | yes | Country the row's value applies to. "United States" for domestic salient statistics; a trading-partner country name for Import Sources rows |
statistic |
string | yes | Top-level statistic category as published (e.g. Production, Import, Export, Consumption, Price, Reserves, Capacity, Employment, Stock, Supply, "Net import reliance"). Filter on this for a specific measure across all commodities/years — this table is long-format, one row per data point. |
statistic_ |
string | yes | USGS's finer line-item label within statistic (e.g. "Production: Fused aluminum oxide, crude" under Production for Abrasives) |
unit |
string | yes | Unit of measure for value_num as published (e.g. "metric tons", "percent") |
data_ |
integer | yes | Calendar year this statistic describes, when data_year_text is a single 4-digit year; null for a multi-year aggregate period (see data_year_text) |
data_ |
string | yes | Raw year/period text as published — a single year matching data_year, or a multi-year range (e.g. "2021-24") for a handful of aggregate rows |
value_ |
double | yes | Parsed numeric value; null when the source cell is a non-numeric annotation (a USGS withheld/not-available marker) rather than a real number — see value_text |
value_ |
string | yes | Raw value cell as published, including any non-numeric annotation |
notes |
string | yes | USGS footnote/qualifier text for this data point |
is_ |
boolean | yes | Whether USGS's 2025 critical-minerals list includes this commodity |
other_ |
string | yes | Additional USGS notes column, when published |
year |
integer | no | MCS release year (partition column; stamped by the provider from the release title) |
comtrade_bilateral_trade · view¶
Enriched UN Comtrade bilateral flows: one row per year x reporter x partner x HS-6 x flow, with reporter/partner country names and M49 regions joined from ref.countries. Country-level only (aggregate/world-total partner rows filtered out). Use for supply-chain and partner-exposure analysis; complements the one-sided U.S. trade_exports/trade_imports with the partner side of each flow.
View — columns are resolved by the query engine at runtime.
comtrade_partner_balances · view¶
Bilateral trade balance from UN Comtrade, aggregated across commodities: one row per year x reporter x partner with total exports, total imports, and net balance (exports - imports) in USD. Country-level only. Enables bilateral surplus/deficit analysis and pairs with econ.trade_balance_summary (U.S. macro).
View — columns are resolved by the query engine at runtime.
ilostat_labor_snapshot · view¶
Headline ILOSTAT labor indicators pivoted wide: one row per country x year with unemployment rate, employment-to-population ratio, labour force participation rate, and youth NEET rate (total sex, all ages). The international analog of the econ BLS labor tables — join to ref.countries for names/regions, or compare a given year against U.S. employment_statistics.
View — columns are resolved by the query engine at runtime.
state_economic_snapshot · view¶
State-month economic snapshot joining BLS QCEW annual state wages with JOLTS monthly state labor-market data, enriched with geographic identifiers. Restricted to own_code='0' (all ownership) and industry_code='10' (all industries) on the QCEW side — the only state_wages row per state+year that is conceptually comparable to jolts_state, which is total-nonfarm only with no industry breakdown of its own (see jolts_state's comment); every other own_code/ industry_code combination would cross-multiply against every JOLTS month x metric row for no added meaning. jolts_month (period, e.g. 'M01') is exposed so the grain — one row per state+year+jolts_month+jolts_series — is explicit in the output. Use state_fips to join with geo.states, census, or health schemas.
View — columns are resolved by the query engine at runtime.
fdi_data · view¶
Unified tall view over the two BEA MNE foreign-direct-investment tables: fdi_direct_investment (stat_type='DI' — position, income, financial transactions) and fdi_activities (stat_type='AMNE' — employment, sales, assets, value added). One row per stat_type x direction x classification x year x series_id x row_code. data_value is expressed in the units named by table_scale (dollars for financial series, thousands of employees for employment). Use fdi_by_country for a country-level wide pivot.
View — columns are resolved by the query engine at runtime.
fdi_by_country · view¶
Country-level wide pivot of BEA foreign direct investment: one row per direction (inward/outward) x partner country x year, with the headline DI and AMNE statistics as columns. Position and income are historical-cost balance-of-payments direct investment; employment/sales/assets/value_added are majority-owned affiliate activities. Dollar figures are in millions; employment is in thousands of employees. Each source series is a single country row (verified), so the MAX pivot is a lossless reshape.
View — columns are resolved by the query engine at runtime.