Legacy Data Migration and Consistency Verification

한국어 원문으로 표시합니다.

목표

지저분한 레거시 CSV 를 스테이징에 적재하고, 프로파일링 → 정제 → 중복 제거 → 코드 매핑 → 정합성 검증 → 제외 건 관리 → 확인서 작성까지 데이터 이관 한 사이클을 완주합니다.

왜 중요한가

"이행은 잘 됐습니다"라는 구두 확인은 아무것도 증명하지 않습니다. 건수 비교와 샘플 검증을 문서화해야 하고, 그중에서도 건수와 합계만 보면 한 건이 빠지고 다른 한 건이 두 배가 된 상황을 놓칩니다. 그리고 제외된 건을 목록으로 남기지 않으면, 오픈 후 "데이터가 없는데요"라는 문의가 왔을 때 처음부터 조사해야 합니다. 남겨 두면 "그 13건은 코드 미매핑으로 제외됐고 D+3 재이관 예정입니다"라고 30초 만에 답할 수 있습니다. 그 차이가 안정화 기간의 삶의 질을 바꿉니다.

단계

원천: /opt/lab/fixtures/dbo/legacy/CUST_LEGACY.csv (1,200행) 코드 매핑표: /opt/lab/fixtures/dbo/legacy/grade_map.csv
/root/db/etl.db 를 만들고 원천 CSV 를 가공 없이 STG_CUST 테이블에 적재합니다. 모든 컬럼은 TEXT 이고 행 수는 1200 이어야 합니다.
/root/db/profile.csv 를 만듭니다. 첫 줄은 column,nulls,spaces,distinct. STG_CUST 의 모든 컬럼에 대해
- nulls: 값이 비었거나 앞뒤 공백을 없앤 값이 문자열 NULL/null 인 건수
- spaces: 앞뒤에 공백이 붙어 있는 건수
- distinct: 서로 다른 값의 개수 (원본 문자열 기준) 를 테이블 컬럼 순서대로 적습니다.
TGT_CUST 테이블을 만들고 정제 규칙을 적용해 적재합니다. 규칙은
- 모든 문자열 앞뒤 공백 제거
- 문자열 NULL/null → 진짜 NULL
- 날짜(REG_DATE)를 YYYYMMDD 8자리로 통일 (원천에는 YYYYMMDD, YYYY-MM-DD, YY/MM/DD 세 형식이 섞여 있습니다. YY 는 20YY 로 봅니다.)
- 금액(TOT_AMT)의 콤마 제거 후 정수로 적재 후 TGT_CUST 에 REG_DATE 가 8자리가 아닌 행이 0건이어야 합니다.
같은 CUST_ID 가 여러 건이면 UPD_DT 가 가장 큰 행 하나만 남깁니다. UPD_DT 가 같으면 원천 순서상 뒤의 행을 남깁니다. 제거한 건수를 /root/db/dedup.txt 에 removed=<건수> 로 저장합니다.
grade_map.csv 로 GRADE 를 신규 코드로 변환해 GRADE_CD 에 넣습니다. 매핑표에 없는 값은 GRADE_CD 를 99 로 두고, 그 행의 CUST_ID 와 원래 값을 /root/db/unmapped.csv 에 저장합니다. 첫 줄은 cust_id,legacy_grade, cust_id 오름차순입니다.
/root/db/recon.csv 를 만듭니다. 첫 줄은 item,source,target,diff,result. 아래 세 행을 채웁니다.
- count : 원천 행 수 vs 대상 행 수
- amount: 원천 TOT_AMT 합계 vs 대상 합계 (원천은 콤마 제거 후 정수로 환산해 비교합니다)
- distinct_id: 원천의 고유 CUST_ID 수 vs 대상 행 수 result 는 일치하면 OK, 다르면 NG 입니다.
이관되지 않은 건(중복 제거로 빠진 건)의 CUST_ID 를 /root/db/excluded.csv 에 저장합니다. 첫 줄은 cust_id,reason, reason 은 duplicate 입니다.
/root/db/etl-signoff.md 를 작성합니다. ## 이관 대상, ## 제외 사유, ## 검증 결과, ## 재이관 대상, ## 확인 다섯 개의 h2 제목이 있어야 하고, 아래 네 줄이 정확히 들어가야 합니다.
```
source_rows=<원천 행 수>
target_rows=<대상 행 수>
excluded=<제외 건수>
unmapped=<코드 미매핑 건수>
```

참고

CSV 적재: .mode csv / .import --skip 1 <파일> <테이블>
문자열 정리: trim(), replace(), substr()
중복 제거: ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 또는 GROUP BY + MAX() 조합
흔한 실수 1: 스테이징에 적재하면서 동시에 정제해 원본과 대조할 수 없게 되는 것.
흔한 실수 2: YY/MM/DD 를 19YY 로 해석하는 것. 이 데이터는 2000년대입니다.
흔한 실수 3: 제외 건을 세기만 하고 목록으로 남기지 않는 것.

