SQL 데이터 품질 확인

데이터 품질 확인방법
1. 중복된 레코드들 체크
2. 최근 데이터의 존재여부 체크(freshness)
3. Primary key uniqueness가 지켜지는지 체크
4. 값이 비어있는 컬럼들이 있는지 체크

CTAS : SELECT를 가지고 테이블을 생성
- adhoc위에서 write가능
DROP TABLE IF EXISTS adhoc.suha_session_summary;
CREATE TABLE adhoc.suha_session_summary AS
SELECT B.*, A.ts FROM raw_data.session_timestamp A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid;

1. 중복체크 
SELECT COUNT(1) FROM adhoc.suha_session_summary;

SELECT COUNT(1)
FROM (
    SELECT DISTINCT userId, sessionId, ts, channel FROM adhoc.suha_session_summary
);
위 둘의 값이 같아야한다.

CTE 사용
With ds AS (
  SELECT DISTINCT userId, sessionId, ts, channel FROM adhoc.suha_session_summary
)
SELECT COUNT(1)
FROM ds;

2. 최근 데이터의 존재여부 체크
SELECT MIN(ts), MAX(ts)
FROM adhoc.suha_session_summary;

3. Primary key uniqueness가 지켜지는지 체크하기
SELECT sessionId, COUNT(1)
FROM adhoc.suha_session_summary
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;
값이 항상 1이어야 한다.

4. 값이 비어있는 칼럼들이 있는지 체크하기
SELECT
    COUNT(CASE WHEN sessionId is NULL THEN 1 END) sessionid_null_count,
    COUNT(CASE WHEN userId is NULL THEN 1 END) userid_null_count,
    COUNT(CASE WHEN ts is NULL THEN 1 END) ts_null_count,
    COUNT(CASE WHEN channel is NULL THEN 1 END) channel_null_count
FROM adhoc.suha_session_summary;

 

예제

 

월별 유니크한 사용자 수
SELECT
TO_CHAR(A.ts, 'YYYY-MM') AS month,
COUNT(DISTINCT B.userid) AS mau
FROM raw_data.session_timestamp A
JOIN raw_data.user_session_channel B ON A.sessionid = B.sessionid
GROUP BY 1
ORDER BY 1 DESC;