Module 2: Clean and trust the data · Lesson 2.5 · about 40 min
Duplicates, keys and entity resolution
Tell copies, new versions and genuine repeats apart, keep the latest version with row_number(), and match messy names with a reviewed mapping table.
Module 2 · about 40 minutes · read first: 2.1, 2.2. You’ll be able to tell the three kinds of “duplicate” apart, keep the latest version of each record with a window function, create natural, surrogate and line-number keys deliberately, and match messy names across systems without trusting a score blindly.
1. Why this matters
Double-counted revenue is the bug that ends trust in a dashboard. Deleted real sales are just as bad, and harder to notice. Both come from treating every repeated-looking row the same way.
Think of a library. Two identical photocopies of the same page are a true duplicate: keep one. The second edition of a book replaces the first on the “current” shelf, while the first stays in the archive. And two copies of a bestseller on the shelf are not duplicates at all: two readers can borrow them.
2. Three kinds of “duplicate”
| Kind | Example | Fix |
|---|---|---|
| Exact re-send | The till re-sent a batch; every column is identical | distinct on all business columns (keep the file lineage in raw) |
| New version | Hotel booking 512004 appears again with status = cancelled |
Keep the latest version per business key |
| Genuine repeat | Two separate Classic Burgers on one order | Not a duplicate. It needs a line number, or a quantity |
Keeping the latest version is a job for a window function: a calculation over a group of rows that keeps every row. row_number() numbers the rows inside each group in the order you choose.
-- latest version per booking (DuckDB, Postgres and SQL Server all support this)
select * from (
select *, row_number() over (partition by booking_id
order by file_received_at desc, src_row desc) as rn
from raw_hotel_bookings
) where rn = 1;
-- DuckDB shortcut
select * from raw_hotel_bookings
qualify row_number() over (partition by booking_id order by file_received_at desc) = 1;
The src_row desc tie-breaker matters: if one file holds two versions of the same booking, the later row wins, and the result is the same on every run.
Watch seven rows become four bookings:
booking_id and sort each group newest file first.row_number() gives 1 to the newest row in each group, 2 to the next, and so on.Maths and principle
Principle: deduplication picks one representative from each group of rows that share a key.
Equation: say two rows are equivalent, , when . The deduplicated table holds one row from each group, chosen by an order (latest received), so its size is
the number of distinct keys.
Analogy: simplifying fractions: 2/4, 3/6 and 1/2 are one group, represented by 1/2. Where it breaks: if is too coarse (order_id alone for order lines), you merge rows that are not the same thing, which is how real sales disappear.
Try it: 1,000 booking rows, 880 distinct booking_ids. After keeping the latest per id, how many rows? (880.)
3. Keys you create
- Natural key: comes from the business (
booking_id,store_code,sku). It can change, clash between systems, or be reused. - Surrogate key: made by you, meaningless and stable. In a warehouse it is an identity number, or a hash of the source plus the natural key:
md5(source || '|' || booking_id). - Line number: when the source has none, create one deterministically:
row_number() over (partition by order_id order by src_row). Deterministic means a rerun gives exactly the same numbers.
4. Entity resolution: same thing, different names
“Store #104”, “STORE 104 KING ST” and “St. 104 - King” can be the same store in three systems. Entity resolution is deciding which records describe the same real thing. The steps:
- Normalise: case, punctuation, common abbreviations (
St.toStreet). - Block: compare only plausible pairs, for example names sharing a region or a number, so you do not compare every name with every other.
- Score: a string similarity from 0 to 100.
- Decide with a threshold, measuring precision and recall on a hand-labelled sample.
- Keep a manual mapping table that overrides the algorithm.
from rapidfuzz import process, fuzz
canon = stores["store_name"].tolist()
match, score, idx = process.extractOne("STORE 104 KING ST", canon, scorer=fuzz.token_sort_ratio)
For people (guest records across hotels, loyalty members) use a probabilistic matcher such as Splink, which learns how much each field (name, email, postcode) counts as evidence.
- Exact duplicate
- A row identical in every business column to another: a re-send. Keep one.
- Version
- The same record again with changes. Keep the latest in staging, all of them in raw.
- Window function
- A calculation over a group of rows that keeps every row, such as
row_number(). - Surrogate key
- A meaningless, stable id you create, so keys never change when the business renames things.
- Entity resolution
- Deciding which records in different systems describe the same real store, product or person.
- Physical meaning
- The reviewed mapping table is the real deliverable:
map_store_alias(alias, store_code, confirmed_by, confirmed_on), kept in version control and winning over any score. Algorithms suggest; a person confirms. Matching people also raises privacy questions (in Canada, PIPEDA), so match only on fields you are allowed to use, and write down that you checked.
“SELECT DISTINCT removes the duplicates.” It removes only exact copies. It keeps every version of a booking (they differ in status) and it would wrongly merge two genuine burger lines if they have no line number. Decide which kind of duplicate you have before choosing the fix.
5. Try it
- In
qsr-clean, find the re-sent till batches: count rows identical on every business column. Remove them, and log how many per restaurant per day. - Check whether the till data has genuine repeat lines (the same item twice on one order). In the course data each order has one line, so you should find none. Write the check anyway, and add a deterministic
line_noso the key survives if a real source has them. - Hotels: from all booking files, build
stg_bookings_latest(latest per booking_id). Count how many bookings changed status at least once. - Make a messy alias list for the 12 grocery stores (ask an AI assistant for 40 variants with typos). Resolve them with RapidFuzz, choose a threshold, measure precision and recall against the truth, and write
map_store_alias.csvfor the misses. - Write a test for each rule.
Commit: feat(clean): dedup rules, deterministic line numbers, store alias resolution.
What to check: stg_bookings_latest has exactly one row per booking_id (count(*) = count(distinct booking_id)), and rows in = rows kept + exact copies removed for the till data.
6. Practice
- Two rows are identical except
loaded_at. Exact re-send or new version? - Why add
src_rowto theorder byinsiderow_number()? - Your matcher has 97% precision and 60% recall. What does that mean in practice?
Answers
- An exact re-send.
loaded_atis your own metadata, not a business column, so leave it out when comparing. - As a tie-breaker. If two versions share a received time (same file), the result would otherwise change between runs. Deterministic order means stable output.
- When it proposes a match it is almost always right, but it misses 40% of the true matches. Lower the threshold a little, or send the unmatched names to the manual mapping table.
New words: exact duplicate, version, genuine repeat, window function, row_number, natural key, surrogate key, deterministic, entity resolution, blocking, precision, recall.
- Three kinds of “duplicate”: exact re-sends (keep one), versions (keep the latest) and genuine repeats (keep all, add a line number).
- Keep the latest version with
row_number() over (partition by key order by received desc), with a tie-breaker. - Create surrogate keys and line numbers deterministically, so reruns give the same keys.
- Fuzzy scores are suggestions; a reviewed mapping table decides.
Check yourself
Sources
- Fellegi, I. P. and Sunter, A. B. (1969), A Theory for Record Linkage, Journal of the American Statistical Association 64(328), 1183-1210 https://doi.org/10.1080/01621459.1969.10501049
- Kimball, R. and Ross, M. (2013), The Data Warehouse Toolkit, 3rd edition, Wiley, Chapter 3 (surrogate and natural keys)
- RapidFuzz documentation https://rapidfuzz.github.io/RapidFuzz/
- Splink documentation (probabilistic record linkage) https://moj-analytical-services.github.io/splink/
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.