[SQL] 데이터 품질 확인

🔎 항상 시도해 봐야 하는 데이터 품질 확인 방법

• 중복된 레코드들 체크하기
• 최근 데이터 존재 여부 체크
• Primary key uniqueness 지켜지는지 체크
• 값 비어 있는 컬럼들이 있는지 체크

📌 네 가지 품질 확인 방법을 SQL을 사용해서 확인해 보자

• 해당 테이블을 조인하여 만든 ADHOC.SESSION_SUMMARY 테이블을 사용해 보자.
  

-- 기존에 해당 테이블이 있었다면 DROP하여 테이블 삭제 
DROP TABLE IF EXISTS ADHOC.SESSION_SUMMARY;

-- CTAS를 이용해 ADHOC 데이터베이스에 테이블 생성
CREATE TABLE ADHOC.SESSION_SUMMARY
          AS SELECT B.*
                  , A.TS
               FROM RAW_DATA.SESSION_TIMESTAMP A
               JOIN RAW_DATA.USER_SESSION_CHANNEL B
                 ON A.SESSION_ID = B.SESSION_ID

• 두 테이블을 조회하면 각각 USERID, SESSIONID, CHANNEL, TS를 컬럼으로 가지는 테이블이 된다.

1. 중복된 레코드 확인

• 단순하게 테이블의 모든 데이터를 조회해서 COUNT한 것과 DISTINCT를 이용해 COUNT 한 수를 비교한다. 일치하지 않는다면 중복된 데이터가 있는 것.

SELECT COUNT(1)
  FROM ADHOC.SESSION_SUMMARY; -- 이 테이블의 모든 데이터 카운트
  
SELECT COUNT(1)
  FROM (
  			SELECT DISTINCT USERID
                 , SESSIONID
                 , TS
                 , CHANNEL 
           	  FROM ADHOC.SESSION_SUMMARY  
  		)  -- 중복 제거를 해서 다시 카운트 

• CTE를 사용해서 중복 제거를 한 후 카운트를 해 볼 수도 있다.

WITH DS AS (
	SELECT DISTINCT USERID
         , SESSIONID
         , TS
         , CHANNEL
      FROM ADHOC.SESSION_SUMMARY   
)
SELECT COUNT(1)
  FROM DS;

2. 최근 데이터 존재 여부 확인 (freshness)

• TS는 세션의 시간을 담고 있는 컬럼이다.
• 이 시간 정보를 담고 있는 컬럼의 최소(MIN)와 최대(MAX)를 보면 이 데이터가 처음 생성된 데이터는 언제 생성이 되었는지, 가장 마지막에 생성된 데이터는 언제 생성되었는지를 확인해 줄 수 있다.
• 데이터의 저장 시간을 담고 있는 컬럼을 활용해 보자.

SELECT MIN(TS)
     , MAX(TS)
  FROM ADHOC.SESSION_SUMMARY 

3. Primary Key Uniqueness가 지켜지는지 확인

• PK인 SESSIONID로 그룹핑을 하여 COUNT를 한다.
• 만약 COUNT가 2인 것이 있다면 Primary Key Uniqueness가 깨진 것이기 때문에 ORDER BY에서 COUNT한 값을 내림차순하여 하나만 조회해 온다.
• COUNT가 모두 1이라면 LIMIT 1을 걸어도 조회되는 하나의 데이터의 COUNT는 1일 것이다.
• 그렇지 않다면 Primary Key Uniqueness가 깨진 것이다.

SELECT SESSIONID
     , COUNT(1)
  FROM ADHOC.SESSION_SUMMARY
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1

4. 값이 비어 있는 컬럼들이 있는지 확인

• CASE-WHEN 절을 사용해서 각 컬럼들에 NULL인 값이 있다면 1이 출력되게 하는 방식으로 각 컬럼의 데이터들을 확인한다.

SELECT COUNT(CASE WHEN SESSIONID IS NULL
				  THEN 1 END) 				SESSION_NULL_CNT
     , COUNT(CASE WHEN USERID IS NULL
				  THEN 1 END) 				USERID_NULL_CNT
     , COUNT(CASE WHEN TS IS NULL
				  THEN 1 END) 				TS_NULL_CNT
     , COUNT(CASE WHEN CHANNEL IS NULL
				  THEN 1 END) 				CHANNEL_NULL_CNT
  FROM ADHOC.SESSION_SUMMARY