정합성 조회 — UI 로는 보이지 않는 중복·고아·합계 불일치 찾기

개념

정합성 결함은 조용히 쌓인다

기능 결함은 시끄럽다. 버튼이 안 눌리면 누군가 바로 신고한다. 반면 정합성 결함은 아무도 불편하지 않은 채로 쌓이다가 한참 뒤에 터진다. 주문상품 행이 하나 더 들어간 주문은 화면에서 멀쩡해 보이고, 월말 정산에서야 금액이 안 맞는다. 쿠폰 사용 처리가 빠진 주문은 그 쿠폰을 다시 쓸 수 있다는 사실이 알려질 때까지 조용하다.

QA 가 이걸 잡을 수 있는 이유는 단순하다. 정합성은 "참이어야 하는 문장"으로 표현할 수 있고, 그 문장을 뒤집으면 바로 조회가 된다.

기대 결과가 전부 0건 이라는 점이 핵심이다. 정합성 조회는 "무언가 나오면 결함"인 조회다. 이 성질 덕분에 여러 개를 묶어 점검표로 만들 수 있고, 회귀 테스트처럼 반복 실행할 수 있다.

스키마는 레슨 1의 네 테이블(members·orders·order_items·coupons)을 그대로 쓴다. 조회는 전부 읽기 전용 이다. 참고로 퍼널 전환율이나 코호트 잔존율처럼 제품이 잘 되고 있는가 를 묻는 분석 조회는 모듈 45(레슨 8)의 몫이다. 여기서는 데이터가 스스로와 앞뒤가 맞는가 만 묻는다.

패턴 1 — 합계 불일치

가장 자주 터지고 가장 비싼 유형이다. 금액이 여러 곳에 중복 저장돼 있으면 반드시 어긋나는 날이 온다.

-- 주문 헤더 금액과 주문상품 합계가 맞지 않는 주문
SELECT o.order_id,
       o.total_amount,
       o.discount_amount,
       o.shipping_fee,
       SUM(oi.item_amount) AS items_sum,
       o.total_amount - (SUM(oi.item_amount) - o.discount_amount + o.shipping_fee) AS gap
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.ordered_at >= DATE '2026-09-01'
GROUP BY o.order_id, o.total_amount, o.discount_amount, o.shipping_fee
HAVING o.total_amount <> SUM(oi.item_amount) - o.discount_amount + o.shipping_fee
ORDER BY ABS(o.total_amount - (SUM(oi.item_amount) - o.discount_amount + o.shipping_fee)) DESC;

gap 열을 같이 뽑는 것이 요령이다. 차이가 모두 같은 값 이면 규칙이 하나 빠진 것이고(예: 배송비를 안 더함), 차이가 제각각 이면 계산 시점이나 반올림 문제다. 차이가 정확히 라인 하나 금액과 같으면 라인 중복이나 누락을 먼저 의심한다.

라인 안에서도 검산한다.

-- 단가 x 수량과 라인 금액이 다른 행
SELECT order_item_id, order_id, product_name, unit_price, qty, item_amount,
       unit_price * qty AS expected
FROM order_items
WHERE item_amount <> unit_price * qty;

정률 할인이 라인에 분배되는 제품이라면 이 조회가 정상적으로도 걸린다. 그럴 때는 기획서에서 정한 계산식 을 조회에 그대로 옮겨야 한다. 옮기면서 "그 식이 문서에 명확히 있는가"를 확인하게 되는데, 없으면 그것부터가 요구사항 결함이다(모듈 2).

패턴 2 — 중복

중복은 대부분 재시도 에서 나온다. 사용자가 결제 버튼을 두 번 누르거나, 네트워크 타임아웃 후 클라이언트가 자동 재요청하거나, 배치가 두 번 돌거나.

-- 같은 회원이 같은 금액으로 1분 안에 두 번 이상 주문한 경우 (중복 결제 의심)
SELECT member_id, total_amount, COUNT(*) AS cnt,
       MIN(ordered_at) AS first_at, MAX(ordered_at) AS last_at
