Cross-column consistency rules

Description

Everything this module has built so far compares orders_s04 against another table, dim_product. This lesson opens a second category of consistency rules, with an important difference: it needs no external table at all. It compares columns within the same table, against each other, to confirm they don't contradict one another. The concrete case this lesson solves was already mentioned, in prose, by module 1's lesson 6: when ORD-9502 appears twice in orders_2026-08-14.csv, is it a reliable retransmission — the same sale, counted twice by a network glitch — or are they two different sales that, by coincidence or by a worse bug, ended up with the same order_id? This lesson builds the check that answers that question with evidence, not with a visual read of the CSV.

Connection to the module. Lessons 2 through 4 of this module worked across tables (orders_s04 against dim_product). This lesson works within a single table, comparing rows against each other. Lesson 7 combines both categories — cross-table (lesson 4) and cross-column (this lesson) — together with Pandera's OrdersSchema, into a single report.

An analogy: the package a courier delivers twice

Imagine a courier knocks on your door twice the same day, with what looks like the same package — same tracking number, same sender. There are two possible explanations, and they're easy to tell apart if you open both boxes: the first is that the courier app glitched, generated a duplicate notification, and it's literally the same package, with the same content, delivered (or logged) twice due to a technical error — annoying, but harmless. The second is much more serious: two different packages, with different content, ended up sharing the same tracking number due to an assignment error — there, it isn't a simple retransmission, it's an identifier collision hiding two genuinely different shipments under the same name.

The only way to tell the two cases apart is to open both boxes and compare the content, not just look at the label. ORD-9502, appearing twice in orders_2026-08-14.csv, is exactly this situation: module 2 already confirmed the order_id repeats (uniqueness), but never checked whether the content of the two appearances matches. This lesson opens both boxes.

Worked example: check_retransmission_consistency(), run against real S04

# retransmission_consistency.py
import duckdb
import polars as pl

con = duckdb.connect("kiosko.duckdb")
orders_df = con.sql("SELECT * FROM orders_s04").pl()


def check_retransmission_consistency(df: pl.DataFrame, key: str = "order_id") -> pl.DataFrame:
    """For each 'key' value that repeats, confirms the other columns
    (everything except 'key' and order_ts) match across all its appearances.
    Returns the INCONSISTENT groups -- empty means everything is fine."""
    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


print("=== Groups with a repeated order_id, in orders_s04 ===")
compare_cols = [c for c in orders_df.columns if c not in ("order_id", "order_ts")]
all_repeated = (
    orders_df.group_by("order_id")
    .agg([pl.col(c).n_unique().alias(f"{c}_nunique") for c in compare_cols] + [pl.len().alias("occurrences")])
    .filter(pl.col("occurrences") > 1)
)
print(all_repeated)

print("\n=== check_retransmission_consistency(): INCONSISTENT groups ===")
inconsistent = check_retransmission_consistency(orders_df)
print(f"Inconsistent groups: {inconsistent.height}")
print(inconsistent)

What to expect. Running python3 retransmission_consistency.py, the output is exactly this:

=== Groups with a repeated order_id, in orders_s04 ===
shape: (1, 6)
┌──────────┬──────────────────┬──────────────────┬─────────────────┬─────────────────┬─────────────┐
│ order_id ┆ store_id_nunique ┆ product_id_nuniq ┆ quantity_nuniqu ┆ unit_price_nuni ┆ occurrences │
│ ---      ┆ ---              ┆ ue               ┆ e               ┆ que             ┆ ---         │
│ str      ┆ u32              ┆ ---              ┆ ---             ┆ ---             ┆ u32         │
│          ┆                  ┆ u32              ┆ u32             ┆ u32             ┆             │
╞══════════╪══════════════════╪══════════════════╪═════════════════╪═════════════════╪═════════════╡
│ ORD-9502 ┆ 1                ┆ 1                ┆ 1               ┆ 1               ┆ 2           │
└──────────┴──────────────────┴──────────────────┴─────────────────┴─────────────────┴─────────────┘

=== check_retransmission_consistency(): INCONSISTENT groups ===
Inconsistent groups: 0
shape: (0, 6)
┌──────────┬──────────────────┬──────────────────┬─────────────────┬─────────────────┬─────────────┐
│ order_id ┆ store_id_nunique ┆ product_id_nuniq ┆ quantity_nuniqu ┆ unit_price_nuni ┆ occurrences │
│ ---      ┆ ---              ┆ ue               ┆ e               ┆ que             ┆ ---         │
│ str      ┆ u32              ┆ ---              ┆ ---             ┆ ---             ┆ u32         │
│          ┆                  ┆ u32              ┆ u32             ┆ u32             ┆             │
╞══════════╪══════════════════╪══════════════════╪═════════════════╪═════════════════╪═════════════╡
└──────────┴──────────────────┴──────────────────┴─────────────────┴─────────────────┴─────────────┘

