What consistency means across tables

Description

Module 1's lesson 3 already defined the six data quality dimensions, and already gave consistency a precise definition: "Does every reference to another table actually point to something that exists there?", checked at "row level, against another table." This lesson stops on that definition and develops it in depth — what a "reference" precisely is, why the correct technical name is referential integrity, and why the word "consistency" in general database vocabulary is broader than what this module builds — because the rest of this guide uses this vocabulary precisely, without re-explaining it.

Connection to the module. Lesson 1 showed the distinction with an analogy (the airplane boarding pass). This lesson gives that same distinction the correct technical vocabulary, and lesson 3 demonstrates, with evidence executed against real orders_s04, why no single-table schema can close it.

Consistency in the broad sense, versus consistency in this guide's sense

A clarification is worth making before continuing, because "consistency" is a word that also shows up in other data engineering contexts with different meanings. In the world of transactional databases, the C in ACID (Atomicity, Consistency, Isolation, Durability) refers to a transaction never leaving the database in a state that violates its own declared rules — types, UNIQUE constraints, CHECK constraints —; it's a database engine guarantee about individual transactions. In streaming, "eventual consistency" versus "strong consistency" describes how fast every node in a distributed system sees the same value after a write. Neither of those two ideas is what this module works with.

This guide's consistency — one of the six data quality dimensions module 1 defined — is more specific: it's the property that a reference declared in one table points to something that really exists in another table (or, as you'll see in lesson 5, that two columns in the same row don't contradict each other). The precise technical name for the first case — the one that dominates this module — is referential integrity, a term that comes straight from the relational database model, older than any tool in this guide. ORD-9508's product_id="P099" is, in precise vocabulary, a referential integrity violation: orders_s04 has a column meant to represent a product, and that product doesn't exist in dim_product, the table that should contain it.

An analogy: the key and the lock, not the key alone

Picture a physical key, freshly cut at a hardware store. You can verify, with no door needed at all, that the key has the correct shape: the material is metal, the tooth size is as expected, it isn't bent. That's validity — a property of the key, in isolation. But there's a completely different question no inspection of the key alone can answer: does this key open any real lock? To answer that, you need the lock — the other half of the relationship. A perfectly well-cut key that doesn't match any lock in your house is a useless key, even though it's, in itself, a perfect object.

product_id is the key. dim_product is the complete ring of real locks that exist at Kiosko. A well-formed product_id — a string, with the P-followed-by-three-digits pattern — is a well-cut key. But "well-cut" isn't the same as "opens something real." Referential integrity is, precisely, the question of whether the key opens a lock that really exists.

Worked example: the relationship between orders and dim_product, explained with the right vocabulary

In the relational model's vocabulary, dim_product.product_id is dim_product's primary key — the identifier that distinguishes, unambiguously, every row in that table. orders_s04.product_id is a foreign key: a column that, by design, is expected to contain only values that also exist as a primary key in dim_product. This code, with no dependency yet — no Polars, no DuckDB —, expresses that relationship in the simplest possible way, with plain Python structures:

# foreign_key_vocabulary.py
dim_product = [
    {"product_id": "P001", "product_name": "Bottled Water 600ml"},
    {"product_id": "P002", "product_name": "Energy Bar"},
    {"product_id": "P003", "product_name": "Instant Coffee Sachet"},
    {"product_id": "P004", "product_name": "Phone Charger Cable"},
]

orders_sample = [
    {"order_id": "ORD-9501", "product_id": "P001"},
    {"order_id": "ORD-9508", "product_id": "P099"},
]

primary_keys = {row["product_id"] for row in dim_product}
print(f"dim_product's primary key (primary_keys): {sorted(primary_keys)}\n")

for order in orders_sample:
    fk_value = order["product_id"]
    is_valid_reference = fk_value in primary_keys
    print(f"{order['order_id']}: product_id (foreign key) = '{fk_value}' -> "
          f"{'points to a real row' if is_valid_reference else 'DOES NOT EXIST in dim_product'}")

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

dim_product's primary key (primary_keys): ['P001', 'P002', 'P003', 'P004']

ORD-9501: product_id (foreign key) = 'P001' -> points to a real row
ORD-9508: product_id (foreign key) = 'P099' -> DOES NOT EXIST in dim_product

ORD-9501 has referential integrity: its foreign key (P001) points to a row that really exists in dim_product. ORD-9508 violates it: its foreign key (P099) has no matching row. Referential integrity's complete mechanics — this module's technical heart — is exactly this set comparison, fk_value in primary_keys, applied to each row. Lesson 4 does the same thing, but vectorized, over a complete Polars DataFrame, instead of a row-by-row for loop.

Diagram: two tables, one arrow that can point to nothing

flowchart LR
    subgraph orders_s04
        O1["ORD-9501\nproduct_id: P001"]
        O2["ORD-9508\nproduct_id: P099"]
    end

    subgraph dim_product
        P1["P001\nBottled Water 600ml"]
        P2["P002\nEnergy Bar"]
        P3["P003\nInstant Coffee Sachet"]
        P4["P004\nPhone Charger Cable"]
    end

    O1 -->|"valid reference"| P1
    O2 -.->|"broken reference\n(P099 doesn't exist)"| X["? (nothing)"]

ORD-9501's solid arrow reaches a real destination — P001, a row that exists in dim_product. ORD-9508's dotted arrow reaches nowhere: P099 isn't a row in dim_product, it's just a string that looks like a reference, without actually being one. That's the complete picture of a referential integrity violation: not a value "broken" in the sense of having the wrong type, but an arrow pointing into empty space.

Going deeper: why "broken reference" is different from "empty reference"

