[QA/자동화] 데이터 정합성 자동 검증 대시보드 구축

자동화 검증과 이중 교차 검증(Cross-Check) 체계로 QA 실시간 데이터 정합성 확보

 

---

 

⚠️ 문제 정의

• BS(Business Service) 데이터에서 Bonus Revenue가 일부 누락되어 있어 정확한 Service Fee 검증이 필요한 상황
• 누락 데이터는 타팀에서 관리하고 있어 단순 조회만으로는 정합성 확인 불가

---

 

🛠️ 수행 내용

• Bonus Revenue 역산: 시스템 산정 Fee와 Rate를 활용해 누락 보너스 값을 계산
• 정합성 검증:
  • 내부 데이터 ↔ 외부 Bonus Revenue 데이터 비교
• Redash 대시보드 구축:
  • PASS/FAIL 지표 실시간 시각화
  • Bonus Revenue 발생 건 집중 모니터링
  • 세션별 상세 추적 필터 제공
• Slack 자동 알림 연동:
  • 매일 10시, Bonus Revenue 발생 건 요약 알림 발송
  • 알림 클릭 시 해당 Redash 대시보드로 즉시 이동
  • 정합성 FAIL 또는 이상 데이터 발생 시 실시간 알럿 트리거

---

📊 쿼리/데이터 요약

• 핵심 지표: 전체 세션 수, Bonus Revenue 발생 빈도, PASS/FAIL 건수
• 계산 방식: Expected_Bonus_Revenue = (Service_Fee / Markup_Rate) - Revenue
• 최신 데이터 추출: OUTER APPLY 활용, 조건별 TOP1 최신 데이터 조회
• 성능 최적화: 불필요 컬럼 제거, 기간/필터별 그룹화 적용

SELECT 
    SUB.*,
    CASE 
        WHEN ROUND(SERVICE_FEE, 2) = ROUND(CALC_FEE_WITH_BONUSREVENUE, 2) 
             AND ROUND(ESTIMATED_SERVICE_FEE, 2) = ROUND(CALC_ESTIMATED_FEE_WITH_BONUSREVENUE, 2) 
             AND ABS(APPLIED_RATE - MARKUP_RATE) <= 0.000001
             THEN 'PASS'
        ELSE 'FAIL'
    END AS RESULTS,
    
    COUNT(
        CASE 
            WHEN ROUND(SERVICE_FEE, 2) <> ROUND(CALC_FEE_WITH_BONUSREVENUE, 2)
              OR ROUND(ESTIMATED_SERVICE_FEE, 2) <> ROUND(CALC_ESTIMATED_FEE_WITH_BONUSREVENUE, 2)
              OR ABS(APPLIED_RATE - MARKUP_RATE) > 0.000001
            THEN 1
        END
    ) OVER() AS TOTAL_FAIL_CNT,

    COUNT(CASE 
        WHEN BONUS_REVENUE_CHECK = '+ Bonus Revenue' THEN 1
        ELSE NULL
    END) OVER() AS TOTAL_BONUS_CNT
    