FROM orders
WHERE ordered_at >= DATE '2026-09-01'
  AND status IN ('PLACED','PAID')
GROUP BY member_id, total_amount
HAVING COUNT(*) >= 2
ORDER BY cnt DESC;

시간 간격 조건은 DB 방언을 타므로 1차로 이렇게 뽑아 놓고 first_at·last_at 을 눈으로 본다. 두 시각이 몇 초 차이면 거의 확실한 중복이고, 며칠 차이면 정상 재구매다.

업무 키가 유일해야 하는 곳은 더 단순하게 잡는다.

-- 한 쿠폰이 두 건 이상의 주문에 쓰였다
SELECT coupon_id, COUNT(*) AS used_count
FROM orders
WHERE coupon_id IS NOT NULL
GROUP BY coupon_id
HAVING COUNT(*) >= 2;
 
-- 이메일이 중복된 회원 (대소문자·공백까지 정규화해서 비교)
SELECT LOWER(TRIM(email)) AS norm_email, COUNT(*) AS cnt
FROM members
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) >= 2;

두 번째 조회가 중요한 이유가 있다. 애플리케이션은 test@a.com 과 Test@a.com 을 다른 값으로 보고 통과시키지만, 사람은 같은 계정으로 인식한다. 정규화해서 비교하면 유일성 제약이 있는 줄 알았던 컬럼에서도 중복이 나온다.

패턴 3 — 고아 레코드

부모가 없는 자식 행, 자식이 없어야 정상인데 남아 있는 행이다. 외래 키 제약이 없거나(성능 이유로 빼는 경우가 흔하다) 삭제가 애플리케이션 코드로만 처리될 때 생긴다.

-- (a) 주문상품이 하나도 없는 주문
SELECT o.order_id, o.ordered_at, o.status, o.total_amount
FROM orders AS o
LEFT JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE oi.order_item_id IS NULL;
 
-- (b) 존재하지 않는 주문을 가리키는 주문상품
SELECT oi.order_item_id, oi.order_id, oi.product_name
FROM order_items AS oi
LEFT JOIN orders AS o ON o.order_id = oi.order_id
WHERE o.order_id IS NULL;
 
-- (c) 존재하지 않는 회원의 주문
SELECT o.order_id, o.member_id, o.ordered_at
FROM orders AS o
LEFT JOIN members AS m ON m.member_id = o.member_id
WHERE m.member_id IS NULL;

세 조회가 같은 뼈대다. LEFT JOIN 한 뒤 오른쪽 기본키가 NULL 이면 짝이 없다. 이 한 문장이 고아 레코드 조회의 전부다.

WHERE o.order_id IS NULL 자리에 반드시 기본키 열 을 쓴다. 원래 값이 NULL 일 수 있는 일반 열을 쓰면 짝이 있는데도 걸리는 오탐이 섞인다.

탈퇴 회원 처리도 같은 방식으로 본다.

-- 탈퇴했는데 진행 중인 주문이 남아 있는 회원
SELECT m.member_id, m.email, m.withdrawn_at, o.order_id, o.status
FROM members AS m
JOIN orders AS o ON o.member_id = m.member_id
WHERE m.withdrawn_at IS NOT NULL
  AND o.status IN ('PLACED','PAID','SHIPPED');

이건 고아가 아니라 업무 규칙 위반 이지만 찾는 모양은 같다. "탈퇴하면 진행 주문이 없어야 한다"는 문장을 뒤집었을 뿐이다.

패턴 4 — 범위·경계 위반

경계값 분석(모듈 3)을 화면 입력이 아니라 저장된 값 에 적용한다. 화면에서 막혔다고 데이터에 못 들어가는 것은 아니다. API 직접 호출, 배치, 관리자 도구, 과거 버전이 만든 데이터가 전부 경로다.

