Completeness and uniqueness, declared

Description

This lesson adds the first two real rules to OrdersSchema: nullable=False for completeness, unique=True for uniqueness. Unlike lesson 5 — where a types-only schema let all twelve of S04's rows through with no warning at all —, this time each new rule catches exactly the row it's supposed to catch: ORD-9503 for its empty unit_price, and ORD-9502's duplicate pair for repeating the same order_id. It's the first time in this module Pandera says, with concrete evidence, "there's a problem here."

Connection to the module. This lesson builds two of the three pieces lesson 7's final schema (and lesson 8's project) needs: completeness and uniqueness. The third piece — validity, over quantity — gets built in lesson 7, along with the right way to see all of a single run's failures at once, instead of one at a time the way this lesson does.

An analogy: the hospital admission form, and the ID wristband

When someone arrives at a hospital's admission desk, two completely different controls happen before that person can enter any exam room. The first is the admission form: every required field — name, date of birth, reason for visit, emergency contact — has to be complete; a form with the "reason for visit" field left blank gets sent back before proceeding, no matter how well-filled the other fields are. That control is completeness: every required field, present, no exceptions.

The second control is different: once the person is admitted, they get a wristband with a unique ID number, and the hospital's system verifies that number isn't already assigned to another active patient. That control doesn't look at whether the form is complete — it looks at whether the identifier about to be assigned already exists somewhere else. That's uniqueness: not a property of an isolated field, but a property of the relationship between a new value and every value that already exists.

Pandera's Field(nullable=False) is the first control: it checks, field by field, that no value is empty. Field(unique=True) is the second: it checks, against the entire column, that no value repeats. They're two different questions, about two different kinds of problem — and this lesson builds them separately, one at a time, so it's clear what each one does without mixing them up.

Worked example: two minimal schemas, each isolating its own rule

Completeness: unit_price, with nullable=False

# completeness_check.py
import duckdb
import pandera.polars as pa

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

class CompletenessSchema(pa.DataFrameModel):
    order_id: str
    unit_price: float = pa.Field(nullable=False)
    quantity: int

try:
    CompletenessSchema.validate(df)
except pa.errors.SchemaError as exc:
    print(f"SchemaError: {exc}\n")
    print("failure_cases:")
    print(exc.failure_cases)

What to expect.

SchemaError: non-nullable column 'unit_price' contains null values

failure_cases:
shape: (1, 1)
┌────────────┐
│ unit_price │
│ ---        │
│ f64        │
╞════════════╡
│ null       │
└────────────┘

A single row in failure_cases, with the exact value that broke the rule: null, in the unit_price column. That row corresponds to ORD-9503 — the same one foundations' validate_orders() already caught in module 1, now caught by a one-word declaration (nullable=False) instead of a hand-written if. exc.failure_cases, even in a simple SchemaError like this one — not yet the full SchemaErrors report you'll see in lesson 7 —, is already a real DataFrame, not just a text message: you can keep working with it like any other Polars DataFrame.

Uniqueness: order_id, with unique=True

# uniqueness_check.py
class UniquenessSchema(pa.DataFrameModel):
    order_id: str = pa.Field(unique=True)
    unit_price: float
    quantity: int

try:
    UniquenessSchema.validate(df)
except pa.errors.SchemaError as exc:
    print(f"SchemaError: {exc}\n")
    print("failure_cases:")
    print(exc.failure_cases)

What to expect.

SchemaError: column 'order_id' not unique:
shape: (2, 1)
┌──────────┐
│ order_id │
│ ---      │
│ str      │
╞══════════╡
│ ORD-9502 │
│ ORD-9502 │
└──────────┘

failure_cases:
shape: (2, 1)
┌──────────┐
│ order_id │
│ ---      │
│ str      │
╞══════════╡
│ ORD-9502 │
│ ORD-9502 │
└──────────┘

