테이블 스키마와 데이터 흐름

변경 이유가 서로 다른 코드를 한 파일에 두지 않으려고 나눴습니다. 병합이 한 덩어리였을 때는 정책을 한 줄 바꾸는 작업이 이력 기록과 검증 쿼리까지 같이 열게 만들었습니다. 지금은 후보 생성(master_candidates)은 변경 감지 규칙이나 정책이 바뀔 때, 이력(master_history)은 남길 항목이 바뀔 때, 변이(master_mutations)는 마스터 컬럼이 바뀔 때, 검증(reconciliation)은 검증 항목이 늘 때 각각 열립니다.

나눈 뒤 얻은 것이 하나 더 있습니다. 이 파이프라인은 순서가 설계입니다. 건수는 반영 전에 세야 하고, 이력은 갱신 전에 써야 하고, 검증까지 한 트랜잭션이어야 합니다. master_sync는 그 순서만 조립하고 각 단계 구현을 갖고 있지 않아서, 181줄 한 파일을 읽으면 순서가 그대로 보입니다.

트랜잭션 경계도 구조로 고정했습니다. atomic을 여는 곳은 apply_master 하나뿐이고 나머지 모듈은 BEGIN·COMMIT을 모릅니다. 커밋 지점이 여러 곳에 생기는 사고를 파일 경계로 막았습니다. 테스트 표면도 함께 갈라집니다. 정규화·품질은 DB 없이 순수 함수로, 후보 생성은 임시 테이블 결과로, 검증은 숫자만으로 확인합니다. 한 덩어리면 이 전부를 통합 테스트를 통해서만 볼 수 있습니다.

merge.py는 로직 없는 공개 파사드입니다(39줄, 재수출과 __all__뿐). 내부를 어떻게 나눠도 DAG·CLI·테스트가 부르는 진입점(merge.stage, merge.apply_master 등)은 그대로 두려는 경계이고, __all__로 "여기까지가 쓰라고 만든 표면"을 코드로 표시했습니다.

비용도 있습니다. 파일이 늘어 호출 흐름을 따라가려면 한 번 더 점프해야 합니다. 그래서 순서를 master_sync 한 곳에 모아 그 비용을 줄였습니다. 처음부터 이 구조는 아니었고 구현 중에는 한 모듈이었습니다. 트랜잭션 경계와 이력 순서가 코드에서 드러나지 않는다고 판단해 제출 전에 책임별로 나눴습니다. 현재 include/pipeline/ 19개 모듈에서 가장 큰 파일이 quality.py 209줄이고, 병합 관련 4개는 59~159줄입니다.

아닙니다. is_valid = not any(issue.severity == ERROR) — "이 행에 ERROR급 문제가 하나라도 있나"를 접은 종합 플래그입니다. 중복 키(PK_DUPLICATE)는 ERROR를 만드는 원인 중 하나일 뿐이고, 키 형식 오류(PK_FORMAT)나 근로자수 파싱 불가(EMP_INVALID)도 같은 플래그를 내립니다. 검증번호 불일치나 미래 성립일자는 WARN이라 is_valid에 영향이 없습니다.

두 말은 충돌하지 않고 같은 기준의 두 표현입니다. is_valid = not any(issue.severity == ERROR)이므로 is_valid=false ⇔ 그 행에 ERROR 이슈가 1개 이상 있음 — "is_valid=false면 값 반영 제외"와 "ERROR일 때만 제외"는 동일한 문장입니다. 흐름도의 is_valid 마름모는 ERROR 판정을 다시 하는 게 아니라 이미 내려진 판정의 요약 플래그를 읽을 뿐입니다.

혼동 포인트는 "quarantine에 기록됨 = 제외"가 아니라는 것. ops_quarantine은 두 종류를 담습니다 — ERROR는 격리(마스터에서 뺀 행), WARN/INFO는 확인 필요(넣긴 했지만 사람이 봐야 하는 행). 그래서 키 형식 오류(PK_FORMAT)·중복 키 앞 행(PK_DUPLICATE)·근로자수 파싱 불가(EMP_INVALID)는 ERROR라 값 반영에서 빠지지만, 검증번호 불일치와 성립일자 후퇴 17건(DATE_REGRESSION)은 WARN이라 quarantine에 기록되면서도 마스터에는 최신 값이 반영됩니다. 제외 기준은 severity=ERROR 하나입니다.

