Why a valid row can still be wrong

Description

This lesson stops, with the same precision a forensic examiner brings to a scene, on a single row: ORD-9509. Not to build any new tool yet — that starts in lesson 4 — but to dissect, check by check, exactly why it's a valid row and exactly why, at the same time, it's wrong. By the end of this lesson, the distinction between those two words — validity and correctness — stops being an abstract concept and becomes something you can point to, line by line of code.

Connection to the module. This lesson closes the diagnosis lessons 1 and 2 started: you already know the four earlier tools don't flag ORD-9509, and you already know why accuracy, in general, is the hardest dimension. This lesson connects those two facts to the specific case, check by check, leaving the ground ready for lesson 4 to start building the baseline that's finally going to catch it.

An analogy: the detective who checks the alibi, not the ID document

A customs officer reviewing a passport does one kind of check: does the document have the correct format? is the hologram genuine? has the expiration date not passed yet? All of these are questions about the document itself, and a well-made forged passport can pass every one of them. A detective investigating whether that person was really where they say they were does a completely different kind of check: they don't care whether the passport "looks good" — they cross-reference the date and place against other independent sources (security cameras, receipts, witnesses) to confirm whether the story, as a whole, holds up.

OrdersSchema and validate_orders() are the customs officer: they review the document — the row — and ORD-9509 has an impeccable passport. This lesson does the detective's work: it doesn't look at the document again, it cross-references its content against independent sources — P002's other three prices in the same file, the price from the complete canonical week — to confirm that the story that number, 60.00, tells doesn't hold up.

Worked example: every check, applied to ORD-9509, one by one

The complete row, exactly as it appears in orders_2026-08-14.csv:

ORD-9509,S04,P002,1,60.00,2026-08-14T09:04:00

It's worth walking through every check that already exists in this guide, in exactly the order they were built, and confirming each one's result against this specific row.

# ord_9509_forensics.py
import polars as pl
import pandera.polars as pa


class OrdersSchema(pa.DataFrameModel):
    order_id: str = pa.Field(unique=True)
    unit_price: float = pa.Field(nullable=False, ge=0)
    quantity: int = pa.Field(gt=0)


ord_9509 = {
    "order_id": "ORD-9509",
    "store_id": "S04",
    "product_id": "P002",
    "quantity": 1,
    "unit_price": 60.00,
    "order_ts": "2026-08-14T09:04:00",
}

print("=== ORD-9509, check by check ===\n")

# 1. completeness (module 1/2): is unit_price present?
print(f"1. completeness -- unit_price present and not empty? {ord_9509['unit_price'] is not None}")

# 2. uniqueness (module 1/2): does order_id repeat in the file?
print(f"2. uniqueness -- does 'ORD-9509' appear only once in the file? True (it's not ORD-9502)")

# 3. validity (module 1/2): quantity > 0? unit_price >= 0?
print(f"3. validity -- quantity > 0? {ord_9509['quantity'] > 0} | unit_price >= 0? {ord_9509['unit_price'] >= 0}")

# 4. consistency (module 3): does product_id exist in dim_product?
known_product_ids = {"P001", "P002", "P003", "P004"}
print(f"4. consistency -- does product_id='{ord_9509['product_id']}' exist in dim_product? "
      f"{ord_9509['product_id'] in known_product_ids}")

# 5. Pandera end to end, on the isolated row
df = pl.DataFrame({k: [v] for k, v in ord_9509.items() if k != "order_ts"})
validated = OrdersSchema.validate(df, lazy=True)
print(f"\n5. OrdersSchema.validate() -- exception raised? False (passed with no issue)")

print("\n=== Result: 4 of 4 EXISTING checks, all OK ===")
print("None of modules 1-4's four tools has any way to flag this row.")

What to expect. Running python3 ord_9509_forensics.py, the output is exactly this:

=== ORD-9509, check by check ===

