> For the complete documentation index, see [llms.txt](https://docs.dataplex-consulting.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.dataplex-consulting.com/data-catalog/census-lehd-lodes-dataset.md). # Census LEHD LODES Employment Dataset ### About the Dataset Census block-level employment data from the U.S. Census Bureau's Longitudinal Employer-Household Dynamics (LEHD) program. Origin-destination commute flows, workforce demographics, and job characteristics with geographic crosswalk for county, metro, and state aggregation. 22 years of annual data (2002-2023) covering all 50 states, DC, and Puerto Rico. **75,000 files condensed into one SQL query.** {% hint style="info" %} **Get Full Access** | [Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZT1Z7QRSWHE/dataplex-consulting-data-products-census-lehd-lodes-employment-dataset) | [Free Trial](https://trial.dataplex-consulting.com/?platform=snowflake) {% endhint %} ### Quick Access **Tables**: OD, RAC, WAC, XWALK + 4 metadata tables\ **Sources**: 4 Census LEHD data sources\ **Coverage**: All US Census blocks (\~8 million), 22 years (2002-2023)\ **Update Frequency**: Annually (1-2 year lag from reference year to Census release) ## LEHD Origin-Destination Employment Statistics (LODES), Queryable in SQL LODES (the LEHD Origin-Destination Employment Statistics) is the U.S. Census Bureau's Census-block-level employment dataset, produced by the Longitudinal Employer-Household Dynamics (LEHD) program. This product delivers the full LODES data as ready-to-query SQL: origin-destination commute flows (OD), residence and workplace area characteristics (RAC and WAC), and the geographic crosswalk (XWALK): 22 years of annual data (2002–2023) across all 50 states, DC, and Puerto Rico, every Census block joinable in a single query. ## Census LEHD Data: What the LEHD Program Publishes The U.S. Census Bureau's Longitudinal Employer-Household Dynamics (LEHD) program publishes several public data products built from linked employer and worker records. Searching for "LEHD data" usually lands on one of these four: | LEHD data product | What it measures | In this dataset? | | --------------------------------------------------------- | --------------------------------------------------------------------------------------------------- | ---------------------------------------------- | | **LODES** (LEHD Origin-Destination Employment Statistics) | Jobs by Census block: where workers live, where they work, and the commute flows between the two | **Yes**: OD, RAC, WAC, and the XWALK crosswalk | | **QWI** (Quarterly Workforce Indicators) | Quarterly employment, hires, separations, and earnings by county, industry, and worker demographics | No | | **J2J** (Job-to-Job Flows) | Worker moves between employers, industries, and states | No | | **PSEO** (Post-Secondary Employment Outcomes) | Earnings and employment of graduates by institution, degree, and field of study | No | This dataset is the LODES product, the only LEHD data published at Census-block granularity. Census distributes LEHD data through per-state files, the [OnTheMap](https://onthemap.ces.census.gov/) application, and the LED Extraction Tool; here the same LODES data is delivered as typed SQL views alongside the geographic crosswalk (`XWALK`), joinable on the 15-digit block code in a single query. The full LEHD program catalog is at [lehd.ces.census.gov/data](https://lehd.ces.census.gov/data/). ## Download LODES Data or Query It You can download LODES data as per-state, per-year files from the Census LEHD site; for a single state-year, that's the quickest path. This product exists for everything the file downloads make painful: | | Census LEHD LODES files (free) | Census LEHD LODES Dataset | | ------- | ----------------------------------------------------- | --------------------------------------------------- | | Access | Per-state, per-year file downloads from the LEHD site | SQL across all states and years at once | | Schema | Raw CSVs; geocodes lose leading zeros in spreadsheets | Typed views; 15-digit geocodes preserved as strings | | Joins | Manual stitching of OD, RAC, WAC, and the crosswalk | Join OD, RAC, WAC, and XWALK directly in one query | | History | Download each reference year's file separately | 22 years (2002–2023) in one queryable table | | Updates | Re-download after every annual Census release | Views update with each annual release | ## OnTheMap vs. Querying LODES in SQL [OnTheMap](https://onthemap.ces.census.gov/) is the Census Bureau's web application built on LODES data: select an area and it maps and reports where that area's workers live, where its residents work, and the commute flows in between. For a single-area report it is the fastest option. This dataset covers the work OnTheMap is not designed for: | | OnTheMap (Census web app) | Census LEHD LODES Dataset | | ------------------ | ------------------------------------------------------ | -------------------------------------------------------------------- | | Scope per run | One selected area at a time | Every block, county, metro, or state in one query | | Output | Interactive map, report, and CSV export of that report | Joinable SQL tables for BI tools, notebooks, and agents | | Custom geographies | Draw or upload one area per session | Any XWALK level, or your own polygons via block centroid coordinates | | History | One reference year per report | Every year since 2002 in one `GROUP BY year` | | Repeatability | Manual, interactive | Scheduled queries, versioned with batch lineage | The LED Extraction Tool returns CSV extracts of selected LODES measures; those files still need typing and joining downstream. The views here already preserve 15-digit geocodes as strings and join to the crosswalk on `tabblk2020`. ## Overview The Census LEHD LODES dataset provides comprehensive access to employment geography data including: * **Origin-Destination** (`OD`) - Block-to-block commute flows: where workers live and where they work. 2.6 billion rows across 22 years. * **Residence Area Characteristics** (`RAC`) - Jobs by where workers live with 42 demographic columns (age, earnings, 20 NAICS sectors, race, ethnicity, education, sex). 119 million rows. * **Workplace Area Characteristics** (`WAC`) - Jobs by where people work with 52 columns including firm age and firm size (WAC-only). 48.5 million rows. * **Geographic Crosswalk** (`XWALK`) - Maps every 2020 Census block to tracts, counties, metros, congressional districts, ZCTAs, and coordinates. 8.2 million rows. Always fully available (no trial limit). ### Metadata Tables Every Dataplex data product includes these standard metadata tables: | Table | Purpose | | ----------------- | ------------------------------------------------------------- | | `FEEDS` | Dataset catalog: available tables, descriptions, update dates | | `FEEDS_FILES` | Batch load history with `is_latest` flag for data freshness | | `CHANGELOG` | Change log of data loads, schema changes, corrections | | `DATA_DICTIONARY` | Column descriptions for all tables | ## Entity Relationship Diagram ![Census LEHD LODES Entity Relationship Diagram](https://813439891-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDeimGtBflXKQn786VLvj%2Fuploads%2Fgit-blob-dc93c606ab9c4ac998a22d89313c5eb86d8afb33%2Fentity-relationship.png?alt=media) **Join pattern:** OD, RAC, and WAC all join to XWALK via geocode columns (`w_geocode` or `h_geocode` = `tabblk2020`) for geographic aggregation from blocks to counties, metros, and states. All data tables link to `FEEDS` and `FEEDS_FILES` via `feed_id` and `feeds_files_id` for data lineage. ## Data Tables ### OD (Origin-Destination) Block-to-block commute flows: where workers live and where they work. Each row represents a unique home-block to work-block pair for a given year. JOIN to XWALK on `w_geocode` or `h_geocode` to aggregate to county, metro, or state level. **Key Features:** * 2.6 billion rows across 22 years (2002-2023) * Census block-level granularity (15-digit FIPS codes) * Job counts segmented by age (3), earnings (3), and industry (3) * `main` files (both residence and workplace in same state) and `aux` files (workplace in state, residence elsewhere) **Primary Key:** `w_geocode` + `h_geocode` + `year` + `part` See [Schema Reference](/data-catalog/census-lehd-lodes-dataset/schema-reference.md#od-origin-destination) for all 19 columns. ### RAC (Residence Area Characteristics) Jobs by where workers live. Each row is a Census block where workers reside, with 42 demographic breakdown columns. JOIN to XWALK on `h_geocode` to aggregate. **Key Features:** * 119 million rows across 22 years * 20 NAICS industry sectors (CNS01-CNS20) * 7 race categories, 2 ethnicity groups, 4 education levels, 2 sex categories * 3 age segments and 3 earnings brackets **Primary Key:** `h_geocode` + `year` See [Schema Reference](/data-catalog/census-lehd-lodes-dataset/schema-reference.md#rac-residence-area-characteristics) for all 48 columns. ### WAC (Workplace Area Characteristics) Jobs by where people work. Each row is a Census block where jobs are located. Identical demographic columns to RAC plus firm age (CFA) and firm size (CFS) columns not available in RAC. **Key Features:** * 48.5 million rows across 22 years * All RAC columns plus 5 firm age groups (CFA01-CFA05) and 5 firm size groups (CFS01-CFS05) * WAC uses noise infusion (NOT synthetic like RAC/OD), so it is reliable at 10+ jobs per block **Primary Key:** `w_geocode` + `year` See [Schema Reference](/data-catalog/census-lehd-lodes-dataset/schema-reference.md#wac-workplace-area-characteristics) for all 58 columns. ### XWALK (Geographic Crosswalk) Reference table mapping every 2020 Census block to higher geographies. This is the aggregation enabler: JOIN OD/RAC/WAC to XWALK to roll up block-level data to counties, metros, states, congressional districts, or ZCTAs. **Key Features:** * 8.2 million rows (one per 2020 Census block) * Maps to: state, county, tract, block group, CBSA/metro, ZCTA, congressional district, place, school district, and more * Includes block centroid coordinates (latitude/longitude) * Always fully available with no trial limit (essential for any analysis) **Primary Key:** `tabblk2020` See [Schema Reference](/data-catalog/census-lehd-lodes-dataset/schema-reference.md#xwalk-geographic-crosswalk) for all 45 columns. ## LODES Data by Year and State Census publishes LODES as one file per state, per table, per year. Here every row carries a `year` column and a `state_fips` column, so selecting LODES data for a state-year is a `WHERE` clause instead of a download: * **Years**: annual reference years since 2002 through the latest Census release. Every state and DC carries the full series; Puerto Rico is included for the years Census publishes it (2002-2008). * **States**: `state_fips` is derived from the block code (the workplace block in OD and WAC, the residence block in RAC). Join to `XWALK.stusps` for the two-letter abbreviation. * **Job totals**: the all-jobs totals (`S000` in OD, `C000` in RAC and WAC) with the age, earnings, industry, and demographic breakdowns listed in the [Schema Reference](/data-catalog/census-lehd-lodes-dataset/schema-reference.md). This corresponds to the LODES all-segments, all-job-types files (`S000`, `JT00`). * **Block vintage**: all years are tabulated on 2020 Census blocks (LODES version 8), so multi-year comparisons need no block-to-block crosswalk between vintages. * **New releases**: when Census publishes the next LODES reference year, it is loaded into the same views. `SELECT MAX(year) FROM DWV.RAC` shows the latest year available. ```sql -- State-year coverage of LODES data in this dataset SELECT x.stusps AS state, r.state_fips, MIN(r.year) AS first_year, MAX(r.year) AS last_year, COUNT(DISTINCT r.year) AS years_available FROM DWV.RAC r JOIN (SELECT DISTINCT st, stusps FROM DWV.XWALK) x ON r.state_fips = x.st GROUP BY x.stusps, r.state_fips ORDER BY x.stusps; ``` ```sql -- LODES data for one state (California), all years: total jobs by residence county SELECT r.year, x.ctyname AS county, SUM(r.C000) AS total_jobs FROM DWV.RAC r JOIN DWV.XWALK x ON r.h_geocode = x.tabblk2020 WHERE r.state_fips = '06' GROUP BY r.year, x.ctyname ORDER BY r.year DESC, total_jobs DESC; ``` ## Data Quality ### Data Generation Methods | Table | Method | Reliability | | ----- | ------------------------------ | ---------------------------------------------------------------- | | OD | Full synthetic data generation | Statistically representative; reliable at county level and above | | RAC | Full synthetic data generation | Statistically representative; reliable at county level and above | | WAC | Multiplicative noise infusion | Reliable at 10+ jobs per block | | XWALK | Exact Census geography | Exact | ### Standardization * All geocode columns zero-padded to 15 digits (preserved as strings, not integers) * All geographic codes preserve leading zeros (state, county, tract, CBSA) * Year extracted from filenames and added as a typed integer column * All job count columns cast to NUMBER with `TRY_TO_NUMBER` for safe handling ### Data Freshness Check when data was last updated: ```sql SELECT source_name, data_period, row_count, created_at, is_latest FROM DWV.FEEDS_FILES WHERE is_latest = TRUE; ``` ## Getting Started ### Platform Schema Reference This dataset is available on Snowflake. Queries use schema-only references, since the database is already set by the share context: | Platform | Schema | Example | | ------------- | ------ | -------- | | **Snowflake** | `DWV` | `DWV.OD` | ### Discover Available Data Start with the `FEEDS` table to see what's available, and `FEEDS_FILES` to understand data freshness. ```sql -- Browse the data catalog SELECT table_name, description, latest_data_period, latest_row_count, last_loaded_at, total_batches FROM DWV.FEEDS ORDER BY table_name; ``` ```sql -- Check data freshness per source SELECT source_name, data_period, row_count, row_count_delta, created_at AS loaded_at, is_latest FROM DWV.FEEDS_FILES ORDER BY source_name, created_at DESC; ``` ### Working with Data Lineage Every data row links to `FEEDS_FILES` via `feeds_files_id`, which tells you exactly which batch loaded that data. Use this to filter to the current data version or trace any row back to its source load. ```sql -- Get current WAC data with batch lineage SELECT w.w_geocode, w.year, w.C000, w.CNS16, ff.data_period, ff.created_at AS data_loaded_at FROM DWV.WAC w JOIN DWV.FEEDS_FILES ff ON w.feeds_files_id = ff.id WHERE ff.is_latest = TRUE LIMIT 20; ``` ### Top Employment Counties Aggregate workplace block data to county level to find the highest employment centers. ```sql SELECT x.ctyname AS county, x.stname AS state, SUM(w.C000) AS total_jobs, SUM(w.CNS16) AS healthcare_jobs, SUM(w.CNS05) AS manufacturing_jobs FROM DWV.WAC w JOIN DWV.XWALK x ON w.w_geocode = x.tabblk2020 WHERE w.year = 2023 GROUP BY 1, 2 ORDER BY total_jobs DESC LIMIT 25; ``` ### Commute Flows Between Counties Find the largest commute flows between counties using OD + XWALK. ```sql SELECT xw.ctyname AS work_county, xw.stname AS work_state, xh.ctyname AS home_county, xh.stname AS home_state, SUM(od.S000) AS total_commuters FROM DWV.OD od JOIN DWV.XWALK xw ON od.w_geocode = xw.tabblk2020 JOIN DWV.XWALK xh ON od.h_geocode = xh.tabblk2020 WHERE od.year = 2023 AND od.part = 'main' GROUP BY 1, 2, 3, 4 ORDER BY total_commuters DESC LIMIT 25; ``` ### Workforce Demographics by Metro Analyze workforce age, earnings, and industry composition at the metropolitan area level. ```sql SELECT x.cbsaname AS metro_area, SUM(r.C000) AS total_workers, ROUND(SUM(r.CE03) / NULLIF(SUM(r.C000), 0) * 100, 1) AS pct_high_earners, ROUND(SUM(r.CA01) / NULLIF(SUM(r.C000), 0) * 100, 1) AS pct_under_30 FROM DWV.RAC r JOIN DWV.XWALK x ON r.h_geocode = x.tabblk2020 WHERE r.year = 2023 AND x.cbsa IS NOT NULL GROUP BY 1 HAVING SUM(r.C000) > 100000 ORDER BY total_workers DESC LIMIT 25; ``` ### Employment Trend Over Time Track total job counts by county across years to identify growth and decline. ```sql SELECT x.ctyname AS county, x.stname AS state, w.year, SUM(w.C000) AS total_jobs FROM DWV.WAC w JOIN DWV.XWALK x ON w.w_geocode = x.tabblk2020 WHERE x.cty = '06037' -- Los Angeles County GROUP BY 1, 2, 3 ORDER BY w.year; ``` ### Tracking Data Changes Over Time `FEEDS_FILES` records every batch load with `row_count_delta` showing what changed. Use this to monitor source data updates. ```sql SELECT source_name, data_period, row_count, row_count_delta, created_at AS loaded_at, is_latest FROM DWV.FEEDS_FILES ORDER BY source_name, created_at; ``` ### Frequently Asked Questions **What is LODES data?** LODES (the LEHD Origin-Destination Employment Statistics) is U.S. Census Bureau data on where workers live and where they work at Census-block granularity, produced by the Longitudinal Employer-Household Dynamics (LEHD) program. This product delivers it as typed, joinable SQL tables. **What does LEHD stand for?** Longitudinal Employer-Household Dynamics, the U.S. Census Bureau program that produces the LODES origin-destination employment statistics. **Can I download LODES data, or query it directly?** Both are options: the Census LEHD site offers per-state, per-year file downloads, while this product delivers all 22 years across every state as SQL views you can join and aggregate without re-stitching files. **What tables are included?** Origin-Destination (OD) commute flows, Residence Area Characteristics (RAC), Workplace Area Characteristics (WAC), and the Geographic Crosswalk (XWALK) that rolls block-level data up to counties, metros, and states. **Is this the official Census LEHD LODES data?** Yes. The values are the U.S. Census Bureau's published LODES version 8 files, loaded unchanged into typed SQL views alongside the Census geographic crosswalk. Nothing is modeled, imputed, or re-estimated. The processing applied is typing (geocodes kept as 15-digit strings, counts as numbers), the added `year` and `state_fips` columns, batch-lineage columns (`feed_id`, `feeds_files_id`, `created_at`, `updated_at`), and removal of duplicate source rows at each table's primary key (block and year for RAC and WAC; workplace block, residence block, year, and file part for OD). **What is the difference between LEHD and LODES?** LEHD is the Census Bureau program; LODES is one of the data products it publishes, the one at Census-block granularity. The other LEHD products (QWI, J2J, PSEO) are county-level or higher and are not part of this dataset. **How do I get LODES data for one state or one year?** Filter on `state_fips` and `year` in any of the three tables, then join to `XWALK` to roll blocks up to counties, tracts, metros, or ZCTAs. There is no per-state file to locate or download. **Is the newest LODES release included?** Census publishes each LODES reference year one to two years after the fact. New years are loaded into the same views when Census releases them, so `SELECT MAX(year) FROM DWV.RAC` always shows the latest year available, and `FEEDS_FILES` shows when it was loaded. **Does this replace OnTheMap?** For a single-area map or report, OnTheMap is quicker. For every county or metro at once, multi-year trends, custom geographies, or repeatable analysis in SQL and BI tools, this dataset is the better fit. {% hint style="success" %} **Ready to access Census LEHD LODES data?** {% endhint %} | Platform | Action | | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Snowflake** | [Get on Marketplace](https://app.snowflake.com/marketplace/listing/GZT1Z7QRSWHE/dataplex-consulting-data-products-census-lehd-lodes-employment-dataset) | {% hint style="success" %} Questions? [Contact our team](mailto:support@dataplex-consulting.com) for a walkthrough. {% endhint %} --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.dataplex-consulting.com/data-catalog/census-lehd-lodes-dataset.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.