Writing a referential integrity check

Description

This lesson builds the entire module's central piece: validate_referential_integrity(), a Polars function that compares two DataFrames and precisely returns the rows from one that have no counterpart in the other. Before touching S04's real data — that's lesson 6 —, this lesson writes it and tests it against a small, controlled example, so the mechanics are clear without the distraction of twelve real rows. It also loads dim_product into kiosko.duckdb for the first time in this guide: until now, the file only contained orders_s04.

Connection to the module. This lesson builds the tool lessons 1 through 3 justified. Lesson 5 adds a second category of rules (cross-column, within a single table), and lesson 6 applies this same function, with no changes, to S04's real file.

An analogy: the turnstile that actually checks the real card registry

Picture two subway station turnstiles. The first only checks that the card you present has the right size and chip — a shape check, exactly like validate_orders() or OrdersSchema. The second does the same, but also checks, on the spot, the transit system's real database of active cards: if the card has the correct chip but was never registered, or got deactivated, the turnstile rejects it, even though the piece of plastic is, physically, indistinguishable from a valid one. validate_referential_integrity() is that second turnstile: it doesn't care whether product_id "looks right" — OrdersSchema already confirmed that in module 2 —, it checks the real catalog, dim_product, and answers, with evidence, whether the reference really exists.

Worked example: dim_product into kiosko.duckdb, and the anti-join first over small data

Step 1 — load dim_product into kiosko.duckdb

kiosko.duckdb, as module 2 left it, only has one table: orders_s04. This lesson adds the second table this module needs, with the same four products data-engineering-foundations-guide (module 4) already established and every guide in this ecosystem has reused since then:

# load_dim_product.py
import duckdb

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

print(f"dim_product loaded: {con.sql('SELECT COUNT(*) FROM dim_product').fetchone()[0]} rows\n")
print(con.sql("SELECT * FROM dim_product ORDER BY product_id"))

What to expect. Running python3 load_dim_product.py in the folder where you already have module 2's kiosko.duckdb, the output is exactly this:

dim_product loaded: 4 rows

┌────────────┬───────────────────────┬─────────────┬───────────┐
│ product_id │     product_name      │  category   │ unit_cost │
│  varchar   │        varchar        │   varchar   │  double   │
├────────────┼───────────────────────┼─────────────┼───────────┤
│ P001       │ Bottled Water 600ml   │ beverages   │       0.4 │
│ P002       │ Energy Bar            │ snacks      │      0.6  │
│ P003       │ Instant Coffee Sachet │ beverages   │      0.35 │
│ P004       │ Phone Charger Cable   │ electronics │       2.1 │
└────────────┴───────────────────────┴─────────────┴───────────┘

CREATE OR REPLACE TABLE, the same decision already explained in module 2 — safe to run as many times as needed —, and four INSERTs with Kiosko's catalog's exact values: the same product_id, product_name, category, and unit_cost data-engineering-foundations-guide already used since its module 4. kiosko.duckdb now has two tables — orders_s04 (module 2) and dim_product (this lesson) —, ready to be compared against each other.

Step 2 — validate_referential_integrity(), first over a toy example

Before touching S04's real data, test the function with three hand-built rows, where you already know ahead of time which one should fail:

# referential_check_toy.py
import polars as pl


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


toy_orders = pl.DataFrame({
    "order_id": ["ORD-T1", "ORD-T2", "ORD-T3"],
    "product_id": ["P001", "P777", "P002"],
    "quantity": [2, 1, 3],
})

toy_products = pl.DataFrame({
    "product_id": ["P001", "P002", "P003"],
    "product_name": ["Bottled Water 600ml", "Energy Bar", "Instant Coffee Sachet"],
})

print("toy_orders:")
print(toy_orders)
print("\ntoy_products:")
print(toy_products)

orphans = validate_referential_integrity(toy_orders, toy_products)
print(f"\norphans.shape: {orphans.shape}")
print(orphans)

What to expect.

toy_orders:
shape: (3, 3)
┌──────────┬────────────┬──────────┐
│ order_id ┆ product_id ┆ quantity │
│ ---      ┆ ---        ┆ ---      │
│ str      ┆ str        ┆ i64      │
╞══════════╪════════════╪══════════╡
│ ORD-T1   ┆ P001       ┆ 2        │
│ ORD-T2   ┆ P777       ┆ 1        │
│ ORD-T3   ┆ P002       ┆ 3        │
└──────────┴────────────┴──────────┘

