Project: S04's complete consistency report

Description

This project closes the module. You have the criteria (lessons 1 through 3), the two tools — validate_referential_integrity() (lesson 4) and check_retransmission_consistency() (lesson 5) —, the application to the real case that caught ORD-9508 (lesson 6), and the combined report that brings them together with Pandera (lesson 7). One step remains: assembling everything into a single closing script, run end to end against S04's complete file, with a final, readable report a Kiosko analyst — someone who never saw this guide's code — could read and understand unaided.

Connection to the module. This project introduces no new concept — it's the complete synthesis of lessons 1 through 7, applied end to end to the same incident modules 1 and 2 diagnosed. It closes this module's thread exactly where module 4 picks it back up: ORD-9508 is already caught, but nobody has yet written what's expected of an S04 file in an artifact any system can read — that's a data contract, the next module's central topic.

An analogy: the final audit report, not each reviewer's loose notes

An auditor who reviewed three different areas of a company — regulatory compliance, financial statements, internal controls — doesn't hand the board three separate folders and expect someone else to combine them. They deliver a single report, with one section per reviewed area, a clear conclusion at the end, and an explicit list of what wasn't reviewed yet in this audit. That last point matters as much as the findings: an audit report that doesn't say "this falls outside this scope" leaves the reader with a false sense of complete coverage.

This project is exactly that final report: it brings together this module's three findings — Pandera, referential integrity, cross-column — into a single readable report, and ends, with the same honesty a good auditor would show, explicitly naming which data quality dimensions remain unreviewed.

The material you need

You need, in this module's same working folder:

modulo_3_consistencia/
├── kiosko.duckdb              (module 2's orders_s04, lesson 4's dim_product)
└── consistency_report.py      (this project)

If your kiosko.duckdb doesn't have both tables yet, repeat module 2's lesson 4 step 2 (orders_s04) and this module's lesson 4 step 1 (dim_product) before continuing — this project doesn't explain those steps again, it assumes they're done.

The reference solution, verified

# consistency_report.py -- module 3 closing project
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,
    }


DIMENSIONS_STILL_OPEN = ["accuracy", "freshness"]


def main() -> None:
    print("=== Kiosko: S04's complete consistency report ===")
    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()
    print(f"orders_df: {orders_df.height} rows | dim_product_df: {dim_product_df.height} rows\n")

    report = run_consistency_checks(orders_df, dim_product_df)

    print("=== 1. Pandera: OrdersSchema (completeness, uniqueness, validity) ===")
    print(report["pandera"])

    print(f"\n=== 2. Referential integrity: validate_referential_integrity() ===")
    if report["referential"].height == 0:
        print("No orphan rows.")
    else:
        for row in report["referential"].iter_rows(named=True):
            print(f"  {row['order_id']} | product_id={row['product_id']} does NOT exist in dim_product")

    print(f"\n=== 3. Cross-column: check_retransmission_consistency() ===")
    if report["cross_column"].height == 0:
        print("No inconsistent retransmission -- ORD-9502 is reliable.")
    else:
        print(report["cross_column"])

    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
    clean_ids = set(orders_df["order_id"].to_list()) - all_flagged

    print(f"\n=== Final summary ===")
    print(f"Total rows in orders_s04: {orders_df.height}")
    print(f"Distinct order_id with a known problem: {len(all_flagged)} ({sorted(all_flagged)})")
    print(f"Distinct order_id with no known problem: {len(clean_ids)} ({sorted(clean_ids)})")

    print(f"\n=== Data quality dimensions: status after this module ===")
    print("Covered -- completeness, uniqueness, validity (module 2), consistency (this module)")
    print(f"Pending -- {', '.join(DIMENSIONS_STILL_OPEN)}")


if __name__ == "__main__":
    main()

What to expect (verified by actually running python3 consistency_report.py, with kiosko.duckdb containing orders_s04 and dim_product, pandera==0.32.1):

=== Kiosko: S04's complete consistency report ===
pandera version: 0.32.1

