Why schema checks alone don't catch referential bugs

Description

Lessons 1 and 2 explained, with analogies and technical vocabulary, why a value can have the correct shape and still not really exist in its reference table. This lesson leaves the analogies behind and demonstrates the same point three times, with real, executed code: first against module 2's OrdersSchema, then against the SQL engine that holds up this entire guide (DuckDB), and finally against the real table you already built. By the end of this lesson you're going to have direct evidence — not a conceptual explanation — of why ORD-9508 keeps circulating with no flag at all.

Connection to the module. This lesson is the last piece of "criteria" before building starts: it confirms, with evidence, the exact limit lessons 1 and 2 explained in words. Lesson 4 writes the solution.

An analogy: the paperwork that only checks the document's format

Imagine a hiring process that asks for your ID number. The company's system checks that the number has the correct number of digits, that it has no letters, that it follows your country's national ID document format. If all that checks out, the system says "valid document" and moves on. What that system never did is call the real national registry and confirm that ID number corresponds to a person who really exists. A made-up number, but with the perfect format, would pass exactly the same way as a real one — the system never had, in its design, the ability to make that second call.

OrdersSchema is, precisely, that first system: it checks product_id's shape with the same rigor the paperwork checks the document's format. It never "calls" dim_product to confirm the product really exists, because nobody designed it to make that call — not out of carelessness, but because a Pandera DataFrameModel, by construction, never receives a second table as an argument.

Worked example, part 1: OrdersSchema, run again, looking specifically at ORD-9508

You already saw, in module 2, that OrdersSchema.validate(df, lazy=True) flags four physical rows. This time, instead of looking at the complete report, you isolate a single question: does ORD-9508 show up anywhere in that report?

# schema_alone_is_not_enough.py
import duckdb
import pandera.polars as pa
import polars as pl

con = duckdb.connect("kiosko.duckdb")
df = con.sql("SELECT * FROM orders_s04").pl()


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(df, lazy=True)
except pa.errors.SchemaErrors as exc:
    fc = exc.failure_cases

flagged_order_ids = set(
    fc.with_columns(
        pl.col("index").map_elements(lambda i: df["order_id"][i], return_dtype=pl.String).alias("order_id")
    )["order_id"].to_list()
)

print(f"order_id flagged by OrdersSchema: {sorted(flagged_order_ids)}")
print(f"'ORD-9508' is in that list: {'ORD-9508' in flagged_order_ids}")

