Module introduction: consistency and referential checks

Why this module exists

Module 2 closed with a complete OrdersSchema — three Pandera rules, actually run against orders_2026-08-14.csv's twelve lines — and with a number that repeated, unchanged, since module 1: eight of the twelve rows pass with no error, but two of those eight still have real problems. ORD-9508, with product_id="P099", a product that doesn't exist in Kiosko's four-product catalog. ORD-9509, with unit_price=60.00 for an Energy Bar, fifty times the usual price. Two completely different tools — a hand-written Python function (module 1), a declarative Pandera schema (module 2) — independently arrived at exactly the same blind spot.

That isn't a coincidence, and it isn't a bug in either tool. It's a structural limit: both validate_orders() and OrdersSchema look at one row at a time, from a single table. They can ask ORD-9508 whether its product_id is a non-empty string — it is —, but neither can ask "does that product really exist in Kiosko's catalog?", because that question needs to look at another table. This module builds the missing piece: a check that compares orders_s04 against dim_product, able to answer exactly the question no earlier tool in this guide could ask.

Connection to the previous module. This module picks up exactly the row where module 2's project left off: ORD-9508, silent, passing OrdersSchema.validate() with no flag at all. The difference isn't the tool this time — Pandera is still part of the apparatus —, it's the scope: instead of a schema that describes one table, this module writes a check that compares two.

An analogy: the boarding pass that says "seat 500" on a 200-seat plane

Imagine you board a plane and show your boarding pass to the flight attendant. The pass reads: passenger name, flight number, seat 52C. Each of those fields, checked separately, is perfectly well-formed — the name is text, the flight number follows the expected pattern, 52C has the exact shape of a valid seat: a number followed by a letter between A and E. A check that only reviewed each field's shape — exactly what validate_orders() and OrdersSchema did — would say the pass is perfectly valid. And yet, when the flight attendant walks back to row 52, they don't find it: this plane, a regional jet, only has 40 rows. Seat 52C, well-formed in every sense, doesn't exist on this particular plane.

This code, actually executed, makes exactly that distinction:

# boarding_pass_intro.py
VALID_SEATS = {f"{row}{letter}" for row in range(1, 41) for letter in "ABCDE"}


def seat_is_well_formed(seat: str) -> bool:
    """A typical shape check: is it text, with the number+letter pattern.
    This is what validate_orders() and OrdersSchema already did."""
    return bool(seat) and seat[:-1].isdigit() and seat[-1] in "ABCDE"


boarding_pass = {"passenger": "M. Nieva", "seat": "52C"}

print(f"Total seats on the plane: {len(VALID_SEATS)}")
print(f"seat_is_well_formed('{boarding_pass['seat']}'): {seat_is_well_formed(boarding_pass['seat'])}")
print(f"'{boarding_pass['seat']}' exists in VALID_SEATS: {boarding_pass['seat'] in VALID_SEATS}")

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

Total seats on the plane: 200
seat_is_well_formed('52C'): True
'52C' exists in VALID_SEATS: False

There's the complete distinction, in two lines of result. seat_is_well_formed('52C') is True — the field is perfectly well-formed, exactly the kind of question a single-table schema already knows how to ask. '52C' exists in VALID_SEATS is False — a completely different question, one that needs to compare the value against an external catalog (this plane's real seat list), not against a text pattern. ORD-9508's product_id="P099" is, precisely, the same case: a perfectly valid string (seat_is_well_formed would say yes) that doesn't exist in Kiosko's real catalog (VALID_SEATS would say no). This module builds the exact equivalent of '52C' in VALID_SEATS, but against dim_product, with real data, using Polars.

Diagram: two different questions, about the same value

flowchart TD
    A["product_id = 'P099'"] --> B{"Question 1:\nis it non-empty text?"}
    B -->|"Yes"| C["validate_orders() /\nOrdersSchema: PASSES"]
    A --> D{"Question 2:\ndoes it exist in dim_product?"}
    D -->|"No"| E["This module: FAILS\n(anti-join)"]

