Freshness is a table-level question, not a row-level one

Description

The five quality tools this guide already built — validate_orders(), OrdersSchema, validate_referential_integrity(), module 4's contract, check_price_baseline() — share a way of working: they take a DataFrame, walk through it row by row (or a pair of tables, in module 3's case), and return a verdict per row: this one passes, this one doesn't. Module 1's Valid: 9, Rejected: 3 is, underneath, twelve individual verdicts, summarized into two numbers. This lesson demonstrates, with executed evidence and not just prose, why freshness can't be written that same way — and what happens, exactly, if someone tries anyway.

Connection to the module. Lesson 1 previewed the idea with the milk analogy. This lesson turns it into something verifiable: you're going to really try writing a "per row" version of a freshness check over orders_2026-08-14.csv's twelve lines, and you're going to see, with the executed result in front of you, why that attempt adds no real information to S04's diagnosis.

A complete analogy: the carton's expiration date, not each sip's

A carton of milk has a single expiration date, printed once, on the whole container. Nobody asks each sip of milk "have you, specifically, expired yet?" — the question makes no sense at that level. The sip you took today and the one you'll take tomorrow, from the same carton, share exactly the same expiration date, because that date describes a property of the whole carton — when it was bottled, how long it can stay out of the factory before it stops being safe — not a property of each individual drop of liquid inside.

orders_2026-08-14.csv is the carton. Each of its twelve rows is a sip. Asking "did this specific row arrive late?" makes as little sense as asking a sip of milk whether it expired on its own — the row doesn't have a "when it was reviewed" date, it has an order_ts that says when the sale was generated, a completely different piece of data. What does make sense, and what this lesson is going to demonstrate with real numbers, is asking about the whole file: how much time passed from when its most recent sale was generated, until the moment someone finally reviewed it?

Worked example: the failed attempt at a "per row" check

Before writing the correct check — that's lesson 4 — it's worth seriously attempting the incorrect version: taking each of S04's twelve rows and calculating, individually, how many hours passed between its order_ts and PIPELINE_RUN_AT.

# naive_row_freshness.py
from datetime import datetime

import duckdb
import polars as pl

pl.Config.set_tbl_rows(20)  # all twelve rows, no default truncation "..."

PIPELINE_RUN_AT = "2026-08-16T09:00:00"

con = duckdb.connect("kiosko.duckdb")
con.execute("""
    CREATE OR REPLACE TABLE orders_s04 AS
    SELECT * FROM read_csv('orders_2026-08-14.csv', header=True,
        columns={
            'order_id': 'VARCHAR', 'store_id': 'VARCHAR', 'product_id': 'VARCHAR',
            'quantity': 'BIGINT', 'unit_price': 'DOUBLE', 'order_ts': 'TIMESTAMP'
        })
""")
df = con.sql("SELECT * FROM orders_s04 ORDER BY order_ts").pl()

run_at = datetime.fromisoformat(PIPELINE_RUN_AT)

per_row = df.with_columns(
    ((pl.lit(run_at) - pl.col("order_ts")).dt.total_seconds() / 3600)
    .round(2)
    .alias("hours_since_this_row")
).with_columns(
    pl.when(pl.col("hours_since_this_row") > 24)
    .then(pl.lit("FAIL"))
    .otherwise(pl.lit("PASS"))
    .alias("row_level_status_24h_sla")
).select(["order_id", "order_ts", "hours_since_this_row", "row_level_status_24h_sla"])

print(per_row)
print(f"\nUnique values of row_level_status_24h_sla: {per_row['row_level_status_24h_sla'].unique().to_list()}")
print(f"Range of hours_since_this_row: {per_row['hours_since_this_row'].min()} - {per_row['hours_since_this_row'].max()}")

What to expect. Running python3 naive_row_freshness.py (with orders_2026-08-14.csv in the same folder), the output is exactly this:

shape: (12, 4)
┌──────────┬─────────────────────┬───────────────────────┬───────────────────────────┐
│ order_id ┆ order_ts            ┆ hours_since_this_row   ┆ row_level_status_24h_sla  │
│ ---      ┆ ---                 ┆ ---                    ┆ ---                       │
│ str      ┆ datetime[μs]        ┆ f64                    ┆ str                       │
╞══════════╪═════════════════════╪═══════════════════════╪═══════════════════════════╡
│ ORD-9501 ┆ 2026-08-14 08:05:00 ┆ 48.92                  ┆ FAIL                      │
│ ORD-9502 ┆ 2026-08-14 08:12:00 ┆ 48.8                   ┆ FAIL                      │
│ ORD-9503 ┆ 2026-08-14 08:19:00 ┆ 48.68                  ┆ FAIL                      │
│ ORD-9504 ┆ 2026-08-14 08:27:00 ┆ 48.55                  ┆ FAIL                      │
│ ORD-9505 ┆ 2026-08-14 08:34:00 ┆ 48.43                  ┆ FAIL                      │
│ ORD-9506 ┆ 2026-08-14 08:41:00 ┆ 48.32                  ┆ FAIL                      │
│ ORD-9507 ┆ 2026-08-14 08:49:00 ┆ 48.18                  ┆ FAIL                      │
│ ORD-9508 ┆ 2026-08-14 08:56:00 ┆ 48.07                  ┆ FAIL                      │
│ ORD-9509 ┆ 2026-08-14 09:04:00 ┆ 47.93                  ┆ FAIL                      │
│ ORD-9502 ┆ 2026-08-14 09:11:00 ┆ 47.82                  ┆ FAIL                      │
│ ORD-9510 ┆ 2026-08-14 09:18:00 ┆ 47.7                   ┆ FAIL                      │
│ ORD-9511 ┆ 2026-08-14 09:25:00 ┆ 47.58                  ┆ FAIL                      │
└──────────┴─────────────────────┴───────────────────────┴───────────────────────────┘

Unique values of row_level_status_24h_sla: ['FAIL']
Range of hours_since_this_row: 47.58 - 48.92

Read this table with the same care you've already trained across the five earlier modules. Twelve rows, twelve individual hours_since_this_row calculations — technically, yes, a different number can be calculated for every row, because each order_ts is a few minutes different from the previous one. But look at the row_level_status_24h_sla column: all twelve say exactly the same thing, FAIL. per_row['row_level_status_24h_sla'].unique().to_list() returns a single-element list. Compare this against any of the five row-level dimensions you already built: module 1's validate_orders() gave two different verdicts across the twelve rows (valid/rejected), and inside rejected there were three different reasons. Module 5's check_price_baseline() flagged exactly one of twelve rows as anomalous, leaving the other eleven unflagged. Each of those tools produces real variation, row by row. This "per row" version of freshness produces none: the same bit of information, FAIL, repeated twelve times.

Diagram: real variation versus false variation

flowchart TD
    subgraph A["The five row-level dimensions (M1-M5)"]
        A1["12 rows -> 12 verdicts that DO vary\n(9 valid, 3 rejected, different reasons)"]
    end
    subgraph B["Freshness's 'per row' attempt (this lesson)"]
        B1["12 rows -> 12 slightly different hour numbers\n(47.58 to 48.92)"]
        B2["12 rows -> 1 single repeated verdict\n('FAIL' x 12, with no exception)"]
        B1 --> B2
    end
    B2 --> C["Conclusion: the variation between rows is noise\n(minutes of difference in order_ts).\nThe real information -- fresh or not -- is\nONE single answer for the whole file."]

Going deeper: why the number varies a bit, but the verdict doesn't vary at all

It's worth explaining the difference between the previous table's two columns, because at first glance it looks like a contradiction: hours_since_this_row does change from row to row (47.58, 47.7, 47.82...), but row_level_status_24h_sla never changes. The reason is simple arithmetic: S04's twelve rows were generated within a range of barely 80 minutes, all on the same 2026-08-14, between 08:05 and 09:25. Compared against the same PIPELINE_RUN_AT almost 48 hours later, that 80-minute gap between the first and last sale is tiny — barely 1.34 hours out of the nearly 48 total hours elapsed. The "noise" between rows (each sale's exact minute) never comes close to moving the number enough to cross the SLA's threshold in either direction.

This isn't a coincidence specific to this file — it's a direct consequence of the problem's definition. Any file that arrives complete, generated during a workday (a few hours of sales), is going to have that same pattern: its rows are going to sit very close to each other in time, compared against the hours-or-days scale a freshness SLA usually measures. The real variation — the one that matters for deciding whether the file arrived on time or not — never lived in the difference between rows. It always lived in the difference between the file as a whole and the external clock reviewing it. This module's lesson 4 builds check_freshness() exactly on that idea: a single timestamp representing the whole file (the most recent of its rows), compared once against PIPELINE_RUN_AT.

Common mistakes