orders_df: 12 rows | dim_product_df: 4 rows

=== 1. Pandera: OrdersSchema (completeness, uniqueness, validity) ===
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)  │
└──────────┴────────────┴──────────────────┘

=== 2. Referential integrity: validate_referential_integrity() ===
  ORD-9508 | product_id=P099 does NOT exist in dim_product

=== 3. Cross-column: check_retransmission_consistency() ===
No inconsistent retransmission -- ORD-9502 is reliable.

=== Final summary ===
Total rows in orders_s04: 12
Distinct order_id with a known problem: 4 (['ORD-9502', 'ORD-9503', 'ORD-9507', 'ORD-9508'])
Distinct order_id with no known problem: 7 (['ORD-9501', 'ORD-9504', 'ORD-9505', 'ORD-9506', 'ORD-9509', 'ORD-9510', 'ORD-9511'])

=== Data quality dimensions: status after this module ===
Covered -- completeness, uniqueness, validity (module 2), consistency (this module)
Pending -- accuracy, freshness

Read the complete result with the same care you already trained in the earlier modules. Sections 1 and 2 confirm, in one place, everything this guide already knows about S04: four physical rows flagged by Pandera, one orphan row caught by referential integrity. Section 3 confirms, with evidence, that ORD-9502's retransmission is reliable — the content matches across its two appearances. And the "Final summary" holds the number that matters: seven of the twelve distinct order_ids have no known problem, and among them, deliberately, is ORD-9509 — the unit_price=60.00 row that remains exactly where module 2 left it, waiting on module 5. This report doesn't claim S04 is now "clean" — it names, with the same precision as a good audit report, exactly what was reviewed and what wasn't.

Diagram: where you came from, where you landed

flowchart LR
    A["Module 1:\nvalidate_orders()\nORD-9508: unflagged"] --> B["Module 2:\nOrdersSchema (Pandera)\nORD-9508: unflagged"]
    B --> C["Lesson 4:\nvalidate_referential_integrity()\nwritten and tested"]
    C --> D["Lesson 5:\ncheck_retransmission_consistency()\nsecond category of rules"]
    D --> E["Lesson 6:\nORD-9508 CAUGHT\nat last, with evidence"]
    E --> F["Lesson 7:\nrun_consistency_checks()\ncombined report"]
    F --> G["This project:\nfinal readable report\n4 of 12 with problems,\n2 dimensions still pending"]
    G --> H["Module 4:\ndata contracts --\nwhat's expected of S04,\nversioned in YAML"]

Closing the module's promise, point by point

run_consistency_checks(), as it stands at this module's close, is one more piece of Kiosko's trust system — reusable over any orders file with the right columns, with no S04 value hand-written inside its logic. Module 4 takes this same system and adds the piece it's still missing: a versioned artifact, contracts/orders_contract.yaml, that states in writing exactly what's expected of an S04 file — not just what the code checks, but what Kiosko agreed with the store —, and that generates the same OrdersSchema module 2 already built, instead of keeping it hand-written in two separate places.

Common mistakes

Considering S04's entire incident resolved because this report "looks complete." What happens: someone, satisfied with this report's three well-organized sections, concludes S04 already went through an exhaustive data quality check. Why it happens: a report with three distinct sections, each with its own result, conveys a sense of thoroughness. How to spot it: check this project's "What to expect" last section — "Pending -- accuracy, freshness" is right there, printed, in the same report. How to fix it: two of the six data quality dimensions still have no real check at all; this module closes consistency (from two angles: cross-table and cross-column), not one dimension more.

Modifying run_consistency_checks() to "get ahead" on module 5's work about ORD-9509. What happens: someone, seeing ORD-9509 still in clean_ids's list, adds a manual condition — say, unit_price < 10 — directly inside run_consistency_checks() to flag it too. Why it happens: it seems like a quick fix, and that specific row's problem is already known. How to spot it: if your price rule is written with a fixed number, with no reference to real historical data, you have the same problem module 2 already warned about with check_business_rules(): a made-up threshold that doesn't sync with any real Kiosko data. How to fix it: this guide's module 5 builds a price baseline computed over Kiosko's clean canonical week (S01-S03), not an arbitrary number — that's the right path, even though it takes a whole module instead of one line of code.

