파트너 정산 데이터 불일치 원인을 SQL 감사 보고서로 규명

파트너 88, 90번의 정산 데이터에서 숫자가 안 맞는다는 신호가 들어왔을 때, 처음엔 로그 몇 줄 보고 넘길 수 있을 거라 생각했다. 근데 파고들수록 단순한 문제가 아니었음. 로그는 이벤트 흐름을 보여주지만, "그래서 지금 DB에 뭐가 얼마나 있어야 하는데 실제로 얼마가 있냐"를 한 번에 보여주진 않는다. 그게 답답해서 SQL로 직접 집계하고 결과를 HTML 보고서로 정리했음.

보고서 타이틀은 20260421 1800 partner 88 90 settlement audit. 이름에 날짜와 시각, 파트너 식별자를 박아두는 건 나중에 같은 패턴이 반복됐을 때 히스토리를 추적하기 위해서다.

왜 SQL 감사 보고서가 필요한가

정산 로직이 복잡해질수록 코드 리뷰만으로는 오류를 잡기 어렵다. 정산 파이프라인 중간 어딘가에서 집계 단위나 기간 범위가 조금만 어긋나도 최종 합산이 틀려지는데, 그 원인이 코드 어느 줄인지 특정하기 전에 데이터 레벨에서 먼저 봐야 한다.

접근 방법은 단순하다. 내가 기대하는 숫자를 먼저 정의하고, DB에서 실제 집계된 숫자를 뽑아서 나란히 놓는다. 차이가 있는 행이 버그 위치를 가리킨다. 이 방식이 효과적인 이유는 "어디서 틀렸는가"를 감으로 좁히는 게 아니라 데이터가 직접 가리켜주기 때문임.

-- 파트너별 정산 집계 vs 원천 트랜잭션 합산 비교
SELECT
    p.partner_id,
    p.settled_amount               AS settled,
    COALESCE(t.tx_sum, 0)          AS tx_total,
    p.settled_amount
      - COALESCE(t.tx_sum, 0)      AS diff
FROM partner_settlements p
LEFT JOIN (
    SELECT partner_id, SUM(amount) AS tx_sum
    FROM transactions
    WHERE status = 'confirmed'
      AND settled_at BETWEEN '2026-04-21 00:00:00'
                         AND '2026-04-21 18:00:00'
    GROUP BY partner_id
) t ON p.partner_id = t.partner_id
WHERE p.partner_id IN (88, 90)
  AND ABS(p.settled_amount - COALESCE(t.tx_sum, 0)) > 0
ORDER BY ABS(diff) DESC;

이런 쿼리를 여러 각도로 변형하면서 불일치 구간을 좁혀간다. 특정 시각 이후부터 diff가 생기면 그 시각에 배포가 있었거나 배치 잡이 돌았거나 둘 중 하나다. 쿼리 성능 쪽은 이번에 인덱스를 타고 있어서 허용 범위 내였지만, 집계 대상 기간이 길어질수록 풀스캔 가능성이 있으니 주기적으로 확인해야 함.

보고서 구성과 실제 발견 사항

HTML로 뽑는 이유는 공유가 쉽고, 표와 차트를 같은 파일 안에 넣을 수 있어서다. 매번 슬랙에 쿼리 결과 스크린샷을 올리는 것보다, 파일 하나 던져주는 게 훨씬 낫다. 브라우저에서 바로 열리니까 환경 문제도 없고, 숫자가 많은 케이스는 차트로 이상치를 시각화해서 한눈에 보이게 할 수도 있음.

이번 감사에서 확인된 항목들:

정합성 불일치에서 가장 의심스러웠던 건 경계 시각 처리였다. settled_at BETWEEN 조건을 쓸 때 시작과 끝 포인트를 어떻게 잡느냐에 따라 같은 트랜잭션이 두 정산 구간에 걸치거나 아예 빠질 수 있다. 이런 종류의 버그는 대부분의 케이스에서 숫자가 맞아 보이다가 특정 타이밍에만 터진다. 재현 케이스를 만들기 까다로운 이유가 여기 있음. BETWEEN 대신 >= start AND < end 패턴을 쓰면 닫힌 구간 문제는 피할 수 있고, 구간 정의를 코드 레벨에서 상수로 뽑아두면 로직이 흩어지지 않는다.

처리 누락 건은 배치 잡 실행 시각과 트랜잭션 confirmed 타임스탬프 사이의 타이밍 레이스로 보였다. 배치가 집계를 시작한 뒤 들어온 트랜잭션이 다음 배치에서도 빠지는 경우. 확인하려면 배치 로그 타임스탬프와 트랜잭션 타임스탬프를 교차 대조해야 하고, 픽스 방향은 배치 커밋 시각을 기준으로 처리 대상을 잠그거나 재처리 큐를 두는 방식 중에 택해야 한다. 잠그는 방식이 단순하지만 배치 실행 시간이 늘면 락 범위가 커지는 트레이드오프가 있음.

보고서-수정-재검증 사이클

이 방식의 실용적인 이점은 수정 이후에 같은 보고서를 다시 돌리면 개선 여부가 바로 보인다는 점이다. diff 컬럼이 0이 되면 픽스가 됐다는 거고, 0이 아니면 아직 남아 있다는 것. 코드 수정만 하고 "됐겠지" 하고 넘어가는 것과, 보고서를 다시 돌려서 0을 눈으로 확인하는 것 사이에는 꽤 큰 차이가 있다.

보고서 자체를 PR 근거 문서로 활용하는 것도 효과적이었다. "어느 쿼리의 어떤 조건에서 불일치가 발생했다"를 PR 설명에 첨부하면 리뷰어 입장에서 맥락을 잡기 쉽고, 나중에 같은 파트너에서 다시 이슈가 생겼을 때도 과거 보고서가 첫 번째 단서가 된다. 히스토리 없이 코드만 남기면 두 번째 이슈 때도 처음부터 다시 파야 함.

반복적으로 느끼는 건, 정산처럼 금액이 오가는 도메인에서는 "코드가 맞아 보인다"는 판단보다 "DB에서 직접 세어봤더니 맞더라"는 확인이 훨씬 신뢰할 만하다는 거다. 코드 리뷰와 SQL 감사를 병행하는 게 귀찮아 보여도, 정산 금액에 버그가 한 번 반영되고 나면 수습이 훨씬 더 귀찮다.