Read both tables together, because they tell the complete story. The first confirms what you already knew from module 1: ORD-9502 repeats (occurrences: 2), and no other column repeats within that group. Notice the exact detail: each comparison column — store_id_nunique, product_id_nunique, quantity_nunique, unit_price_nunique — is 1, not 2. That means, even though ORD-9502 appears twice, every other column has the same single value across both appearances — exactly the definition of a reliable retransmission. The second table, the one that really matters, is empty: check_retransmission_consistency() only keeps groups where some column has more than one distinct value, and there are none. With executed evidence, not just the CSV reading module 1 already did, you confirmed ORD-9502's duplicate pair is the analogy's first type — the same package, delivered twice — never the second.

Diagram: what each of this module's layers compares

flowchart TB
    subgraph M2["Module 2 -- Pandera"]
        U["unique=True on order_id:\nDOES ORD-9502 REPEAT?"]
    end

    subgraph M3L5["This lesson -- cross-column"]
        C["If it repeats:\ndoes the CONTENT of the\ntwo appearances match?"]
    end

    U -->|"Yes, it repeats"| C
    C -->|"Matches (this case)"| OK["Reliable retransmission"]
    C -->|"Does NOT match"| BAD["order_id collision --\na more serious problem"]

The diagram shows this lesson doesn't replace module 2 — it extends it. Pandera's unique=True already answered "does it repeat?" with a yes. This lesson answers the question that comes next, which Pandera never asked: "if it repeats, is it safe to treat as a single sale counted twice?"

Going deeper: when the answer IS "they don't match"

It's worth seeing, with a deliberately built example, what check_retransmission_consistency() reports when the content does differ between appearances — because S04 has no such case, and without seeing one, it's easy to think the function "always returns empty":

# retransmission_consistency_bad.py -- contrast example, not part of S04
import polars as pl

toy_bad = pl.DataFrame({
    "order_id": ["ORD-T1", "ORD-T1", "ORD-T2"],
    "product_id": ["P001", "P001", "P002"],
    "unit_price": [0.55, 0.75, 1.20],
    "order_ts": ["2026-08-14T08:00:00", "2026-08-14T08:07:00", "2026-08-14T08:10:00"],
})

print(check_retransmission_consistency(toy_bad))

What to expect.

shape: (1, 4)
┌──────────┬────────────────────┬────────────────────┬─────────────┐
│ order_id ┆ product_id_nunique ┆ unit_price_nunique ┆ occurrences │
│ ---      ┆ ---                ┆ ---                ┆ ---         │
│ str      ┆ u32                ┆ u32                ┆ u32         │
╞══════════╪════════════════════╪════════════════════╪═════════════╡
│ ORD-T1   ┆ 1                  ┆ 2                  ┆ 2           │
└──────────┴────────────────────┴────────────────────┴─────────────┘

ORD-T1 shows up with unit_price_nunique=2 — its two appearances have different prices (0.55 and 0.75), the same product_id but a price that changed midway. This is exactly the analogy's second case: not a harmless retransmission, but an order_id hiding two different versions of the "same" row. In a production system, a group like this shouldn't be treated the same as S04's ORD-9502 — it needs investigation, not automatic cleanup, because there's no way to know, from this data alone, which of the two prices is correct.

Common mistakes

Confusing "repeated groups" with "inconsistent groups." What happens: someone reads the worked example's all_repeated — one row, ORD-9502 — and concludes they already found a consistency problem. Why it happens: the word "repeated" sounds like a problem, and the worked example's two tables look similar at first glance. How to spot it: check that row's *_nunique columns — all equal 1. A repeated group with every comparison column at 1 is exactly what uniqueness (module 2) already flagged, with no new problem for this lesson to point out. How to fix it: the result that matters from this lesson is always the second block — check_retransmission_consistency() — never the first. A group shows up in the first block every time it repeats; it only shows up in the second when, additionally, some column differs.

Including order_ts in compare_cols, and flagging any retransmission as inconsistent. What happens: someone forgets to exclude order_ts from the list of columns to compare, and check_retransmission_consistency() flags ORD-9502 as inconsistent, because its two appearances have different timestamps (08:12:00 and 09:11:00). Why it happens: it's easy to think "every column should match" without thinking about which one, by design, is the only one expected to change between one retransmission and the next. How to spot it: if your function flags any retransmission as inconsistent, with no exception, check whether you're comparing order_ts — the moment of each send attempt is, almost by definition, different between retries. How to fix it: always exclude the columns that represent "when this got processed" from the set of business columns that must match — the key parameter and the explicit exclusion of order_ts in check_retransmission_consistency() do exactly that.

