Catching S04's orphan product

Description

This is this entire module's central lesson. ORD-9508, with product_id="P099", passed validate_orders() in module 1 with no flag. It passed OrdersSchema.validate() in module 2, also with no flag. Two different tools, two different modules, the same silent result. This lesson runs validate_referential_integrity() — the function lesson 4 built, with no changes — against S04's real, complete file, and closes the diagnosis module 1 opened: this guide's first tool to catch this row with evidence.

Connection to the module. Lessons 2 through 4 built the criteria and the tool. Lesson 5 added a second category of rules. This lesson is the complete application, over real data, of everything this module has built so far. Lesson 7 combines this result with Pandera's into a single report.

The material: the same twelve lines, the same dim_product

orders_s04 is still exactly the same file you opened in module 1 and validated with Pandera in module 2 — twelve lines, six clean, six broken, unchanged. dim_product, loaded in this module's lesson 4, has Kiosko's four real products: P001, P002, P003, P004. No new data enters this lesson — the point is, precisely, that the same evidence you already had since module 1 is enough to catch the problem, once you have the right tool.

Worked example: validate_referential_integrity(), run against the complete file

# catch_orphan_product.py
import duckdb
import polars as pl

con = duckdb.connect("kiosko.duckdb")
orders_df = con.sql("SELECT * FROM orders_s04").pl()
dim_product_df = con.sql("SELECT * FROM dim_product").pl()


def validate_referential_integrity(orders_df: pl.DataFrame, dim_product_df: pl.DataFrame) -> pl.DataFrame:
    """Rows from orders_df whose product_id does NOT exist in dim_product_df (anti-join)."""
    return orders_df.join(dim_product_df, on="product_id", how="anti")


orphans = validate_referential_integrity(orders_df, dim_product_df)

print(f"orders_df: {orders_df.height} rows | dim_product_df: {dim_product_df.height} rows")
print(f"orphans: {orphans.height} rows out of {orders_df.height}\n")
print(orphans)

print("\n=== Readable report ===")
for row in orphans.iter_rows(named=True):
    print(f"{row['order_id']} | store_id={row['store_id']} | product_id={row['product_id']} "
          f"(doesn't exist in dim_product) | quantity={row['quantity']} | unit_price={row['unit_price']}")

What to expect. Running python3 catch_orphan_product.py (with kiosko.duckdb containing both tables, module 2's orders_s04 and lesson 4's dim_product), the output is exactly this:

orders_df: 12 rows | dim_product_df: 4 rows
orphans: 1 rows out of 12

shape: (1, 6)
┌──────────┬──────────┬────────────┬──────────┬────────────┬─────────────────────┐
│ order_id ┆ store_id ┆ product_id ┆ quantity ┆ unit_price ┆ order_ts            │
│ ---      ┆ ---      ┆ ---        ┆ ---      ┆ ---        ┆ ---                 │
│ str      ┆ str      ┆ str        ┆ i64      ┆ f64        ┆ datetime[μs]        │
╞══════════╪══════════╪════════════╪══════════╪════════════╪═════════════════════╡
│ ORD-9508 ┆ S04      ┆ P099       ┆ 2        ┆ 1.0        ┆ 2026-08-14 08:56:00 │
└──────────┴──────────┴────────────┴──────────┴────────────┴─────────────────────┘

