# Data dictionary - h1b-lca-fy2010-fy2025-2026-10-09

Every column of every table. `source_status` says whether a value is reported by the filer, normalized, or derived by Persistfolio; the rule for derived fields is in the methodology. `null_share` is the share of null or empty values; `allowed_values` lists the values of low-cardinality columns (most frequent first).

## cases

| column | type | source status | unit | null share | allowed values | description |
|---|---|---|---|---|---|---|
| `case_number` | VARCHAR | source-reported |  | 0.0 |  | DOL LCA case number as filed (format I-200-YYDDD-NNNNNN). One application has one case number; the same case number can appear in several rows (status records). |
| `source_key` | VARCHAR | normalized |  | 0.0 |  | Which DOL file this row came from: fiscal year (2010-2019 annual) or year+quarter (2020Q1-2025Q4). Lineage key with source_row. |
| `source_quarter` | INTEGER | normalized |  | 0.58123 | 3/2/4/1 | Quarter number (1-4) for quarterly files; empty for annual files. |
| `source_fy` | INTEGER | normalized |  | 0.0 | 2021/2019/2018/2016/2023/2022/2017/2015/2025/2020/2024/2014/2013/2012/2011/2010 | U.S. federal fiscal year (Oct 1 - Sep 30) of the DOL file. |
| `source_file` | VARCHAR | normalized |  | 0.0 |  | Original DOL file name. SHA-256 of each file is in SOURCE_INVENTORY.csv. |
| `source_row` | BIGINT | normalized |  | 0.0 |  | Worksheet row number in the original DOL workbook, header = row 1; non-H-1B rows and blank padding rows are counted, so values have gaps. With source_key it identifies the row uniquely. |
| `case_status` | VARCHAR | normalized |  | 0.0 | CERTIFIED/CERTIFIED-WITHDRAWN/WITHDRAWN/DENIED/PENDING QUALITY AND COMPLIANCE REVIEW - UNASSIGNED/REJECTED | DOL case status as filed, upper-cased; 'Certified - Withdrawn' is written CERTIFIED-WITHDRAWN from FY2020 on (CERTIFIED, DENIED, WITHDRAWN, CERTIFIED-WITHDRAWN; a few other filed values in FY2013-FY2014). Meaning is not otherwise changed. |
| `dol_stats_status` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 2e-06 | CERTIFIED/WITHDRAWN/DENIED | Persistfolio's classification of the row into DOL's four published status counts (CERTIFIED, DENIED, WITHDRAWN). CERTIFIED-WITHDRAWN rows are split by a rule that is PROVISIONAL (not documented by DOL); for FY2010-FY2015 it uses a submit-date proxy. Empty for statuses outside the four. |
| `dol_stats_status_basis` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 2e-06 | STATUS_AS_FILED/ORIGINAL_CERT_DATE_VS_FY_START/NO_ORIGINAL_CERT_DATE_IN_SOURCE_ASSUMED_CERTIFIED/SUBMIT_DATE_PROXY_NO_ORIGINAL_CERT_DATE_IN_SOURCE/ORIGINAL_CERT_DATE_BLANK_ASSUMED_CERTIFIED | How dol_stats_status was decided (STATUS_AS_FILED, ORIGINAL_CERT_DATE_VS_FY_START, SUBMIT_DATE_PROXY_NO_ORIGINAL_CERT_DATE_IN_SOURCE, NO_ORIGINAL_CERT_DATE_IN_SOURCE_ASSUMED_CERTIFIED). Rows classified without an original certification date are estimates. |
| `case_submitted` | TIMESTAMP | normalized |  | 0.0 |  | Date the LCA was submitted, as filed (FY2010-FY2015 files use a differently named column; mapped here). |
| `decision_date` | TIMESTAMP | normalized |  | 0.0 |  | Date DOL decided the case, as filed. |
| `original_cert_date` | TIMESTAMP | normalized |  | 0.958374 |  | ORIGINAL_CERT_DATE as filed. Present in FY2016 onward; empty for FY2010-FY2015 (column does not exist in those files). |
| `first_seen_fy` | INTEGER | derived / inferred (see METHODOLOGY) |  | 0.0 | 2021/2016/2018/2019/2022/2023/2015/2017/2020/2025/2024/2014/2013/2012/2011/2010 | Earliest fiscal year in which this case number appears in the loaded files. |
| `n_fy_appearances` | BIGINT | derived / inferred (see METHODOLOGY) |  | 0.0 | 1/2/3/4/5/11/10 | Number of fiscal years in which this case number appears in the loaded files. |
| `row_role` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 | ORIGINAL_DECISION/REPEATED_SNAPSHOT/LATER_STATUS_EVENT | ORIGINAL_DECISION (first decision record of an application in the loaded files); LATER_STATUS_EVENT (a later status record, for example certified then withdrawn); REPEATED_SNAPSHOT (identical copy of an earlier file's row, produced by cumulative year-to-date files; not a new event). Count applications with ORIGINAL_DECISION only. |
| `row_role_evidence` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.921182 | IDENTICAL_TO_EARLIER_FILE_ROW/ORIG_CERT_DATE_BEFORE_PERIOD+EARLIER_LOADED_ROW/SUBMIT_DATE_PROXY_BEFORE_PERIOD+EARLIER_LOADED_ROW/ORIG_CERT_DATE_BEFORE_PERIOD/EARLIER_LOADED_ROW/SUBMIT_DATE_PROXY_BEFORE_PERIOD/DUPLICATE_CASE_NUMBER_WITHIN_FILE/ORIG_CERT_DATE_BEFORE_PERIOD+EARLIER_LOADED_ROW+DUPLICATE_CASE_NUMBER_WITHIN_FILE/EARLIER_LOADED_ROW+DUPLICATE_CASE_NUMBER_WITHIN_FILE | Why the role was assigned (tokens joined by +): ORIG_CERT_DATE_BEFORE_PERIOD, SUBMIT_DATE_PROXY_BEFORE_PERIOD, EARLIER_LOADED_ROW, DUPLICATE_CASE_NUMBER_WITHIN_FILE, IDENTICAL_TO_EARLIER_FILE_ROW. Empty for ORIGINAL_DECISION. |
| `visa_class` | VARCHAR | source-reported |  | 0.0 | H-1B | Visa class as filed. All rows in this product are H-1B. FY2010 has no visa-class column: all FY2010 rows are ASSUMED H-1B (see LIMITATIONS). |
| `job_title` | VARCHAR | source-reported |  | 4.1e-05 |  | Job title as typed by the employer. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `full_time_position` | VARCHAR | source-reported |  | 0.109528 | Y/N | Full-time position flag as filed (Y/N). |
| `total_worker_positions` | INTEGER | source-reported | workers | 4e-05 |  | Number of worker positions requested, as filed (TOTAL_WORKER_POSITIONS / TOTAL WORKERS). Does NOT reproduce DOL published position totals (+2% to +4%); unsupported for reconciliation. |
| `period_start` | TIMESTAMP | normalized |  | 0.073375 |  | Intended employment begin date, as filed. |
| `period_end` | TIMESTAMP | normalized |  | 0.073377 |  | Intended employment end date, as filed. |
| `soc_code_raw` | VARCHAR | source-reported (formula wrapper removed) |  | 6.2e-05 |  | SOC code as filed, with any Excel formula wrapper removed. Some FY2010-FY2012 and FY2022 values were converted to dates by Excel inside DOL's files and are unreadable; they are kept as filed. |
| `soc_title_raw` | VARCHAR | source-reported |  | 0.002454 |  | SOC occupation title as filed. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `soc_code_std` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.002867 |  | Filed SOC code standardized only in format (e.g. 15-1132; O*NET suffix .00 removed). Never mapped to a different code. Empty if unparseable. |
| `soc_std_method` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 | AS_FILED/ONET_8DIGIT_TRUNCATED/UNPARSEABLE/CODE_FOUND_IN_TITLE_FIELD/MISSING/HYPHEN_ADDED | How soc_code_std was produced (AS_FILED, ONET_8DIGIT_TRUNCATED, UNPARSEABLE, ...). |
| `soc_vintage_used` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.013719 | 2010/2018/2000 | SOC classification vintage in which soc_code_std was found: 2000, 2010 or 2018. Empty when the code is in no vintage (for example legacy 15-1799). |
| `soc_vintage_basis` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.002867 | FILE_EXPECTED_VINTAGE/FALLBACK_OTHER_VINTAGE_THAN_FILE_EXPECTED/CODE_IN_NO_LOADED_SOC_VINTAGE | Why that vintage was chosen (expected vintage for the file from code evidence, then fallback order). |
| `soc_vintage_evidence` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.013719 | 2010/2000/2010/2018/2018/2000/2000/2010/2010/2018 | The code evidence behind the vintage choice (which vintages contain the code). |
| `soc_mapping_class` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 | SPLIT_UNRESOLVED/ONE_TO_ONE_SAME_CODE/ONE_TO_ONE_RENUMBERED/NATIVE_2018_CODE/CHAIN_2000_TO_2018_UNIQUE/CHAIN_2000_SPLIT_UNRESOLVED/NOT_IN_CROSSWALK_OTHER/NOT_IN_CROSSWALK_R&D_VARIANT_CODE/INVALID_OR_MISSING/MANY_TO_ONE_MERGE | Result of mapping the filed code to SOC 2018: ONE_TO_ONE, SPLIT_UNRESOLVED (several 2018 codes possible), NOT_IN_CROSSWALK_*, INVALID_OR_MISSING, and others. Check before aggregating across vintages. |
| `soc2018_code` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.358014 |  | SOC 2018 code, filled ONLY for one-to-one mappings. |
| `soc2018_candidates` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.655705 |  | For split mappings: the candidate SOC 2018 codes (pipe separated). A candidate list, not a choice. |
| `employer_name_raw` | VARCHAR | source-reported |  | 4.7e-05 |  | Employer legal name as filed, whitespace-trimmed. May contain filer-typed text such as numbers (see LIMITATIONS 12). Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `employer_dba_raw` | VARCHAR | source-reported |  | 0.884397 |  | Employer trade name (d/b/a) as filed. May contain filer-typed text such as numbers (see LIMITATIONS 12). Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `employer_id` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 |  | Persistfolio employer key after the R5-v1 identity rule. PROVISIONAL analytical key, not a statement about corporate identity. For the employers listed in LIMITATIONS 15 the value is opaque (not derivable from published columns); otherwise it is a hash of the normalized name key. |
| `employer_id_l1` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 |  | Employer key from the name-normalization step before any R5 split. Join on this to undo every split. For the employers listed in LIMITATIONS 15 the value is opaque (not derivable from published columns); otherwise it is a hash of the normalized name key. |
| `evidence_component_id` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.328337 |  | Opaque id of the disconnected evidence component (address, phone, sector) the row belongs to inside its name group. Not derivable from any published column (LIMITATIONS 15). |
| `identity_split_status` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 | MERGED_BACK_NO_POSITIVE_DISTINCTNESS/NOT_APPLICABLE_SINGLE_COMPONENT/NOT_EVALUABLE_MISSING_INDUSTRY_OR_STATE/SPLIT_R5 | SPLIT_R5 (name split into several ids by R5-v1), MERGED_BACK_NO_POSITIVE_DISTINCTNESS, NOT_APPLICABLE_SINGLE_COMPONENT, NOT_EVALUABLE_MISSING_INDUSTRY_OR_STATE. |
| `identity_policy_version` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 | R5-v1 | Identity rule version (R5-v1). |
| `employer_key_l1` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 4.7e-05 |  | Normalized employer name key (upper-case, punctuation and accents removed, HTML artifacts cleaned). Derived, not a legal name. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `identity_status` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 | NORMALIZED/UNRESOLVED_REVIEW/AS_FILED | AS_FILED (single spelling), NORMALIZED (several spellings merged by name rule), UNRESOLVED_REVIEW (name with unresolved variants or disconnected evidence). |
| `identity_flags` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.106749 | MULTI_SPELLING_MERGED_BY_L1/MULTI_SPELLING_MERGED_BY_L1/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/SPACING_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/SPACING_VARIANT_CANDIDATE_UNMERGED/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/SAME_NAME_DISCONNECTED_LOCATIONS_MULTI_STATE/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/SAME_NAME_DISCONNECTED_LOCATIONS_MULTI_STATE/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/SPACING_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/SAME_NAME_DISCONNECTED_LOCATIONS_MULTI_STATE/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/SAME_NAME_DISCONNECTED_LOCATIONS_MULTI_STATE/SPACING_VARIANT_CANDIDATE_UNMERGED/SPACING_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/SPACING_VARIANT_CANDIDATE_UNMERGED/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/SPACING_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/SPACING_VARIANT_CANDIDATE_UNMERGED/EMPLOYER_NAME_MISSING | Pipe-separated flags, for example MULTI_SPELLING_MERGED_BY_L1, LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED, SPACING_VARIANT_CANDIDATE_UNMERGED, SAME_NAME_DISCONNECTED. |
| `n_evidence_components` | BIGINT | derived / inferred (see METHODOLOGY) |  | 4.7e-05 | 1/2/3/4/5 | Number of disconnected evidence components in the name group (more than 1 means one name spans separate addresses/phones). |
| `candidate_group_id` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.575064 |  | Optional link to possibly related names (shared looser name key). NEVER an assertion that the names are one company. For the employers listed in LIMITATIONS 15 the value is opaque (not derivable from published columns); otherwise it is a hash of the normalized name key. |
| `employer_city` | VARCHAR | source-reported |  | 4.7e-05 |  | Employer city as filed. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `employer_state` | VARCHAR | source-reported |  | 0.000127 |  | Employer state as filed. |
| `employer_postal5_clean` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.003276 |  | Employer ZIP reduced to 5 digits when the filed value is a valid ZIP. |
| `employer_postal_method` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 | AS_FILED_5/LEADING_ZERO_RESTORED/ZIP_PLUS4_TRUNCATED/MISSING/UNPARSEABLE/SHORT_UNRESTORED_STATE_NOT_SUPPORTING | How the 5-digit ZIP was derived. |
| `naics_code` | VARCHAR | source-reported |  | 4.8e-05 |  | NAICS code as filed. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `h1b_dependent` | VARCHAR | source-reported |  | 0.422549 | No/N/Y/Yes | H-1B dependent employer flag as filed for this filing (Y/N). A per-filing fact, not an attribute of a company. |
| `willful_violator` | VARCHAR | source-reported |  | 0.229316 | N/No/Y/Yes | Willful violator flag as filed for this filing (Y/N). A per-filing fact, not an attribute of a company. |
| `primary_ws_city` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 6e-05 |  | City of the first worksite listed. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `primary_ws_state` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 6.5e-05 |  | State of the first worksite listed. |
| `primary_ws_postal5_clean` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.237423 |  | 5-digit ZIP of the first worksite when valid. |
| `primary_ws_postal_method` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 | AS_FILED_5/MISSING/LEADING_ZERO_RESTORED/SHORT_UNRESTORED_STATE_NOT_SUPPORTING/ZIP_PLUS4_TRUNCATED/UNPARSEABLE | How the worksite ZIP was derived. |
| `n_worksites_with_location` | BIGINT | derived / inferred (see METHODOLOGY) |  | 0.0 | 1/2/3/4/5/0/6/7/10/8/9 | Number of worksite slots with a location in the filing. |
| `n_wage_only_dup_slots` | BIGINT | derived / inferred (see METHODOLOGY) |  | 0.0 | 0/2/1 | Worksite slots that repeat only a wage line (not counted as separate locations). |
| `n_distinct_wage_tuples_located` | BIGINT | derived / inferred (see METHODOLOGY) |  | 0.0 | 1/2/0/3/4/5/6/7/8/9/10 | Distinct located wage tuples across worksites. |
| `worksite_states` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 6.5e-05 |  | Pipe-separated worksite states. |
| `wage_from_raw` | VARCHAR | source-reported |  | 5.6e-05 |  | Offered wage (low end) as filed (text). Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `wage_to_raw` | VARCHAR | source-reported |  | 0.553601 |  | Offered wage (high end) as filed (text). Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `wage_unit_raw` | VARCHAR | source-reported |  | 2.7e-05 | Year/Hour/Month/Week/Bi-Weekly/Select Pay Range | Offered wage unit as filed (Year, Hour, Week, Bi-Weekly, Month). FY2010 has no unit column: the unit is INFERRED from the prevailing-wage unit. |
| `wage_annual_from_calc` | DOUBLE | derived / inferred (see METHODOLOGY) | USD per year | 7e-05 |  | Offered wage low end converted to an annual amount. Use ONLY where wage_status starts with OK_. Rows with wage_status AMBIGUOUS can carry a populated but unreliable value (some absurd); ignore it. |
| `wage_annual_to_calc` | DOUBLE | derived / inferred (see METHODOLOGY) | USD per year | 0.719217 |  | Offered wage high end annualized under the same rule; use only where wage_status starts with OK_. |
| `wage_status` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.0 | OK_AS_FILED_ANNUAL/OK_CONVERTED/AMBIGUOUS/UNSUPPORTED_MISSING/UNSUPPORTED_UNIT_MISSING/UNSUPPORTED_NONPOSITIVE | OK_AS_FILED_ANNUAL, OK_CONVERTED, AMBIGUOUS, UNSUPPORTED_*, MISSING. Use the OK_* values for wage statistics. |
| `wage_flags` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.265159 |  | Pipe-separated wage plausibility flags. |
| `wage_unit_correction_candidate_not_applied` | VARCHAR | derived / inferred (see METHODOLOGY) |  | 0.999429 | AS_ANNUAL | Set when the filed unit looks wrong (for example hourly figure with Year unit). Reported, never applied. |
| `pw_raw` | VARCHAR | source-reported |  | 0.003939 |  | Prevailing wage as filed (text). Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `pw_unit_raw` | VARCHAR | source-reported |  | 0.003948 | Year/Hour/Month/Week/Bi-Weekly | Prevailing wage unit as filed. |
| `pw_level_raw` | VARCHAR | source-reported |  | 0.347807 | II/Level II/III/Level I/I/IV/Level III/Level IV/N/A/V | Prevailing wage level as filed (FY2015, FY2017-FY2019 'Level I-IV'; FY2020 on 'I'-'IV'). Empty for FY2010-FY2014 and FY2016 (no such column in those files). |
| `pw_annual` | DOUBLE | derived / inferred (see METHODOLOGY) | USD per year | 0.003957 |  | Prevailing wage annualized when the unit is supported. |

## worksites

| column | type | source status | unit | null share | allowed values | description |
|---|---|---|---|---|---|---|
| `case_number` | VARCHAR | source-reported |  | 0.0 |  | DOL LCA case number as filed (format I-200-YYDDD-NNNNNN). One application has one case number; the same case number can appear in several rows (status records). |
| `source_key` | VARCHAR | normalized |  | 0.0 |  | Which DOL file this row came from: fiscal year (2010-2019 annual) or year+quarter (2020Q1-2025Q4). Lineage key with source_row. |
| `source_fy` | INTEGER | normalized |  | 0.0 | 2019/2021/2018/2016/2023/2022/2017/2015/2014/2025/2020/2024/2013/2012/2011/2010 | U.S. federal fiscal year (Oct 1 - Sep 30) of the DOL file. |
| `source_row` | BIGINT | normalized |  | 0.0 |  | Worksheet row number in the original DOL workbook, header = row 1; non-H-1B rows and blank padding rows are counted, so values have gaps. With source_key it identifies the row uniquely. |
| `slot` | INTEGER | source-reported |  | 0.0 | 1/2/3/4/5/6/7/8/9/10 | Worksite slot number (1-N) within the filing. |
| `worksite_workers_raw` | VARCHAR | source-reported |  | 0.534758 |  | Workers at this worksite as filed. |
| `ws_city` | VARCHAR | source-reported |  | 0.00947 |  | Worksite city as filed. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `ws_county` | VARCHAR | source-reported |  | 0.26855 |  | Worksite county as filed. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `ws_state` | VARCHAR | source-reported |  | 0.009494 |  | Worksite state as filed. |
| `ws_postal` | VARCHAR | source-reported |  | 0.258348 |  | Worksite postal code as filed (text). Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `is_location_bearing` | BOOLEAN | derived / inferred (see METHODOLOGY) |  | 0.0 | true/false | True when the slot carries a worksite location. |
| `wage_from_raw` | VARCHAR | source-reported |  | 5.3e-05 |  | Offered wage (low end) as filed (text). Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `wage_to_raw` | VARCHAR | source-reported |  | 0.563105 |  | Offered wage (high end) as filed (text). Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `wage_unit_raw` | VARCHAR | source-reported |  | 0.000808 | Year/Hour/Month/Week/Bi-Weekly/Select Pay Range | Offered wage unit as filed (Year, Hour, Week, Bi-Weekly, Month). FY2010 has no unit column: the unit is INFERRED from the prevailing-wage unit. |
| `pw_raw` | VARCHAR | source-reported |  | 0.013183 |  | Prevailing wage as filed (text). Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `pw_unit_raw` | VARCHAR | source-reported |  | 0.013968 | Year/Hour/Month/Week/Bi-Weekly | Prevailing wage unit as filed. |
| `pw_level_raw` | VARCHAR | source-reported |  | 0.371784 | II/Level II/III/Level I/I/IV/Level III/Level IV/N/A/V | Prevailing wage level as filed (FY2015, FY2017-FY2019 'Level I-IV'; FY2020 on 'I'-'IV'). Empty for FY2010-FY2014 and FY2016 (no such column in those files). |
| `pw_source_raw` | VARCHAR | source-reported |  | 0.45031 |  | Prevailing wage source as filed (for example OES). Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `pw_source_year_raw` | VARCHAR | source-reported |  | 0.104054 |  | Prevailing wage source year as filed. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `is_wage_only_dup_of_slot1` | BOOLEAN | derived / inferred (see METHODOLOGY) |  | 0.0 | false/true | True when the slot only repeats a wage line of slot 1 (not a separate location). |

## applications

| column | type | source status | unit | null share | allowed values | description |
|---|---|---|---|---|---|---|
| `case_number` | VARCHAR | derived |  | 0.0 |  | DOL LCA case number; one row per case number across all loaded files. |
| `n_status_records_loaded` | BIGINT | derived |  | 0.0 | 1/2/3/4/11/10 | Number of distinct status records for the case in the loaded files (repeated snapshots excluded). |
| `n_repeated_snapshot_rows_loaded` | BIGINT | derived |  | 0.0 | 0/1/2/3 | Rows for the case that are identical copies of earlier file rows (cumulative files). Not events. |
| `status_history_loaded` | VARCHAR | derived |  | 0.0 |  | Status history across files, "source_key:status > source_key:status", repeated snapshots excluded. |
| `employer_id` | VARCHAR | derived |  | 0.0 |  | Employer key (R5-v1; provisional) of the case. For the employers listed in LIMITATIONS 15 the value is opaque (not derivable from published columns); otherwise it is a hash of the normalized name key. |
| `soc_code_std` | VARCHAR | derived |  | 0.002947 |  | Filed SOC code, format-standardized. |
| `positions_requested` | INTEGER | derived | workers | 4.3e-05 |  | Maximum worker positions requested across the case's records, as filed. |
| `n_distinct_positions_across_records` | BIGINT | derived |  | 0.0 | 1/0/2/3 | Number of distinct position counts across records; more than 1 means the filings disagree. |
| `original_decision_fy_loaded` | INTEGER | derived |  | 0.002742 | 2019/2018/2016/2022/2015/2017/2025/2020/2024/2021/2023/2014/2013/2012/2011/2010 | Fiscal year of the case's original decision record, if loaded. |
| `original_decision_source_key_loaded` | VARCHAR | derived |  | 0.002742 |  | Source file of the original decision record, if loaded. |
| `original_row_status` | VARCHAR | derived |  | 0.0 | ORIGINAL_LOADED/ORPHAN_ORIGINAL_PERIOD_NOT_LOADED | ORIGINAL_LOADED, or ORPHAN_ORIGINAL_PERIOD_NOT_LOADED when only later status records exist in the loaded files. |
| `original_cert_fy_inferred` | INTEGER | derived |  | 0.957535 | 2016/2017/2021/2022/2018/2020/2015/2023/2019/2024/2025/2014/2013/2012/2011/2010/2009 | Fiscal year implied by ORIGINAL_CERT_DATE, when present. |
| `latest_status_in_loaded_files` | VARCHAR | derived |  | 0.0 | CERTIFIED/CERTIFIED-WITHDRAWN/WITHDRAWN/DENIED/PENDING QUALITY AND COMPLIANCE REVIEW - UNASSIGNED/REJECTED | Latest status in the loaded files. Not necessarily the final status. |
| `latest_loaded_fy` | INTEGER | derived |  | 0.0 | 2019/2018/2016/2017/2022/2025/2015/2020/2024/2023/2021/2014/2013/2012/2010/2011 | Latest fiscal year in which the case appears. |
| `final_status_confidence` | VARCHAR | derived |  | 0.0 | OPEN_FINAL_STATUS_UNKNOWN_LATER_PERIODS_NOT_LOADED/TERMINAL_IN_LOADED_FILES | TERMINAL_IN_LOADED_FILES, or OPEN_FINAL_STATUS_UNKNOWN_LATER_PERIODS_NOT_LOADED (a CERTIFIED case could still be withdrawn later; the last file ends at FY2025). |

## panel

| column | type | source status | unit | null share | allowed values | description |
|---|---|---|---|---|---|---|
| `employer_id` | VARCHAR | derived |  | 0.0 |  | Employer key (R5-v1; provisional). For the employers listed in LIMITATIONS 15 the value is opaque (not derivable from published columns); otherwise it is a hash of the normalized name key. |
| `employer_name_display` | VARCHAR | derived |  | 4.2e-05 |  | Most frequent filed spelling of the employer name. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `identity_status` | VARCHAR | derived |  | 0.0 | NORMALIZED/UNRESOLVED_REVIEW/AS_FILED | See cases.identity_status. |
| `identity_flags` | VARCHAR | derived |  | 0.247229 | MULTI_SPELLING_MERGED_BY_L1/MULTI_SPELLING_MERGED_BY_L1/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/MULTI_SPELLING_MERGED_BY_L1/SPACING_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/SAME_NAME_DISCONNECTED_LOCATIONS_MULTI_STATE/SPACING_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/SPACING_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/SAME_NAME_DISCONNECTED_LOCATIONS_MULTI_STATE/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/SPACING_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/SAME_NAME_DISCONNECTED_LOCATIONS_MULTI_STATE/SPACING_VARIANT_CANDIDATE_UNMERGED/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/SPACING_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/SAME_NAME_DISCONNECTED_LOCATIONS_MULTI_STATE/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/SPACING_VARIANT_CANDIDATE_UNMERGED/MULTI_SPELLING_MERGED_BY_L1/MERGED_SPELLINGS_WITHOUT_SHARED_PHONE_OR_ADDRESS/LEGAL_SUFFIX_VARIANT_CANDIDATE_UNMERGED/SPACING_VARIANT_CANDIDATE_UNMERGED/EMPLOYER_NAME_MISSING | See cases.identity_flags. |
| `employer_id_n_disconnected_evidence_components` | BIGINT | derived |  | 4.2e-05 | 1/2/3/4/5 | Number of disconnected evidence components the id spans. |
| `candidate_group_id` | VARCHAR | derived |  | 0.765601 |  | Link to possibly related names. Not an assertion of one company. For the employers listed in LIMITATIONS 15 the value is opaque (not derivable from published columns); otherwise it is a hash of the normalized name key. |
| `employer_id_l1` | VARCHAR | derived |  | 0.0 |  | Pre-split employer key; join on it to undo splits. For the employers listed in LIMITATIONS 15 the value is opaque (not derivable from published columns); otherwise it is a hash of the normalized name key. |
| `identity_split_status` | VARCHAR | derived |  | 0.0 | MERGED_BACK_NO_POSITIVE_DISTINCTNESS/NOT_APPLICABLE_SINGLE_COMPONENT/SPLIT_R5/NOT_EVALUABLE_MISSING_INDUSTRY_OR_STATE | See cases.identity_split_status. |
| `identity_policy_version` | VARCHAR | derived |  | 0.0 | R5-v1 | Identity rule version. |
| `soc_code_filed_std` | VARCHAR | derived |  | 0.0 |  | Filed SOC code (format-standardized) or INVALID_OR_MISSING. |
| `soc_vintage_used` | VARCHAR | derived |  | 0.0 | 2010/2018/2000/NONE | SOC vintage of the filed code (2000, 2010, 2018) or NONE. |
| `soc_mapping_class` | VARCHAR | derived |  | 0.0 | ONE_TO_ONE_SAME_CODE/SPLIT_UNRESOLVED/NATIVE_2018_CODE/ONE_TO_ONE_RENUMBERED/CHAIN_2000_TO_2018_UNIQUE/CHAIN_2000_SPLIT_UNRESOLVED/NOT_IN_CROSSWALK_OTHER/NOT_IN_CROSSWALK_R&D_VARIANT_CODE/INVALID_OR_MISSING/MANY_TO_ONE_MERGE | Mapping class to SOC 2018 (see cases). |
| `soc2018_code_if_unique` | VARCHAR | derived |  | 0.254747 |  | SOC 2018 code for one-to-one mappings only. |
| `soc2018_candidates_if_split` | VARCHAR | derived |  | 0.765937 |  | Candidate SOC 2018 codes for split mappings. |
| `soc_title_modal_filed` | VARCHAR | derived |  | 0.001821 |  | Most frequent filed SOC title. Identifier-like strings (e-mail, tax-id format, phone, long digit runs) are redacted to [REDACTED-...] tokens (LIMITATIONS 12). |
| `fiscal_year_processed` | INTEGER | derived |  | 0.0 | 2025/2022/2018/2024/2019/2017/2016/2015/2023/2011/2014/2010/2012/2013/2020/2021 | Fiscal year of the DOL file (not the year of the original decision for later status events). |
| `source_files_included` | VARCHAR | derived |  | 0.0 |  | Pipe-separated source_key values feeding the cell. |
| `fiscal_year_coverage` | VARCHAR | derived |  | 0.0 | COMPLETE_FY | COMPLETE_FY when an annual file or all four quarters are loaded. Every fiscal year in this edition is COMPLETE_FY. |
| `unique_applications_original_decision_this_fy` | BIGINT | derived |  | 0.0 |  | UNIQUE LCA applications whose original decision falls in this fiscal year. Additive across years. Use this to count filings. |
| `unique_applications_originally_certified` | BIGINT | derived |  | 0.0 |  | Of those, DOL status CERTIFIED at the original decision. |
| `unique_applications_originally_denied` | BIGINT | derived |  | 0.0 |  | Of those, DENIED. |
| `unique_applications_withdrawn_before_certification` | BIGINT | derived |  | 0.0 |  | Of those, WITHDRAWN before certification. |
| `worker_positions_requested_unique_applications` | HUGEINT | derived |  | 0.034485 |  | Sum of positions requested over those unique applications, as filed. Unsupported for reconciliation with DOL totals. |
| `worker_positions_requested_originally_certified` | HUGEINT | derived |  | 0.050965 |  | Same, for originally certified applications. |
| `fy_status_records_dol_processed_basis` | BIGINT | derived |  | 0.0 |  | Status records in the fiscal year on DOL's decision-event basis, repeated snapshots excluded. NOT unique filings: includes later status events on earlier applications. Use only to compare with DOL fiscal-year statistics. |
| `fy_repeated_snapshot_rows_excluded` | BIGINT | derived |  | 0.0 |  | Rows in the cell excluded from the fy_* columns because they are identical copies of earlier rows from cumulative files. |
| `fy_later_status_events_on_earlier_fy_applications` | BIGINT | derived |  | 0.0 |  | Later status events (for example certified then withdrawn) recorded in this fiscal year. |
| `fy_dol_basis_certified` | BIGINT | derived |  | 0.0 |  | Records classified CERTIFIED on DOL's basis (provisional rule for certified-withdrawn). |
| `fy_dol_basis_denied` | BIGINT | derived |  | 0.0 |  | Records classified DENIED. |
| `fy_dol_basis_withdrawn` | BIGINT | derived |  | 0.0 |  | Records classified WITHDRAWN. |
| `fy_cw_classified_without_original_cert_date` | BIGINT | derived |  | 0.0 |  | Records whose certified/withdrawn class is an estimate because the source has no original certification date (FY2010-FY2015). |
| `fy_status_records_positions_may_repeat_across_years` | HUGEINT | derived |  | 1.3e-05 |  | Sum of positions over fy status records; positions of one application may repeat across records and years, so this is not additive. |
| `wage_stat_n_cases` | BIGINT | derived |  | 0.0 |  | Originally certified applications with a usable annual wage (the n behind the wage statistics). |
| `wage_offered_annual_median` | DOUBLE | derived | USD per year | 0.092093 |  | Median offered annual wage (low end) over those cases, rounded to whole dollars. |
| `wage_offered_annual_p25` | DOUBLE | derived | USD per year | 0.092093 |  | 25th percentile, rounded to whole dollars. |
| `wage_offered_annual_p75` | DOUBLE | derived | USD per year | 0.092093 |  | 75th percentile, rounded to whole dollars. |
| `wage_excluded_ambiguous_n` | BIGINT | derived |  | 0.0 |  | Originally certified applications excluded from wage statistics because the unit was ambiguous. |
| `wage_excluded_unsupported_n` | BIGINT | derived |  | 0.0 | 0/1 | Excluded because the unit was unsupported. |
| `wage_stat_n_converted_from_non_annual_unit` | BIGINT | derived |  | 0.0 |  | Cases in the statistic whose wage was converted from an hourly/weekly/monthly unit. |
| `pw_level_i_n` | BIGINT | derived |  | 0.0 |  | Originally certified applications at prevailing wage Level I (matches 'Level I' and 'I'). 0 where the year has no level column (FY2010-FY2014, FY2016). |
| `pw_level_ii_n` | BIGINT | derived |  | 0.0 |  | Level II (same rule). |
| `pw_level_iii_n` | BIGINT | derived |  | 0.0 |  | Level III (same rule). |
| `pw_level_iv_n` | BIGINT | derived |  | 0.0 |  | Level IV (same rule). |
| `soc_cases_with_standardization_applied` | BIGINT | derived |  | 0.0 |  | Records where SOC format standardization changed the filed text. |
| `cases_multi_worksite` | BIGINT | derived |  | 0.0 |  | Records with more than one located worksite. |

## soc_crosswalk_2010_to_2018

| column | type | source status | unit | null share | allowed values | description |
|---|---|---|---|---|---|---|
| `soc2010_code` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `soc2010_title` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `soc2018_code` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `soc2018_title` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `fanout_2010_to_2018` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `fanin_2018_from_2010` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `mapping_type` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `same_code` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `same_title` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |

## soc_crosswalk_2000_to_2010

| column | type | source status | unit | null share | allowed values | description |
|---|---|---|---|---|---|---|
| `soc2000` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `title2000` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `soc2010` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `title2010` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `n_2010_for_2000code` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `n_2000_for_2010code` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |
| `pair_class` |  | reference (BLS/Census SOC crosswalk as published) |  |  |  | BLS/Census SOC crosswalk column as published; see METHODOLOGY. |