Treating this function as a replacement for lessons 2 through 4's referential integrity. What happens: someone, satisfied that check_retransmission_consistency() runs and finds no problems in S04, concludes validate_referential_integrity() is no longer needed. Why it happens: both are "consistency checks" from this same module, and it's easy to think one covers the other. How to spot it: ask yourself whether check_retransmission_consistency(), as written, ever consults dim_product — it never does; it only compares orders_s04 against itself. How to fix it: the two functions answer different, complementary questions — one compares the table against another table (cross-table, referential integrity); the other compares the table against itself (cross-column). Lesson 7 combines them, but neither replaces the other.

Exercises

Exercise 1 — Confirm check_retransmission_consistency() never flags any row that appears only once. Without running anything yet, predict: why could ORD-9501 (which appears only once in orders_s04) never show up in check_retransmission_consistency()'s result, no matter what values it has? Then confirm it by reviewing the function's code.

Exercise 2 — Extend toy_bad with a third case, this time with a different product_id instead of unit_price. Add a row to the contrast example's toy_bad where ORD-T2 repeats, but with a different product_id in its second appearance. Run check_retransmission_consistency() against the result and confirm it also flags it.

toy_bad_v2 = pl.DataFrame({
    "order_id": ["ORD-T1", "ORD-T1", "ORD-T2", "ORD-T2"],
    "product_id": ["P001", "P001", "P002", "P004"],
    "unit_price": [0.55, 0.75, 1.20, 1.20],
    "order_ts": ["2026-08-14T08:00:00", "2026-08-14T08:07:00", "2026-08-14T08:10:00", "2026-08-14T08:15:00"],
})

print(check_retransmission_consistency(toy_bad_v2))

shape: (2, 4)
┌──────────┬────────────────────┬────────────────────┬─────────────┐
│ order_id ┆ product_id_nunique ┆ unit_price_nunique ┆ occurrences │
│ ---      ┆ ---                ┆ ---                ┆ ---         │
│ str      ┆ u32                ┆ u32                ┆ u32         │
╞══════════╪════════════════════╪════════════════════╪═════════════╡
│ ORD-T1   ┆ 1                  ┆ 2                  ┆ 2           │
│ ORD-T2   ┆ 2                  ┆ 1                  ┆ 2           │
└──────────┴────────────────────┴────────────────────┴─────────────┘

Exercise 3 — Argue why this function, as written, doesn't tell you "which" appearance is correct when there's an inconsistency. In 2-3 sentences, explain why check_retransmission_consistency() can only tell you that there's an inconsistency, never which of the two versions is the true one, and why that's a reasonable limitation for this function, not a flaw to fix.

Summary and next step

In this lesson you opened a second category of consistency rules — within a single table, needing no external table at all — and built check_retransmission_consistency(), which confirms, with executed evidence, something module 1 had only claimed in prose: ORD-9502's two appearances in orders_2026-08-14.csv are a reliable retransmission, not an identifier collision. You also saw, with a deliberately built contrast example, what the same function reports when the content really does differ between appearances.

Before moving on you should be able to: explain the difference between "an order_id repeats" (uniqueness, module 2) and "the content of those repeats matches" (this lesson); and predict, without running code, whether a row that appears only once could show up in check_retransmission_consistency()'s result.

You have both of this module's tools complete: validate_referential_integrity() (lesson 4, cross-table) and check_retransmission_consistency() (this lesson, cross-column). Lesson 6 goes back to the first one and finally runs it against S04's real file — this module's central moment.

Resources

• Polars — official group_by() and agg() documentation, the basis for check_retransmission_consistency(). docs.pola.rs/api/python/stable/reference/dataframe/api/polars.DataFrame.group_by.html. In English.
• Polars — official any_horizontal() documentation, the function that combines several boolean conditions column by column. docs.pola.rs/api/python/stable/reference/expressions/api/polars.any_horizontal.html. In English.
• Module 1, lesson 6, of this same guide — the first time this guide described, in prose, ORD-9502's duplicate pair as "the exact pattern of a duplicate retransmission, not of two different sales" — the claim this lesson confirms with code. src/guides/data-reliability-and-governance-guide/workbook/module-01-when-green-does-not-mean-correct/es/06-running-the-old-quality-gate-on-s04.md. In Spanish.
• This guide's DESIGN. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.