=== Readable report ===
ORD-9508 | store_id=S04 | product_id=P099 (doesn't exist in dim_product) | quantity=2 | unit_price=1.0

There it is, with executed evidence: exactly one row, ORD-9508, exactly the row module 1 flagged as "silent" and module 2 confirmed, again, kept passing clean. validate_referential_integrity() needed no adjustment, no additional rule, no special exception for this case — the same three-line function you already tested against toy data in lesson 4 catches, with no changes, the real case. The other eleven rows — including the three OrdersSchema already caught (ORD-9502 x2, ORD-9503, ORD-9507) and the accuracy row that's still unresolved (ORD-9509) — disappear from the anti-join's result, because they all have a product_id that really exists in dim_product. The anti-join doesn't care whether a row has other problems — it only asks about referential integrity, and answers exclusively about that.

Table: the same row, three tools, three modules

Three completely different tools, three ways of working — imperative code, declarative schema, cross-table comparison —, and only the third can, by design, ask the right question. This isn't a criticism of the first two: each fulfilled exactly what it promised. It's the final confirmation of the argument this module opened in lesson 1: the right tool depends on the question, not on how sophisticated the tool is in general.

Diagram: ORD-9508's complete timeline

flowchart LR
    A["Module 1, lesson 6:\nreal validate_orders()\nORD-9508: valid (unflagged)"] --> B["Module 1, lesson 7:\nthe problem gets named,\nno tool to catch it"]
    B --> C["Module 2, lessons 7-8:\nOrdersSchema.validate()\nORD-9508: still unflagged"]
    C --> D["Module 3, lesson 6 (this one):\nvalidate_referential_integrity()\nORD-9508: CAUGHT"]

Four points in time, the same row. The diagram shows no change in the data — ORD-9508 was never modified —, it shows this guide's tools advancing, each module building on the previous one, until the right question finally got asked.

Going deeper: what did NOT change after this lesson

Before celebrating, it's worth being precise about this result's scope. orphans has a single row. That means this lesson solved one of module 1's six data quality dimensions — consistency —, not one more. ORD-9509, with unit_price=60.00, remains exactly where module 2 left it: product_id="P002" exists perfectly in dim_product, so validate_referential_integrity() never flags it — there's no referential integrity problem in that row, the problem is a completely different kind. You can confirm it yourself with one line:

# confirm_9509_still_clean.py -- continuation of catch_orphan_product.py
print(f"\n'ORD-9509' is in orphans: {'ORD-9509' in orphans['order_id'].to_list()}")

What to expect.

'ORD-9509' is in orphans: False

Confirmed: ORD-9509 doesn't appear in orphans, and it shouldn't — the 60.00 price is an accuracy problem, not a consistency one, and that dimension has its own tool in this guide's module 5, with its own logic (a historical price baseline, not an existence catalog). This module closes exactly one gap, precisely, without pretending to close the ones that aren't its job.

Common mistakes

Expecting orphans to have more than one row, "for the module to be worth it." What happens: someone, seeing the result has a single row, wonders if they did something wrong, expecting a more "dramatic" result. Why it happens: after seeing 3- and 4-row reports in modules 1 and 2, a single row can feel like too little. How to spot it: revisit module 1's original twelve-line table — only one row (ORD-9508) specifically has a consistency problem. Every data quality dimension in this guide corresponds to exactly one row in S04's incident (except uniqueness, which counts both duplicate appearances). How to fix it: the correct number of orphan rows for this file is one — a single-row result, exact and correct, isn't a sign something failed in your code.

Being surprised orphans doesn't include any of the three rows OrdersSchema already caught. What happens: someone expects to see ORD-9502, ORD-9503, or ORD-9507 also in the anti-join's result, because they "already know" those rows have problems. Why it happens: it's easy to think of "problem rows" as a single category, instead of remembering that each data quality dimension is a different question, and each row in S04's incident breaks exactly one. How to spot it: check those three rows' product_id — P002, P003, P001, in that order — all three exist perfectly in dim_product. How to fix it: validate_referential_integrity() can only flag referential integrity problems; a row with a null unit_price or a negative quantity, but with a product_id that does exist, is completely invisible to this function, by design — it isn't an oversight, it's exactly the scope it belongs to.

Concluding that, with this result, S04 is now "fully validated." What happens: someone, satisfied with this lesson's clean, precise result, reports that S04's file already went through a complete quality check. Why it happens: after three consecutive modules in this guide, it's easy to lose track of how much is still left. How to spot it: count how many of the six data quality dimensions have, at this point in the guide, a real, executed check: completeness, uniqueness, validity (module 2), consistency (this module) — four of six. How to fix it: lesson 8 — this module's project — does exactly that count precisely, and confirms which two dimensions remain open: accuracy (module 5) and freshness (module 6).

Exercises

Exercise 1 — Confirm the three rows Pandera already caught have a valid product_id. Without running anything yet, write down from memory the product_id of ORD-9502, ORD-9503, and ORD-9507 (check module 1's CSV if needed), and confirm all three are in the list ['P001', 'P002', 'P003', 'P004'].

Exercise 2 — Verify the count of dimensions covered so far, with code. Write a small script that combines OrdersSchema.validate()'s result (module 2) with this lesson's orphans, and counts how many of orders_s04's twelve rows have at least one problem known up to this point in the guide.

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)

try:
    OrdersSchema.validate(orders_df, lazy=True)
    pandera_ids = set()
except pa.errors.SchemaErrors as exc:
    pandera_ids = set(
        exc.failure_cases.with_columns(
            pl.col("index").map_elements(lambda i: orders_df["order_id"][i], return_dtype=pl.String).alias("order_id")
        )["order_id"].to_list()
    )

referential_ids = set(orphans["order_id"].to_list())
all_flagged = pandera_ids | referential_ids

print(f"Pandera: {sorted(pandera_ids)}")
print(f"Referential: {sorted(referential_ids)}")
print(f"Total distinct order_id with a known problem: {len(all_flagged)} of {orders_df.height}")

Pandera: ['ORD-9502', 'ORD-9503', 'ORD-9507']
Referential: ['ORD-9508']
Total distinct order_id with a known problem: 4 of 12

Exercise 3 — Argue whether validate_referential_integrity() would need to change if S04 sent a second file tomorrow, with a different orphan product. In 2-3 sentences, explain why the function, as written, wouldn't need any change if S04 sent orders_2026-08-17.csv tomorrow with, say, product_id="P200" instead of "P099".

Summary and next step

In this lesson you ran validate_referential_integrity(), with no changes from lesson 4, against orders_2026-08-14.csv's twelve real lines and dim_product's four real products. The result: exactly one orphan row, ORD-9508, the same row two earlier tools in this guide — validate_orders() in module 1, OrdersSchema in module 2 — let through with no flag at all. You also confirmed, with code, that the result doesn't spill over onto the other rows: the three Pandera already caught remain outside this result (they have a valid product_id), and ORD-9509 remains completely silent (its problem isn't a referential integrity one).

