Combining Pandera with custom checks

Description

So far, this module has run three tools separately: Pandera's OrdersSchema (inherited from module 2), validate_referential_integrity() (lesson 4), and check_retransmission_consistency() (lesson 5). Each lives in its own script, with its own way of reporting results. This lesson brings them together into a single function, run_consistency_checks(), that runs all three and returns one structured report — the first sketch of the complete trust system module 8 (this guide's capstone) is going to fully assemble.

Connection to the module. This lesson adds no new data quality rule — it combines, into a single piece, exactly the three lessons 4, 5, and module 2 already built. Lesson 8, the closing project, runs this same function against S04's complete file and presents the module's final report.

An analogy: the instrument panel, not a single gauge

Go back to this module's lesson 1 airplane. A pilot doesn't fly looking at a single gauge — they have a complete panel: the altimeter measures altitude, the airspeed indicator measures speed, the fuel gauge measures how much is left in the tank. Each instrument measures something different, with its own internal mechanism, and none replaces the others — the altimeter will never tell you you're running low on fuel. But the pilot doesn't check each instrument separately, at different times, with different procedures: they see them all together, on a single panel, at a glance.

OrdersSchema, validate_referential_integrity(), and check_retransmission_consistency() are three instruments with completely different mechanisms — a declarative class, an anti-join, a group-by aggregation —, each measuring a different question about the same data. This lesson builds the panel: a single function that runs all three and returns their three results together, with nobody having to remember to run three separate scripts or remember which does what.

Worked example: run_consistency_checks(), the complete panel

# combined_consistency_report.py
import duckdb
import pandera
import pandera.polars as pa
import polars as 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)


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")


def check_retransmission_consistency(df: pl.DataFrame, key: str = "order_id") -> pl.DataFrame:
    """Groups with a repeated 'key' whose content does NOT match across appearances."""
    compare_cols = [c for c in df.columns if c not in (key, "order_ts")]
    grouped = df.group_by(key).agg(
        [pl.col(c).n_unique().alias(f"{c}_nunique") for c in compare_cols]
        + [pl.len().alias("occurrences")]
    )
    repeated = grouped.filter(pl.col("occurrences") > 1)
    inconsistent = repeated.filter(
        pl.any_horizontal([pl.col(f"{c}_nunique") > 1 for c in compare_cols])
    ).sort(key)
    return inconsistent


def run_consistency_checks(orders_df: pl.DataFrame, dim_product_df: pl.DataFrame) -> dict:
    """Combines Pandera (OrdersSchema) with this module's custom checks
    into a single structured report."""
    try:
        OrdersSchema.validate(orders_df, lazy=True)
        pandera_failures = pl.DataFrame(schema={"order_id": pl.String, "column": pl.String, "check": pl.String})
    except pa.errors.SchemaErrors as exc:
        pandera_failures = exc.failure_cases.with_columns(
            pl.col("index").map_elements(lambda i: orders_df["order_id"][i], return_dtype=pl.String).alias("order_id")
        ).select(["order_id", "column", "check"])

    referential_failures = validate_referential_integrity(orders_df, dim_product_df)
    cross_column_failures = check_retransmission_consistency(orders_df)

    return {
        "pandera": pandera_failures,
        "referential": referential_failures,
        "cross_column": cross_column_failures,
    }


def main() -> None:
    print("=== Kiosko: combined report (Pandera + custom checks) ===")
    print(f"pandera version: {pandera.__version__}\n")

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

    report = run_consistency_checks(orders_df, dim_product_df)

    print(f"[Pandera] OrdersSchema: {report['pandera'].height} physical rows with an error")
    print(report["pandera"])

    print(f"\n[Referential] validate_referential_integrity(): {report['referential'].height} orphan rows")
    print(report["referential"].select(["order_id", "product_id"]))

    print(f"\n[Cross-column] check_retransmission_consistency(): {report['cross_column'].height} inconsistent groups")

    flagged_by_pandera = set(report["pandera"]["order_id"].to_list())
    flagged_by_referential = set(report["referential"]["order_id"].to_list())
    all_flagged = flagged_by_pandera | flagged_by_referential

    print(f"\n=== Combined summary ===")
    print(f"Distinct order_id flagged by Pandera: {sorted(flagged_by_pandera)}")
    print(f"Distinct order_id flagged by referential integrity: {sorted(flagged_by_referential)}")
    print(f"Combined total: {len(all_flagged)} of {orders_df.height} rows")