SELECT 'qty_not_positive' AS rule, order_item_id AS id
FROM order_items WHERE qty IS NULL OR qty < 1
UNION ALL
SELECT 'negative_price', order_item_id
FROM order_items WHERE unit_price < 0
UNION ALL
SELECT 'discount_over_total', order_id
FROM orders WHERE discount_amount > total_amount + discount_amount
UNION ALL
SELECT 'coupon_date_reversed', coupon_id
FROM coupons WHERE valid_from > valid_to
UNION ALL
SELECT 'unknown_status', order_id
FROM orders
WHERE status NOT IN ('PLACED','PAID','SHIPPED','DELIVERED','CANCELLED','REFUNDED');

UNION ALL 로 규칙을 이어 붙이면 하나의 조회가 점검표가 된다. 결과가 0행이면 전부 통과, 나오면 rule 열이 어느 규칙이 깨졌는지 알려 준다. 규칙을 추가할 때마다 블록 하나를 덧붙이면 된다.

마지막 규칙(unknown_status)을 눈여겨볼 만하다. 상태 값이 문자열로 저장되는 시스템에서는 오타나 구버전 값이 섞이는 일 이 흔하고, 화면은 "기타"로 표시하고 넘어가 버린다. 열거형으로 정의된 모든 컬럼에 같은 조회를 걸어 둘 가치가 있다.

NULL 을 빠뜨리지 않는 것도 중요하다. qty < 1 만 쓰면 qty 가 NULL 인 행은 걸리지 않는다(레슨 1). 정합성 조회에서는 항상 IS NULL OR … 형태로 명시 한다.

패턴 5 — 상태와 시각의 모순

상태 기계를 가진 데이터는 상태와 타임스탬프가 서로 증언한다. 둘이 어긋나면 어느 한쪽이 거짓말이다.

-- (a) 취소된 주문인데 쿠폰이 사용 상태로 남아 있다
SELECT o.order_id, o.status, c.coupon_id, c.used_at
FROM orders AS o
JOIN coupons AS c ON c.coupon_id = o.coupon_id
WHERE o.status IN ('CANCELLED','REFUNDED')
  AND c.used_at IS NOT NULL;
 
-- (b) 쿠폰이 유효기간 밖에서 사용됐다
SELECT coupon_id, code, valid_from, valid_to, used_at
FROM coupons
WHERE used_at IS NOT NULL
  AND (CAST(used_at AS DATE) < valid_from OR CAST(used_at AS DATE) > valid_to);
 
-- (c) 가입 시각보다 이른 주문
SELECT o.order_id, o.ordered_at, m.member_id, m.joined_at
FROM orders AS o
JOIN members AS m ON m.member_id = o.member_id
WHERE o.ordered_at < m.joined_at;

(c) 같은 조회는 평소에 0건이다가 데이터 이관·시스템 통합 직후에 무더기로 걸린다. 이관 검증에서 가장 먼저 돌려야 하는 유형이고, 이관 작업이 예정돼 있으면 사전에 만들어 두면 좋다.

결과를 결함으로 판정하는 법

조회가 행을 뱉었다고 전부 결함은 아니다. 등록하기 전에 세 가지를 확인한다.

1. 규칙의 출처를 확인한다. "주문상품 합계 = 결제금액"이 정말 기획서의 규칙인가, 내가 가정한 것인가. 문서에 없으면 결함이 아니라 질문 으로 먼저 던진다
2. 언제부터인지 본다. ordered_at 으로 범위를 좁혀 가며 최초 발생 시점을 찾는다. 특정 배포일 이후에만 걸리면 원인 후보가 단숨에 좁혀진다
3. 범위를 센다. 몇 건인지, 전체 대비 몇 %인지, 금액 합계가 얼마인지. 이 세 숫자가 우선순위를 결정한다

-- 최초 발생 시점과 영향 범위를 한 번에
SELECT MIN(o.ordered_at) AS first_seen,
       MAX(o.ordered_at) AS last_seen,
       COUNT(*)          AS affected_orders,
       SUM(o.total_amount) AS affected_amount