"아니오" 분기가 "등장으로만 집계"인 이유는 has_valid_key가 별도 플래그이기 때문입니다. 중복 키 앞 행처럼 is_valid=false여도 has_valid_key=true면 "값은 못 쓰지만 이 사업장이 이번 소스에 등장한 건 사실"이라 값 반영만 빠지고 등장으로는 집계됩니다 — 멀쩡히 존재하는 사업장이 ABSENT로 오판되는 걸 막는 장치입니다. 키 자체를 못 쓰는 행(has_valid_key=false)은 이 분기에 오기 전에 이미 ERROR 격리로 빠져 있습니다.

스테이징의 resolve_duplicate_keys가 마지막 행만 반영 후보로 남깁니다. 원천에 갱신 시각이 없어 원본 행 순서를 결정적 tie-breaker로 썼고, 앞 행들은 is_valid=false + PK_DUPLICATE ERROR로 격리 기록에 어느 행을 채택했는지까지 남깁니다. 단, has_valid_key는 true 유지 — 중복이어도 그 사업장이 소스에 등장한 건 사실이니까요.

실데이터엔 파일 내 중복이 0건이라 발동한 적은 없고, 그래도 master PK 제약과 검증 8종 중 PK_UNIQUE가 이중으로 막습니다.

규칙 12종은 quality.py 코드에 있고, 규칙 정의 테이블은 없습니다. 테이블에는 정의가 아니라 발동 결과만 남습니다 — 행 단위는 ops_quarantine.rule_id, 회차 집계는 ops_sync_run.issue_summary_json.

규칙을 DB로 빼는 건 규칙이 런타임에 바뀌거나 비개발자가 관리할 때 가치가 있는데, 이 과제는 12개 전부가 코드 리뷰·테스트 대상이고 배포와 함께 바뀌는 게 안전해서 코드 정의를 택했습니다. 변경 이력도 git이 남깁니다.

해시를 쓰면 Python(정규화)과 SQL(병합) 양쪽에 같은 직렬화 규칙을 두 번 구현하게 되고, 두 구현이 갈라지는 순간 "변경 없음"으로 조용히 오판됩니다. 어차피 변경 이력에 컬럼별 전/후 값을 남겨야 해서 컬럼 단위 IS DISTINCT FROM 비교는 중복 비용도 아닙니다. NULL 안전 비교라 결측 ↔ 값 전환도 정확히 잡습니다.

세 덩어리입니다: ① raw 연도 교체, ② 스테이징+품질 기록, ③ 병합+이력+비활성 표시+검증 8종. 특히 ③을 apply_master가 atomic() 하나로 묶는 이유는 — 따로 커밋하면 검증 실패 회차가 마스터에 절반만 반영된 채 남기 때문입니다. 검사가 하나라도 어긋나면 ReconciliationFailedError로 마스터와 이력이 통째로 롤백되고 회차는 FAILED로 마감됩니다.

Insert/Update/무변경 분류는 집합 연산이라 SQL이 자연스럽습니다. build_merge_candidates가 stg를 master에 LEFT JOIN해 TEMP merge_candidate(신규 여부·정책 반영 목표값·old 값)를 만들고, 이력·변이·집계가 전부 이 한 임시 테이블을 근거로 움직여 서로 어긋날 수 없습니다. 행 단위 파이썬 루프는 30K 행에서도 느리고, 정책(NULL 보존)을 CASE 식으로 한 곳에 박기도 어렵습니다.

두 겹입니다. 데이터는 트랜잭션 경계 덕에 반쯤 반영된 상태가 없고 — 커밋 전이면 이전 스냅샷 그대로입니다. 회차 기록은 _finish_unexpected_failure가 RUNNING으로 남은 ops_sync_run 행을 FAILED로 닫아서, "영원히 RUNNING인 유령 회차"가 남지 않습니다.

raw·stg는 해당 연도 delete-insert라 멱등이고, ops_sync_run은 run_id 기준 UPSERT입니다. 단 마스터가 이미 더 새 연도까지 반영된 상태에서 과거 연도를 다시 돌리는 건 assert_year_is_not_regressive가 의도적으로 거부합니다 — 2023이 반영된 마스터에 2022를 덮으면 최신 상태가 과거로 후퇴하니까요. 전체 재현은 ./up.sh(볼륨 초기화 포함)로 합니다.

