이번 포스팅에서는 프로젝트 수행 사례를 통해 데이터 품질 점검을 위한 비즈니스 규칙 유형에는 어떤 것이 있는지, 그리고 실제로 품질 진단 측정을 위한 SQL 쿼리는 어떻게 작성해야 하는지에 대해서 공유하고자 한다.
Intro_ 프로젝트 개요
이번 프로젝트에서는 크게 두 가지 업무를 수행했다.
(1) 데이터 자산화
첫번째는 데이터 자산화 업무로, 고객사의 시스템별로 존재하는 테이블 및 컬럼 정보를 수집해서 데이터 모델을 작성해 총 10,000여 개의 테이블을 가시화했다. 이 중 미사용 테이블을 제외하고 사용 중인 테이블 5,000 개에 대해서는 한글화 및 용어 표준화 작업도 진행하였다.
(2) 데이터 품질 관리
두번째는 고객사에서 품질 관리 활동이 가능하도록, 데이터 품질 관리에 필요한 지침을 수립하고 고객 업무규칙을 확인하여 품질 규칙을 정의하고 품질 측정을 수행하였다.
이번 프로젝트를 통해서 향후 전사 표준에 맞추어 신규 시스템 구축이 가능하며, 데이터 품질 중요도가 높은 영역에 대한 품질 진단 및 개선 활동이 지속적으로 이루어질 수 있을 것으로 기대된다.
이번 포스팅에서는 품질 진단 단계에서 업무규칙을 진단 쿼리로 변환 작성하는 과정에서 DBMS 제약으로 인해 정규식 사용이 불가능한 경우 어떻게 해결하였는지에 대해 다룬다.
1_데이터 품질 측정이란
1.1_프로세스
데이터 품질 측정을 위해서는 다양한 태스크를 단계별로 수행하여야 한다. 오늘 포스팅에서는 업무규칙(BR) 정의 단계를 중점으로 다룬다.
1.2_업무규칙(BR) 정의
품질 진단을 수행하려면 우선적으로 어떤 테이블과 컬럼의 품질을 측정할 것인지 정의해야 한다. 이때, 업무 담당자와 시스템 담당자 양쪽의 협업이 필요할 수 있다.
업무 담당자의 경우, 실제 업무처리를 하면서 '문제적 데이터'로 인한 불편을 직접 겪어 문제 사항에 대한 이해가 높은 반면에, 해당하는 데이터가 어느 테이블의 어떤 컬럼에 해당하는 지 알기 어렵다. 또한 시스템적 관점에서 명확하게 문제를 정의하는 데에 어려움을 겪는다.
시스템 담당자의 경우, 데이터 흐름이나 연관도에 대한 이해가 높으나 비즈니스 로직에 대해 명확히 알기 어렵고, 품질 개선 시 업무적으로 임팩트가 좋은 영역에 대해 파악하기 어려울 수 있다.
해당 컬럼의 어떤 값이 정상값이고 오류값인지 구분할 수 있는 업무규칙(Business Rule, BR)을 명확하게 정의하기 위해서는 업무 담당자와 시스템 담당자 양쪽 모두를 인터뷰하거나 설문을 진행하여 종합적인 관점에서 파악할 필요가 있다.
2_업무규칙(BR)의 유형
우리 팀은 업무 담당자와 시스템 담당자 양측으로부터 BR 요건을 수집하기로 했다. 서면으로 조사한 BR을 취합해서 적용해야 할 테이블/컬럼을 식별했다. 그리고 품질 측정을 위한 쿼리로 변환했다. 자주 발생하는 BR 유형은 다음과 같았다.
(1) 허용 값 오류
여부 컬럼에 허용 값 이외의 0, 1, Null 등이 존재하는 경우나 코드 컬럼에 허용 코드 값을 벗어난 값이 입력되어 있는 경우 이다.
코드 값의 경우 유효 코드 정보가 확보되어 있지 않으면 검증이 어렵다.
(2) 유효 값 오류
해당 컬럼에 유효 범위(Max~Min)를 벗어 나는 값이 존재하는 경우이다.
(3) 번호 생성규칙 오류
해당 번호에 입력되어서는 안 되는 특수문자, 자릿수가 존재하는 경우이다.
쉬운 사례로 주민등록번호도 있다. 주민등록번호는 총 13자리여야 하고, 각 자리에 올 수 있는 숫자가 있다.
(4) 시간 순서 오류
시간 순서의 일관성이 확보되지 않은 경우를 말한다.
위와 같은 시작-종료일자 오류 외에 다른 예시로는, 주문배송 시스템에 주문일자보다 빠른 배송일자가 있다던지 증권거래 관련 정보에 한국거래소 설립일자(1956년 2월 11일)보다 이른 거래일자가 있다던지의 논리적으로 불가능한 시간이 입력되어 있는 경우이다.
이외에도 조건에 따른 필수 입력 값이 누락되어 있다거나, 복잡한 업무 로직을 위배하는 경우 등의 BR이 있을 수 있다.
3_업무규칙(BR) 쿼리(SQL) 변환
지금까지 업무규칙(BR)의 사례를 살펴보았다. 그럼 이러한 BR들은 어떻게 쿼리로 변환해야 할까? BR을 준수하지 않는 오류 값을 검출할 수 있도록 쿼리로 작성하면 된다. 이번 프로젝트에서는 다수 DB를 대상으로 품질 측정 쿼리를 작성하여 수행 결과를 확인했다.
각 DB별로 사용 중인 DMBS가 다르다보니 범용성을 고려해 ANSI 표준 쿼리를 사용하더라도 세부적인 함수나 Syntax 구조가 다른 경우가 있어 결국 DBMS별로 추가 확인 및 변경이 필요하다. 그 중에서도 SAP ASE의 경우 정규식(Regular Expression) 함수 미지원이라는 치명적인 제약이 있었다.
이번 포스팅에서는 동일한 업무규칙을 이기종 DBMS(MariaDB와 SAP ASE)에 적용하려는 경우 어떻게 다른 쿼리로 변환될 수 있는지, 유형별 사례를 정리하였다.
편의상 `SELECT`, `FROM`절 및 정확한 테이블/컬럼명은 생략하고, `WHERE`절만 표기하였다.
3.1_정규식이 필요 없는 BR
정규식이 필요 없는 BR의 경우 MariaDB와 SAP ASE 간에 함수명이 조금씩 다를 뿐 큰 차이는 없다.
(1) 허용 값 오류
`BR` : 해당 컬럼의 데이터 값은 'Y', 'N' 값만 허용한다.
MariaDB & SAP ASE
WHERE 컬럼명 NOT IN ('Y', 'N')
(2) 유효 값 오류
`BR` : 해당 컬럼의 최소값은 0, 최대값은 1이다.
MariaDB & SAP ASE
WHERE 컬럼명 NOT BETWEEN 0 AND 1
(3) 길이 오류
`BR` : 해당 컬럼의 데이터 값은 10자리로 이루어져야 한다.
MariaDB
WHERE CHAR_LENGTH(컬럼명) <> 10
SAP ASE
WHERE LEN(컬럼명) <> 10
3.2_정규식을 사용하는 BR
MariaDB는 정규식을 지원하지만, SAP ASE는 정규식을 미지원한다. 때문에 일반적으로 정규식을 사용해 쿼리를 작성해온 BR의 경우 정규식을 사용하지 않는 방식으로 오류 데이터를 검출할 수 있도록 쿼리를 변경해야 했다.
이 과정에서 성능상으로 우려되는 지점들이 있었다. 정규식 함수도 성능상 좋지 않은데, 정규식 사용을 회피하려다보니 `OR` 조건이나 `SUBSTRING`을 반복 사용하게 되는 경향이 있었다.
(1) 길이 + 코드 규칙
`BR` : 해당 컬럼에는 3자리의 코드 값이 온다. 코드 값은 숫자나 영문으로 이루어진다.
MariaDB
WHERE 컬럼명 NOT REGEXP '^[0-9a-z]{3}$'
--MariaDB는 정규식에서 대/소문자를 구분하지 않음
SAP ASE
WHERE 컬럼명 NOT LIKE '[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]'SAP ASE의 경우 `LIKE` 구문에서 제한적으로 값의 범위를 지정할 수 있다. 그러나 정규식 `{3}와 같이 글자수를 별도 지정할 수는 없다.
(2) 연월일(YYYYMMDD)
`BR` : 해당 컬럼의 데이터 값은 연월일(YYYYMMDD)의 스트링 값으로, 날짜 범위에 해당하지 않는 범위의 값을 가질 수 없다. (예: 2월30일)
또한 1800년도 이전의 날짜 값은 존재해서는 안 되며, 2039년까지의 데이터 값만 존재할 수 있다.
MariaDB
WHERE 컬럼명 NOT REGEXP '^(1[8-9][0-9][0-9]|20[0-3][0-9])(0[1-9]|1[0-2])(0[1-9]|[1-2][0-9]|3[0-1])$'
SAP ASE
WHERE
NOT
(
(
컬럼명 LIKE '1[8-9]______' /* YYYY 1800 이상 */
OR 컬럼명 LIKE '20[0-3]_____' /* YYYY 2040 미만 */
)
AND (
컬럼명 LIKE '____0[1-9]__' /* MM 01~09 */
OR 컬럼명 LIKE '____1[0-2]__' /* MM 10~12 */
)
AND (
컬럼명 LIKE '______0[1-9]' /* DD 01~09 */
OR 컬럼명 LIKE '______2[0-9]' /* DD 10~29 */
OR 컬럼명 LIKE '______3[0-1]' /* DD 30~31 */
)
)
OR 컬럼명 LIKE '____023_' /* 2월 30일, 31일 */SAP ASE의 경우 `LIKE` 구문에서 제한적으로 값의 범위를 지정할 수 있었다. 하지만 정규식에서의 `|(OR)`을 사용할 수 없어 어쩔 수 없이 YYYY, MM, DD에 해당하는 각 데이터 자릿수별로 조건을 나누어 확인해야 했다.
(3) 영문 값
`BR` : 해당 컬럼은 영문 알파벳으로만 이루어져야 한다. 숫자나 특수문자는 들어갈 수 없다.
MariaDB
WHERE 컬럼명 NOT REGEXP '[a-z]{1,}'
SAP ASE
WHERE LEN(컬럼명) <> DATALENGTH(컬렴명)
OR 컬럼명 LIKE '%[0-9]%'
OR 컬럼명 LIKE '%[ -/:-@[-`{-~]%'아스키(ASCII) 코드는 `datalength = 1`이고, 그 외 문자는 `datalength = 2`인 점을 이용하면 한글이 들어간 컬럼을 거를 수 있다.
다만, 아스키 코드에 포함되는 숫자 및 특수문자가 있기 때문에 별도 조건으로 재 검사를 해야 했다.
(참고) 아스키 코드 범위
32~47번 코드: ` !"#$%&'()*+,-./`
48~57번 코드: `0123456789`
58~64번 코드: `:;<=>@`
65~90번 코드: `ABCDEFGHIJKLMNOPQRSTUVWXYZ`
91~96번 코드: `[\]^_`, 백틱
97~122번 코드: `abcdefghijklmnopqrstuvwxyz`
(4) 사업자등록번호
`BR` : 사업자등록번호는 10자리 숫자여야 한다. 마지막 10번째 자릿수는 검증코드로 검증코드가 유효해야 한다.
SAP ASE
WHERE 컬럼명 NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' /* 10자리 숫자로만 이루어지지 않은 컬럼 */
OR 컬럼명 LIKE '0%' /* 사업자등록번호는 101부터 시작됨 */
OR 컬럼명 LIKE '101%' /* 사업자등록번호는 101부터 시작됨 */
OR SUBSTRING(컬럼명, 4, 2) = '00' /* 사업자등록번호 4-5번째자리 '00' 불가 */
OR SUBSTRING(컬럼명, 6, 4) = '0000' /* 사업자등록번호 6-9번째자리 '0000' 불가 */
OR /* 사업자등록번호 검증코드 유효성 체크 */
(컬럼명 LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
AND (
( 10 -
(
( CAST(SUBSTRING(컬럼명, 1, 1) AS INT) * 1
+ CAST(SUBSTRING(컬럼명, 2, 1) AS INT) * 3
+ CAST(SUBSTRING(컬럼명, 3, 1) AS INT) * 7
+ CAST(SUBSTRING(컬럼명, 4, 1) AS INT) * 1
+ CAST(SUBSTRING(컬럼명, 5, 1) AS INT) * 3
+ CAST(SUBSTRING(컬럼명, 6, 1) AS INT) * 7
+ CAST(SUBSTRING(컬럼명, 7, 1) AS INT) * 1
+ CAST(SUBSTRING(컬럼명, 8, 1) AS INT) * 3
+ CAST(SUBSTRING(컬럼명, 9, 1) AS INT) * 5
+ FLOOR((CAST(SUBSTRING(컬럼명, 9, 1) AS INT) * 5) / 10)
) % 10
)
) % 10
) <> CAST(SUBSTRING(컬럼명, 10, 1) AS INT)
)
```이 BR의 경우 SAP ASE에만 해당 컬럼이 있어 MariaDB는 쿼리를 작성하지 않았다.
10번째 자릿수 검증코드의 체크 로직은 https://hongpage.kr/96 의 javascrip 코드를 참고했다.
주의해야 할 점은, STRING ➡️ INT 로 형변환 시도 시 [0-9] 이외의 값을 만나게 되면 형변환 에러가 발생한다. 따라서 형변환 에러를 방지하기 위해서, 전체 스트링이 아라비아숫자로 이루어져 있는지 사전 검증한 뒤 체크로직을 검사하는 식으로 쿼리를 작성했다.
이상으로 데이터 품질 향상을 위한 비즈니스 규칙에는 어떤 유형이 있는지, 그리고 실제로 품질 진단 측정을 위한 SQL 쿼리는 어떻게 작성해야 하는지 알아보았다.
품질 진단 및 관리에 대해서 조금 더 심도 있는 정보 확인을 하고 싶다면, 공공데이터 품질관리 매뉴얼 및 지침을 참고해보시면 좋을 것 같다.
참고 문서
한국지능정보사회진흥원, 공공데이터 관리지침(품질관리), 2019.
'데이터베이스 Database > 프로젝트 Project' 카테고리의 다른 글
| [데이터 아키텍트] 프로젝트 착수 시 DBMS 체크포인트 (4) | 2024.10.26 |
|---|---|
| 데이터 거버넌스의 첫걸음, 데이터 표준화 (1) | 2024.01.07 |