The diagram has two arrows coming out of the same value, toward two different questions, with two opposite results. P099 never changed value between the two questions — what changed is what got asked. This is, in one picture, this entire module's argument.

The map of this guide's 8 modules (reminder)

The map of this module

Lesson    Question it answers
────────  ──────────────────────────────────────────────────────────────
L1        (this one) Why a single-table schema isn't enough
L2        What does "consistency" mean BETWEEN tables, precisely?
L3        Why can a declarative schema, on its own, never see a
          referential bug?
L4        Write validate_referential_integrity(): the Polars
          anti-join, first over toy data
L5        Consistency rules WITHIN a table (cross-column): a
          retransmission, evaluated by its content, not just its ID
L6        Run the anti-join against real S04: catching ORD-9508
L7        Combine Pandera (OrdersSchema) with this module's
          custom checks, into a single report
L8        Project: S04's complete consistency report

Lessons 2 and 3 build the criteria: what "consistency" precisely means (a property of a relationship between tables, not of an isolated value), and why no declarative single-table schema — not Pandera's, not any other's — can, by design, check it. Lesson 4 writes this module's central function, validate_referential_integrity(), and tests it first against a small, controlled example, before touching S04's real data. Lesson 5 opens a second category of rules — ones that compare columns within the same table, needing no external table — with a concrete Kiosko case: confirming a duplicate retransmission carries the same content, not just the same order_id. Lesson 6 is the module's central moment: it runs the anti-join against S04's real file, and catches, with executed evidence, the row two earlier tools let through. Lesson 7 combines everything — Pandera and this module's hand-written checks — into a single report. And lesson 8, the project, runs that complete report against S04, closing the module.

The boundary: what does NOT belong in this module

This module builds a referential integrity check that runs over raw data, before it reaches any transformation system — the same orders_s04 that came straight from a CSV, via DuckDB, without going through any dbt project. There's an important boundary with a sister guide: dbt has its own declarative test for exactly this same problem — relationships in schema.yml, one of the four data_tests: generic tests —, already taught in depth by dbt-analytics-engineering-guide (module 4). The difference isn't conceptual — both check that a foreign key exists in the referenced table —, it's scope and timing: a dbt relationships test lives inside a dbt project's graph, runs when dbt test runs, and only makes sense over models that already live there, generally after the data has already passed through some transformation layer. This module's check is a reusable Polars function that runs over any DataFrame, whether inside a dbt project or not — exactly the same argument module 2 already made about Pandera versus dbt's tests, now applied to the consistency dimension.

And a forward boundary, within this same guide: this module doesn't solve accuracy (ORD-9509, the 60.00 price) or freshness (the file arrived late). Those two remain exactly where module 2 left them — module 5 and module 6 close them, with their own tools.

Common mistakes

Thinking "consistency" is just a fancy synonym for "validity." What happens: someone, hearing "consistency," assumes it's the same thing they already saw in module 2 with quantity > 0 — a rule about a value's range. Why it happens: the two words sound like "the value is fine," and this module's lesson 3 (which you haven't read yet) is the one that draws the precise difference, so at this first lesson it's still easy to mix them up. How to spot it: if your mental definition of "consistency" doesn't include the word "relationship" or "between tables," you're missing the central piece. How to fix it: hold on to this lesson's airplane boarding pass image — seat_is_well_formed() is validity (does it have the correct shape?); seat in VALID_SEATS is consistency (does it really exist in the reference catalog?). They're two different questions about the same value, and the second needs an external catalog the first never consults.

Assuming adding more rules to OrdersSchema would eventually solve this. What happens: someone, motivated by how well module 2 worked, imagines that with enough additional Fields, OrdersSchema would end up covering consistency too. Why it happens: Pandera felt, in module 2, like a tool with plenty of room to grow. How to spot it: try to concretely imagine what Pandera Field could check "does this product_id exist in another table" without passing that other table as an argument — no such Field exists, because a DataFrameModel is designed, by construction, to validate one table at a time. How to fix it: accept the limit as structural, not as a temporary limitation of Pandera — this module's lesson 3 demonstrates it with technical evidence, not just this explanation.