네, 그리고 그게 한 번 어긋났던 적이 있어 특히 신경 썼습니다. 초기엔 테스트 헬퍼가 병합·부재·검증을 세 트랜잭션으로 따로 부르고 있었는데, DAG과 CLI는 apply_master 한 트랜잭션을 탑니다. 리뷰에서 발견해 헬퍼도 apply_master를 직접 호출하게 고치고, 운영이 안 쓰는 래퍼 함수 2개를 삭제했습니다. 최종적으로 로컬 101 passed / 컨테이너 107 passed이고, 실제 CSV로 문서의 모든 건수가 재현됩니다.

고용보험 성립일자는 사업장이 고용보험 관계를 성립한 날짜라 원래 바뀔 이유가 없는 값인데, 두 연도에 모두 있는 21,087곳 중 17곳은 2023 파일의 성립일자가 2022 파일 값보다 더 과거로 바뀌어 있었습니다. 실제 예 — 강원과학고 방과후학교 강사(2248303794)는 딱 하루(2022-04-20 → 2022-04-19, 오탈자 냄새), 인천병방초 방과후학교 강사(1228302844)는 8개월(2022-03-01 → 2021-07-01, 소급 정정 냄새), 에스코프(3488102699)는 13일.

"2023이 소급 정정"(새 값이 맞음)과 "2023이 추출 오류"(옛 값이 맞음) 두 해석이 모두 그럴듯한데 파이프라인엔 판정 근거가 없습니다. 그래서 임의로 확정하지 않고 — 과제 규칙대로 최신 소스 값을 마스터에 반영하되, 병합 단계에서 DATE_REGRESSION WARN 17건을 quarantine에 남겼습니다(메시지: "고용보험 성립일자가 과거로 이동: 2022-04-20 -> 2022-04-19"). 이 규칙은 마스터의 기존 값과 비교해야 해서 스테이징 12종과 달리 병합 단계에서 기록됩니다.

덧붙일 관찰 하나 — 17건에 방과후학교 강사·특고 계약 건이 유난히 많습니다. 계약 단위 재산정 같은 원천 특성일 수 있지만 데이터로 확증되지 않으므로, 면접에서는 "원천 부서에 확인해볼 지점"까지만 말하는 게 정확합니다.

학교 전학 처리로 설명하면 — 우리 학교 출석부(마스터)에 30,513명이 있는데 교육청에서 올해 명단(새 CSV)이 왔습니다. 전학생은 등록하고, 정보 바뀐 학생은 고치고, 명단에 없는 학생은 "전출 간 듯"으로 표시했습니다. 이 작업이 진짜 제대로 됐는지 끝나자마자 8가지를 직접 세어보고, 하나라도 안 맞으면 오늘 작업 전체를 없던 일로 되돌리는(롤백+FAILED) 게 정합성 검사 8종입니다.

앞 4개는 숫자 세어보기(검산): ① PK_UNIQUE — 출석부에 같은 학생(사업자번호)이 두 줄인가(중복 INSERT 사고). ② ROW_COUNT_DELTA — 아침 30,513명 + 전학생 2,174명 = 지금 32,687명이 맞는가. 한 명이라도 다르면 어디선가 새거나 끼어든 것. ③ CLASSIFICATION_TOTAL — 올해 명단 23,261명을 "전학 2,174 + 변경 4,000 + 그대로 17,087"로 나눴는데 합이 안 맞으면 누락 또는 이중 분류. ④ ALL_SOURCE_KEYS_PRESENT — 숫자만 맞으면 될까? 박영희가 빠지고 이민수가 잘못 들어와도 총원은 같다. 그래서 개수가 아니라 이름(키)을 하나하나 대조해 명단의 유효 키가 전부 마스터에 실재하는지 본다.