toy_products:
shape: (3, 2)
┌────────────┬───────────────────────┐
│ product_id ┆ product_name          │
│ ---        ┆ ---                   │
│ str        ┆ str                   │
╞════════════╪═══════════════════════╡
│ P001       ┆ Bottled Water 600ml   │
│ P002       ┆ Energy Bar            │
│ P003       ┆ Instant Coffee Sachet │
└────────────┴───────────────────────┘

orphans.shape: (1, 3)
shape: (1, 3)
┌──────────┬────────────┬──────────┐
│ order_id ┆ product_id ┆ quantity │
│ ---      ┆ ---        ┆ ---      │
│ str      ┆ str        ┆ i64      │
╞══════════╪════════════╪══════════╡
│ ORD-T2   ┆ P777       ┆ 1        │
└──────────┴────────────┴──────────┘

Exactly one orphan row, ORD-T2, with product_id="P777" — a value that exists in toy_orders but never in toy_products. ORD-T1 (P001) and ORD-T3 (P002) disappear from the result because they do have a counterpart: the anti-join discards them, not because they're wrong, but because they're the good rows — validate_referential_integrity() returns exclusively the ones that fail, the same contract module 1's validate_orders() and module 2's Pandera SchemaErrors.failure_cases already followed: an empty DataFrame means "everything is fine," you never have to interpret an absence as a silent half-success.

Diagram: the anti-join's mechanics, step by step

flowchart LR
    A["toy_orders\n3 rows"] --> C{"join on='product_id'\nhow='anti'"}
    B["toy_products\n3 rows"] --> C
    C --> D["For each toy_orders\nrow: does it have a match\nin toy_products?"]
    D -->|"Yes (P001, P002)"| E["gets DISCARDED\n(the reference is valid)"]
    D -->|"No (P777)"| F["gets KEPT\nORD-T2, product_id=P777"]

The anti-join flips the usual join intuition: in an inner join, you keep what matches; in an anti join, you keep exactly what doesn't match. Polars's official documentation says it precisely: "an anti join will return the rows of the left dataframe that do not have a match in the right dataframe" — the DataFrame on the left is always orders_df in validate_referential_integrity(), never the other way around (this lesson's Common mistakes section goes deeper into what happens if it's reversed).

Going deeper: why a join and not a simple is_in()

Someone familiar with Polars might wonder why use join(how="anti") instead of something more direct, like filtering with is_in(). The two paths, in this specific case, reach the same result:

# is_in_equivalent.py -- same question, a different path
import polars as pl

via_is_in = toy_orders.filter(~pl.col("product_id").is_in(toy_products["product_id"].to_list()))
print(via_is_in)

With the same toy_orders/toy_products from the worked example, via_is_in produces exactly the same row as orphans — ORD-T2. So why does this guide choose join? Three concrete reasons, not an arbitrary preference: first, is_in() needs to materialize the entire reference column in memory as a Python list before comparing (.to_list()), while join lets Polars's engine decide the most efficient strategy, something that matters once dim_product stops having four rows and grows to thousands; second, join generalizes with no changes to composite keys — for example, if Kiosko ever needed to compare (store_id, product_id) together, on=["store_id", "product_id"] would be enough, while is_in() has no clean way to express that; third, and most important for the rest of this guide, a join with how="left" (not "anti") is exactly the mechanism lesson 5 needs to bring in columns from the reference table (like unit_cost) into the orders DataFrame, something is_in(), by design, can never do because it only returns True/False. Learning join now, instead of is_in(), leaves you using the same tool for several related problems.

Common mistakes

Reversing the DataFrames' order in the join. What happens: someone writes dim_product_df.join(orders_df, on="product_id", how="anti") — backward from how validate_referential_integrity() is defined — and the result's meaning changes with no visible error. Why it happens: a join in Polars isn't symmetric — A.join(B, how="anti") and B.join(A, how="anti") answer different questions —, and it's easy not to notice because the syntax looks nearly identical. How to spot it: if your result has dim_product's columns (product_name, category) instead of orders's columns (order_id, quantity), you reversed the order. How to fix it: remember the exact rule — the DataFrame that calls .join() is always the one that supplies the result's rows; orders_df.join(dim_product_df, ...) asks "which orders_df rows have no match?", while dim_product_df.join(orders_df, ...) asks "which dim_product_df rows have no match?" — a legitimate question (which products never sold), but completely different from referential integrity.

Confusing how="anti" with how="inner". What happens: someone, in a hurry, writes how="inner" expecting to get the orphan rows, and the result comes out empty or with the wrong rows. Why it happens: inner is the best-known how value, and it's easy to write it out of habit without thinking about which semantics are needed. How to spot it: an inner join keeps the rows that do match — if your goal is finding problems and your result has 11 of 12 rows instead of 1, you probably swapped anti for inner. How to fix it: memorize the exact opposition — inner keeps matches, anti keeps the absence of a match. They're, literally, complementary results over the same pair of tables.

