Project: S04's accuracy audit

Description

This project closes the module. You have the complete diagnosis (lessons 1 through 3), the baseline built over the canonical week (lesson 4), the detection function tested on toy data (lesson 5), the real result on S04 (lesson 6), and its threshold's calibration (lesson 7). One step remains: a closing script that brings check_price_baseline() together with module 3's validate_referential_integrity(), and reports, with executed evidence, the complete state of the six data quality dimensions after this module — exactly the same kind of honest audit modules 3 and 4's projects already closed with.

Connection to the module. This project introduces no new concept — it's the synthesis of lessons 2 through 7, applied end to end to the same incident modules 1 through 4 diagnosed. It closes this module's thread exactly where module 6 picks it back up: the only dimension still with no runnable check is freshness, at the file level, not the row level — the next module's central work.

An analogy: the audit report, with a new section added

Module 3 already built the analogy of the complete audit report, with one section per reviewed area and an explicit list of what still hasn't been reviewed. This project is that same audit, with a new section: where module 3's report ended saying "pending: accuracy, freshness," this project adds the accuracy section to the report, with its own finding, its own evidence, and its own flagged row — and updates the pending list to a single remaining item.

The material you need

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

module_5_accuracy/
├── kiosko.duckdb              (orders_s04 from module 2, orders from this module's lesson 4)
└── accuracy_audit.py          (this project)

If your kiosko.duckdb doesn't have the orders table (the canonical week, forty rows) yet, repeat this module's lesson 4, step 1 before continuing — this project doesn't re-explain that step, it assumes you've already done it. This project also loads dim_product again (module 3's same four-product catalog), so a single report can recall validate_referential_integrity()'s result alongside check_price_baseline()'s.

The verified reference solution

# accuracy_audit.py -- module 5 closing project
import duckdb
import polars as pl

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

con.execute("""
    CREATE OR REPLACE TABLE dim_product (
        product_id VARCHAR,
        product_name VARCHAR,
        category VARCHAR,
        unit_cost DOUBLE
    )
""")
con.execute("""
    INSERT INTO dim_product VALUES
        ('P001', 'Bottled Water 600ml', 'beverages', 0.40),
        ('P002', 'Energy Bar', 'snacks', 0.60),
        ('P003', 'Instant Coffee Sachet', 'beverages', 0.35),
        ('P004', 'Phone Charger Cable', 'electronics', 2.10)
""")


def build_reference_prices(con: duckdb.DuckDBPyConnection) -> dict[str, float]:
    """Calculates the per-product reference price over the clean canonical week (S01-S03)."""
    week_df = con.sql("SELECT * FROM orders").pl()
    baseline = (
        week_df.group_by("product_id")
        .agg(pl.col("unit_price").mean().alias("reference_price"))
        .sort("product_id")
    )
    return dict(zip(baseline["product_id"].to_list(), baseline["reference_price"].to_list()))


def check_price_baseline(
    df: pl.DataFrame, reference_prices: dict[str, float], tolerance: float = 0.5
) -> pl.DataFrame:
    """Rows in df whose unit_price deviates from the baseline beyond `tolerance`."""
    return (
        df.with_columns(
            pl.col("product_id").replace_strict(reference_prices, default=None).alias("reference_price")
        )
        .filter(pl.col("unit_price").is_not_null() & pl.col("reference_price").is_not_null())
        .with_columns(
            ((pl.col("unit_price") - pl.col("reference_price")).abs() / pl.col("reference_price"))
            .alias("deviation")
        )
        .filter(pl.col("deviation") > tolerance)
    )


def validate_referential_integrity(orders_df: pl.DataFrame, dim_product_df: pl.DataFrame) -> pl.DataFrame:
    """Rows in orders_df whose product_id does NOT exist in dim_product_df (module 3, no change)."""
    return orders_df.join(dim_product_df, on="product_id", how="anti")


DIMENSIONS_STILL_OPEN = ["freshness"]


def main() -> None:
    print("=== Kiosko: S04's accuracy audit ===\n")

    reference_prices = build_reference_prices(con)
    print(f"reference_prices (canonical week S01-S03): {reference_prices}\n")

    df = con.sql("SELECT * FROM orders_s04").pl()
    dim_product_df = con.sql("SELECT * FROM dim_product").pl()
    print(f"orders_s04: {df.height} rows | dim_product: {dim_product_df.height} rows\n")

    print("=== 1. check_price_baseline(df, reference_prices, tolerance=0.5) ===")
    price_anomalies = check_price_baseline(df, reference_prices, tolerance=0.5)
    print(price_anomalies.select(["order_id", "product_id", "unit_price", "reference_price", "deviation"]))

    print("\n=== 2. validate_referential_integrity(df, dim_product_df) (module 3, reminder) ===")
    orphans = validate_referential_integrity(df, dim_product_df)
    print(orphans.select(["order_id", "product_id"]))

    flagged_accuracy = set(price_anomalies["order_id"].to_list())
    flagged_consistency = set(orphans["order_id"].to_list())
    flagged_completeness = set(df.filter(pl.col("unit_price").is_null())["order_id"].to_list())
    flagged_validity = set(df.filter(pl.col("quantity") <= 0)["order_id"].to_list())

    dup_counts = df.group_by("order_id").agg(pl.len().alias("n"))
    duplicated_ids = set(dup_counts.filter(pl.col("n") > 1)["order_id"].to_list())
    # the 2nd physical appearance gets flagged, the same criterion validate_orders has used since foundations
    flagged_uniqueness_rows = (
        df.with_row_index()
        .filter(pl.col("order_id").is_in(list(duplicated_ids)))
        .sort("index")
        .group_by("order_id")
        .tail(1)
    )
    flagged_uniqueness = set(flagged_uniqueness_rows["order_id"].to_list())

    dimensions_covered = {
        "completeness": flagged_completeness,
        "uniqueness": flagged_uniqueness,
        "validity": flagged_validity,
        "consistency": flagged_consistency,
        "accuracy": flagged_accuracy,
    }

    print("\n=== 3. Summary by dimension (row-level, not counting freshness) ===")
    for dim, ids in dimensions_covered.items():
        print(f"  {dim:<14}{sorted(ids)}")

    n_dimensions = sum(1 for ids in dimensions_covered.values() if ids)
    print(f"\nRow-level dimensions covered through module 5: {n_dimensions} of 6")
    print(f"Pending -- {', '.join(DIMENSIONS_STILL_OPEN)}")

    physical_problem_rows = df.with_row_index().filter(
        pl.col("order_id").is_in(list(flagged_accuracy | flagged_consistency | flagged_completeness | flagged_validity))
        | pl.col("order_id").is_in(list(duplicated_ids))
    )
    print(f"\nPhysical rows with at least one known problem: {physical_problem_rows.height} of {df.height}")
    print(f"Genuinely clean physical rows: {df.height - physical_problem_rows.height} of {df.height}")


if __name__ == "__main__":
    main()

What to expect (verified by actually running python3 accuracy_audit.py, with kiosko.duckdb containing orders_s04, orders, and dim_product, polars==1.43.2, duckdb==1.5.5):

=== Kiosko: S04's accuracy audit ===

reference_prices (canonical week S01-S03): {'P001': 0.55, 'P002': 1.2, 'P003': 0.75, 'P004': 4.5}

orders_s04: 12 rows | dim_product: 4 rows

=== 1. check_price_baseline(df, reference_prices, tolerance=0.5) ===
shape: (1, 5)
┌──────────┬────────────┬────────────┬─────────────────┬───────────┐
│ order_id ┆ product_id ┆ unit_price ┆ reference_price ┆ deviation │
│ ---      ┆ ---        ┆ ---        ┆ ---             ┆ ---       │
│ str      ┆ str        ┆ f64        ┆ f64             ┆ f64       │
╞══════════╪════════════╪════════════╪═════════════════╪═══════════╡
│ ORD-9509 ┆ P002       ┆ 60.0       ┆ 1.2             ┆ 49.0      │
└──────────┴────────────┴────────────┴─────────────────┴───────────┘

=== 2. validate_referential_integrity(df, dim_product_df) (module 3, reminder) ===
shape: (1, 2)
┌──────────┬────────────┐
│ order_id ┆ product_id │
│ ---      ┆ ---        │
│ str      ┆ str        │
╞══════════╪════════════╡
│ ORD-9508 ┆ P099       │
└──────────┴────────────┘

=== 3. Summary by dimension (row-level, not counting freshness) ===
  completeness  ['ORD-9503']
  uniqueness    ['ORD-9502']
  validity      ['ORD-9507']
  consistency   ['ORD-9508']
  accuracy      ['ORD-9509']

Row-level dimensions covered through module 5: 5 of 6
Pending -- freshness

Physical rows with at least one known problem: 6 of 12
Genuinely clean physical rows: 6 of 12

Read the full result with the same care you've already trained in earlier projects. Sections 1 and 2 confirm, in a single report, the two findings that until now lived in different modules: ORD-9509 by accuracy, ORD-9508 by consistency. Section 3 is the summary that closes module 1's complete diagnosis: five row-level dimensions, each with exactly one row flagged, none with more than one. And the final two lines contain the number that has anchored this entire guide since its design: six of orders_2026-08-14.csv's twelve physical rows have at least one known problem, and six are genuinely clean — the exact "six clean, six broken, one per dimension" structure this guide's design fixed from the start, now confirmed with executed evidence, dimension by dimension, instead of assumed.

Diagram: where you came from, where you arrived

flowchart LR
    A["Module 1:\ndiagnosis -- 4 tools,\nORD-9509 with no flag"] --> B["Lesson 2:\nwhy accuracy is\nthe hardest dimension"]
    B --> C["Lesson 3:\nORD-9509 dissected --\n4 checks OK, none is accuracy"]
    C --> D["Lesson 4:\nreference_prices from\nthe canonical week"]
    D --> E["Lesson 5:\ncheck_price_baseline()\ntested on toy data"]
    E --> F["Lesson 6:\nORD-9509 CAUGHT\ndeviation=49.0"]
    F --> G["Lesson 7:\ntolerance calibrated --\nneither 50 nor 0.05"]
    G --> H["This project:\n5 of 6 dimensions,\n6 of 12 rows with a problem"]
    H --> I["Module 6:\nfreshness, volume,\nlineage"]

Closing the module's promise, point by point

With this project, check_price_baseline() and reference_prices stop being one module's isolated work — from here on, they're the fifth piece of a data quality system that already covers five of the six dimensions module 1 defined. Module 6 doesn't touch any individual row again: freshness and volume are properties of the whole file, and they're, precisely, this list's last pending item.

Common mistakes

Thinking "5 of 6 dimensions" means S04 is "almost ready" for production. What happens: someone, satisfied with the progress — from zero dimensions covered in module 1 to five at this project's close —, concludes there's little work left before Kiosko can fully trust S04's data. Why it happens: 5 of 6 feels, numerically, like nearly complete progress. How to spot it: review what the missing dimension actually is — freshness, already confirmed violated since module 1, lesson 5 (57 hours of delay past expected arrival, 33 hours past the 24-hour SLA). It isn't a "minor" dimension compared to the other five, it's an already-known, already-confirmed violation, simply without a formal check yet. How to fix it: treat the dimension count as a measure of tool coverage, not of the file's real state — S04 still has a file that arrived late, no matter how many row-level dimensions already have their own check.

Recalculating dimensions_covered by hand, instead of reusing the sets the script already builds. What happens: someone, extending this project with an additional report, filters df from scratch again for each dimension, instead of reusing flagged_completeness, flagged_validity, etc., already calculated. Why it happens: it's easy to forget which variables already exist when a script grows. How to spot it: if your script extension has more than one line filtering df.filter(pl.col("quantity") <= 0) or its equivalent, you're already duplicating logic the original script already calculated. How to fix it: this project's dimensions_covered dictionary already centralizes the five sets of flagged order_id — any additional report should build from it, not recalculate each filter from scratch.

Forgetting why uniqueness in this project reports a single order_id, not two physical rows. What happens: someone, comparing this project's result against module 2's (which reported ORD-9502 twice, once for each physical appearance), gets confused seeing this report count it only once in section 3. Why it happens: the two projects deliberately use a different counting unit. How to spot it: review the flagged_uniqueness line in the script — it groups by order_id and takes the last physical row (group_by("order_id").tail(1)), the same "the second appearance gets flagged" criterion validate_orders() has established since foundations. How to fix it: when comparing reports from different modules of this guide, always check whether they're counting distinct order_id or physical rows — they aren't the same unit, and this guide uses both in different contexts, always explaining which one it's using each time.

Exercises

Exercise 1 — Run the whole project yourself, from scratch. In a new folder, with kiosko.duckdb containing orders_s04 (module 2) and orders (this module's lesson 4), run python3 accuracy_audit.py. Confirm you see exactly 5 of 6 dimensions and 6 of 12 physical rows with some problem.

Exercise 2 — Extend the report to explicitly show the six genuinely clean physical rows. Using physical_problem_rows already calculated in main(), add a block that prints the order_id of the rows that have no known problem — S04's six clean rows.

clean_rows = df.with_row_index().filter(~pl.col("index").is_in(physical_problem_rows["index"].to_list()))
print(f"\nGenuinely clean physical rows ({clean_rows.height}):")
for row in clean_rows.select(["order_id", "product_id", "unit_price"]).iter_rows(named=True):
    print(f"  {row['order_id']} | product_id={row['product_id']} | unit_price={row['unit_price']}")

Genuinely clean physical rows (6):
  ORD-9501 | product_id=P001 | unit_price=0.55
  ORD-9504 | product_id=P004 | unit_price=4.5
  ORD-9505 | product_id=P001 | unit_price=0.55
  ORD-9506 | product_id=P003 | unit_price=0.75
  ORD-9510 | product_id=P004 | unit_price=4.5
  ORD-9511 | product_id=P002 | unit_price=1.2

Exercise 3 — Argue whether check_price_baseline() should run before or after validate_referential_integrity() in a real pipeline. In 2-3 sentences, considering that check_price_baseline() silently excludes rows with an unknown product_id (already seen in lesson 5), argue whether the execution order between the two functions matters for the final result.

Summary and next step: closing this module

With this project you close module 5. You learned, with evidence accumulated across four earlier modules, why accuracy is the hardest of the six dimensions to test; you dissected ORD-9509 check by check, confirming a row can pass completeness, uniqueness, validity, and consistency and still be wrong; you built reference_prices over the only portion of Kiosko's data already confirmed reliable, never over the file under suspicion; you wrote check_price_baseline(), tested first on toy data, and ran it against the real incident — ORD-9509, caught, with deviation=49.0 —; and you calibrated its threshold, seeing with evidence what happens at both extremes, too loose and too strict. And, along the way, you kept the Machine Learning boundary firm in every lesson: every rule in this module is explainable in a single sentence.

With this project, five of S04's six data quality dimensions already have their own runnable check: completeness, uniqueness, and validity (modules 1-2), consistency (module 3), and now accuracy (this module). One dimension remains unresolved, and it isn't one more row — it's a property of the whole file, already confirmed violated since this guide's first lesson.

Where you go next. Module 6 — Freshness, volume, and lineage — closes the complete diagnosis of the six dimensions: check_freshness(df, run_at=PIPELINE_RUN_AT, sla_hours=24), run on the same file, is going to fail — the first time in this guide a file-level check actually runs, not just gets calculated by hand as in module 1 —; check_volume(df, min_rows=5, max_rows=20) is going to pass, the deliberate contrast that not everything in S04 is broken. And, beyond the six dimensions, that module maps Kiosko's lineage for the first time: where every column of the warehouse eight earlier guides in this ecosystem built actually comes from.

Resources

• Polars — complete official documentation (group_by, agg, replace_strict, join, filter, with_row_index — the complete set of expressions used across this module). docs.pola.rs. In English.
• Module 1, lesson 5, of this same guide ("Meet S04: Kiosko's fourth store") — the source of the freshness calculation module 6 picks back up. src/guides/data-reliability-and-governance-guide/workbook/module-01-when-green-does-not-mean-correct/en/05-meet-s04-kioskos-fourth-store.md. In English.
• Module 3, project (lesson 8), of this same guide — this project's exact comparison baseline (DIMENSIONS_STILL_OPEN, the same honest-reporting pattern). src/guides/data-reliability-and-governance-guide/workbook/module-03-consistency-and-referential-checks/en/08-project-s04s-full-consistency-report.md. In English.
• This guide's DESIGN — the complete map of all eight modules, including the module 6 that follows. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.