Catching the dollars-to-cents bug

Description

This is the lesson where ORD-9509 stops being invisible. With no change at all to check_price_baseline() or to reference_prices — the exact same two pieces lessons 4 and 5 built — this lesson runs them over orders_2026-08-14.csv's twelve real rows, and for the first time in four modules, something flags that row.

Connection to the module. This lesson closes the narrative arc lesson 1 opened: four tools, four times ORD-9509 with no flag at all. This is the fifth tool, and the first that does catch it — with executed evidence, not a promise.

An analogy: the security guard, now at the real door

Earlier lessons tested lesson 5's security guard in a drill — three toy badges, one of them clearly fake, to confirm the comparison mechanism works before putting it to real work. This lesson puts it at the real door, with the day's real traffic: S04's twelve rows, with their six known problems and their six genuinely clean rows, mixed exactly as they arrived.

Worked example: check_price_baseline(), run on orders_s04

Step 1 — the same two pieces, with no change at all

# catch_dollars_to_cents.py
import duckdb
import polars as pl


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

Zero new lines of logic — it's, literally, the same code from lessons 4 and 5, copied with no modifications. That's intentional, and it's worth noting before continuing: if this lesson needed to rewrite something to make it work on real data, the earlier lessons' work would have been, at best, a draft. It wasn't.

Step 2 — run on orders_s04

# catch_dollars_to_cents.py -- continuation
con = duckdb.connect("kiosko.duckdb")

reference_prices = build_reference_prices(con)
print(f"reference_prices: {reference_prices}\n")

df = con.sql("SELECT * FROM orders_s04").pl()
print(f"orders_s04.shape: {df.shape}\n")

anomalies = check_price_baseline(df, reference_prices, tolerance=0.5)
print(f"check_price_baseline(df, reference_prices, tolerance=0.5) -> anomalies.shape: {anomalies.shape}")
print(anomalies.select(["order_id", "product_id", "unit_price", "reference_price", "deviation"]))

What to expect. Running python3 catch_dollars_to_cents.py in the folder where you already have kiosko.duckdb with orders (canonical week, lesson 4) and orders_s04 (module 2) loaded, the output is exactly this:

reference_prices: {'P001': 0.55, 'P002': 1.2, 'P003': 0.75, 'P004': 4.5}

orders_s04.shape: (12, 6)