Thinking that, since the hour number does vary per row, the "per row" check is more precise than a whole-file one. What happens: someone sees hours_since_this_row changing from 47.58 to 48.92 between rows, and concludes calculating it this way gives finer-grained information than a single number for the whole file. Why it happens: more digits, more rows with their own value, feels like more precision. How to spot it: ask yourself what decision changes with that extra precision — if all twelve rows produce the same verdict (FAIL) with no exception, the variation between 47.58 and 48.92 doesn't change any action Kiosko would take. How to fix it: distinguish between "a number that varies" and "information that matters" — this lesson demonstrated they're different things. check_freshness(), in lesson 4, uses a single number (the file's most recent order_ts) precisely because adding more numbers changes no real decision.

Trying to "fix" the problem by calculating hours_since_this_row's average instead of the maximum or minimum. What happens: someone, noticing that twelve individual numbers aren't very useful, decides to summarize them with AVG(hours_since_this_row), instead of using the most recent row's value. Why it happens: averaging is the first instinct for "summarizing many numbers into one." How to spot it: ask yourself what an average actually measures here — it would mix the day's oldest sale with its most recent, diluting the signal that truly matters (how up to date the file is relative to its most recent data). How to fix it: lesson 4 uses the maximum order_ts — the most recent sale the file contains —, not an average. That's the industry's standard freshness definition: comparing the newest available data against the review moment, not an average of the file's whole history.

Confusing "every row shares a verdict" with "freshness doesn't need to review the data." What happens: someone concludes, after this lesson, that freshness can be calculated without opening the file, just by looking at the name (orders_2026-08-14.csv already states the date). Why it happens: the filename, in this case, does match the real date of the sales it contains. How to spot it: ask yourself what would happen if the filename were wrong, or if a delayed file arrived with "today's" name instead of the sales' real day — the name is a convention, not a guarantee. How to fix it: check_freshness(), in lesson 4, always measures the real date inside the data (order_ts, the column), never the filename — the only reliable source, because nobody can accidentally edit it without also editing the data itself.

Exercises

Exercise 1 — Confirm the 80-minute range cited in Going deeper. Using naive_row_freshness.py's df DataFrame, calculate the difference between S04's twelve rows' maximum and minimum order_ts, in minutes.

delta = df.select(
    ((pl.col("order_ts").max() - pl.col("order_ts").min()).dt.total_seconds() / 60).alias("range_minutes")
)
print(delta)

shape: (1, 1)
┌───────────────┐
│ range_minutes │
│ ---           │
│ f64           │
╞═══════════════╡
│ 80.0          │
└───────────────┘

Exercise 2 — Construct a hypothetical file where the variation between rows would actually matter. Imagine an S04 file with sales spread across three complete days (not 80 minutes), where the first row is from 2026-08-12 and the last from 2026-08-14. In 2-3 sentences, explain whether check_freshness(), as you're going to build it in lesson 4 (based on the maximum order_ts), would still be the correct way to measure freshness in that case.

Exercise 3 — Argue why this lesson's row_level_status_24h_sla could never have had two different values, without running the code again. Using only Exercise 1's 80.0-minute number and the 24-hour SLA (1440 minutes), explain why it was mathematically impossible for any S04 row to cross the 24-hour threshold while another one didn't.

Summary and next step

In this lesson you confirmed, with executed evidence and not just the milk analogy, why freshness can't be written as a "per row" check: the attempt — calculating hours_since_this_row for each of S04's twelve rows — does produce numbers that vary slightly, but the verdict that truly matters (FAIL/PASS against a 24-hour SLA) turned out identical across all twelve, ['FAIL'], a single element. You contrasted this against the real variation the five row-level dimensions this guide already built do produce, and you understood why the maximum order_ts — not the average, not the minimum — is the correct way to summarize how up to date a whole file is.

Before moving on you should be able to: explain, citing this lesson's executed result, why S04's twelve rows share the same freshness verdict; and justify why the maximum order_ts, not an average, is the correct reference for measuring how recent a file is.

Freshness is already confirmed, conceptually, as a whole-file property. A second, equally important problem remains: against exactly which moment does that file get compared. Lesson 3 answers that — and the answer isn't "the system's current time."

Resources

• Polars — official documentation, date and time expressions (.dt.total_seconds(), the Datetime arithmetic used in this lesson's worked example). docs.pola.rs. In English.
• dbt Labs — official documentation, "Add freshness checks to sources" (the industry's freshness definition: comparing the maximum loaded_at_field against the review moment, never a per-row average — the same principle this lesson uses, independently verified against a real production tool). docs.getdbt.com/reference/resource-properties/freshness. In English.
• Module 1, lesson 5, of this same guide — the original source of the freshness calculation this lesson picks back up with a more precise definition. 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.
• This guide's DESIGN. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.