Before moving on you should be able to: explain why this lesson's correct result has exactly one row, no more, no less; and name, without looking at the code again, which four distinct order_ids have, at this point in the guide, at least one known problem.

You have the row caught. Lesson 7 combines this result with OrdersSchema's (Pandera) and with check_retransmission_consistency()'s (lesson 5) into a single report — the first draft of the complete trust system this guide builds across its eight modules.

Resources

• Module 1, lesson 6, of this same guide — the first time ORD-9508 shows up in the real file, passing validate_orders() with no flag. src/guides/data-reliability-and-governance-guide/workbook/module-01-when-green-does-not-mean-correct/es/06-running-the-old-quality-gate-on-s04.md. In Spanish.
• Module 2, lesson 7, of this same guide — the confirmation, with Pandera, that ORD-9508 remains unflagged after OrdersSchema.validate(lazy=True). src/guides/data-reliability-and-governance-guide/workbook/module-02-declarative-data-quality-tests-with-pandera/es/07-validity-checks-and-reading-failure-cases.md. In Spanish.
• Polars — "Joins" (official user guide), the same reference from lesson 4, reused here against real data. docs.pola.rs/user-guide/transformations/joins. In English.
• This guide's DESIGN — the exact row (ORD-9508, product_id="P099") this module was meant to catch, confirmed here with executed evidence. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.