Building a price baseline from Kiosko's clean week

Description

This lesson builds, for the first time in this guide, reference_prices — the dictionary that answers the first of accuracy's two business questions: what's the normal price of each Kiosko product? The answer doesn't come from any new file, or from any assumption — it comes from the canonical week, the forty sales rows eight earlier guides in this ecosystem already used, reviewed, and confirmed reliable, from 2026-08-03 through 2026-08-09.

Connection to the module. This lesson solves, with real code, the problem lesson 3 left open: a baseline needs to live outside the file under suspicion. Here you build exactly that — and only then does lesson 5 write the function that uses it to compare.

An analogy: the normal price of milk at your usual store

You go to your usual store to buy milk, and without a second thought you know, roughly, how much it should cost — not because you memorized it from an official catalog, but because you've bought it there dozens of times before, always at a similar price. That accumulated knowledge is your personal baseline: it doesn't come from today's purchase — the one you're about to make, the one you don't yet know is going to be right or wrong — it comes from an already-confirmed history, built from earlier purchases you trust. If today that same milk costs fifty times more, you don't need any official catalog to notice — the contrast against what you already know is normal is immediate and obvious.

reference_prices is exactly that accumulated knowledge, applied to Kiosko. It isn't built by looking at the S04 file suspected of being problematic — that would be like deciding milk's normal price by looking at today's price, which is exactly what you want to verify. It's built by looking at the canonical week: forty already-confirmed purchases, already reviewed by eight complete guides in this ecosystem, the equivalent of "every earlier time you bought this milk and know the price was right."

Worked example: reference_prices, calculated over the canonical week's 40 rows

Step 1 — the canonical week, loaded into kiosko.duckdb

Kiosko's canonical week — Monday 2026-08-03 to Sunday 2026-08-09, forty order lines, already built in data-engineering-foundations-guide — lives in kiosko.duckdb's orders table, alongside orders_s04 (module 2) and dim_product (module 3):

# load_canonical_week.py
import duckdb

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

con.execute("""
    CREATE OR REPLACE TABLE orders AS
    SELECT * FROM read_csv('canonical_week.csv', header=True,
        columns={
            'order_id': 'VARCHAR', 'store_id': 'VARCHAR', 'product_id': 'VARCHAR',
            'quantity': 'BIGINT', 'unit_price': 'DOUBLE', 'order_ts': 'TIMESTAMP'
        })
""")

row_count = con.sql("SELECT COUNT(*) FROM orders").fetchone()[0]
print(f"Rows loaded into orders (canonical week S01-S03): {row_count}")

canonical_week.csv is exactly the concatenation of the seven daily files data-engineering-foundations-guide already built and validated — Monday through Sunday, eight stores per day, forty rows total — with no S04 row mixed in. CREATE OR REPLACE TABLE, the same decision already explained in module 2: safe to run as many times as needed.

What to expect.

Rows loaded into orders (canonical week S01-S03): 40

Step 2 — cross into Polars, and calculate the reference price with group_by().agg()

# build_reference_prices.py
import duckdb
import polars as pl

con = duckdb.connect("kiosko.duckdb")
week_df = con.sql("SELECT * FROM orders").pl()

print(f"week_df.shape: {week_df.shape}")

baseline = (
    week_df.group_by("product_id")
    .agg(pl.col("unit_price").mean().alias("reference_price"))
    .sort("product_id")
)

print("\nbaseline (group_by('product_id').agg(mean)):")
print(baseline)

reference_prices = dict(zip(baseline["product_id"].to_list(), baseline["reference_price"].to_list()))
print(f"\nreference_prices: {reference_prices}")

week_df.group_by("product_id") groups the forty rows by product — Polars's official documentation describes this pattern as group_by + agg in its aggregation guide: group, and apply an aggregation expression (pl.col("unit_price").mean()) over each group separately. The result, baseline, is a four-row DataFrame — one per product —, which then gets turned into the reference_prices dictionary with dict(zip(...)), the same pattern contract_to_pandera_schema() already used in module 4 to build a dictionary from two parallel columns.

What to expect.

week_df.shape: (40, 6)

baseline (group_by('product_id').agg(mean)):
shape: (4, 2)
┌────────────┬─────────────────┐
│ product_id ┆ reference_price │
│ ---        ┆ ---             │
│ str        ┆ f64             │
╞════════════╪═════════════════╡
│ P001       ┆ 0.55            │
│ P002       ┆ 1.2             │
│ P003       ┆ 0.75            │
│ P004       ┆ 4.5             │
└────────────┴─────────────────┘

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

Four reference prices, one per product — and notice a fact worth confirming before moving on: each of those four numbers is exactly the price you already knew from eight earlier guides in this ecosystem (P001=0.55, P002=1.20, P003=0.75, P004=4.50). That's not a coincidence or a rounding error — it confirms a real fact about the canonical week: each product sold, across that week's forty rows, always at the exact same price, with no variation at all. You can confirm this yourself with the next step.