What to expect. Running python3 schema_alone_is_not_enough.py (with kiosko.duckdb and module 2's orders_s04 in the same folder), the output is exactly this:

order_id flagged by OrdersSchema: ['ORD-9502', 'ORD-9503', 'ORD-9507']
'ORD-9508' is in that list: False

Three order_ids flagged — the same three as always, completeness/uniqueness/validity. ORD-9508 isn't there, and can't be there: check this very code block's OrdersSchema class — none of its three lines mentions dim_product, or receives any second DataFrame as an argument. It isn't that Pandera "failed" to detect something it should have; it's that the class, as written, never had the information needed to ask that question.

Worked example, part 2: DuckDB really can enforce referential integrity, if you explicitly ask it to

Before concluding that "no tool in this stack" can check this, it's worth an honest test: does the SQL engine this guide already uses — DuckDB — have, in principle, the ability to reject a broken reference? The answer is yes, with an explicitly declared FOREIGN KEY constraint:

# duckdb_fk_proof.py
import duckdb

con = duckdb.connect(":memory:")

con.execute("""
    CREATE TABLE dim_product_fk (
        product_id VARCHAR PRIMARY KEY,
        product_name VARCHAR
    )
""")
con.execute("INSERT INTO dim_product_fk VALUES ('P001', 'Bottled Water 600ml')")

con.execute("""
    CREATE TABLE orders_fk (
        order_id VARCHAR,
        product_id VARCHAR REFERENCES dim_product_fk(product_id)
    )
""")

try:
    con.execute("INSERT INTO orders_fk VALUES ('ORD-1', 'P099')")
    print("INSERT allowed (no error)")
except duckdb.ConstraintException as e:
    print(f"INSERT blocked: {type(e).__name__}: {e}")

What to expect.

INSERT blocked: ConstraintException: Constraint Error: Violates foreign key constraint because key "product_id: P099" does not exist in the referenced table

DuckDB, when the table is declared with REFERENCES dim_product_fk(product_id), immediately rejects the INSERT, with a message naming the exact key and the exact table that doesn't recognize it. The capability really exists, in the engine this guide already uses. So the question changes: if DuckDB can do this, why doesn't orders_s04 do it?

Worked example, part 3: orders_s04, exactly as module 2 created it, has no constraint at all

# inspect_orders_s04_constraints.py
import duckdb

con = duckdb.connect("kiosko.duckdb")

print("orders_s04's real SQL definition:")
print(con.sql("SELECT sql FROM duckdb_tables() WHERE table_name = 'orders_s04'"))

print("\nConstraints declared on orders_s04:")
print(con.sql("SELECT constraint_type FROM duckdb_constraints() WHERE table_name = 'orders_s04'"))

What to expect.

orders_s04's real SQL definition:
┌───────────────────────────────────────────────────────────────────────────────────────┐
│                                          sql                                           │
│                                        varchar                                         │
├───────────────────────────────────────────────────────────────────────────────────────┤
│ CREATE TABLE orders_s04(order_id VARCHAR, store_id VARCHAR, product_id VARCHAR, quan…  │
│ tity BIGINT, unit_price DOUBLE, order_ts TIMESTAMP);                                   │
└───────────────────────────────────────────────────────────────────────────────────────┘

Constraints declared on orders_s04:
┌──────────────────┐
│ constraint_type   │
│      varchar      │
├──────────────────┤
│      0 rows        │
└──────────────────┘

There's the complete answer. duckdb_tables() and duckdb_constraints() are system tables — DuckDB's internal catalog, the way the engine itself describes what it contains —, and they confirm, with no ambiguity, that orders_s04 was created with no PRIMARY KEY, no FOREIGN KEY, no NOT NULL, no constraint of any kind. CREATE OR REPLACE TABLE ... AS SELECT * FROM read_csv(...) — exactly the pattern module 2's lesson 4 built — creates a table with types, but no constraints: it's what SQL calls CTAS (create table as select), and no DuckDB CTAS syntax lets you declare a FOREIGN KEY in the same step. P099 violated no rule when it got inserted into orders_s04, because there was no rule there for it to violate.

Diagram: the same question, three different places, three results

flowchart TD
    A["product_id = 'P099'\nORD-9508"] --> B["OrdersSchema.validate()\n(Pandera)"]
    B --> B1["PASSES:\nthe class never\nreceives dim_product"]

    A --> C["orders_fk with a\ndeclared FOREIGN KEY\n(proof of concept)"]
    C --> C1["BLOCKED:\nDuckDB CAN\nask this question"]

    A --> D["real orders_s04\n(CTAS, no constraints)"]
    D --> D1["PASSES:\nno FOREIGN KEY\nwas ever declared"]

Notice that the left and right results are identical — PASSES — for completely different reasons. Pandera can't ever ask this question (a design limitation of the tool, explained in the "Going deeper" section). orders_s04 could have had this protection, but nobody declared it (a decision — or an omission — in the table's design). The middle one is proof the problem isn't that "this is impossible to check" — it's that, in this pipeline, at this point, nobody checked it.

Going deeper: why Kiosko doesn't declare FOREIGN KEY on orders_s04, and why that isn't necessarily a mistake

It's worth resisting the easy conclusion of "then Kiosko should add FOREIGN KEY to all its tables." There's a real reason, not just laziness, for the current design. DuckDB's official documentation says it plainly: "Constraints have a strong impact on performance: they slow down loading and updates but speed up certain queries" — constraints cost something, every time a new row gets inserted. Even more important for Kiosko: orders_2026-08-14.csv gets loaded with CREATE OR REPLACE TABLE ... AS SELECT * FROM read_csv(...), a deliberately simple pattern that rebuilds the whole table from scratch every time it runs — adding a FOREIGN KEY constraint to that flow would mean any row with an unknown product_id would fail the entire file's load, with a low-level engine error, instead of letting the row load and get reported with the full business context an application check — like the one lesson 4 builds — can give.

This is, in essence, the same design decision data-engineering-foundations-guide already made in its M7: when S04 still didn't exist in DIM_STORE, the pipeline rejected the entire file with an application-level ValueError, not with a database constraint. Modern data systems, almost always, prefer loading raw data with no strict engine constraints, and moving validation to an explicit, observable application layer — exactly the role this entire guide plays. FOREIGN KEY in the engine is a real, valid tool in other contexts (transactional systems with small, frequent writes), but it isn't the one this ecosystem chose for batch data pipelines.

Common mistakes

Concluding Pandera "has a bug" for not catching ORD-9508. What happens: someone, after seeing this lesson's part 1 result, reports this as a surprising limitation or a Pandera flaw. Why it happens: after seeing OrdersSchema catch three different dimensions in module 2, it seems reasonable to expect it to solve this one too. How to spot it: check pa.DataFrameModel's and .validate()'s signature — no method in Pandera's public API accepts a second DataFrame as an argument. How to fix it: it isn't a bug, it's a design limit consistent throughout the library — Pandera validates one table, always. The solution isn't "fixing" Pandera, it's building the additional check lesson 4 writes.

