Green Light, Wrong Numbers
One-line summary
A pipeline's success/failure is a different axis from the correctness/incorrectness of the data. If you do not measure quality, the dashboard stays lit green on top of a silently empty table.
Why this was needed
In the Monday morning meeting, a report comes in that sales fell 40% from the previous week. The sales team is thrown into turmoil. They search for the cause all day, and it comes out in the afternoon — the upstream system did not send the file on Saturday, and the pipeline processed the empty file normally and loaded 0 rows.
The pipeline did not fail. There were no errors, no retries, and no alerts. The problem is that nobody knew that nothing had happened.
The four quality axes
| Axis | Question | Measurement |
|---|---|---|
| Freshness | How recent is the data | The difference between the latest record's time and now |
| Completeness | Is there as much as there should be | Count, null ratio |
| Uniqueness | Are there no duplicates | Number of duplicates per key |
| Validity | Do the values follow the rules | Range, format, referential integrity |
Each can be measured with a single line of query.
-- 신선도: 마지막 데이터가 몇 분 전인가
SELECT extract(epoch from now() - max(event_at))/60 AS lag_min FROM events;
-- 완전성: 어제 건수가 지난 7일 평균 대비 얼마인가
WITH d AS (
SELECT date_trunc('day', event_at) AS day, count(*) AS n
FROM events WHERE event_at >= now() - interval '8 days' GROUP BY 1)
SELECT (SELECT n FROM d ORDER BY day DESC LIMIT 1)::float
/ NULLIF(avg(n), 0) AS ratio FROM d;
-- 유일성
SELECT count(*) FROM (
SELECT order_id FROM orders GROUP BY 1 HAVING count(*) > 1) x;
-- 유효성
SELECT count(*) FROM orders WHERE amount < 0 OR status NOT IN ('NEW','PAID','CANCELLED');
Thresholds as ratios, not absolute values
A rule like "alert if the count is below 1000" soon becomes useless. As the service grows, you have to keep fixing the threshold, and nobody does.
Instead you compare it with itself.
- Yesterday's count / the average of the same weekday over the last 7 days → warn if below 0.5
- The null ratio has increased to 2 times or more compared with last week
- The freshness lag exceeds 3 times the usual p95
If you ignore the weekday effect, you get false positives every Monday. For a service with less weekend traffic, "compared with yesterday" raises a false alarm every Monday morning. Compare the same weekdays with each other.
Data contracts
Quality checks are a reactive response. The fundamental fix is to reach an agreement with upstream.
A data contract is an explicit promise between a producer and a consumer.
dataset: orders
owner: order-team
schema:
order_id: { type: string, required: true, unique: true }
amount: { type: decimal, required: true, min: 0 }
status: { type: enum, values: [NEW, PAID, CANCELLED] }
created_at: { type: timestamp, required: true }
sla:
freshness: 30m # 30분 이내 데이터가 있어야 함
completeness: 0.99 # 널 비율 1% 미만
breaking_change: 30일 전 공지
Two things change once there is a contract.
- Responsibility becomes clear. It is no longer "it broke because the schema changed" but "the contract was violated."
- Automated validation becomes possible. The contract file itself becomes the check rules.
Where to put the checks
추출 → [입력 검사] → 변환 → [출력 검사] → 적재 → [사후 검사]
- Input check — Does what upstream gave us match the contract? If you block here, contamination does not spread downstream.
- Output check — Is the result our transformation produced correct? Count conservation, matching totals.
- Post-load check — The freshness and completeness of the loaded table. It runs periodically.
The most common mistake is having only the post-load check. Then you learn about it only after contaminated data has already spread to dashboards and other pipelines.
How to handle a failure
When a check fails, there are three options.
| Response | When |
|---|---|
| Stop (fail) | When downstream must not use wrong data. Payment, settlement |
| Quarantine | When only part of it is a problem. Set aside only the bad rows and proceed with the rest |
| Warn | When even low quality is better than nothing. Exploratory data |
Stopping unconditionally will not do — if the whole thing stops for a trivial problem, people turn the checks off. Warning unconditionally will not do either — nobody looks. You must decide for each dataset.
What it looks like in the field
- The upstream did not send the file but it "succeeded" with 0 rows loaded → no freshness or completeness check.
- When a column was added to the schema, parsing shifted and values were off by one column → no input check.
- The count doubled after reprocessing → no uniqueness check.
What to check in the next quiz
You implement the four quality axes as queries and apply them to a real dataset, and detect anomalies with weekday-aware relative thresholds. You create a contract violation and confirm that the checks catch it, and apply the three responses of stop, quarantine, and warn distinctly.