A Validator That Cannot Say "Passed" Is Not a Validator

In one line

The value of a validator is decided not by what it catches but by whether it can say a normal file is normal.

Why this was needed

A cleansing script is written once and thrown away. A validator runs every week. This difference changes the design.

Once you attach a data integration at the customer site, files of the same format start arriving every week from next month. In some week the upstream system changes, and a column is added, the date format changes, or the encoding changes. The point at which a person spots that change by eye is usually after a report comes in that the aggregated numbers look strange.

The validator exists to eliminate that time lag.

How it works

A usable validator has four kinds of rules.

Schema rules — the number and names of the columns, the presence of required values, and types. "There must be 5 fields, customer must not be empty, and amount must be an integer."

Duplicate rules — whether the key is unique. The thing to watch here is the trap seen in the previous module. If the key can have missing values, count the rows with missing values separately.

Range rules — whether the value is in a sensible interval. A row where amount is 0, negative, or 99 million won is a valid integer in form but, in business terms, almost certainly wrong data. A validator without range rules passes everything as long as the type matches, and lets through a value whose decimal point is shifted by one place.

Reference rules — consistency with other data. Whether the customer number of an order actually exists in the customer table.

And there is one property more important than these four. For a normal file, it must always say pass.

What it looks like in the field

There are two ways a validator fails, and both are common.

Over-detection. The rules are too tight, so even normal files fail. Then people start ignoring it every week with "it's probably that again", and two months later when a real problem comes, they ignore it in the same way. At that point having the validator is worse than not having one. This is because it makes people believe it is there while actually preventing nothing.

Under-detection. The rules are loose and it catches nothing. A validator built "just so it doesn't crash" usually ends up like this.

So when you build a validator, you must always test it in two directions. Run it on a normal file to see whether it passes, and on a broken file to see whether it fails. A validator for which only one of the two has been checked is half built.

Finally, the output format. A validator must speak through its exit code. 0 for pass, a nonzero value for failure. That is how it plugs straight into CI or a batch pipeline. The message a person reads is layered on top of that, and the signal a machine reads comes first.

What a validator must have

A validator that only says "wrong" is half a validator. For a person to be able to fix it, four things are needed.

Element	Without it
Which line and which field	A day spent finding where it is among 1 million rows
What was expected	You do not know what to fix it to
What the actual value is	You have to open the original again
How many cases	You cannot tell whether it is a one-off exception or a structural problem

❌ 검증 실패: 잘못된 데이터가 있습니다
✅ 행 4213, 컬럼 order_date: '2026-13-45' 가 날짜 형식이 아닙니다 (기대: YYYY-MM-DD)
   같은 오류 1,842건 — 원본의 8행 이후 전부. 인코딩이나 구분자를 먼저 확인하세요.

The last sentence is the key. If errors are concentrated after a particular point, it is not a problem of individual values but a parsing problem. If the validator notices that pattern and tells you, the direction of the investigation is set right away.

Where to block it

Putting the same check at several layers is not a waste. Each blocks something different.

1. 수집 경계   형식·필수 필드·인코딩       → 나쁜 데이터가 들어오지 못하게
2. 변환 중     비즈니스 규칙·참조 무결성    → 조용히 잘못된 결과를 만들지 못하게
3. 적재 후     행 수·합계·분포 대조         → 옮기다 잃어버린 것을 잡게

Number 3 is often missed. If the original 12,043 rows become 12,041 rows after loading, 2 rows have vanished somewhere. One line that cross-checks the row count and the sum of amounts catches this kind of thing.

-- 적재 후 대조
select
  (select count(*) from staging_orders) as src,
  (select count(*) from orders where load_id = :id) as dst,
  (select sum(amount) from staging_orders) as src_amt,
  (select sum(amount) from orders where load_id = :id) as dst_amt;

How to handle bad rows

If you fail everything, you cannot use even the good data. If you block nothing, it gets contaminated. The practical answer is quarantine.

읽은 행 12,043
 ├ 통과   11,998 → 적재
 └ 격리       45 → quarantine 표 + 사유

Leave the quarantined rows with their reasons, and put a threshold on the quarantine count. If what was usually 0.1% jumps to 5%, something has changed on the source side, so that in itself is an alert. If you throw them away quietly, this signal disappears.

What you will do in the next lab

You take three files deliberately broken in different ways and one intact file, count how many rows in each break the rules, and in the end build a validation script that can judge both pass and failure.