Thinking declaring FOREIGN KEY on orders_s04 would be the right solution for this guide. What happens: someone, motivated by this lesson's part 2, proposes modifying module 2's bridge_duckdb_to_polars.py to add a real FOREIGN KEY constraint to orders_s04. Why it happens: you just saw DuckDB can do it, so it seems like the most direct solution. How to spot it: if your plan is for orders_s04's CREATE TABLE to hard-fail on any unknown product_id, revisit this lesson's Going deeper section — that would reject S04's entire file, with no readable report, the first time a new product (or a typo) shows up, even if the other eleven rows are perfect. How to fix it: lesson 4 builds an application check, which reports the problem with full business context (which row, which value, what was expected), without blocking the loading of the rows that are fine.

Assuming "no constraints in the engine" means "any SQL engine always behaves this way." What happens: someone generalizes this lesson's part 3 result into the claim "SQL databases never check this by default." Why it happens: it's easy to lose sight of the fact that the result depends on how the table was declared, not on a universal property of SQL. How to spot it: re-read this lesson's part 2 — the same DuckDB, the same version, blocked the INSERT when the orders_fk table did declare REFERENCES. How to fix it: the correct, precise claim is "orders_s04, as this guide creates it, doesn't have that constraint" — not a claim about SQL in general, but about a specific design decision in this pipeline.

Exercises

Exercise 1 — Confirm that dim_product, once you create it in lesson 4, also has no constraints yet. Without looking at lesson 4 yet, predict: if dim_product gets created with the same CREATE OR REPLACE TABLE ... AS SELECT pattern (or with a direct INSERT, with no declared PRIMARY KEY), would any row show up in duckdb_constraints() for that table? Justify your answer in one sentence.

Exercise 2 — Reproduce this lesson's part 2, but with a key that really exists. Modify duckdb_fk_proof.py to insert ('ORD-2', 'P001') instead of ('ORD-1', 'P099'). What do you expect to happen?

con.execute("INSERT INTO orders_fk VALUES ('ORD-2', 'P001')")
print("INSERT allowed (no error)")

INSERT allowed (no error)

Exercise 3 — Argue, in 2-3 sentences, why "an application check" and "a database constraint" aren't mutually exclusive. Based on this lesson's Going deeper section, explain why a mature data system could, in theory, use both mechanisms at once, and what each gains that the other doesn't provide.

Summary and next step

In this lesson you demonstrated, with three pieces of executed evidence, the exact limit of the tools you already built: OrdersSchema can never catch ORD-9508 because its API never receives a second table; DuckDB really can enforce referential integrity, but only if someone explicitly declares it with FOREIGN KEY; and orders_s04, as module 2's CTAS pattern creates it, declares no constraint at all. The problem isn't that this is impossible to check — it's that, in this pipeline, at this exact point, nobody checked it yet.

Before moving on you should be able to: explain, with evidence and not just the analogy, why OrdersSchema doesn't catch ORD-9508; and argue why adding a direct FOREIGN KEY to orders_s04 wouldn't be the best solution for this guide's case.

You have the complete criteria. Lesson 4 finally builds the solution: validate_referential_integrity(), an application check written in Polars, able to ask exactly the question none of this lesson's three pieces could ask on its own.

Resources

• DuckDB — official documentation, constraints section (PRIMARY KEY, FOREIGN KEY, the performance cost quoted in this lesson). duckdb.org/docs/current/sql/constraints.html. In English.
• DuckDB — official documentation, system metadata functions (duckdb_tables(), duckdb_constraints()), used in this lesson's part 3 to inspect orders_s04 from the inside. duckdb.org/docs/current/sql/meta/duckdb_table_functions.html. In English.
• Pandera — official API documentation (DataFrameModel, Field, Check, dataframe_check) — confirms no public method receives a second DataFrame. pandera.readthedocs.io. In English.
• Module 2, lesson 4, of this same guide — the exact source of the CREATE OR REPLACE TABLE ... AS SELECT * FROM read_csv(...) pattern this lesson audits. src/guides/data-reliability-and-governance-guide/workbook/module-02-declarative-data-quality-tests-with-pandera/es/04-installing-pandera-and-bridging-duckdb-to-polars.md. In Spanish.
• data-engineering-foundations-guide, module 7 — the source of S04's application-level rejection (ValueError: unknown store_id: S04), the same design philosophy this lesson's Going deeper explains. src/guides/data-engineering-foundations-guide/workbook/module-07-partitioning-and-orchestration/es/. In Spanish.
• This guide's DESIGN. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.