원본 정제와 스키마 계약

목표

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

왜 중요한가

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

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

거부 사유도 함께 남겨야 합니다. 이유 없이 버려진 행은 복구할 수 없습니다. 그리고 값이 없는 것과 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일까지는 오류도 없이 조용히 틀립니다.

단계 8개

1. 원본의 결함 세어 보기
2. 네 가지 날짜 형식 파싱하기
3. 금액에서 기호와 쉼표 제거하기
4. 상태값 정규화하기
5. 정제 표에 적재하기
6. 거부 표에 사유와 함께 남기기
7. 보존 법칙 확인하기
8. 스키마 계약서 남기기