900만 행, 17년, 단일 ETL: H-1B 데이터를 정제한 방법

260개 컬럼의 Excel 파일을 충돌 없이 어떻게 로드하나요?

첫 번째 타깃은 FY2024 Q4. 깔끔한 29개 컬럼, 멀쩡한 이름, 얌전한 Excel. 파이프라인은 2분 만에 읽었습니다. 자신감이 높았죠. 그러다 FY2019가 도착했습니다. 260개 컬럼. _1부터 _10까지 끝나는 이름, 근무지마다 하나씩. 같은 급여가 WAGE_RATE_OF_PAY_FROM_1과 다른 9곳에 저장됨. 순진한 로더는 RAM 3GB를 찍고 죽었습니다. 첫 번째 교훈: 필요한 것만 읽어라. 컬럼 범위 읽기로 메모리 8배, 실행 시간 5배 감소.

4년마다 바뀌는 컬럼 이름을 어떻게 처리하나요?

FY2008~FY2009는 기본 키를 CASE_NO라고 부릅니다. FY2010~FY2014는 LCA_CASE_NUMBER로 이름을 바꾸고 모든 필드에 LCA_CASE_ 접두사를 붙입니다. FY2015~FY2018은 접두사를 뺍니다. FY2019는 _1 접미사를 추가합니다. FY2020은 CASE_NUMBER로 정착합니다. 모든 필드가 이런 역사를 가졌습니다. EMPLOYER_NAME은 한때 NAME, 그다음 LCA_CASE_EMPLOYER_NAME이었습니다. SOC_TITLE은 OCCUPATIONAL_TITLE, 그다음 LCA_CASE_SOC_NAME, 그다음 SOC_NAME.

답은 약 50개 항목의 COLUMN_ALIASES 사전. 모든 과거 이름은 다운스트림 로직이 실행되기 전에 정규 FY2020+ 형식으로 재작성됩니다. 그 이후로 파이프라인은 하나의 언어만 말합니다. 새 시대를 추가하는 것은 사전에 몇 줄을 추가하는 것이 됩니다.

Excel은 왜 SOC 직업 코드를 파괴하나요?

15-1132 같은 SOC 코드는 직업을 설명합니다(이 경우 소프트웨어 개발자). Excel은 15-1132를 보고 1132년 11월 15일을 떠올립니다. 셀을 그 날짜로 재작성하고 내부에 숫자로 저장하며 원본 코드는 사라집니다. 때로는 Excel이 더 나아가 15-1132를 Jan-15 문자열로 변환합니다. 때로는 Feb-32로. 때로는 1132-01-15로. 모두 복구 불가능입니다.

파이프라인은 이제 4가지 Excel 손상 패턴을 감지합니다. 월 이름 SOC 코드: NULL. ISO 날짜 SOC 코드: NULL. 2000년 이전 신청의 오래된 DOT 코드: NULL (현대 SOC와 1:1 매핑되지 않음). 대시 없는 6자리 SOC 코드(151132): 대시 재삽입. 17년간의 SOC 코드 중 약 2%가 이 경로 중 하나에 해당합니다. 모든 규칙은 배포된 전투입니다.

5가지 다른 급여 단위 형식을 어떻게 연간화하나요?

신청자는 5가지 단위로 급여를 보고합니다. 연간, 월간, 격주, 주간, 시간당. 비교하려면 모두 연간이 되어야 합니다. 연간 x1. 월 x12. 격주 x26. 주 x52. 시간 x2080 (40시간 x 52주). 벡터화된 pandas가 파일당 밀리초로 계산합니다.

오래된 파일은 단위를 다르게 약칭합니다. FY2008은 소문자 yr, hr, mth, wk, bi를 사용합니다. FY2009는 대문자 YR, HR, MTH, WK, BI로 뒤집습니다. 매핑을 놓치면 120만 개의 오래된 행이 NaN 급여를 생성하고 승수 조회가 조용히 실패합니다. 수정: 모든 계산 전에 실행되는 WAGE_UNIT_NORMALIZE 사전. FY2015~FY2018은 새로운 괴물을 추가합니다. 66000 - 70000 같은 단일 문자열로 저장된 급여. 파이프라인은 문자열을 나누고 두 반쪽을 숫자로 강제하며 급여 단위 자체가 없으면 우세 임금 단위로 대체합니다.

더러운 값을 NULL 처리할 때와 행을 삭제할 때는 언제인가요?

연 $0의 급여는 합법적인 급여가 아닙니다. 신청자가 필드를 건너뛴 것입니다. NULL. 시간당 $1,000의 우세 임금도 합법적이지 않습니다. 누군가 시간 필드에 연간 급여를 입력한 것입니다. NULL. SUITE 100이나 123 MAIN ST 같은 근무지 도시는 도시가 아닙니다. NULL. 실제로는 국가인 주 코드 (GEORGIA가 항상 Atlanta와 Alpharetta 신청에 대해 국가 필드에 282번 나타남). GA 주로 수정, UNITED STATES OF AMERICA를 국가 필드로 이동.

배우는 데 가장 오래 걸린 규칙: 행을 삭제하지 말라. 필드를 NULL 처리하라. 300행당 약 1행이 최소 하나의 쓰레기 값을 가집니다. 전체 행을 삭제하면 수백만 개의 유용한 레코드를 잃었을 것입니다. 기본 키(case_number)가 없는 행만 삭제됩니다. 나머지는 필요한 곳에 NULL을 두고 살아남습니다.

ETL 파이프라인의 출력을 독립적으로 어떻게 검증하나요?

ETL은 자신의 테스트를 통과합니다. 그것은 아무것도 증명하지 않습니다. 그래서 ETL과 0의 로직을 공유하지 않는 별도의 QA 스크립트가 만들어졌습니다. 최종 SQLite 데이터베이스를 블랙박스로 열고 모든 행을 참조 값에 대해 검증합니다. 50개 미국 주와 영토, 유효한 사건 상태(Certified, Certified - Withdrawn, Denied, Withdrawn), 타당한 급여 범위, SOC 코드 형식, 날짜 경계. ETL과 QA는 서로 말하지 않기 때문에 서로를 속일 수 없습니다.

그다음 verify_computed.py가 나왔습니다. 모든 파생 컬럼(salary_min, salary_max, prevailing_wage_annual, employer_name_clean)은 원시 필드에서 독립적으로 재계산되어 ETL이 쓴 것과 비교됩니다. 불일치는 ETL이 비결정적이라는 것을 의미합니다. 그다음 compare_dbs.py는 처음부터 두 번 전체 ETL을 실행하고 모든 행을 비교합니다. 같은 입력, 같은 출력, 제로 드리프트. 이 삼총사(QA, verify_computed, compare_dbs)가 품질 계약입니다.

깔끔한 900만 행 SQLite 데이터베이스는 어떻게 생겼나요?

출력은 하나의 SQLite 파일. 900만 행. 37개 컬럼. 일반적인 쿼리 패턴을 커버하는 7개 인덱스(고용주 검색, SOC 코드, 주, 회계연도, 급여 범위, 상태, 직책). FTS5 가상 테이블이 고용주 이름, 직책, 도시에 대해 밀리초 미만 검색을 제공합니다. 총 크기 약 4GB. ETL은 전체 이력에 대해 노트북에서 30~60분, 분기 업데이트는 2분에 실행됩니다. 다음 아크가 이미 시작되고 있습니다. FY2025와 FY2026 Q1 데이터가 도착 중이고, 컬럼 감사가 다시 시작됩니다.