Confusing "final report" with "Kiosko's complete trust system." What happens: someone, seeing this project's "where you came from, where you landed" diagram, assumes nothing relevant remains before the guide's capstone. Why it happens: after three consecutive modules, this report feels like an important conclusion. How to spot it: revisit this lesson's diagram's last row — it explicitly points to module 4 (contracts), not module 8 (the real capstone). How to fix it: this project closes one module out of eight; Kiosko's complete trust system — contract, declarative tests, consistency, anomalies, freshness/volume, lineage, quarantine/alerting, access/masking/catalog — is, specifically, module 8's project, not this one's.

Exercises

Exercise 1 — Run the whole project yourself, from scratch. In a new folder, with kiosko.duckdb (with orders_s04 and dim_product loaded) and this project's consistency_report.py, run python3 consistency_report.py. Confirm you see exactly 4 order_ids with known problems and 7 with none.

Exercise 2 — Add a fourth "covered" dimension when it applies, without hand-writing it. Modify main() so the list of covered dimensions gets computed dynamically — the same way lesson 7's Exercise 1 did — instead of being fixed text ("completeness, uniqueness, validity (module 2), consistency (this module)").

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

covered = {CHECK_TO_DIMENSION[c] for c in report["pandera"]["check"].to_list()}
if report["referential"].height > 0:
    covered.add("consistency")

print(f"\n=== Data quality dimensions: status after this module ===")
print(f"Covered -- {', '.join(sorted(covered))}")
print(f"Pending -- {', '.join(DIMENSIONS_STILL_OPEN)}")

Covered -- completeness, consistency, uniqueness, validity
Pending -- accuracy, freshness

Exercise 3 — Argue why this report, as it stands, shouldn't yet be shown as-is to someone in business at Kiosko. In 2-3 sentences, based on the technical vocabulary this report uses (field_uniqueness, not_nullable, product_id, order_id), explain why one more "translation" step would be needed before sharing it with someone who doesn't know this guide's code.

Summary and next step: closing this module

With this project you close module 3. You learned to distinguish consistency from validity, with precise technical vocabulary — primary key, foreign key, referential integrity —; confirmed, with three pieces of executed evidence, exactly why no single-table schema can solve this problem on its own; wrote two new, reusable tools — validate_referential_integrity() for cross-table consistency, check_retransmission_consistency() for consistency within a table —; and combined them, together with Pandera's OrdersSchema, into a single report that finally catches ORD-9508, the row two earlier modules let through silently.

And, along the way, you made clear — again, with the same honesty as the earlier modules — this module's exact limit: seven of S04's twelve rows have no known problem at this point in the guide, but one of those seven, ORD-9509, still has a real problem — a price fifty times higher than usual — that no check in this module can express, because it needs to be compared against a historical baseline, not against an existence catalog.

Where you go next. This guide's module 4 — Data contracts as versioned artifacts — takes everything S04 has demonstrated it needs so far — a schema, business rules, and now also the relationship with dim_product — and writes it, for the first time, as a versioned artifact any system can read: contracts/orders_contract.yaml. That contract doesn't just describe what's expected of the data — it also generates the same OrdersSchema you already built by hand in module 2, closing the loop between "what Kiosko agreed with S04" and "what the code actually checks."

Resources

• Polars — "Joins" (official user guide), this entire module's base reference. docs.pola.rs/user-guide/transformations/joins. In English.
• Pandera — official documentation (complete DataFrameModel, Field, lazy, SchemaErrors, dataframe_check reference). pandera.readthedocs.io. In English.
• Module 1, project (lesson 8), and module 2, project (lesson 8), of this same guide — the two earlier diagnoses of the same incident this project finally closes. 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 the module 4 that follows. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.