FROM (
    SELECT 
        TO_VARCHAR(S.CREATED_AT, 'YYYY-MM-DD HH24:MI') AS DATE_TIME,
        S.SEQ,
        S.USER_SESSION_ID,
        S.BRAND_ID,
        S.SITE_ID,
        S.BUSINESS_SERVICE,
        M.SERVICE_CATEGORY,
        S.SERVICE_TYPE,
        CAST(M.RATE AS NUMBER(18, 8)) AS MARKUP_RATE,

        CASE
            WHEN S.REVENUE = 0 AND S.ESTIMATED_REVENUE = 0 THEN
                CAST(M.RATE AS NUMBER(18, 8))
            WHEN ABS(S.SERVICE_FEE) > 0 THEN
		        CAST(
		        	ROUND(
		        		CAST(S.SERVICE_FEE AS NUMBER(18, 10))
				        / NULLIF(CAST(S.REVENUE + (CASE
				                WHEN M.RATE IS NULL OR M.RATE = 0 THEN 0
				                ELSE (S.SERVICE_FEE / M.RATE) - S.REVENUE END)
				          AS NUMBER(18, 10)), 0), 8) AS NUMBER(18, 8))
		
		    ELSE
		    	ROUND(
			        CAST(S.ESTIMATED_SERVICE_FEE AS NUMBER(18, 10))
			        / NULLIF(CAST(S.ESTIMATED_REVENUE + (CASE
			                WHEN M.RATE IS NULL OR M.RATE = 0 THEN 0
			                ELSE (S.ESTIMATED_SERVICE_FEE / M.RATE) - S.ESTIMATED_REVENUE END)
			          AS NUMBER(18, 10)), 0), 8)
        END AS APPLIED_RATE,
        
        CASE 
            WHEN M.RATE IS NULL OR M.RATE = 0 THEN 'Error'
            WHEN ABS(S.SERVICE_FEE - (S.REVENUE * M.RATE)) > 0.015 THEN '+ Bonus Revenue'
            ELSE '-' 
        END AS BONUS_REVENUE_CHECK,

        CASE 
            WHEN M.RATE IS NULL OR M.RATE = 0 THEN 0 
            WHEN ABS(S.SERVICE_FEE) > 0 THEN (S.SERVICE_FEE / M.RATE) - S.REVENUE
            ELSE (S.ESTIMATED_SERVICE_FEE / M.RATE) - S.ESTIMATED_REVENUE 
        END AS EXPECTED_BONUS_REVENUE,

        S.REVENUE, 
        S.SERVICE_FEE, 
        
        ROUND(
		  (
		    S.REVENUE +
		    (CASE
		       WHEN APPLIED_RATE IS NULL OR APPLIED_RATE = 0 THEN 0
		       ELSE (S.SERVICE_FEE / APPLIED_RATE) - S.REVENUE
		     END)
		  ) * APPLIED_RATE
		, 8) AS CALC_FEE_WITH_BONUSREVENUE,

        S.ESTIMATED_REVENUE,
        S.ESTIMATED_SERVICE_FEE,
        
        ROUND(
		  (
		    S.ESTIMATED_REVENUE +
		    (CASE
		       WHEN APPLIED_RATE IS NULL OR APPLIED_RATE = 0 THEN 0
		       ELSE (S.ESTIMATED_SERVICE_FEE / APPLIED_RATE) - S.ESTIMATED_REVENUE
		     END)
		  ) * APPLIED_RATE
		, 8) AS CALC_ESTIMATED_FEE_WITH_BONUSREVENUE

    FROM DW_ANALYTICS.AGGREGATED.BS_STATISTICS_SERVICE_PER_SESSION S
    INNER JOIN (
        SELECT DISTINCT 
            TRIM(UPPER(BRAND_ID)) AS BRAND_ID,
            TRIM(UPPER(SITE_ID)) AS SITE_ID,
            TRIM(UPPER(BUSINESS_SERVICE)) AS BUSINESS_SERVICE, 
            SERVICE_CATEGORY,
            RATE
        FROM DW_ANALYTICS.AGGREGATED.BS_SERVICE_MARKUP
        WHERE (BRAND_ID <> 'SYSTEM_EXCLUDE' OR BRAND_ID IS NULL) 
    ) M 
        ON  TRIM(UPPER(S.BRAND_ID)) = M.BRAND_ID
        AND TRIM(UPPER(S.SITE_ID)) = M.SITE_ID
        AND M.BUSINESS_SERVICE = (
            CASE 
                WHEN TRIM(UPPER(S.BUSINESS_SERVICE)) IN ('SUB_A', 'SUB_B') THEN 'CORE_NETWORK'
                ELSE TRIM(UPPER(S.BUSINESS_SERVICE))
            END
        )

    WHERE 
        S.CREATED_AT >= DATEADD(HOUR, 9, DATEADD(DAY, -1, DATE_TRUNC('DAY', CURRENT_TIMESTAMP())))
        AND S.CREATED_AT < DATEADD(HOUR, 9, DATE_TRUNC('DAY', CURRENT_TIMESTAMP()))
        AND S.BUSINESS_SERVICE IS NOT NULL
        AND (TRIM(UPPER(S.BRAND_ID)) <> 'SYSTEM_EXCLUDE' OR S.BRAND_ID IS NULL)
        AND (TRIM(UPPER(S.SITE_ID)) <> 'SYSTEM_EXCLUDE_SYS' OR S.SITE_ID IS NULL)
) SUB

WHERE 
    ( '{{ status_select }}' = 'ALL' OR RESULTS = '{{ status_select }}' )
    AND 
    ( '{{ bonus_select }}' = 'ALL' OR BONUS_REVENUE_CHECK = '{{ bonus_select }}' )
ORDER BY DATE_TIME DESC;

 

---

🎯 결과

• 수동 검증 제거 → 실시간 검증 + 자동 알림 체계 구축
• Bonus Revenue 및 포인트 불일치 발생 시 즉시 인지 및 대응 가능
• QA/개발팀 커뮤니케이션 없이도 문제 상황 자가 추적 가능
• 신규 Service 추가 시 즉시 정합성 검증 가능
• 데이터 이슈 대응 속도 대폭 개선

---

🚀 역할 및 기여

• 누락 Bonus Revenue 역산 로직 설계
• 대시보드 구축 및 KPI 시각화
• Cross-Check 아키텍처 적용으로 다른 팀 데이터와 검증
• QA 업무 효율화 및 실시간 모니터링 체계 구축