Week 2 · Clean and trust the data › Day 12

Day 12. Duplicates, keys and entity resolution

1. Why this matters

Double-counted revenue is the bug that ends trust in a dashboard. Duplicates come in three kinds and each needs a different fix. Mix them up and you'll either double-count or delete real sales.

2. Three kinds of "duplicate"

-- latest version per booking (DuckDB / Postgres / T-SQL all support this)
select * from (
  select *, row_number() over (partition by booking_id order by file_received_at desc, src_row desc) 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;

3. Keys you create

• Natural key: from the business (booking_id, store_code, sku). Can change, collide across systems, or be reused.
• Surrogate key: made by you, meaningless, stable. In a warehouse an int identity or a hash of the natural key plus source: 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 the same numbers.

4. Entity resolution

"Northgate Market #104", "NORTHGATE MKT 104" and "Northgate - Store 104" are the same store in three systems. Steps: normalise (case, punctuation, abbreviations), block (only compare candidates sharing a region or number), score (string similarity), decide with a threshold, and keep a manual mapping table that overrides the algorithm.

from rapidfuzz import process, fuzz
canon = stores["store_name"].tolist()
match, score, idx = process.extractOne("NORTHGATE MKT 104", canon, scorer=fuzz.token_sort_ratio)

For people (guest records across hotels, loyalty members) use a probabilistic matcher like Splink (open source, runs on DuckDB), which learns weights for each field.