What slips through a local gate

Description

Lesson 6 left two silent rows inside valid: ORD-9508 (product_id=P099, a product that doesn't exist) and ORD-9509 (unit_price=60.00, a price off by two orders of magnitude). This lesson doesn't run any new validate_orders() code — you already know exactly what it does — instead, it writes the minimal code, outside that function, needed to make those two rows visible, and uses that exercise to explain precisely why a local gate — one that only looks at one row at a time, with no external reference — could never have caught them, no matter how many more rules got added to it.

Connection to the module. This lesson closes the module's technical diagnosis, and lays the ground for modules 2 through 6 of this guide: each one solves, with a specific tool, exactly one of the gaps this lesson names precisely.

An analogy: the spell-checker that doesn't know history

A spell-checker reviews a text word by word: is this sequence of letters a real word in the language? It's extraordinarily good at that specific task, and it solves it by looking at each word in isolation, with no need to know anything about the rest of the document. But a spell-checker has no way of detecting that the sentence "Christopher Columbus reached America in 1592" has an error — every individual word is spelled perfectly correctly. The error is one of fact, not of spelling, and a checker designed to verify letter by letter is never going to catch it, no matter how many more grammar rules you add to it: checking historical facts isn't, and can't become, an extension of checking spelling. They're two completely different kinds of verification, needing completely different tools.

validate_orders() is that spell-checker, applied to orders rows. It's extraordinarily good at reviewing each row in isolation: does it have all the fields? Are the types correct? Do the values respect a simple range? But ORD-9508 with product_id=P099 is, to validate_orders(), a perfectly "well-spelled" row — every character, every type, every individual value passes review. The error isn't one of form, it's one of fact: P099 isn't a product that exists in Kiosko's real world. And that kind of error needs something no simple range rule can provide: a reference to another source of truth, in this case, the real product catalog.

Worked example: making visible what validate_orders() doesn't see

This code doesn't replace or extend validate_orders() — it lives entirely apart, as a manual diagnosis, exactly what befits a module that doesn't build the real solution yet:

# what_slips_through.py
import csv
from kiosko import PRODUCTS

KNOWN_PRODUCT_IDS = {p["product_id"] for p in PRODUCTS}

with open("orders_2026-08-14.csv", newline="") as f:
    rows = list(csv.DictReader(f))

# 1. consistency: rows with a product_id that doesn't exist in the catalog
orphan_rows = [row for row in rows if row["product_id"] not in KNOWN_PRODUCT_IDS]
print("=== Rows with an orphan product_id (consistency) ===")
for row in orphan_rows:
    print(f"  {row['order_id']}: product_id='{row['product_id']}' is not in {sorted(KNOWN_PRODUCT_IDS)}")

# 2. accuracy: compare each P002 price against the other P002 prices in the SAME file
p002_rows = [row for row in rows if row["product_id"] == "P002"]
p002_prices = [float(row["unit_price"]) for row in p002_rows]
print(f"\n=== All P002 prices in this file (accuracy) ===")
for row in p002_rows:
    print(f"  {row['order_id']}: unit_price={row['unit_price']}")

typical_price = sorted(p002_prices)[len(p002_prices) // 2]  # the median, without hand-picking the outlier row
print(f"\nTypical P002 price in this file (median): {typical_price}")
for row in p002_rows:
    price = float(row["unit_price"])
    ratio = price / typical_price
    flag = " <-- out of the ordinary" if ratio > 2 or ratio < 0.5 else ""
    print(f"  {row['order_id']}: unit_price={price} ({ratio:.1f}x the typical){flag}")

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

=== Rows with an orphan product_id (consistency) ===
  ORD-9508: product_id='P099' is not in ['P001', 'P002', 'P003', 'P004']

=== All P002 prices in this file (accuracy) ===
  ORD-9502: unit_price=1.20
  ORD-9509: unit_price=60.00
  ORD-9502: unit_price=1.20
  ORD-9511: unit_price=1.20

Typical P002 price in this file (median): 1.2
  ORD-9502: unit_price=1.2 (1.0x the typical)
  ORD-9509: unit_price=60.0 (50.0x the typical) <-- out of the ordinary
  ORD-9502: unit_price=1.2 (1.0x the typical)
  ORD-9511: unit_price=1.2 (1.0x the typical)

Notice something real that just happened: ORD-9502 appears twice in this list, in first and third place, because it appears twice in the file — the duplicate retransmission you already know from lesson 6 — and therefore twice in p002_rows. Not even this manual diagnosis, written for an introductory lesson, is immune to a duplicate sneaking into a count if you're not careful: four price lines, not three, even though only three distinct order_ids are involved. The figure that actually matters for this lesson's point stays just as clear: ORD-9509, with unit_price=60.00, is the only row flagged <-- out of the ordinary, at 50 times P002's typical price within this same file — you didn't even need the canonical week's history to see it, the anomaly is obvious just comparing the file against itself.

Diagram: why adding "more rules" to validate_orders() isn't enough

flowchart TB
    subgraph LOCAL["What a local rule CAN check"]
        A["Does this field exist?"]
        B["Does this value have\nthe correct type?"]
        C["Is this value within\na fixed range, known ahead of time?"]
    end

    subgraph EXTERNO["What needs an EXTERNAL reference"]
        D["Does this product_id exist\nin the product table?"]
        E["Does this price resemble\nwhat this product\nnormally costs?"]
    end

    LOCAL -->|"validate_orders() lives here"| F["Local gate:\nenough for A, B, C"]
    EXTERNO -->|"needs another table\nor a baseline"| G["Local gate:\nstructurally blind here"]

The diagram distinguishes, precisely, between two kinds of rule. A local rule — "quantity must be greater than zero" — can be written and evaluated by looking only at one column's value, within a single row, needing nothing else. A rule that needs an external reference — "product_id must exist in the product table," "unit_price must resemble this product's normal price" — needs, by definition, something that lives outside the row: another table, an aggregate calculation over historical data, a baseline. validate_orders(), as foundations M5 designed it, never receives either of those two things as an argument — it only receives the list of raw rows. It's not that it's missing one more rule; it's missing a kind of input its current design doesn't account for.

Going deeper: the difference between "didn't try" and "couldn't"

It's worth being precise about a distinction already hinted at in lesson 4: validate_orders() didn't "fail" to catch ORD-9508 and ORD-9509 in the sense that it tried and got it wrong. It never tried, because its signature — validate_orders(rows: list[dict]) — only receives the rows themselves, with no additional parameter representing "the catalog of valid products" or "reference historical prices." You could, in theory, modify the signature to receive that extra data — validate_orders(rows, known_products, reference_prices) —, but the moment you do that, you've stopped extending a schema/nulls/type/range gate: you're building a different system, with a different kind of dependency (an external table, a computed baseline), that deserves its own architecture, not one more parameter tacked onto a function that already fulfills its original purpose.

This is, precisely, the design reason behind this entire guide's structure: module 3 doesn't modify validate_orders() to receive dim_product as an argument — it builds a new, separate function, validate_referential_integrity(orders_df, dim_product_df), with its own responsibility. Module 5 doesn't modify check_business_rules() to receive a price baseline — it builds check_price_baseline(), also separate. Every dimension that needs an external reference earns its own piece, instead of bloating a function that was already complete for what it did.

Common mistakes

Trying to "fix" validate_orders() in this module, by adding a hand-written list of valid products. What happens: someone, motivated by seeing ORD-9508 unflagged, edits check_business_rules() to add if order.product_id not in {"P001", "P002", "P003", "P004"}: reasons.append(...), with the product list hard-coded directly into the code. Why it happens: it's the fastest, most obvious fix for the problem just seen. How to spot it: if your check_business_rules() now has a hand-written list of valid product_ids inside the function, you have a patch that works today, with four products, but that goes out of sync the moment Kiosko adds a fifth product anywhere else in the system (say, in the warehouse's dim_product) without remembering to also update this list. How to fix it: this module is deliberately about diagnosis, not fixing — the real solution, which reads the real catalog from kiosko.duckdb instead of hard-coding it, is this guide's module 3's whole job.

Thinking the median used in the worked example "already solves" accuracy. What happens: someone sees that comparing against the file's own median successfully caught the 60.00 price, and concludes that's the correct and sufficient method for this entire guide. Why it happens: it visibly worked, in this specific case. How to spot it: ask yourself what would happen if S04's file had only one P002 row — the median of a single-element list is that same element, so no price would ever get flagged as anomalous, no matter how absurd it was. How to fix it: the real accuracy baseline, which this guide's module 5 builds, is calculated over Kiosko's full canonical week — forty already-known, trustworthy rows — not over the new, potentially problematic file being reviewed. Comparing a suspicious file against itself is a useful diagnostic trick for this lesson, but it isn't the correct architecture for real anomaly detection.

Concluding this module "already solved" consistency and accuracy because the diagnosis named them. What happens: after seeing this lesson's code successfully catch ORD-9508 and ORD-9509 with a script of a few lines, someone assumes the work of this guide's modules 3 and 5 is already done. Why it happens: the diagnosis superficially feels similar to the solution. How to spot it: this lesson's script doesn't integrate into any pipeline, doesn't split rows into valid/rejected in a reusable way, doesn't read the real catalog from the warehouse, and its accuracy method (comparing against the same file's median) was already ruled out as insufficient in the previous common mistake. How to fix it: this code is exactly what it claims to be — a one-off, manual diagnosis, to visually confirm what slips past the old gate. The reusable, integrated solution, with real warehouse data, is the job of the seven modules that follow.

Exercises

Exercise 1 — Calculate the same ratio against the canonical week's price, not against the file's median. You already know, from data-engineering-foundations-guide, that P002 sold consistently at 1.20 throughout Kiosko's entire canonical week (2026-08-03 through 2026-08-09). Calculate ORD-9509's ratio (60.00) against that historical price, instead of against S04's file median. Does the result change?

CANONICAL_WEEK_PRICE_P002 = 1.20  # confirmed across the 40 rows of the canonical week, foundations M1-M2
ratio_vs_canonical = 60.00 / CANONICAL_WEEK_PRICE_P002
print(f"ratio against the canonical week's price: {ratio_vs_canonical}x")

ratio against the canonical week's price: 50.0x

Exercise 2 — Design, without implementing it, a third kind of external check. Besides consistency (against another table) and accuracy (against a baseline), can you think of another question about Kiosko's data that would need, just like these two, something external to the individual row? Describe it in 2-3 sentences, without writing code.

Exercise 3 — Argue why separating diagnostic logic from validate_orders() is the right decision, not a shortcut. In 3-4 sentences, and using the validate_referential_integrity() example mentioned in this lesson's Going deeper section, explain why building new, separate functions — instead of continuing to add parameters and ifs to validate_orders() — is a solid design decision, not just a way of postponing the work.

Summary and next step

In this lesson you made visible, with a separate diagnostic script — never integrated into validate_orders() —, exactly what lesson 6 left silent: ORD-9508 doesn't have a real product_id (P099 isn't in the four-product catalog), and ORD-9509 has a price fifty times higher than P002's normal one within the same file. And, more important than the specific finding, you understood why — with structural precision, not just as an observation — a gate that only looks at one row at a time could never catch these two problems without access to an external reference.

Before moving on you should be able to: explain the difference between "a local rule" and "a rule that needs an external reference"; name why adding parameters to validate_orders() isn't the right solution; and describe, without looking at the code again, what would need to exist — a table, a baseline — for each of the two silent rows to be caught in a reusable way.

You have this module's complete diagnosis: what the old gate catches, what slips through it, and why. Lesson 8 — the module's project — pulls it all together into a single diagnostic script, with a written report, closing this module before module 2 starts building the first real piece of the solution: declarative quality tests with Pandera.

Resources

• Joe Reis & Matt Housley, Fundamentals of Data Engineering (O'Reilly, 2022) — the data quality chapter explicitly distinguishes between schema/range checks and referential integrity checks. oreilly.com/library/view/fundamentals-of-data/9781098108298. In English.
• Python — official documentation on list and set (set) comprehensions, the basis for this lesson's orphan product_id filtering. docs.python.org/3/tutorial/datastructures.html#list-comprehensions. In English.
• Python — official sorted() documentation and the median calculation used in the worked example. docs.python.org/3/library/functions.html#sorted. In English.
• This guide's DESIGN — the exact specification of validate_referential_integrity() (module 3) and check_price_baseline() (module 5), the real solutions that replace this lesson's manual diagnosis. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.