원천 데이터 적재

/root/db/etl.db 를 만들고 원천 CSV 를 가공 없이 STG_CUST 테이블에 적재합니다. 모든 컬럼은 TEXT 이고 행 수는 1200 이어야 합니다.

원천은 있는 그대로 스테이징에 넣습니다. 여기서 정제하면 나중에 원본과 대조할 수 없게 됩니다.

데이터 프로파일링

/root/db/profile.csv 를 만듭니다. 첫 줄은 column,nulls,spaces,distinct. STG_CUST 의 모든 컬럼에 대해

nulls: 값이 비었거나 앞뒤 공백을 없앤 값이 문자열 NULL/null 인 건수
spaces: 앞뒤에 공백이 붙어 있는 건수
distinct: 서로 다른 값의 개수 (원본 문자열 기준) 를 테이블 컬럼 순서대로 적습니다.

정제 규칙은 프로파일링 결과에서 나옵니다. 컬럼별로 NULL 이 몇 건인지, 값의 종류가 몇 가지인지부터 봅니다.

정제 규칙 적용

TGT_CUST 테이블을 만들고 정제 규칙을 적용해 적재합니다. 규칙은

모든 문자열 앞뒤 공백 제거
문자열 NULL/null → 진짜 NULL
날짜(REG_DATE)를 YYYYMMDD 8자리로 통일 (원천에는 YYYYMMDD, YYYY-MM-DD, YY/MM/DD 세 형식이 섞여 있습니다. YY 는 20YY 로 봅니다.)
금액(TOT_AMT)의 콤마 제거 후 정수로 적재 후 TGT_CUST 에 REG_DATE 가 8자리가 아닌 행이 0건이어야 합니다.

날짜 형식이 여러 가지 섞여 있습니다. 각 형식을 어떻게 판별하고 변환할지 먼저 정한 뒤 코드를 쓰세요.

중복 제거

같은 CUST_ID 가 여러 건이면 UPD_DT 가 가장 큰 행 하나만 남깁니다. UPD_DT 가 같으면 원천 순서상 뒤의 행을 남깁니다. 제거한 건수를 /root/db/dedup.txt 에 removed=<건수> 로 저장합니다.

무엇을 남길지는 기술 판단이 아니라 업무 판단입니다. 규칙이 정해졌으면 그 규칙대로 정확히 구현하세요.

코드 매핑

grade_map.csv 로 GRADE 를 신규 코드로 변환해 GRADE_CD 에 넣습니다. 매핑표에 없는 값은 GRADE_CD 를 99 로 두고, 그 행의 CUST_ID 와 원래 값을 /root/db/unmapped.csv 에 저장합니다. 첫 줄은 cust_id,legacy_grade, cust_id 오름차순입니다.

매핑표에 없는 값이 나오면 어떻게 할지가 정의돼 있어야 합니다. 그 대상은 반드시 목록으로 남기세요.

정합성 검증

/root/db/recon.csv 를 만듭니다. 첫 줄은 item,source,target,diff,result. 아래 세 행을 채웁니다.

count : 원천 행 수 vs 대상 행 수
amount: 원천 TOT_AMT 합계 vs 대상 합계 (원천은 콤마 제거 후 정수로 환산해 비교합니다)
distinct_id: 원천의 고유 CUST_ID 수 vs 대상 행 수 result 는 일치하면 OK, 다르면 NG 입니다.

건수와 합계만으로는 한 건 누락과 한 건 중복이 상쇄되는 경우를 못 잡습니다. 지표를 여러 개 두세요.

제외 건 목록

이관되지 않은 건(중복 제거로 빠진 건)의 CUST_ID 를 /root/db/excluded.csv 에 저장합니다. 첫 줄은 cust_id,reason, reason 은 duplicate 입니다.

'1,200건 중 1,187건 이관'까지가 결과이고, 나머지 13건이 무엇인지 답할 수 있어야 합니다.

이관 결과 확인서

/root/db/etl-signoff.md 를 작성합니다. ## 이관 대상, ## 제외 사유, ## 검증 결과, ## 재이관 대상, ## 확인 다섯 개의 h2 제목이 있어야 하고, 아래 네 줄이 정확히 들어가야 합니다.

source_rows=<원천 행 수>
target_rows=<대상 행 수>
excluded=<제외 건수>
unmapped=<코드 미매핑 건수>

오픈 후 '데이터가 없는데요' 문의에 즉시 답하기 위한 문서입니다. 숫자와 사유가 함께 있어야 합니다.