if __name__ == "__main__":
    main()

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

=== Kiosko: combined report (Pandera + custom checks) ===
pandera version: 0.32.1

[Pandera] OrdersSchema: 4 physical rows with an error
shape: (4, 3)
┌──────────┬────────────┬──────────────────┐
│ order_id ┆ column     ┆ check            │
│ ---      ┆ ---        ┆ ---              │
│ str      ┆ str        ┆ str              │
╞══════════╪════════════╪══════════════════╡
│ ORD-9502 ┆ order_id   ┆ field_uniqueness │
│ ORD-9502 ┆ order_id   ┆ field_uniqueness │
│ ORD-9503 ┆ unit_price ┆ not_nullable     │
│ ORD-9507 ┆ quantity   ┆ greater_than(0)  │
└──────────┴────────────┴──────────────────┘

[Referential] validate_referential_integrity(): 1 orphan rows
shape: (1, 2)
┌──────────┬────────────┐
│ order_id ┆ product_id │
│ ---      ┆ ---        │
│ str      ┆ str        │
╞══════════╪════════════╡
│ ORD-9508 ┆ P099       │
└──────────┴────────────┘

[Cross-column] check_retransmission_consistency(): 0 inconsistent groups

=== Combined summary ===
Distinct order_id flagged by Pandera: ['ORD-9502', 'ORD-9503', 'ORD-9507']
Distinct order_id flagged by referential integrity: ['ORD-9508']
Combined total: 4 of 12 rows

A single report, three instruments, each with its own section. run_consistency_checks() doesn't change any of the three checks' logic in the slightest — each internal function is exactly the one the earlier lessons already built —, it only orchestrates them and returns their results together, in a dictionary with three clear keys. The final "Combined summary" gives a number no individual check could give on its own: 4 of 12 rows, the union of everything this module and module 2 already know about S04.

Diagram: three independent functions, one single entry point

flowchart TB
    subgraph INSTRUMENTOS["Three independent instruments"]
        P["OrdersSchema.validate()\n(Pandera, module 2)"]
        R["validate_referential_integrity()\n(lesson 4)"]
        C["check_retransmission_consistency()\n(lesson 5)"]
    end

    ORDERS["orders_df"] --> P
    ORDERS --> R
    ORDERS --> C
    PRODUCT["dim_product_df"] --> R

    P --> RC["run_consistency_checks()"]
    R --> RC
    C --> RC
    RC --> REPORT["dict with 3 keys:\npandera, referential, cross_column"]