check_price_baseline(df, reference_prices, tolerance=0.5) -> anomalies.shape: (1, 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      │
└──────────┴────────────┴────────────┴─────────────────┴───────────┘

Read it slowly, because four modules of narrative tension resolve into a single table row. ORD-9509, product_id=P002, unit_price=60.0, compared against reference_price=1.2 — an Energy Bar's real price across Kiosko's complete canonical week —, with deviation=49.0. That deviation means 60.00 is 49 times (4900%) above the reference price — put another way, 60.00 / 1.20 = 50, ORD-9509 charged fifty times the product's normal price. With tolerance=0.5 (50%), any deviation above that threshold gets flagged — and 49.0 is light-years from 0.5. No other row in the file appears in this result: exactly one row, exactly the one four earlier modules let through.

The complete diagnosis, closed: S04's six dimensions, all covered per row

With this result, it's worth returning, one last time, to orders_2026-08-14.csv's twelve complete rows and confirming, dimension by dimension, that every one of the six broken rows already has its tool:

Five physical rows, five dimensions, five different tools — and a sixth dimension, freshness, already confirmed violated since module 1, lesson 5, needing no individual row flagged, because it's a property of the whole file. With this lesson, every row-level dimension S04's twelve lines violate already has, each one, its own detection mechanism, built with its own responsibility, with no tool trying to cover another's work.

Diagram: the complete narrative arc, closed

flowchart TD
    A["orders_2026-08-14.csv\n12 rows"] --> B["Module 1: validate_orders()\nORD-9509: valid, no flag"]
    A --> C["Module 2: OrdersSchema\nORD-9509: passing, no flag"]
    A --> D["Module 3: validate_referential_integrity()\nORD-9509: P002 exists, no flag"]
    A --> E["Module 4: contract_to_pandera_schema()\nORD-9509: no known problem"]
    A --> F["Module 5: check_price_baseline()\nORD-9509: ANOMALOUS (deviation=49.0)"]

    B -.->|"4 tools,\nzero flags"| G["The dollars-\nto-cents bug,\ninvisible"]
    C -.-> G
    D -.-> G
    E -.-> G
    F ==>|"the 5th tool,\nwith the correct reference"| H["The bug,\ncaught"]

Going deeper: why this lesson needed no special case for ORD-9509

It's worth noticing something that might go unnoticed: check_price_baseline() has no line of code that mentions ORD-9509, S04, or 60.00. The function is completely generic — it takes any DataFrame with product_id and unit_price columns, and any dictionary of reference prices —, and ORD-9509 gets flagged not because the function knows it, but because the number it brings, compared against the number already known to be correct, produces a deviation that exceeds the configured threshold. This deliberately contrasts with the temptation module 1, lesson 7 already named, of "fixing" a problem by adding a list of hand-coded special cases (if product_id == "P099": ...). A general rule, correctly applied, catches the specific case with no need to know that specific case exists beforehand — the same property validate_referential_integrity() already had in module 3, now confirmed again with a completely different tool.

Common mistakes

Being surprised that only one row gets flagged, expecting to see more. What happens: someone, after seeing validate_orders() flag three rows and OrdersSchema flag four, expects check_price_baseline() to also flag several rows, and gets thrown off seeing only one. Why it happens: the "several rows flagged" pattern already became familiar in earlier modules. How to spot it: review this lesson's "The complete diagnosis" table — accuracy, unlike completeness/uniqueness/validity (which together cover four physical rows), is the only dimension S04's incident design assigns to a single row. How to fix it: the number of rows each tool flags depends on how many real problems of that dimension the file has, not on some general property of the tool — check_price_baseline() would flag as many rows as it found real anomalous prices, no more, no less, and in this specific file that number is one.

Thinking deviation=49.0 means "49% deviation." What happens: someone reads deviation=49.0 and interprets it as a small percentage, confusing it with 0.49 (49%). Why it happens: most percentages people work with daily fall between 0% and 100%, so a number greater than 1.0 in a "deviation" context can feel, at first glance, like a scale error. How to spot it: remember lesson 5's exact formula — deviation is a fraction, not a percentage already multiplied by 100; 49.0 means 4900%, forty-nine times the reference price added on top of itself. How to fix it: if you need to show the deviation as a more readable percentage for a report, multiply by 100 explicitly (deviation * 100) and add the % symbol in the text — never assume the raw number in the deviation column is already on that scale.

Concluding that, with ORD-9509 caught, S04's file is already "clean." What happens: someone, satisfied with this lesson's result, treats S04's file as if it no longer has any pending problem. Why it happens: after five modules, every row-level dimension of the incident already has its own tool — it feels like a complete close-out. How to spot it: review module 1's complete table — six problems, one of them freshness, at the file level, not the row level; no tool in this or earlier modules fixed or resolved that violation, it only named it. How to fix it: this lesson closes the diagnosis of the five row-level dimensions, not the incident's six complete dimensions. This guide's module 6 is, precisely, where freshness (and volume) become a real, runnable check — this guide's work continues.

Exercises

Exercise 1 — Run the complete script yourself, and confirm the exact row. With kiosko.duckdb containing both orders (the canonical week, lesson 4) and orders_s04 (module 2), run python3 catch_dollars_to_cents.py. Confirm anomalies.shape is (1, 5) and the only row is ORD-9509.

Exercise 2 — Calculate how much extra money Kiosko would have charged if ORD-9509 hadn't been caught. Using ORD-9509's quantity=1 and unit_price=60.00, against P002's reference_price=1.20, calculate the difference in real money (not relative deviation) between what got charged and what should have been charged.

overcharge = (60.00 - 1.20) * 1  # (unit_price - reference_price) * quantity
print(f"Difference: {overcharge}")

Difference: 58.8

Exercise 3 — Argue why this lesson, unlike earlier ones, needed no toy example before touching real data. In 2-3 sentences, explain why this lesson went straight to orders_s04, with no repeat of the intermediate toy-data step lesson 5 did use.

Summary and next step

In this lesson you ran check_price_baseline() — with no change at all from lessons 4 and 5 — over orders_2026-08-14.csv's twelve real rows, and confirmed, with executed evidence, the result this entire module promised from its first line: ORD-9509, with unit_price=60.00, gets flagged as anomalous, with deviation=49.0 against a reference_price=1.2 — fifty times an Energy Bar's normal price. With this lesson, S04's incident's five row-level dimensions — completeness, uniqueness, validity, consistency, accuracy — each already have their own detection tool, built with its own responsibility.

Before moving on you should be able to: reproduce this lesson's exact result, including the precise numeric deviation; explain why check_price_baseline() needed no special case for ORD-9509; and complete, from memory, the table of the five row-level dimensions and their five matching tools.

You have the bug caught, with evidence. But one question remains open: how well-chosen is tolerance=0.5? Lesson 7 explores, with real data, what happens when that threshold is miscalibrated — too strict, or too loose.

Resources

• Module 1, lesson 5, of this same guide ("Meet S04: Kiosko's fourth store") — the source of the original freshness calculation, the sixth dimension still pending after this lesson. 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.
• Polars — complete official documentation (group_by, agg, replace_strict, filter, with_columns — the complete set of expressions used across this module). docs.pola.rs. In English.
• src/paths/data-engineering-ecosystem/VALIDACION.md — the market audit that explicitly cites the dollars-to-cents bug as a real incident reported by practitioners. Internal repo document, no public URL.
• This guide's DESIGN — the exact result this lesson confirms with executed evidence. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.