Notice a detail that departs from module 1's result: failure_cases has two rows, not one. Foundations' validate_orders(), running sequentially row by row, only flagged the second appearance of ORD-9502 as a duplicate — the first passed, because at the moment it was processed, seen_order_ids didn't know about it yet. Pandera's Field(unique=True) works differently: it looks at the entire order_id column all at once, and reports every row that shares a repeated value with another — with no concept of "which one came first." Both appearances of ORD-9502 are, for this rule, equally responsible for the problem.

Diagram: two different questions, over the same twelve rows

flowchart TB
    D["orders_s04: 12 rows"]

    D --> C["nullable=False over unit_price\n(completeness: looks at EACH VALUE,\none at a time, in isolation)"]
    D --> U["unique=True over order_id\n(uniqueness: looks at the WHOLE COLUMN,\ncomparing each value against the rest)"]

    C --> C1["ORD-9503:\nunit_price = null\n-> FAILS"]
    U --> U1["ORD-9502 (1st appearance):\nrepeated value -> FAILS"]
    U --> U2["ORD-9502 (2nd appearance):\nrepeated value -> FAILS"]

The diagram marks the structural difference between the two rules: nullable=False is a local check — each value is judged on its own, with no look at any other value in the same column. unique=True is a relational check within a single column — each value is judged by comparing it against every other value in that same column. Both are still, in module 1 lesson 3's vocabulary, checks that don't need to look at another table — that's the boundary with consistency, which this guide's module 3 crosses.

Going deeper: why Pandera treats both appearances equally, and validate_orders() doesn't

It's worth understanding the exact cause of the difference you saw in the worked example, because it isn't a design whim — it's a direct consequence of how each tool processes data. validate_orders(), as foundations M5 built it, goes through rows one at a time, in the order they appear in the file, and keeps a set (seen_order_ids) that grows as it processes: when it reaches row 10 (ORD-9502's second time) and that identifier is already in the set, it flags it. Row 2 (the first appearance) never had anything to compare against at the moment it got processed — the set was still empty for that value.

Pandera's Field(unique=True), on the other hand, doesn't process rows one by one in sequence — it evaluates the whole column as a single dataset at once, typically counting how many times each value appears and flagging as invalid any row whose value appears more than once. This difference doesn't make one tool "more correct" than the other — both are answering, with full internal consistency, the question each one is designed to answer. But if your work depends on an exact count of "duplicate rows," you need to know which of the two conventions you're using: validate_orders() is going to tell you "one duplicate row" (the second appearance); Pandera is going to tell you "two rows with a duplicated value" (both appearances). Both numbers are correct, they describe exactly the same problem, and neither is "the real one" — they describe the same fact from different angles.

Common mistakes

Expecting uniqueness's failure_cases to have a single row, "the duplicate one." What happens: someone, familiar with module 1's validate_orders() result (Rejected: 3, with ORD-9502 appearing only once in the detail), expects UniquenessSchema to also report a single row for the same problem. Why it happens: the intuition of "one row is the duplicate, the other is the original" is natural, but it isn't how a uniqueness check over a whole column works. How to spot it: if your count of problem rows doesn't match between validate_orders() and Pandera, before assuming either is wrong, check whether the problem is uniqueness — it's, so far, the only dimension where the two tools count differently. How to fix it: remember this lesson's Going deeper section — two valid conventions, not an error in either. If you need to count "how many values are duplicated" instead of "how many rows share a duplicated value," use df["order_id"].n_unique() compared against df.height for that specific question.

Putting Field(unique=True) on a column Kiosko knows repeats by design. What happens: someone, motivated by seeing how useful unique=True was on order_id, adds it to store_id or product_id too, and gets surprised when all of S04 "fails" validation. Why it happens: after seeing a rule work well, it's tempting to apply it in more places without thinking about whether it makes sense there. How to spot it: if your schema declares unique=True over a column that, by the table's own design, is expected to repeat (store_id="S04" across all twelve rows, product_id="P002" across several), you're going to see massive failures that don't represent any real problem. How to fix it: unique=True only makes sense over columns that, by design, should identify a row unambiguously — order_id is the only column in orders_s04 with that property; the rest of the columns legitimately repeat values, and that's fine.