No arrow enters another instrument — each receives orders_df (and, in referential integrity's case, also dim_product_df) independently, with none depending on another's result. run_consistency_checks() is the only new piece: it validates nothing on its own, it only organizes the calls and gathers the answers.

Going deeper: why referential integrity lives outside Pandera's class, and not as one more Check

Someone already familiar with Pandera might wonder whether validate_referential_integrity() could live inside OrdersSchema, as one more rule, instead of as a separate function. Pandera does have a mechanism for rules that combine several columns from the same table — the @pa.dataframe_check decorator, documented in the official API reference —, so in theory it could be attempted. But that decorator, like any other Pandera validation method, only receives the table being validated's own data — never a second, external DataFrame, like dim_product. This module's lesson 3 already confirmed it by reviewing the full API signature: no public Pandera method accepts a second table as an argument, and @pa.dataframe_check is no exception — it extends what Pandera can check within a table (the kind of rule lesson 5 does cover), but it doesn't add the ability to compare against an external table.

This isn't a minor limitation that "will probably get fixed in a future version" — it's consistent with the library's entire design: a DataFrameModel describes the shape of one DataFrame, and that is, precisely, the boundary that motivated this entire module since lesson 1. validate_referential_integrity() lives outside OrdersSchema, as an independent function, not because of a temporary limitation to be resolved, but because the question it answers — "does it exist in another table?" — is, by definition, outside any single-table schema's territory.

Common mistakes

Adding report['pandera'].height + report['referential'].height to count "rows with problems." What happens: someone directly adds up each result's row count (4 + 1 = 5) to report how many rows have some problem. Why it happens: adding up the counts seems like the most direct way to combine two reports. How to spot it: check the actual result — "Combined total: 4 of 12 rows," not 5. The direct sum counts physical rows, not distinct order_ids, and report['pandera'] already counts ORD-9502 twice (once per appearance). How to fix it: always use a set union (set() | set()) over the order_ids, as run_consistency_checks() does in the worked example — that automatically eliminates any double-counting, with no need to manually track which check duplicates what.

Assuming an empty result in cross_column means the function failed. What happens: someone, seeing report['cross_column'].height == 0, suspects check_retransmission_consistency() has a bug, because "it shouldn't return something empty." Why it happens: after seeing content-filled reports in the other two sections, an empty result can feel like a silent error. How to spot it: revisit lesson 5 — an empty DataFrame is exactly the correct, expected result when there's no inconsistency at all, the same contract validate_orders(), SchemaErrors.failure_cases, and validate_referential_integrity() already follow throughout this guide. How to fix it: never interpret an empty result as a code failure — it's how every check in this guide communicates "I found no problem here."

Thinking run_consistency_checks() is already the "quarantine" module 7 talks about. What happens: someone, seeing this function already separates problem rows from clean rows, describes it as Kiosko's quarantine mechanism. Why it happens: the result superficially resembles "separating the good from the bad." How to spot it: check what run_consistency_checks() does with its result — it prints it and returns it, never moves any row anywhere, or decides what to do with the flagged rows. How to fix it: this function is a report, the input a real quarantine mechanism would need — physically separating clean_df from quarantined_df, with an explicit decision about each flagged row — which this guide's module 7 builds in depth, with its own vocabulary (quarantine(), raise_alert(), a written runbook).

Exercises

Exercise 1 — Add a fourth section to the report: dimensions covered so far. Extend run_consistency_checks() so it also computes, dynamically (not hand-written), which of module 1's six data quality dimensions have at least one non-empty result in this report.

CHECK_TO_DIMENSION = {
    "not_nullable": "completeness",
    "field_uniqueness": "uniqueness",
    "greater_than(0)": "validity",
}


def dimensions_covered(report: dict) -> set[str]:
    dims = {CHECK_TO_DIMENSION[c] for c in report["pandera"]["check"].to_list()}
    if report["referential"].height > 0:
        dims.add("consistency")
    return dims


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

print(f"Dimensions covered: {sorted(dimensions_covered(report))}")

Dimensions covered: ['completeness', 'consistency', 'uniqueness', 'validity']

Exercise 2 — Confirm run_consistency_checks() is deterministic, by running it twice. Run main() twice in a row, changing nothing. Do you expect any difference between the two runs? Justify in one sentence.

Exercise 3 — Argue why run_consistency_checks() doesn't need to know anything about S04 specifically. In 2-3 sentences, review run_consistency_checks(orders_df, dim_product_df)'s signature and explain why it would work equally well against S01, S02, or S03's data.

Summary and next step

In this lesson you built run_consistency_checks(), this guide's first combined report: a single entry point that runs OrdersSchema (Pandera, module 2), validate_referential_integrity() (lesson 4), and check_retransmission_consistency() (lesson 5), and returns their three results together, with none interfering with the others. You confirmed, with executed evidence, that the combined total of rows with some known problem — counting distinct order_ids, not repeated physical rows — is exactly 4 out of 12. And you went deeper into why referential integrity, specifically, can't live inside Pandera's class, not even with the @pa.dataframe_check decorator that does exist for rules within a single table.

Before moving on you should be able to: explain the difference between adding up DataFrame heights and unioning sets of order_id, and why this guide always uses the latter; and explain why Pandera's @pa.dataframe_check doesn't solve the same problem validate_referential_integrity() does.

You have the combined report working. Lesson 8 — this module's closing project — takes exactly this function and runs it against S04's complete file, with the final, readable report that closes this guide's consistency thread.

Resources

• Pandera — official dataframe_check documentation (the decorator for rules combining several columns from the same table, and its limit: it never receives a second table). pandera.readthedocs.io. In English.
• Module 2, lessons 7-8, of this same guide — the exact source of OrdersSchema, reused with no changes in this lesson's combined report. 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.
• Lessons 4 and 5 of this same module — the exact source of validate_referential_integrity() and check_retransmission_consistency(), combined with no changes in this lesson. src/guides/data-reliability-and-governance-guide/workbook/module-03-consistency-and-referential-checks/es/. In Spanish.
• This guide's DESIGN. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.