Step 3 — confirm, with evidence, that the canonical week has no price variation at all

# confirm_zero_variance.py -- continuation of build_reference_prices.py
variance_check = (
    week_df.group_by("product_id")
    .agg(
        pl.col("unit_price").std().alias("stdev"),
        pl.col("unit_price").n_unique().alias("distinct_prices"),
    )
    .sort("product_id")
)
print(variance_check)

What to expect.

shape: (4, 3)
┌────────────┬───────┬─────────────────┐
│ product_id ┆ stdev ┆ distinct_prices │
│ ---        ┆ ---   ┆ ---             │
│ str        ┆ f64   ┆ u32             │
╞════════════╪═══════╪═════════════════╡
│ P001       ┆ 0.0   ┆ 1               │
│ P002       ┆ 0.0   ┆ 1               │
│ P003       ┆ 0.0   ┆ 1               │
│ P004       ┆ 0.0   ┆ 1               │
└────────────┴───────┴─────────────────┘

Standard deviation 0.0, a single distinct price per product, for all four rows. This isn't an accident of this guide's design — it's, in fact, exactly what you'd expect from an already-validated canonical week: if those forty rows had any real price variation, reference_prices would still be valid as a reasonable average, but this zero-variance confirmation lets you know, with evidence, that in this specific case AVG(unit_price) and "the price that appears in every row" are, literally, the same number.

Diagram: where each piece of reference_prices comes from

flowchart LR
    A["canonical_week.csv\n7 files, Monday-Sunday\n40 rows, S01-S03"] -->|"read_csv() with\nexplicit columns="| B["kiosko.duckdb\norders table"]
    B -->|"con.sql('SELECT * FROM orders').pl()"| C["week_df\npolars.DataFrame (40, 6)"]
    C -->|"group_by('product_id')\n.agg(mean())"| D["baseline\n4 rows, one price per product"]
    D -->|"dict(zip(...))"| E["reference_prices\n{P001: 0.55, P002: 1.2,\nP003: 0.75, P004: 4.5}"]

Going deeper: why the source is the canonical week, and not orders_s04 averaged against itself

It's worth being explicit about an alternative someone might consider reasonable at first glance: why not calculate reference_prices by averaging orders_s04's own twelve prices, instead of bringing in a completely different table? The answer has two parts. Lesson 3 already anticipated the first: averaging the file under suspicion against itself is circular — if ORD-9509 were included in that average, P002's reference price would come out distorted ((1.20 + 60.00 + 1.20 + 1.20) / 4 = 15.90, almost thirteen times the real price), and the anomaly itself would end up contaminating the very yardstick used to measure it. The second part is more general: the canonical week isn't just "another dataset" — it's, specifically, the dataset eight complete guides in this ecosystem already used, reviewed, and confirmed correct, starting with data-engineering-foundations-guide. Using it as the baseline's source isn't an arbitrary choice by this lesson: it's the decision to build on the only portion of Kiosko's history that already has, literally, the largest number of eyes on it confirming it's right.

Common mistakes

Calculating AVG(unit_price) directly in SQL, and being surprised by the result. What happens: someone, instead of bringing the data into Polars with group_by().agg(.mean()), runs SELECT product_id, AVG(unit_price) FROM orders GROUP BY product_id directly in DuckDB, and when printing the result sees numbers with floating-point noise instead of this lesson's clean values.

print(con.sql("SELECT product_id, AVG(unit_price) AS reference_price FROM orders GROUP BY product_id ORDER BY product_id"))

┌────────────┬────────────────────┐
│ product_id │  reference_price   │
│  varchar   │       double       │
├────────────┼────────────────────┤
│ P001       │ 0.5499999999999999 │
│ P002       │ 1.1999999999999997 │
│ P003       │               0.75 │
│ P004       │                4.5 │
└────────────┴────────────────────┘

Why it happens: AVG() in SQL and .mean() in Polars are, in theory, the same mathematical operation, but each engine sums the values internally in a different order, and floating-point arithmetic (DOUBLE/Float64, the same IEEE 754 standard in both cases) isn't perfectly associative — summing P001's sixteen values in one order can give a result with less noise than summing them in another order, even though the mathematically correct result is identical. How to spot it: if your reference prices have long, ugly decimal digits (0.5499999999999999 instead of 0.55) right after a SQL AVG(), it isn't an error in your data — it's floating-point noise accumulated during the sum. How to fix it: this guide always builds reference_prices with week_df.group_by("product_id").agg(pl.col("unit_price").mean()) in Polars — the same mathematical result, with less visible noise in this specific case — never with a direct SQL AVG(). And, more generally: never compare two floating-point values with an exact == in any language — this module's lesson 5 always uses a relative deviation against a tolerance comparison, never an exact equality, precisely so it doesn't depend on floating-point noise happening to disappear.