뒤 4개는 말이 되는지 확인(모순 스캔): ⑤ VALUE_MATCHES_POLICY — 제3자가 명단+규칙집으로 기대값을 처음부터 다시 계산해 마스터 실제 값과 대조. 시험 채점을 다른 선생님이 한 번 더 하는 것 — 병합 CASE 로직 버그가 여기서 걸린다. ⑥ NO_SILENT_NULL_OVERWRITE — "빈칸이면 기존 값을 지우지 말라"는 보존 정책인데 값이 NULL로 조용히 지워진 갱신이 있는가(정책 스위치 무시 감지). ⑦ ACTIVE_FLAG_CONSISTENT — 오늘 명단에 분명히 있는데 "전출 감"(비활성)으로 남은 행이 있는가. ⑧ NO_FUTURE_LAST_SEEN — 2023년 명단을 처리 중인데 last_seen_year가 2024인 행이 있는가(연도 로직 붕괴 감지).

압축 멘트: "①~④는 개수와 명단의 검산, ⑤~⑧은 정책·상태의 모순 스캔이고, 병합과 같은 트랜잭션이라 하나라도 실패하면 마스터가 오염되기 전에 통째로 되돌아갑니다." 검사를 나중에 따로 하면 "틀린 걸 발견은 했는데 이미 절반은 적어버린" 상태가 생기는데, 같은 묶음이면 발견 즉시 통째로 취소되므로 마스터는 항상 검사를 통과한 상태로만 존재합니다.

이 과제의 질문은 전부 "열 하나를 위에서 아래로 쭉 훑는" 것입니다 — 두 연도 키 집합이 몇 개 겹치나, 주소 열 21,087개를 비교하면 몇 개가 다른가, 신규+변경+무변경 합이 소스 행수와 맞나. 한 건 콕 집어 갱신하는 은행 창구식(행 지향: SQLite·MySQL)이 아니라 시험지 1번 문제만 전체를 쭉 채점하는 방식(컬럼 지향)이고, DuckDB가 정확히 그 용도의 "분석용 SQLite"입니다.

실제로 얻은 것 셋 — ① read_csv로 CSV를 SQL로 바로 적재, ② 변경 감지(IS DISTINCT FROM)·신규 판정(LEFT JOIN ... IS NULL)·검증(FILTER 집계)까지 한 엔진에서 처리(도구를 갈아타는 지점이 없어 어긋날 곳도 없음), ③ 결과물이 파일 하나라 검토자는 duckdb -readonly로 열기만 하면 됨. PostgreSQL은 서버 운영을 요구해 과하고(구성의 PostgreSQL은 Airflow 메타데이터 전용), pandas는 트랜잭션과 SQL 검증이 없어 검증 8종을 코드로 다시 짜야 합니다.

단점(다중 프로세스 쓰기 불가)은 알고 골랐고, 태스크 직렬화 + max_active_runs=1로 단일 writer를 구조로 보장했습니다. 3만 행 규모라 속도보다 "적재와 검증을 한 엔진에서 잇는 일관성"을 봤습니다.

DuckDB는 다중 프로세스 동시 쓰기가 제한적이라, DAG 태스크를 직렬로 연결하고 max_active_runs=1로 회차 중첩을 막았습니다. 실행 중 DB는 Docker 이름 볼륨에 두고 두 회차가 성공한 뒤에만 호스트로 내보냅니다. 쓰기 주체가 항상 한 프로세스가 되도록 구조로 보장한 셈입니다.

SQL에서 ''는 "길이 0인 문자열"이라는 엄연한 값이고, NULL은 값이 없음입니다. NULL은 = 비교가 항상 UNKNOWN이라 IS NULL·IS DISTINCT FROM이 필요하고 COUNT(col)에서 빠지는 등 동작 자체가 다릅니다. (Oracle은 ''를 NULL로 취급하지만 표준 SQL·DuckDB는 구분합니다.)

"값이 제공되지 않음"이 원천에서는 빈 문자열, 공백, 전각 공백(17건) 등 여러 표기로 오는데 그대로 두면 결측 집계와 변경 감지가 오염됩니다. 그래서 clean_text가 stg에서 표현을 NULL 하나로 표준화하고(raw는 원문 보존이라 손대지 않음), 그 NULL을 마스터에 덮을지·기존 값을 보존할지의 해석은 정책(SYNC_PRESERVE_EXISTING_ON_NULL)으로 분리했습니다. 두 정책의 실제 차이가 마스터 주소 결측 2,249 vs 156입니다.

