이관 후 월말 집계 불일치
요약
Oracle → 레이크하우스 전환 후 첫 월말, 보험료 수입 집계가 레거시 대비 0.3% 적다. 건수는 일치한다. 원인 가설과 진단 절차, 대응, 재발 방지를 제시한다.
상황 재진술. 전환 후 첫 월말 마감에서 금액 집계만 0.3% 부족하고 행 수는 같다. Reconciliation 6계층(행 수 → 열 집계 → 행 해시 합 → 샘플 행 비교 → 통계 분포 → 리포트) 기준으로 1계층은 통과, 2계층에서 실패한 상태다. 따라서 진단은 3·4계층으로 내려가 "어느 행의 어느 값이 다른가"를 찾는 문제로 환원된다.
1. 문제 분리 — 단서로 가설 좁히기
결론: "건수 일치·금액 부족"은 행 누락이 아니라 (a) 행별 금액 값의 차이, (b) 행의 귀속(기간·상태·필터)은 다르지만 수가 우연히 같은 경우, © 집계 로직(취소 상계·반올림 위치)의 차이 세 범주로 좁혀진다. 두 번째 분기점은 0.3%의 분포다. 전 행에 미세하게 퍼졌으면 정밀도·반올림(a), 특정 일자·시간대·상태 코드에 몰렸으면 귀속·CDC(b), 행 단위는 일치하는데 합계만 다르면 로직©이다. 자릿수(규모) 추정도 가설을 거른다. 월 수입을 30일로 나누면 하루가 약 3.3%이므로 0.3%는 “몇 시간 또는 한 묶음의 거래” 규모다. 반올림만으로 0.3%가 나려면 평균 보험료 수십만 원에서 행당 수백 원씩 깎여야 하므로, 월말 경계·마감 직전 정정·특정 상태 코드가 더 유력하다.
2. 가설 트리
| # | 가설 | 예상 징후 | 검증 방법 | 우선순위 |
|---|---|---|---|---|
| H1 | 타임존·기준 시각: KST 암묵 저장 → UTC 해석으로 월말 마지막 날 15:00(UTC)=24:00(KST) 이후 거래가 다음 달로 귀속 | 차이가 월말 마지막 날 특정 시간대 거래 합과 일치, 해당 행이 다음 달 파티션에 존재 | 월말 ±1일을 시간대별로 분해, 양쪽 월 경계 필터의 타임존 확인 | 높음 |
| H2 | CDC 지연·순서 뒤바뀜(late-arriving): 마감 집계 시점에 마지막 정정 UPDATE가 미반영이거나 out-of-order로 구버전 값이 잔류 | 차이가 월말 마지막 1~2일의 UPDATE 행에 집중, src_lsn이 마감 시각 이후 |
집계 실행 시각 vs CDC 워터마크, 불일치 행의 LSN·반영 시각 비교 | 높음 |
| H3 | 취소·정정 거래 처리: 레거시는 취소를 음수 행으로 상계, 신규는 소프트 삭제 플래그로 제외(또는 반대) | 차이가 취소/정정 상태 코드 행 합과 일치 | 상태 코드별 금액 합 분해 | 중 |
| H4 | 상태 코드 매핑 누락: 신규 상태값(예: 납입유예)이 매핑 테이블에 없어 집계 필터에서 제외 | 특정 상태 코드 금액 합 = 차이 | 매핑 테이블 vs DISTINCT 상태값 대조, 미매핑 행 수 | 중 |
| H5 | 소수점 정밀도: NUMBER(정밀도 무제한) → DOUBLE 부동소수 또는 DECIMAL 자릿수 절삭, 반올림 방식(HALF_UP vs 은행가) 차이 | 차이가 전 행에 미세 분산, 행당 차이 < 1원 | 행 단위 금액 차이 히스토그램, 타깃 스키마 타입 확인 | 중 |
| H6 | SCD2 유효기간 경계: 요율·상품 차원 조인에서 valid_to 포함/배제(< vs ≤) 차이로 월말 변경 계약이 조인 실패 → 금액 NULL/0 |
차원이 월말에 바뀐 계약에 집중, 금액 NULL 행 존재 | anti-join으로 조인 누락 행 추출 | 중 |
| H7 | NULL/빈 문자열: Oracle '' = NULL vs Spark 구분 → IS NULL 필터 결과 또는 할인 코드 처리 차이 |
특정 컬럼이 빈 문자열인 행에 차이 집중 | 빈 문자열 건수, 해당 행 금액 합 | 낮음~중 |
| H8 | 변환 코드 로직: PL/SQL → dbt/Spark 변환 시 ROUND 위치(행별 vs 합계 후), NVL 기본값, 통화 환산 시점(거래일 vs 월말 환율) 차이 | 행 단위 값은 일치하는데 집계만 다름, 외화 계약에 집중 | 동등성 테스트, 외화 행 분해 | 중 |
3. 진단 절차
| 순서 | 작업 | 소요 | 산출물 |
|---|---|---|---|
| 1 | 차원별 분해: 일자(월말 ±2일은 시간대까지)·상품·채널·상태 코드·통화 축으로 양쪽 집계 차이를 계산해 집중 구간 식별 | 1~2시간 | 분해표 — 균일 분산이면 H5/H8, 월말 집중이면 H1/H2, 상태 집중이면 H3/H4 |
| 2 | 행 해시 비교(3계층): 집중 구간에 대해 정규화 문자열(금액 FM9999999990.00·날짜 포맷·NULL 표기 통일) 해시를 양쪽에서 같은 알고리즘(MD5)으로 계산, 불일치 행 추출 |
2~3시간 | 불일치 행 목록 |
| 3 | 샘플 행 전수 비교(4계층): 불일치 행의 레거시 vs 신규 열별 값 대조 — 9시간 차이면 H1, LSN·반영 시각이면 H2, 소수점 자리면 H5, NULL이면 H6/H7 | 1~2시간 | 원인 확정 |
| 4 | 재현·수정 검증: 원인 수정 후 재집계해 차이 0 확인, 다른 월(이전 월 백필)에도 적용 | 반나절 | 수정 전후 Reconciliation 결과 |
정규화 없이 해시를 비교하면 부동소수·타임존·문자셋 차이로 거짓 불일치가 폭증하므로 2단계의 정규화 규칙이 결과 신뢰를 좌우한다.
4. 대응
- 즉시: Parallel Run 기간이므로 규제 보고·마감 수치는 레거시 값으로 확정하고, 신규 플랫폼 수치는 참조용으로 표기한다. 차이 금액·원인 후보·조치 계획을 마감 책임자에게 보고한다.
- 단기(수정): 금액 컬럼은 DECIMAL(38, x) 고정과 컬럼별 정밀도 매핑 표를 적용한다. 타임스탬프는 TIMESTAMP WITH TIME ZONE 또는 UTC + 명시 변환으로 통일하고 "월 경계는 KST 00:00"을 명문화한다. 월말 집계는 CDC 워터마크가 마감 시각을 지난 뒤에만 실행하는 센서를 두고 지연 도착분은 T+1 재집계로 흡수한다. 상태 코드 매핑 테이블을 원천 DISTINCT 값과 대조해 미매핑 값은 격리한다. SCD2 조인 경계(
valid_from ≤ t < valid_to)를 통일한다. - 구조: 변환 코드 동등성 테스트(동일 입력 → 동일 출력)를 자동화하고, 멱등 MERGE의 버전 조건과 취소 상계 규칙을 데이터 컨트랙트에 명시한다.
5. 재발 방지
- 6계층 Reconciliation을 월말 마감 전 게이트로 자동 실행한다. 허용 오차는 금액 합 차이 0·행 수 차이 0·분포 p-value > 0.05로 두고, 불일치 파티션을 대시보드·알림으로 올린다.
- 데이터 컨트랙트에 정밀도(DECIMAL 자릿수)·시간 기준(KST·월 경계 정의)·상태 코드 도메인·취소 처리 규칙을 포함한다.
- CDC 지연 SLO(예: 15분)와 집계 시작 조건(워터마크)을 연동해 "덜 도착한 상태에서 집계"를 구조적으로 막는다.
- 컷오버 승인 조건에 Reconciliation 2주 연속 일치를 포함하고, 월말 마감은 최소 1회 Parallel Run으로 검증한 뒤 전환한다.
결론. "건수 일치·금액 0.3% 부족"은 행은 다 왔지만 값 또는 귀속이 다르다는 신호다. 0.3%는 하루치(약 3.3%)보다 작고 반올림으로 설명하기엔 크므로 1순위는 월말 경계 귀속(H1)과 마감 직전 정정분의 CDC 미반영(H2), 2순위는 특정 상태 코드 묶음(H3/H4), 3순위가 정밀도(H5)다. 1단계 차원 분해에서 1~2시간 안에 갈리고, 집중 구간의 행 해시·샘플 비교로 당일 확정된다.
