原本の整形とスキーマ契約

목표

문자열로만 이루어진 원본 표를 프로파일링하고, 형식을 정규화하고, 정제 표와 거부 표로 나눠 적재하는 한 사이클을 완성합니다.

왜 중요한가

데이터 파이프라인에서 가장 위험한 사고는 실패가 아니라 조용한 유실입니다. 적재에 실패한 행을 그냥 건너뛰면 아무 오류도 나지 않고 숫자만 조금 작아집니다. 몇 달 뒤에 발견되면 어느 시점부터 무엇이 빠졌는지 추적할 방법이 없습니다.

그래서 정제 파이프라인은 보존 법칙을 지켜야 합니다. 정제 건수와 거부 건수의 합이 원본 건수와 정확히 같아야 하고, 어느 쪽에도 속하지 않는 행이 있으면 안 됩니다. 이 검증을 파이프라인 안에 넣어 두면 유실이 발생하는 즉시 드러납니다.

거부 사유도 함께 남겨야 합니다. 이유 없이 버려진 행은 복구할 수 없습니다. 그리고 값이 없는 것과 0 은 다르다는 점도 기억하세요. 빈 금액을 0 으로 채우는 순간 "정보가 누락되었다"는 사실 자체가 지워집니다.

단계

대상은 staging.orders_raw 표입니다.

1. t_raw_profile 표를 만듭니다. 컬럼은 col, bad_count 이며 정확히 3행입니다.
   • customer_email — 값이 비어 있는(NULL) 행 수
   • amount — NULL 이거나 공백만 있는 행 수
   • order_date — YYYY-MM-DD 형식이 아닌 행 수
2. v_raw_dates 뷰를 만듭니다. 컬럼은 raw_id, order_date_parsed(date 타입)이며 모든 행이 파싱되어야 합니다. 형식은 YYYY-MM-DD, MM/DD/YYYY, YYYY.MM.DD, YYYYMMDD 네 가지입니다.
3. v_raw_amount 뷰를 만듭니다. 컬럼은 raw_id, amount_num(numeric 타입)이며 통화 기호와 천 단위 쉼표를 제거하고, 빈 문자열은 0 이 아니라 값 없음으로 둡니다.
4. v_raw_status 뷰를 만듭니다. 컬럼은 raw_id, status_norm 이며 앞뒤 공백을 지우고 소문자로 통일합니다. 결과는 3종이어야 합니다.
5. orders_clean 표를 만듭니다. 컬럼은 raw_id, order_ref, customer_email, order_date, amount, status 순서이며, 이메일이 있고 금액이 비어 있지 않은 행만 타입을 갖춰 적재합니다.
6. orders_reject 표를 만듭니다. 컬럼은 raw_id, reason 이며 5번에서 제외된 행이 사유와 함께 들어갑니다. 사유는 비어 있으면 안 됩니다.
7. orders_clean 건수와 orders_reject 건수의 합이 staging.orders_raw 건수와 같고, 양쪽에 동시에 들어간 행이 없는지 확인합니다.
8. schema_contract 표를 만듭니다. 컬럼은 column_name, data_type 이며 orders_clean 의 컬럼과 타입을 그대로 담습니다.

참고

• 정규식 매칭: order_date ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
• 형식별 파싱: to_date(order_date, 'MM/DD/YYYY')
• 빈 문자열을 값 없음으로: nullif(btrim(값), '')
• 카탈로그 조회: SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders_clean'
• 흔한 실수 1: 빈 문자열은 NULL 이 아니므로 IS NULL 만으로는 잡히지 않습니다.
• 흔한 실수 2: MM/DD/YYYY 를 DD/MM/YYYY 로 읽으면 12일까지는 오류도 없이 조용히 틀립니다.

원본의 결함 세어 보기

t_raw_profile 표를 만듭니다. 컬럼은 col, bad_count 이며 정확히 3행입니다.

• customer_email — 값이 비어 있는(NULL) 행 수
• amount — NULL 이거나 공백만 있는 행 수
• order_date — YYYY-MM-DD 형식이 아닌 행 수

컬럼마다 무엇이 결함인지 정의가 다릅니다. 빈 문자열은 NULL 이 아니라는 점에 주의하세요.

네 가지 날짜 형식 파싱하기

v_raw_dates 뷰를 만듭니다. 컬럼은 raw_id, order_date_parsed(date 타입)이며 모든 행이 파싱되어야 합니다. 형식은 YYYY-MM-DD, MM/DD/YYYY, YYYY.MM.DD, YYYYMMDD 네 가지입니다.

정규식으로 형식을 판별한 뒤 각각 다른 파싱 규칙을 적용합니다. 월과 일의 순서가 다른 형식이 하나 있습니다.

금액에서 기호와 쉼표 제거하기

v_raw_amount 뷰를 만듭니다. 컬럼은 raw_id, amount_num(numeric 타입)이며 통화 기호와 천 단위 쉼표를 제거하고, 빈 문자열은 0 이 아니라 값 없음으로 둡니다.

문자를 지운 뒤 숫자로 바꿉니다. 빈 문자열은 0 이 아니라 값 없음으로 두어야 합니다.

상태값 정규화하기

v_raw_status 뷰를 만듭니다. 컬럼은 raw_id, status_norm 이며 앞뒤 공백을 지우고 소문자로 통일합니다. 결과는 3종이어야 합니다.

앞뒤 공백을 지우고 대소문자를 통일하면 몇 종류로 줄어드는지 확인하세요.

정제 표에 적재하기

orders_clean 표를 만듭니다. 컬럼은 raw_id, order_ref, customer_email, order_date, amount, status 순서이며, 이메일이 있고 금액이 비어 있지 않은 행만 타입을 갖춰 적재합니다.

이메일이 없거나 금액이 비어 있는 행은 제외합니다. 컬럼 타입을 제대로 갖춰야 합니다.

거부 표에 사유와 함께 남기기

orders_reject 표를 만듭니다. 컬럼은 raw_id, reason 이며 5번에서 제외된 행이 사유와 함께 들어갑니다. 사유는 비어 있으면 안 됩니다.

왜 버렸는지를 반드시 적습니다. 사유가 비어 있으면 나중에 아무도 복구하지 못합니다.

보존 법칙 확인하기

orders_clean 건수와 orders_reject 건수의 합이 staging.orders_raw 건수와 같고, 양쪽에 동시에 들어간 행이 없는지 확인합니다.

정제와 거부의 합이 원본과 같아야 하고, 양쪽에 동시에 들어간 행이 없어야 합니다.

스키마 계약서 남기기

schema_contract 표를 만듭니다. 컬럼은 column_name, data_type 이며 orders_clean 의 컬럼과 타입을 그대로 담습니다.

카탈로그에서 컬럼 이름과 타입을 그대로 읽어 표로 만드는 편이 안전합니다.