It's worth distinguishing two ways a foreign key can fail, because this module only deals with one of them. If product_id were empty or null — the problem completeness already solved in module 2, with Field(nullable=False) — there would be no reference to check at all: no arrow to trace from nothing. Referential integrity, on the other hand, presupposes that a value is actually present, and asks whether that specific value corresponds to something real. ORD-9508 doesn't have an empty product_id — it has a perfectly present one, "P099" —; the problem is that specific value has no counterpart. This distinction matters in practice: a well-designed pipeline runs completeness before consistency (exactly the order of this guide's modules 2 and 3), because it doesn't make sense to ask "does this reference exist?" about a field that doesn't even have a value to look up.

Common mistakes

Using "consistency" and "referential integrity" as if they were synonyms in any context. What happens: someone, after this lesson, starts calling any kind of inconsistency "referential integrity," including the ones lesson 5 is going to call cross-column. Why it happens: this lesson presents referential integrity as consistency's main case, and it's easy to over-generalize. How to spot it: if you're describing a problem that doesn't involve any second table — say, two columns in the same row contradicting each other — "referential integrity" isn't the correct term, even though it's still a consistency violation in module 1's broad sense. How to fix it: reserve "referential integrity" specifically for this lesson's foreign-key-against-primary-key case; lesson 5 gives its own name to rules within a single table.

Thinking referential integrity is exclusive to "real" relational databases (Postgres, MySQL). What happens: someone assumes this problem only exists in systems with an explicitly declared FOREIGN KEY, and that a CSV, lacking that mechanism, is exempt from the concept. Why it happens: the vocabulary (primary key, foreign key) historically comes from relational systems with active constraints. How to spot it: orders_2026-08-14.csv is a plain text file — no database engine protects it, and yet ORD-9508 violates referential integrity in exactly the same way it would in any table with a misconfigured FOREIGN KEY. How to fix it: the concept — a reference that points to nothing real — is independent of the storage format. What changes between a CSV and a database with an active FOREIGN KEY isn't whether the problem can exist, it's who detects it and when — this module's lesson 3 goes deeper exactly into that difference.

Confusing "primary key" with "any column that identifies something." What happens: someone, reading dim_product, assumes product_name could also serve as a primary key, because it also identifies the product recognizably. Why it happens: product_name really is unique in Kiosko's current four-product catalog. How to spot it: ask yourself whether that column is guaranteed to be unique and immutable over time — product_name could get changed (a rebrand, a spelling fix) without the product stopping being the same product, while product_id is designed, by convention, to never change. How to fix it: a dimensional table's primary key is, almost always, the technical identifier — product_id, store_id —, not the human-readable name; data-engineering-foundations-guide (module 4) already established this when designing dim_product for the first time.

Exercises

Exercise 1 — Add a third order to orders_sample and confirm its referential integrity. Using this lesson's worked example, add {"order_id": "ORD-9504", "product_id": "P004"} to orders_sample and run the loop again. What result do you expect, and why?

orders_sample.append({"order_id": "ORD-9504", "product_id": "P004"})

for order in orders_sample:
    fk_value = order["product_id"]
    is_valid_reference = fk_value in primary_keys
    print(f"{order['order_id']}: product_id (foreign key) = '{fk_value}' -> "
          f"{'points to a real row' if is_valid_reference else 'DOES NOT EXIST in dim_product'}")

ORD-9504: product_id (foreign key) = 'P004' -> points to a real row

Exercise 2 — Build a referential integrity violation in the opposite direction. The entire worked example checks that every orders_sample product_id exists in dim_product. In 2-3 sentences, explain why the opposite direction — checking that every dim_product product_id appears in orders_sample — would not be a referential integrity violation, even though it also compares the same two tables.

Exercise 3 — Name the primary key and the foreign key in a different Kiosko relationship, with no code. orders_s04 also has a store_id column, and Kiosko has a dim_store table (S01 through S04). In one sentence, name which is the primary key and which is the foreign key in that relationship, following the same pattern as product_id/dim_product.

Summary and next step

In this lesson you gave precise technical vocabulary to the distinction you already saw in lesson 1: primary key, foreign key, referential integrity — the terms the relational database model has used for decades to describe exactly ORD-9508's problem. You distinguished consistency from the broad sense of "consistency" in other contexts (ACID, distributed systems), and saw, with a minimal plain-Python example, the central mechanics that hold up this entire module: comparing a value against a set of valid references.

Before moving on you should be able to: use the terms "primary key" and "foreign key" correctly, without mixing them up; and explain why an empty foreign key (completeness) is a different problem from a present but nonexistent foreign key (consistency).

Lesson 3 takes this vocabulary and puts it up against module 2's real OrdersSchema — with executed evidence of why, no matter how many declarative rules you add to it, a single-table schema can never close this gap.

Resources

• Wikipedia — "Referential integrity" (the term's standard definition, its origin in E.F. Codd's relational model). en.wikipedia.org/wiki/Referential_integrity. In English.
• DuckDB — official documentation, constraints section (PRIMARY KEY, FOREIGN KEY, REFERENCES) — the standard SQL syntax that expresses this same relationship inside the engine. duckdb.org/docs/current/sql/constraints.html. In English.
• data-engineering-foundations-guide, module 4 — the original source of dim_product (product_id, product_name, category, unit_cost) and of dim_store, the two dimensional tables this lesson uses as a primary key example. src/guides/data-engineering-foundations-guide/workbook/module-04-modeling-your-first-tables/es/. In Spanish.
• Module 1, lesson 3, of this same guide — the original definition of the six data quality dimensions, including consistency, which this lesson develops in depth. src/guides/data-reliability-and-governance-guide/workbook/module-01-when-green-does-not-mean-correct/es/03-six-dimensions-of-data-quality.md. In Spanish.
• This guide's DESIGN. src/guides/data-reliability-and-governance-guide/DISENO.md. In Spanish.