Building reference_prices on orders_s04 instead of on the canonical week. What happens: someone, in a hurry, reuses the orders_s04 table already loaded since module 2, instead of loading canonical_week.csv as a new table. Why it happens: orders_s04 is already in kiosko.duckdb, so it seems like the path of least resistance. How to spot it: if your reference_prices for P002 comes out different from 1.20 — say, something close to 15.90, as this lesson's Going deeper section already calculated —, you've already built the baseline on the file under suspicion. How to fix it: recall the milk analogy — the baseline always comes from already-confirmed purchases, never from the purchase currently being evaluated. reference_prices gets calculated, always, on orders (the canonical week), never on orders_s04.

Forgetting reference_prices is a fixed dictionary, not a function recalculated every time. What happens: someone writes code that recalculates reference_prices from scratch every time a price needs comparing, instead of calculating it once and reusing the result. Why it happens: it seems "safer" to always recalculate, instead of trusting a stored value. How to spot it: if your code calls week_df.group_by(...).agg(...) inside a loop that processes each S04 row, you're repeating an expensive, unnecessary calculation. How to fix it: reference_prices gets calculated once — as this lesson did — and the result (a simple dictionary of four key-value pairs) gets reused to compare any number of new rows, exactly as check_price_baseline() is going to do in lesson 5.

Exercises

Exercise 1 — Reproduce the complete calculation yourself, from scratch. With canonical_week.csv (the canonical week's forty rows, already known from data-engineering-foundations-guide) in your working folder, run load_canonical_week.py and build_reference_prices.py in order. Confirm you get exactly {'P001': 0.55, 'P002': 1.2, 'P003': 0.75, 'P004': 4.5}.

Exercise 2 — Calculate reference_prices filtering only the week's first three days, and compare it. Modify load_canonical_week.py's SQL query to include only Monday through Wednesday's files (2026-08-03 through 2026-08-05, sixteen rows total according to data-engineering-foundations-guide's module 2), and recalculate reference_prices only over those rows. Does the result change?

partial_week_df = con.sql("SELECT * FROM orders WHERE order_ts < '2026-08-06'").pl()
print(f"partial_week_df.shape: {partial_week_df.shape}")

partial_baseline = (
    partial_week_df.group_by("product_id")
    .agg(pl.col("unit_price").mean().alias("reference_price"))
    .sort("product_id")
)
print(partial_baseline)

partial_week_df.shape: (16, 6)
shape: (4, 2)
┌────────────┬─────────────────┐
│ product_id ┆ reference_price │
│ ---        ┆ ---             │
│ str        ┆ f64             │
╞════════════╪═════════════════╡
│ P001       ┆ 0.55            │
│ P002       ┆ 1.2             │
│ P003       ┆ 0.75            │
│ P004       ┆ 4.5             │
└────────────┴═════════════════┘

Exercise 3 — Argue whether reference_prices should update automatically every week, or stay fixed. In 2-3 sentences, considering that Kiosko changes P002's price on 2026-08-15 (a fact already resolved by data-modeling-for-analytics-guide, dbt-analytics-engineering-guide, and lakehouse-and-iceberg-guide), argue whether reference_prices, as this lesson builds it, would still be useful after that price change, or would need recalculating.

Summary and next step

In this lesson you built reference_prices, with executed evidence: a dictionary of four prices, one per product, calculated with group_by("product_id").agg(pl.col("unit_price").mean()) over Kiosko's canonical week's forty rows — never over the file under suspicion. You confirmed, with a second, independent calculation (std(), n_unique()), that the canonical week has no price variation at all, and you understood why building the baseline on orders_s04 instead of on orders would be a circular error, with a concrete numeric example (15.90 instead of 1.20).

Before moving on you should be able to: reproduce reference_prices from scratch, with the exact values for all four keys; explain why SQL's AVG() and Polars's .mean() can give results with different floating-point noise on the same data; and explain, with your own numeric example, why building the baseline on the file under suspicion would be circular.

You have the baseline ready. Lesson 5 finally writes the function that uses it to compare — check_price_baseline() —, tested first on toy data before touching S04's real file.

Resources

• Polars — "Aggregation" (official user guide, the group_by().agg() pattern with expressions like .mean(), including a nested-grouping example). docs.pola.rs/user-guide/expressions/aggregation. In English.
• Polars — API reference, DataFrame.group_by() and GroupBy.agg() (complete method signatures used in this lesson). docs.pola.rs/api/python/stable/reference/dataframe/group_by.html. In English.
• DuckDB — "Integration with Polars" (the same .pl() bridge already used since this guide's module 2). duckdb.org/docs/lts/guides/python/polars. In English.
• data-engineering-foundations-guide, module 2, lesson 7 ("Parsing a week of orders") — the exact source of the canonical week's forty rows this lesson loads as canonical_week.csv. src/guides/data-engineering-foundations-guide/workbook/module-02-batch-vs-streaming-and-the-sla/en/07-parsing-a-week-of-orders.md. In English.
• This guide's DESIGN — reference_prices's exact signature and its source (the canonical week S01-S03). src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.