FROM orders AS o
LEFT JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE oi.order_item_id IS NULL;

결함 리포트에는 조회문 전체와 이 요약 결과 를 같이 붙인다. 개발자가 같은 조회를 자기 환경에서 돌려 볼 수 있어야 논쟁이 줄어든다.

점검표로 묶어 두기

한 번 만든 정합성 조회는 버리지 않는다. 릴리스마다, 대량 배치 뒤에, 데이터 이관 뒤에 다시 돌린다. 파일 하나에 규칙 이름과 함께 모아 두고 결과를 이렇게 기록한다.

"지난 실행" 열이 있어야 신규와 기존을 구분 할 수 있다. 이 구분이 없으면 매번 같은 건수를 보고 매번 놀라거나, 반대로 늘어난 것을 못 본다.

실무에서 왜 필요한가

정합성 결함은 발견이 늦을수록 복구가 불가능해지는 유형이다. 기능 결함은 고치면 끝이지만, 잘못 저장된 데이터는 이미 정산·통계·외부 연동으로 퍼져 나간 뒤다. 중복 주문 1,200건은 코드를 고쳐도 사라지지 않고, 누군가 보정 스크립트를 쓰고 고객에게 사과 메일을 보내야 한다. 릴리스 전에 조회 한 줄로 잡을 수 있었던 일 이다.

조직 안에서의 효과도 크다. 정합성 조회를 들고 다니는 QA 는 회의에서 다른 위치에 선다. "결제 모듈 배포 후 헤더-라인 금액 불일치가 0건에서 3건으로 늘었고, 전부 쿠폰과 다건 주문이 겹친 경우입니다"는 기능 테스트 결과 보고와 성격이 다른 정보다. 개발자가 바로 코드로 내려갈 수 있다.

그리고 이 작업은 한 번 만들면 계속 쓴다. 기능 테스트 케이스는 화면이 바뀌면 같이 고쳐야 하지만, 정합성 규칙은 데이터 모델이 바뀌지 않는 한 그대로 산다. 투자 대비 수명이 긴 몇 안 되는 테스트 자산이고, 나중에 자동화로 옮기기도 쉽다(조회를 돌려 행 수가 0인지 보는 것이 전부다).

실습 과제

과제 1 — 규칙 문장 12개 쓰기 (20분)

레슨 1에서 만든 데이터(또는 담당 제품의 스키마)를 놓고 "항상 참이어야 하는 문장"을 12개 적는다. 조회는 아직 쓰지 않는다. 문장만 쓴다.

• 최소 3개는 두 테이블에 걸친 규칙이어야 한다(예: 주문과 주문상품)
• 최소 2개는 상태와 시각의 관계여야 한다(예: 취소된 주문은 배송 기록이 없다)
• 각 문장 옆에 출처 를 적는다 — 기획서 몇 쪽인지, 개발자에게 들은 것인지, 내 추정인지

"내 추정"이 절반을 넘으면 그 자체가 발견이다. 그 목록을 들고 기획자에게 확인을 요청한다.

과제 2 — 점검표 조회 만들기 (35분)

과제 1의 12문장 중 6개를 조회로 옮긴다. 조건은 셋이다.

1. 모든 조회의 기대 결과가 0건 이 되도록 문장을 뒤집어 쓴다
2. 최소 1개는 LEFT JOIN … IS NULL, 1개는 GROUP BY … HAVING COUNT(*) >= 2 를 쓴다
3. 범위·경계 규칙 3개는 UNION ALL 로 묶어 rule 열이 나오는 단일 조회로 만든다

그 다음 일부러 데이터를 깨뜨린다. 로컬 DB 에서 주문상품 행 하나를 지우고, 쿠폰 하나를 두 주문에 붙이고, 수량을 0으로 바꾼다. 조회 6개가 각각 그 위반을 잡아내는지 확인한다. 잡지 못한 조회가 있으면 그 조회가 잘못 쓰인 것이다 — 대개 NULL 처리가 빠져 있다.