1. completeness -- unit_price present and not empty? True
2. uniqueness -- does 'ORD-9509' appear only once in the file? True (it's not ORD-9502)
3. validity -- quantity > 0? True | unit_price >= 0? True
4. consistency -- does product_id='P002' exist in dim_product? True

5. OrdersSchema.validate() -- exception raised? False (passed with no issue)

=== Result: 4 of 4 EXISTING checks, all OK ===
None of modules 1-4's four tools has any way to flag this row.

Four different checks, four "correct" answers — and yet, ORD-9509 is wrong. The block's fifth line, the one none of the earlier four can answer, is the only one that truly matters: does 60.00 look like what an Energy Bar normally costs? That question needs something not found anywhere in this script — an external reference number, calculated over data already confirmed reliable.

The detective, with the sources actually available

The detective in the analogy doesn't need to wait for lesson 4 to start suspecting — already, inside S04's own file, there's enough evidence to raise an informal alert:

# ord_9509_within_file_evidence.py
p002_prices_in_file = {
    "ORD-9502 (1st appearance)": 1.20,
    "ORD-9509": 60.00,
    "ORD-9502 (2nd appearance)": 1.20,
    "ORD-9511": 1.20,
}

print("All of P002's prices within the same file orders_2026-08-14.csv:\n")
for order_id, price in p002_prices_in_file.items():
    print(f"  {order_id}: {price}")

other_prices = [p for oid, p in p002_prices_in_file.items() if oid != "ORD-9509"]
print(f"\nP002's other three appearances in this file: {other_prices}")
print(f"ORD-9509 is the only one that differs -- 3 of 4 appearances agree on 1.20")

What to expect.

All of P002's prices within the same file orders_2026-08-14.csv:

  ORD-9502 (1st appearance): 1.2
  ORD-9509: 60.0
  ORD-9502 (2nd appearance): 1.2
  ORD-9511: 1.2

P002's other three appearances in this file: [1.2, 1.2, 1.2]
ORD-9509 is the only one that differs -- 3 of 4 appearances agree on 1.2

This is exactly the manual diagnosis module 1, lesson 7 already built — comparing P002 against the file's own median — and you already know, from that same lesson's deep-dive, why it isn't the right architecture for real detection: if S04 had sent only a single P002 row that day, there would be no "other three appearances" to compare against, and the error would have stayed invisible even to this manual diagnosis. A real baseline needs to live outside the file under review, not depend on that same file happening to have enough rows of the same product for a pattern to show.

Diagram: the four layers of checking, and the fifth that's missing

flowchart TD
    A["ORD-9509"] --> B{"1. Completeness:\nis unit_price present?"}
    B -->|"Yes"| C{"2. Uniqueness:\ndoes order_id repeat?"}
    C -->|"No"| D{"3. Validity:\nquantity>0, unit_price>=0?"}
    D -->|"Yes"| E{"4. Consistency:\nproduct_id in dim_product?"}
    E -->|"Yes"| F{"5. Accuracy:\nis unit_price close to\nP002's normal price?"}
    F -->|"?? -- none of\nmodules 1-4's tools\ncan answer this"| G["Module 5:\nreference_prices +\ncheck_price_baseline()"]

Four layers, four consecutive "Yes"es, and the fifth layer — the only one that truly matters for this row — with no way to evaluate it yet. The diagram makes visually clear something lesson 1 already insisted on: it isn't that the row "slips through" a crack between existing checks — it's that the fifth layer, literally, doesn't exist until this module.

Going deeper: why the "within the same file" comparison is a diagnosis, not a solution

It's worth pausing on the exact difference between what this lesson's earlier block did — comparing against P002's other three appearances in the same file — and what lesson 4 is going to build. The within-file comparison has three concrete problems, not just one:

First, it depends on the sample's luck: S04 sent four P002 rows that day, and three of them turned out to be correct — but nothing guarantees the next problematic file will have the same proportion. Second, it's circular: if ORD-9509's error had, instead, been that P002's first three appearances were wrong (say, if the whole file had a systematic unit error) and only ORD-9509 had the correct price, comparing "against the same file's majority" would flag exactly the wrong row as suspicious. Third, and most importantly, it doesn't scale: every new file would need enough repetitions of the same product for any pattern to be visible, something that can't be guaranteed for a new store with few sales, which is S04's real case.

The baseline lesson 4 builds solves all three problems at once, with a single design decision: calculate it over a source completely separate from the data under suspicion — Kiosko's canonical week, forty rows that eight earlier guides in this ecosystem already confirmed, independently, as reliable. It doesn't matter how many P002 rows S04's file brings on a specific day, or whether the whole file had a systematic error: the baseline doesn't depend on it at all.

Common mistakes

Thinking "most rows agree" is, on its own, enough evidence that the majority is correct. What happens: someone, seeing that three of four P002 appearances in S04's file agree on 1.20, concludes that agreement alone proves 1.20 is the correct price, with no other source needed. Why it happens: a majority within a small sample feels like solid evidence. How to spot it: review this lesson's Going deeper section — comparing against the same file's majority is circular in the general case, even though in this specific case the majority turned out to be right. How to fix it: use within-file agreement as an early, informal alert signal (exactly what module 1, lesson 7 did), never as the final decision mechanism — the real source of truth is an independent baseline, built outside the file under suspicion.

Looking for a fifth "type or range" check that could have caught this row. What happens: someone, after seeing that unit_price >= 0 isn't enough, tries to imagine a stricter range rule — say, unit_price <= 10.00 — that would have flagged 60.00. Why it happens: it seems like "tightening the range" is a natural extension of what you already know how to do. How to spot it: ask yourself what would happen to P004 (Phone Charger Cable, normal price 4.50) if that same <= 10.00 range applied equally to every product — it would still flag nothing, and a real P004 error of 45.00 also wouldn't get flagged because 45.00 > 10.00 would be the wrong limit for that specific product either way. How to fix it: a fixed range, the same for every product, can't capture that each product has its own normal price — you need a range per product, which, in practice, is already exactly what a baseline does: a different reference value for each product_id, not a single magic number for the whole catalog.

Concluding this lesson has already "solved" accuracy, because it identified the problem with precision. What happens: someone, satisfied with this lesson's forensic diagnosis, assumes the detection work is already done. Why it happens: the analysis feels complete — the exact row got named, exactly why every existing check lets it through got explained. How to spot it: no code block in this lesson returns a reusable result (a function you could run on any future Kiosko file) — everything is manual diagnosis, written once, for this specific row. How to fix it: this lesson diagnoses; lesson 4 onward builds. It's the same difference module 1, lesson 7 already drew, precisely, between a one-off script and an integrated tool.

Exercises

Exercise 1 — Reproduce the forensic analysis with ORD-9508 instead of ORD-9509. Repeat this lesson's same five-check exercise, but for ORD-9508 (product_id="P099", unit_price=1.00). At exactly which check does it differ from ORD-9509, and why is the rest of the analysis identical?

See solution

ORD-9508 passes exactly the same checks 1, 2, and 3 as ORD-9509 (completeness, uniqueness, validity — all correct). It differs at check 4, consistency: product_id="P099" in known_product_ids is False, because P099 was never registered in Kiosko's four-product catalog. ORD-9509, by contrast, passes check 4 with no problem — P002 does exist —, and fails exclusively at the fifth layer, accuracy, which none of modules 1 through 4's tools can evaluate. This exercise precisely confirms the difference between S04's incident's two silent rows: one has a reference problem (the product doesn't exist), the other has a magnitude problem (the product exists, the price doesn't make sense).

Exercise 2 — Construct a hypothetical row that fails accuracy AND consistency at the same time. With no code, describe what fields an S04 row would have that violated both dimensions at once — a product that doesn't exist, with a price also out of the ordinary. Does it even make sense, in this case, to talk about "the normal price" of a product that doesn't exist?

See solution

A row like that could be, for example, product_id="P099", unit_price=999.00. Consistency would flag it immediately — P099 isn't in dim_product — but accuracy, as designed in this guide, wouldn't make any sense applied to it: reference_prices (which lesson 4 builds) only has entries for P001 through P004, so there would be no reference value at all to compare 999.00 against. This is exactly why check_price_baseline(), in lesson 5, has to explicitly decide what to do with rows whose product_id has no known baseline — excluding them from this specific check, letting validate_referential_integrity() (module 3) be the tool that catches them, each with its own responsibility, with neither trying to cover the other's job.

Exercise 3 — Argue whether ORD-9509 "fooled" the system, or whether the system simply never promised to catch it. In 2-3 sentences, and using the distinction between "didn't try" and "couldn't" module 1, lesson 7 already drew, argue why describing this row as having "fooled" OrdersSchema is an imprecise way of telling what happened.

See solution

"Fooling" implies the system tried to detect the problem and got outsmarted — but OrdersSchema, as declared in modules 2 and 4, never promised to check whether a price "makes sense" compared to the product's history; its explicit contract is type, nullability, uniqueness, and simple range. ORD-9509 didn't fool anything — it simply answered, with total honesty, the only five questions (well, four, until this module) this guide's entire system knew how to ask so far. It's the exact same distinction module 1 already made: the row didn't find a crack in an existing check, it found a kind of check that didn't exist yet.

Summary and next step

In this lesson you dissected ORD-9509 check by check, with executed evidence: completeness, uniqueness, validity, and consistency, all four, confirm the row is perfectly well-formed. You also saw the informal diagnosis module 1 already offered — comparing against P002's other appearances in the same file — and understood, precisely, why that method is useful as an early alert but insufficient as a real solution: it depends on the sample's luck, it can be circular, and it doesn't scale to files with few repetitions of the same product.

Before moving on you should be able to: name, from memory, the four checks ORD-9509 passes and explain why each one lets it through; and explain the three concrete problems of comparing a file against its own median, instead of against an external baseline.

You have the complete diagnosis, closed with the same precision that opened the module. Lesson 4 finally starts building: reference_prices, the baseline calculated over the only portion of Kiosko's data already confirmed reliable across eight earlier guides in this ecosystem.

Resources

• Module 1, lesson 7, of this same guide ("What slips through a local gate") — the source of the within-file median diagnosis, and of its explicit critique as an insufficient solution. src/guides/data-reliability-and-governance-guide/workbook/module-01-when-green-does-not-mean-correct/en/07-what-slips-through-a-local-gate.md. In English.
• Pandera — official documentation (DataFrameModel.validate(), behavior on a single-row DataFrame). pandera.readthedocs.io. In English.
• Polars — official DataFrame documentation (construction from a dictionary of lists, the pattern used in this lesson's worked example). docs.pola.rs. In English.
• This guide's DESIGN — the exact structure of orders_2026-08-14.csv's twelve rows, including ORD-9509. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.