ETL 데이터 생성 후 검증 사항
1. 데이터 소스 타겟 집계 건수 확인
2. 메져값 SUM 집계 비교
3. 디멘전 코드 매핑 확인(YN, SEX, AGE)
4. 데이터 타입 체크(넘버)
--1. count check
--2. sum check
SELECT TABLE_NAME, 'SELECT SUM('||ListAgg(COLUMN_NAME, '), SUM(') WITHIN GROUP (ORDER BY COLUMN_ID)||') FROM HTSBA01.'||TABLE_NAME||';' FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HTSBA01'
AND TABLE_NAME IN( 'FDHBW_FAIR_RFD' )
AND (COLUMN_NAME LIKE '%CNT' OR COLUMN_NAME LIKE '%AMT' )
GROUP BY TABLE_NAME;
--3. dimension check
--3.1. distinct check
--CD로 끝나는 컬럼중 DIMENSION 에도 같은 명이 존재 하는 경우
SELECT 'SELECT DISTINCT '||COLUMN_NAME||' FROM '||OWNER||'.'||TABLE_NAME||' D;' A, COLUMN_NAME FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HTSBA01'
AND COLUMN_NAME IN (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HTSBA01'
AND TABLE_NAME IN( 'FDHBW_FAIR_SEAT' )
AND (COLUMN_NAME LIKE '%CD' OR COLUMN_NAME LIKE '%ID')
)
AND TABLE_NAME LIKE 'D%'
UNION ALL
SELECT 'SELECT DISTINCT '||COLUMN_NAME||' FROM '||OWNER||'.'||TABLE_NAME||' T;', COLUMN_NAME FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HTSBA01'
AND TABLE_NAME IN( 'FDHBW_FAIR_SEAT' )
AND (COLUMN_NAME LIKE '%CD' OR COLUMN_NAME LIKE '%ID')
AND COLUMN_NAME IN (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HTSBA01'
AND TABLE_NAME LIKE 'D%'
)
ORDER BY 1;
--CD로 끝나는 컬럼이지만 DIMENSION에 존재하지 않는 경우
SELECT 'SELECT DISTINCT '||COLUMN_NAME||' D FROM '||OWNER||'.'||TABLE_NAME||' T;', COLUMN_NAME FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HTSBA01'
AND TABLE_NAME IN( 'FDHBW_FAIR_SEAT' )
AND (COLUMN_NAME LIKE '%CD' OR COLUMN_NAME LIKE '%ID' )
AND COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HTSBA01'
AND TABLE_NAME LIKE 'D%')
;
-- CD OR ID SQL QUERY
SELECT COLUMN_NAME, 'SELECT D.DIM_CODE, F.CODE FROM (SELECT DIM_CODE AS DIM_CODE FROM HTSBA01.DIMENSION ) D, (SELECT DISTINCT '||COLUMN_NAME||' CODE FROM '||OWNER||'.'||TABLE_NAME||') F WHERE D.DIM_CODE(+) = F.CODE AND D.DIM_CODE IS NULL;' "--CC"
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HTSBA01'
AND TABLE_NAME IN( 'FDHBW_FAIR_SEAT' )
AND (COLUMN_NAME LIKE '%CD' OR COLUMN_NAME LIKE '%ID' )
;
--YN 여부 컬럼
SELECT 'SELECT DISTINCT '||ListAgg(COLUMN_NAME, ' FROM '||OWNER||'.'||TABLE_NAME||' T;
SELECT DISTINCT ') WITHIN GROUP (ORDER BY COLUMN_ID)||' FROM '||OWNER||'.'||TABLE_NAME||' T;' FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HTSBA01'
AND TABLE_NAME IN( 'FDHBW_FAIR_SEAT' )
AND (COLUMN_NAME LIKE '%YN')
GROUP BY OWNER,TABLE_NAME;
;
SELECT * FROM ALL_COL_COMMENTS;
-- CD OR ID SQL QUERY
SELECT '/*'||COLUMN_NAME "--", COMMENTS||'*/' "--" , 'SELECT D.DIM_CODE, F.CODE FROM (SELECT ' "--",' ' "--",' AS DIM_CODE FROM HTSBA01.' "--",' ' "--",' ) D, (SELECT DISTINCT '||COLUMN_NAME||' CODE FROM '||OWNER||'.'||TABLE_NAME||') F WHERE D.DIM_CODE(+) = F.CODE AND D.DIM_CODE IS NULL;' "--CC"
FROM ALL_COL_COMMENTS
WHERE OWNER = 'HTSBA01'
AND TABLE_NAME IN( 'FDHBW_TKT_SETT' )
--AND (COLUMN_NAME LIKE '%CD' OR COLUMN_NAME LIKE '%ID' OR COLUMN_NAME LIKE '%NO' )
;