Getting ahead of yourself and assuming this module's check will also catch ORD-9509 (the 60.00 price). What happens: someone, seeing this module finally "fix" something earlier tools didn't fix, expects it to also solve the second silent row module 2 left behind. Why it happens: the two rows — ORD-9508 and ORD-9509 — have always been mentioned together in modules 1 and 2, so it's natural to expect them to get solved together too. How to spot it: if your expectation for this module's end includes "no silent row is left," revisit module 1's lesson 3 dimensions table — accuracy and consistency are different dimensions, with different causes. 60.00 is a perfectly present number in dim_product... except unit_price isn't a foreign key, it's a business value compared against a historical baseline, not against an existence catalog. How to fix it: this module closes consistency, one of the six dimensions. Accuracy belongs to module 5, with a completely different tool (a price baseline, not a reference catalog).

Exercises

Exercise 1 — Extend the airplane example with a second, genuinely valid boarding pass. Using VALID_SEATS and seat_is_well_formed() from this lesson's worked example, build a second boarding pass with seat="15B" (within the 40-row range) and confirm both questions — shape and existence — return True this time.

boarding_pass_2 = {"passenger": "A. Torres", "seat": "15B"}
print(f"seat_is_well_formed('{boarding_pass_2['seat']}'): {seat_is_well_formed(boarding_pass_2['seat'])}")
print(f"'{boarding_pass_2['seat']}' exists in VALID_SEATS: {boarding_pass_2['seat'] in VALID_SEATS}")

seat_is_well_formed('15B'): True
'15B' exists in VALID_SEATS: True

Exercise 2 — Build a third boarding pass that fails validity, and never even gets evaluated against consistency. Build a boarding pass with seat="5" (no letter at all). What does seat_is_well_formed() return? Does it make sense, in that case, to keep asking whether the seat exists in VALID_SEATS?

boarding_pass_3 = {"passenger": "R. Diaz", "seat": "5"}
print(f"seat_is_well_formed('{boarding_pass_3['seat']}'): {seat_is_well_formed(boarding_pass_3['seat'])}")

seat_is_well_formed('5'): False

Exercise 3 — Without looking at lesson 2 yet, write your own definition of "consistency across tables." In 2-3 sentences, and using your own words (not this lesson's), define what it means for data to be "consistent" in this module's sense, and give a Kiosko example — it can be ORD-9508, or one you invent — that illustrates that definition.

Summary and next step

In this lesson you saw, with an executed example — the airplane boarding pass with seat 52C —, the central distinction that organizes this entire module: it's one thing for a value to have the correct shape, and a completely different thing for that value to really exist in the catalog that gives it meaning. You walked through the full map of this guide's eight modules and this module's eight lessons, and drew the boundary with dbt's relationships test: the same concept, solved there inside a dbt project, solved here over any raw DataFrame.

Before moving on you should be able to: explain, in your own words, the difference between validity and consistency; and name why no Pandera DataFrameModel, no matter how many Fields you add to it, can check consistency on its own.

Lesson 2 gives this idea the precise vocabulary the rest of the module uses without re-explaining it, and lesson 3 demonstrates, running module 2's real OrdersSchema again, exactly how and why a referential bug slips through it.

Resources

• Polars — official documentation, joins guide, including the semi- and anti-join section this module uses starting in lesson 4. docs.pola.rs/user-guide/transformations/joins. In English.
• dbt-analytics-engineering-guide, module 4 — the source of dbt's relationships test, the exact boundary this lesson draws. src/guides/dbt-analytics-engineering-guide/DISENO.md. In Spanish.
• Module 1, project (lesson 8), and module 2, project (lesson 8), of this same guide — this module's exact starting point: the same two silent rows, confirmed twice with different tools. src/guides/data-reliability-and-governance-guide/workbook/module-02-declarative-data-quality-tests-with-pandera/es/08-project-testing-s04-with-pandera.md. In Spanish.
• This guide's DESIGN — the full map of the eight modules, including this module 3's exact mandate. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.