[Redash/SQL] Redash 기반 포인트 정합성 검증 및 모니터링 구축

실제 세션 수익과 포인트 적립 데이터를 PVI 기준으로 역산 검증하여 QA 실시간 데이터 정합성 확보

---

⚠️ 문제 정의

실제 세션 수익과 포인트 적립 데이터가 PVI별로 일치하지 않은 사례가 있었음.

기존에는 명확한 산출이 나올 수 없는 구조라 모니터링이 어려워 수동검증을 하였고, 이로인해 QA 업무 효율도 낮게 나타나며 금전적 오산출 리스크가 종종 발생했음

---

🛠️ 수행 내용

• PVI 기준 그룹화 후 세션 수익과 포인트 적립 데이터를 역산(Cross-Check)
• Uniq 한 user 구분 단위 및 그룹 단위 총액 비교로 불일치 검출
• ZERO_ID_CT, TOTAL_POINT_DIFF 등 지표를 통해 정합성 확인
• 기간 조건(start_date ~ end_date) 적용, SQL 자동화로 반복 검증 가능
• Redash 기반 모니터링 및 Slack 알림 연동
  • 매일 10시 정합성 검증 결과 자동 알림
  • 알림 클릭 시 대시보드로 즉시 이동
  • XX_ID 단위 정합성 결과를 기준으로 Severity(Minor/Major/Critical) 분류 및 알럿 트리거

---

📊 검증/데이터 요약

• TOTAL_XX_ID_COUNT: PVI 그룹 내 XX_ID 전체 수
• ZERO_XXID_CT: 세션 수익과 포인트 적립 완전 일치 XX_ID 수
• TOTAL_V_RAKE: 세션 수익 총합
• TOTAL_POINTS: 포인트 총합 (100분의 1 스케일)
• TOTAL_POINT_DIFF: 총액 차이, 불일치 확인

WITH PVI_CATEGORIZED AS (
    SELECT
        POOL_GROUP_ID, GP_ID, PVI,
        CASE
            WHEN PVI >= 0.1 AND PVI < 0.5 THEN '0.1 ~ 0.49'
            WHEN PVI >= 0.5 AND PVI < 0.8 THEN '0.5 ~ 0.79'
            WHEN PVI >= 0.8 AND PVI < 1.0 THEN '0.8 ~ 0.99'
        END AS PVI_Category
    FROM DW_POKER_DESIGN."PUBLIC".PVI
    WHERE PVI BETWEEN 0.1 AND 0.99
),
SESSION_RAKE AS (
    SELECT 
        GP_ID, 
        TO_DATE(TIMESTAMPADD(hour, -15, USER_SESSION_STARTED_AT)) AS TARGET_DATE,
        SUM(V_RAKE_OR_FEE) AS TOTAL_V_RAKE
    FROM (
        SELECT GP_ID, V_RAKE_OR_FEE, USER_SESSION_STARTED_AT, USER_SESSION_FINISHED_AT FROM DW_ORIGIN_GLOBAL.GLOBAL_GP_DB_STATS.STATISTICS_GAME_POKER_PER_SESSION WHERE IS_PLAY_MONEY = FALSE
        UNION ALL
        SELECT GP_ID, V_RAKE_OR_FEE, USER_SESSION_STARTED_AT, USER_SESSION_FINISHED_AT FROM DW_ORIGIN_WSOP.WSOP_GP_DB_STATS.STATISTICS_GAME_POKER_PER_SESSION WHERE IS_PLAY_MONEY = FALSE
    )
    WHERE USER_SESSION_STARTED_AT >= '2025-07-16 15:00:00'
      AND USER_SESSION_FINISHED_AT < '2025-12-01 15:00:00'
    GROUP BY GP_ID, TO_DATE(TIMESTAMPADD(hour, -15, USER_SESSION_STARTED_AT))
),
SOURCE_SUM AS (
    SELECT 
        B.GP_ID, 
        TO_DATE(TIMESTAMPADD(hour, -15, A.SOURCE_CREATED_AT)) AS TARGET_DATE,
        SUM(A.VALUE) AS TOTAL_SOURCE_VALUE
    FROM DW_WAREHOUSE."PUBLIC".GP_POINT_HISTORY A
    INNER JOIN DW_WAREHOUSE."PUBLIC".GP_REWARD_ATTENDEE B ON A.ATTENDEE_ID = B.ATTENDEE_ID
    WHERE A.SOURCE_TYPE != 'CASINO_SESSION'
      AND A.SOURCE_CREATED_AT >= '2025-07-16 15:00:00'
      AND A.SOURCE_CREATED_AT < '2025-12-01 15:00:00'
    GROUP BY B.GP_ID, TO_DATE(TIMESTAMPADD(hour, -15, A.SOURCE_CREATED_AT))
)
SELECT
    sr.TARGET_DATE,
    p.POOL_GROUP_ID, 
    p.PVI_Category,
    COUNT(DISTINCT p.GP_ID) AS TOTAL_GP_ID_COUNT,
    COUNT(DISTINCT CASE WHEN ROUND(ABS(sr.TOTAL_V_RAKE - (CAST(ss.TOTAL_SOURCE_VALUE AS DECIMAL(18, 2)) / 100)), 2) = 0 THEN p.GP_ID END) AS ZERO_GPID_CT,
    SUM(sr.TOTAL_V_RAKE) AS TOTAL_V_RAKE,
    SUM(CAST(ss.TOTAL_SOURCE_VALUE AS DECIMAL(18, 2)) / 100) AS TOTAL_POINTS,
    ROUND(ABS(SUM(sr.TOTAL_V_RAKE) - SUM(CAST(ss.TOTAL_SOURCE_VALUE AS DECIMAL(18, 2)) / 100)), 2) AS TOTAL_POINT_DIFF
FROM PVI_CATEGORIZED p
INNER JOIN SESSION_RAKE sr ON p.GP_ID = sr.GP_ID
INNER JOIN SOURCE_SUM ss ON p.GP_ID = ss.GP_ID AND sr.TARGET_DATE = ss.TARGET_DATE
WHERE sr.TOTAL_V_RAKE IS NOT NULL AND ss.TOTAL_SOURCE_VALUE IS NOT NULL AND sr.TARGET_DATE >= '{{ start_date }}' AND sr.TARGET_DATE < '{{ end_date }}'
GROUP BY sr.TARGET_DATE, p.POOL_GROUP_ID, p.PVI_Category
ORDER BY sr.TARGET_DATE, p.POOL_GROUP_ID, p.PVI_Category;

---

🎯 결과

• PVI 그룹별 세션 수익과 포인트 적립 데이터 정합성 확보
• 불일치 발생 XX_ID 실시간 Redash를 통해 확인 가능
• 수동 검증 필요 제거, QA 효율 및 데이터 신뢰성 강화
• 데이터 영향 범위 기반 Severity 알럿으로 우선순위 대응 체계 구축

---

🚀 역할 및 기여

• QA 자동화 설계: 역산 기반 Cross-Check 체계 구현
• 데이터 정합성 모니터링: ZERO_XXID_CT, TOTAL_POINT_DIFF 지표 생성
• 실시간 검증 체계 구축: 수일 걸리던 검증을 즉시 확인 가능하도록 개선