과제 3 — 판정과 리포트 (25분)

과제 2에서 걸린 것 중 하나를 골라 결함 리포트 형식으로 정리한다.

• 깨진 규칙 문장과 그 출처
• 재현용 조회문 전체(복사해서 바로 돌릴 수 있게)
• 최초 발생 시점·영향 건수·영향 금액 요약 조회 결과
• 화면에서는 어떻게 보이는지(보이지 않는다면 "화면상 정상"이라고 명시)

마지막 항목을 빠뜨리지 않는 것이 중요하다. "화면은 정상인데 데이터가 틀렸다"는 문장이 이 결함의 심각도를 설명하는 핵심 이고, 그게 없으면 "화면 멀쩡한데 왜"라는 반문에 막힌다.

자가 체크리스트

• 정합성 규칙을 "항상 참이어야 하는 문장"으로 쓰고 뒤집어 조회로 옮길 수 있다
• 정합성 조회의 기대 결과가 0건이어야 하는 이유를 설명할 수 있다
• 헤더 금액과 라인 합계를 대조하고 gap 의 분포로 원인 유형을 가를 수 있다
• GROUP BY … HAVING COUNT(*) >= 2 로 업무 키 중복을 찾을 수 있다
• 이메일처럼 정규화가 필요한 값의 중복을 LOWER·TRIM 으로 잡을 수 있다
• LEFT JOIN 후 오른쪽 기본키가 NULL 인 조건으로 고아 레코드를 양방향으로 찾을 수 있다
• 범위·열거형 위반 규칙을 UNION ALL 로 묶어 단일 점검 조회를 만들 수 있다
• 정합성 조회에서 NULL 을 명시적으로 처리해야 하는 이유를 설명할 수 있다
• 걸린 행을 결함으로 판정하기 전 규칙 출처·최초 발생 시점·영향 범위를 확인한다
• 점검표에 "지난 실행" 열을 두어 신규와 기존을 구분한다

흔한 실수

• 규칙을 확인하지 않고 결함으로 등록한다. "합계가 맞아야 한다"가 내 가정일 수 있다. 출처가 없으면 결함이 아니라 질문으로 먼저 던진다.
• NULL 을 빠뜨린 조건으로 위반을 놓친다. qty < 1 은 qty IS NULL 행을 잡지 못한다. 정합성 조회는 항상 NULL 을 명시한다.
• LEFT JOIN … IS NULL 에서 기본키가 아닌 열을 검사한다. 원래 비어 있을 수 있는 열을 쓰면 짝이 있는 행도 걸린다.
• 중복 판정에 정규화를 빼먹는다. 대소문자·앞뒤 공백·하이픈 유무까지 맞춘 뒤에야 "유일하다"를 말할 수 있다.
• 영향 범위 없이 보고한다. 건수·비율·금액이 없으면 우선순위 회의에서 아무 결정도 못 내린다.
• 한 번 쓰고 버린다. 정합성 조회는 릴리스마다 다시 도는 자산이다. 파일로 모아 두고 결과를 기록한다.
• 운영 DB 에서 조회를 고치려고 데이터를 손댄다. 깨뜨려 보는 실험은 로컬·테스트 DB 에서만 한다(레슨 1).

참고 자료

• PostgreSQL 공식 문서 — Constraints — 유일성·외래 키·체크 제약이 DB 차원에서 막아 주는 범위와 한계
• PostgreSQL 공식 문서 — Aggregate Functions — COUNT·SUM 이 NULL 을 다루는 방식
• ISO/IEC 25012 — Data Quality Model — 정확성·완전성·일관성 같은 데이터 품질 특성의 표준 정의(원문 확인)
• 레슨 s3-m61-l01 — SQL 로 결과를 직접 확인하기(스키마와 문법의 출발점)
• 레슨 s1-m03-l01 — 동등 분할과 경계값 분석(패턴 4의 사고 틀)
• 레슨 s2-m06-l02 — 좋은 결함 리포트 쓰기(과제 3의 형식)