Mixing both rules into one schema before understanding each separately. What happens: someone jumps straight to writing OrdersSchema with nullable=False and unique=True together, without first running each rule in isolation the way this lesson does, and when something fails, doesn't know which of the two rules was responsible. Why it happens: it seems faster to write everything at once. How to spot it: if your only evidence that nullable=False works is a schema where a unique=True is also active, you didn't isolate the cause — in eager mode (without lazy=True, which lesson 7 introduces), Pandera stops at the first rule that fails, following the columns' declaration order, so a combined schema can completely hide whether the second rule works or not. How to fix it: isolate each new rule, as this lesson's worked example does, before combining them — it's the only way to confirm, with real evidence, that each piece works on its own.

Exercises

Exercise 1 — Count how many distinct order_ids exist in S04, without using Pandera. Using this lesson's Polars df, write one line that counts how many distinct order_id values exist, and compare that number against df.height (the total row count).

distinct_count = df["order_id"].n_unique()
print(f"Total rows: {df.height}")
print(f"Distinct order_id: {distinct_count}")

Total rows: 12
Distinct order_id: 11

Exercise 2 — Add nullable=False to quantity too, and predict the result before running it. This lesson's CompletenessSchema doesn't declare any Field for quantity. If you added quantity: int = pa.Field(nullable=False), would the result over S04 change? Justify your answer before running the code.

class CompletenessSchemaV2(pa.DataFrameModel):
    order_id: str
    unit_price: float = pa.Field(nullable=False)
    quantity: int = pa.Field(nullable=False)

try:
    CompletenessSchemaV2.validate(df)
except pa.errors.SchemaError as exc:
    print(exc.failure_cases)

shape: (1, 1)
┌────────────┐
│ unit_price │
│ ---        │
│ f64        │
╞════════════╡
│ null       │
└────────────┘

Exercise 3 — Argue whether unique=True on order_id alone would be enough for Kiosko, with no other control. In 2-3 sentences, argue why unique=True on order_id, by itself, wouldn't be enough to guarantee Kiosko never processes the same sale twice, even if that rule never fails.

Summary and next step

In this lesson you added the first two real rules to a Pandera schema: nullable=False, which caught ORD-9503 for completeness, and unique=True, which caught both appearances of ORD-9502 for uniqueness. You confirmed, with executed evidence, a real difference in how Pandera and foundations' validate_orders() count duplicate rows — two valid conventions, not an error in either tool —, and saw that nullable=False protects against absence, never against a value that's present but invalid.

Before moving on you should be able to: write, from memory, a Field with nullable=False and another with unique=True; explain why Pandera reports two rows for the same duplicate problem validate_orders() reported as one; and name the exact boundary between completeness (absence) and validity (a value present but out of range).

You have two of the three rules. Lesson 7 adds the third — Field(gt=0) over quantity, for validity — and, more importantly, shows how to see all three of a single run's failures at once, with lazy=True and SchemaErrors.failure_cases — the complete report this lesson has, so far, only shown one rule at a time.

Resources

• Pandera — official documentation, Field section (nullable, unique parameters, and their default behavior). pandera.readthedocs.io. In English.
• data-engineering-foundations-guide, module 5, lesson 6 — the source of seen_order_ids and validate_orders()'s sequential behavior against duplicates, compared in this lesson. src/guides/data-engineering-foundations-guide/workbook/module-05-data-quality-gates/es/06-quarantining-bad-rows-instead-of-crashing.md. In Spanish.
• Module 1, lesson 6, of this same guide — the original count of ORD-9502 with validate_orders(), the basis for this lesson's comparison. 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.
• This guide's DESIGN — the exact structure of S04's incident (completeness, uniqueness, validity) this lesson partly catches. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.