"어차피 통일할 거면 왜 ''가 아니라 NULL이냐"고 물으면 — ''로 통일하는 순간 모든 쿼리가 이중 장부가 됩니다. "주소 없는 회사 몇 개"가 IS NULL 한 번으로 안 끝나고 = '' OR IS NULL이 되어 "없음"이 두 가지가 되고(실수로 진짜 NULL이 하나라도 생기면 3중 상태), COUNT(addr)는 ''를 세어 "채워진 회사 수"가 부풉니다. 검산 8종 중 NO_SILENT_NULL_OVERWRITE("보존 정책인데 값이 지워졌나")도 "지워짐 = NULL"이라는 단일 정의가 있어야 검사식이 성립하고, 보존 정책의 판정("소스 값이 없으면 기존 값을 지켜라")도 '없으면'을 IS NULL 한 번으로 검사할 수 있습니다. ''는 '없음'이라는 업무 의미를 문자열 값에 몰래 실어두는 셈입니다.

압축 멘트: "원천은 '없음'을 여러 표기로 주지만, 파이프라인 안에서는 '없음'이 단 하나의 모양이어야 집계·비교·정책·검산이 전부 한 가지 검사식으로 성립합니다. SQL에서 그 모양은 NULL이고, 원문 표기는 raw에 그대로 남아 있습니다."

둘은 별개 메커니즘입니다. PK는 테이블 목적에서 나왔습니다 — master는 "사업장 1건=1행"이 핵심 무결성이라 PK, ops_sync_run은 run_id 기준 UPSERT 대상이라 PK. 반면 raw/stg는 중복까지 원문대로 담아야 해서 PK를 걸 수 없고 회차 교체로 관리하며, quarantine/change_log는 append 로그라 시퀀스 순번이면 충분합니다.

롤백도 6개 전체가 아닙니다. 트랜잭션 경계는 3개(① raw 교체 ② stg+quarantine ③ 병합+이력+비활성+검증)이고, 검증 실패 시 되돌아가는 건 ③ 안의 master·change_log 변경분뿐입니다. raw·stg·quarantine은 커밋된 채 남는데 이게 의도입니다 — 실패 원인을 보려면 "무엇이 들어와 어떻게 판정됐는지"가 남아 있어야 하니까요. ops_sync_run은 롤백 대상이 아니라 FAILED로 기록됩니다.

아닙니다 — 비교 대상은 "이번 소스 vs 마스터"이고 방식은 도장 찍기입니다. 병합 때 이번 소스에 등장한 모든 유효 키(값이 안 바뀐 행 포함)에 last_seen_year=소스연도, is_active=TRUE, absent_since_year=NULL을 찍고, 그다음 _flag_absent가 "아직 활성인데 이번 도장이 없는 행"(is_active=TRUE AND last_seen_year < 소스연도)을 골라 비활성 + ABSENT 이력을 남깁니다.

마스터 기준인 이유는 3년째를 상상하면 명확합니다 — 2024 파일의 비교 상대는 2023 CSV가 아니라 누적된 마스터 전체여야 하고, is_active=TRUE 조건 덕에 이미 비활성인 행이 매년 다시 ABSENT로 기록되는 중복도 없습니다. 비활성 사업장이 재등장하면 같은 도장 단계가 되살리며 REACTIVATE('inactive'→'active') 이력을 남기는데, 회차가 2022·2023 둘뿐이라 실행에선 0건이었습니다.

태스크 실패 시 retries: 1로 1회 자동 재시도하고, 그래도 실패하면 그 회차는 FAILED로 남습니다. 다음 스케줄 run(2024)은 별개로 진행되고(depends_on_past 미설정) 2024 파일이 없으니 SKIPPED가 됩니다. 실패한 2023은 자동 재실행되지 않으며 Airflow UI에서 clear로 수동 재실행합니다.

재실행은 안전합니다 — 같은 run_id로 RUNNING부터 UPSERT, raw/stg는 연도 교체라 멱등, 마스터는 롤백돼 있어 정상 재병합. 단 마스터가 이미 2023까지 반영된 상태에서 2022를 다시 돌리는 건 연도 역행 가드가 의도적으로 거부하므로, 전체 재현은 ./up.sh(볼륨 초기화 포함)로 합니다.