The Problem of Identity

A row in a relational table stands for 1 thing in the world: 1 customer, 1 product, 1 line on 1 order. The database holds the row's columns, and the modeller supplies the rule that says which thing the row stands for. Identity is that rule. When the rule is missing, the database stores 2 rows for 1 customer without complaint, and every query that assumes 1 row per customer returns wrong numbers with no error.

Dorota Kaminski, Twice

FreshCart loaded its customers table from the mobile app and from the website checkout. Each source wrote a row per sign-up, and nothing in the table stopped 2 rows from describing the same person. Dorota Kaminski signed up on both.

The 2 rows differ by 1 capital letter and a channel. To the database they are 2 rows; to the business they are 1 customer with 6 orders in March totalling $412. The orders table stored the email she typed at checkout, so a revenue query joining on email matches each order against both rows.

SELECT c.name, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON LOWER(o.email) = LOWER(c.email)
WHERE o.order_month = '2024-03'
GROUP BY c.name;

Six orders became 12 and $412 became $824. The LOWER() that was meant to tolerate casing is what made both rows match, and the join multiplied every order row by the number of customer rows it found. Across 40 million customers, 1.8 million had a second row, and the March revenue report overstated the settled total by 11%.

Updates and deletes go wrong the same way

Joins are the loudest symptom, and writes carry the same defect. An UPDATE customers SET marketing_opt_in = false WHERE email = 'priya.nair@example.com' changes 1 of her 2 rows, and the next campaign emails her anyway. A GDPR erasure DELETE removes 1 row and leaves the other for an auditor to find. A deduplication job keeping the newest row per name merges Dorota with every other Dorota Kaminski in the table.

Each failure traces to the same missing rule: no column, or set of columns, that the schema guarantees is unique per entity, so nothing can say 'this row and only this row'. Joins and writes need that rule to land on exactly 1 row, and deduplication needs it to know what 'same' means.

Identity is a modelling decision

The database can't invent the rule, but it enforces one once declared: a PRIMARY KEY rejects a repeated value, and a UNIQUE index on LOWER(email) would have rejected Dorota's second sign-up. Whether 2 rows with the same email are 1 customer is the modeller's question, because a household can share an inbox and 1 person can hold 2 accounts, and it gets answered before any constraint is written. The answer starts from the grain, what 1 row is, and the identity column is whatever makes 1 row at that grain distinguishable from every other.

FreshCart's orders table has 3 rows with order_number 'FC-88102', each with a different updated_at. A report that sums total per order shows triple revenue for that order. Which statement describes the defect?

• The report should use MAX(total) instead of SUM(total).
• orders has no column that is unique per order, so the table cannot tell a new order from a new version of an old one.
• updated_at should be dropped so the rows become identical and can be deduplicated.
• The report needs DISTINCT on order_number.

The defect lives in the table, and every fix applied in a query is a patch over it. The column that carries identity, declared so the database enforces it, is the primary key, and choosing one that holds up is the next section.

About this lesson

This section is part of the Keys & Identity lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.