Not checking the on column's types before joining. What happens: someone builds dim_product_df with product_id as a numeric type (say, if someone loaded the catalog from a source that interpreted "P001" as text but "099" as a number), and the join doesn't find matches that should exist, with no error thrown. Why it happens: Polars compares values in the on column respecting their type — "P001" (string) and a numeric value are never equal, even if they "look" similar when printed. How to spot it: if orphans includes rows you know should have a valid reference, print orders_df.schema and dim_product_df.schema and compare the product_id column's exact type in each. How to fix it: exactly the same discipline this guide's module 2 lesson 4 already established — explicitly declaring types when loading each table from read_csv() or INSERT — prevents this problem before it exists.

Exercises

Exercise 1 — Reproduce the toy example with a key that does have a counterpart on both sides. Modify toy_orders so all three rows use product_ids that really exist in toy_products (P001, P002, P003). What do you expect validate_referential_integrity() to return?

toy_orders_clean = pl.DataFrame({
    "order_id": ["ORD-T1", "ORD-T2", "ORD-T3"],
    "product_id": ["P001", "P002", "P003"],
    "quantity": [2, 1, 3],
})

orphans_clean = validate_referential_integrity(toy_orders_clean, toy_products)
print(f"orphans_clean.shape: {orphans_clean.shape}")

orphans_clean.shape: (0, 3)

Exercise 2 — Confirm the join's asymmetry, by reversing the order on purpose. Run toy_products.join(toy_orders, on="product_id", how="anti") (the same DataFrames from the worked example, order reversed) and explain, in 1-2 sentences, what question this result answers.

reversed_result = toy_products.join(toy_orders, on="product_id", how="anti")
print(reversed_result)

shape: (1, 2)
┌────────────┬───────────────────────┐
│ product_id ┆ product_name          │
│ ---        ┆ ---                   │
│ str        ┆ str                   │
╞════════════╪═══════════════════════╡
│ P003       ┆ Instant Coffee Sachet │
└────────────┴───────────────────────┘

Exercise 3 — Write the equivalent is_in() version for S04's real data, without running it yet. Based on this lesson's Going deeper section, write (without running it, just as a code-reading exercise) the line equivalent to validate_referential_integrity(orders_df, dim_product_df) using is_in(), for the real orders_s04/dim_product table you're going to use in lesson 6.

orphans_via_is_in = orders_df.filter(
    ~pl.col("product_id").is_in(dim_product_df["product_id"].to_list())
)

Summary and next step

In this lesson you loaded dim_product into kiosko.duckdb for the first time in this guide, and wrote validate_referential_integrity() — a three-line Polars function that uses an anti-join to return exactly the rows with no real counterpart in the reference table. You tested it against a toy example, where you already knew ahead of time which row should fail, and confirmed the result matched exactly what was expected. You also saw, with evidence, why this guide chooses join over is_in() as the base tool.

Before moving on you should be able to: explain how="anti"'s exact semantics, citing Polars's official documentation; and explain why reversing the two DataFrames' order in the join completely changes the question being answered.

You have the function ready, tested, and kiosko.duckdb with the two tables it needs. Lesson 5 opens a second category of consistency rules — this time within a single table —, and lesson 6 runs validate_referential_integrity(), with no changes, against S04's real twelve-line file.

Resources

• Polars — "Joins" (official user guide, semi- and anti-join section, with this lesson's exact quote). docs.pola.rs/user-guide/transformations/joins. In English.
• Polars — API reference, DataFrame.join() (the method's complete signature, how's valid values). docs.pola.rs/api/python/stable/reference/dataframe/api/polars.DataFrame.join.html. In English.
• data-engineering-foundations-guide, module 4 — the exact source of dim_product's four products (product_id, product_name, category, unit_cost) this lesson loads into kiosko.duckdb. src/guides/data-engineering-foundations-guide/workbook/module-04-modeling-your-first-tables/es/04-designing-kioskos-dimension-tables.md. In Spanish.
• Module 2, lesson 4, of this same guide — the source of the CREATE OR REPLACE TABLE pattern this lesson reuses for dim_product. src/guides/data-reliability-and-governance-guide/workbook/module-02-declarative-data-quality-tests-with-pandera/es/04-installing-pandera-and-bridging-duckdb-to-polars.md. In Spanish.
• This guide's DESIGN — the exact validate_referential_integrity(orders_df, dim_product_df) signature this lesson implements. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.