Deduplicate Spreadsheets: The Messy Truth About Legacy Real Estate Data

A client sends you three files. The first has SMITH, JOHN R & MARY A. The second has John and Mary Smith. The third has SMITH JOHN R ETUX. Same couple, same 40 acres, same mailing address written three different ways, and every equality check you write says these are three different owners. That is the whole problem in one line. When you deduplicate spreadsheets built by different people over different years, string matching does not fail because your code is wrong. It fails because the strings were never the same thing.

I delivered a portal that folded three sprawling spreadsheets into 9,099 searchable property records with a map, a comp engine, and one-click reports on top. The mapping and the reporting were the easy half.

The short version: Do not compare raw strings. Normalize into a derived key first, so case, punctuation, and suffix order stop counting as differences, and put a unique index on that key so the database refuses the second copy no matter which import path runs. Parse addresses with a real parser rather than regular expressions, because USPS Publication 28 defines the abbreviations and nothing in your client’s file follows them. Use blocking to avoid comparing every row to every other row, since 9,099 records is just over 41 million pairs. Score the survivors with trigram similarity, which PostgreSQL ships in pg_trgm with a default threshold of 0.3, and reach for a probabilistic model like Splink only when many weak fields have to vote together.

Why does string matching fail on property data?

Because a name in a county file is not a name. It is a record of how one clerk typed it on one day.

Owner strings carry ownership grammar that no one documents. ETUX means “and wife” and ETVIR means “and husband”, both survivals from Latin that still show up in deed data. ET AL means there are more owners the file did not bother to list. A trust appears as SMITH FAMILY TRUST, SMITH FAM TR, and SMITH FAMILY REVOCABLE LIVING TRUST DTD 03/14/2009. An LLC arrives as RIVERBEND HOLDINGS LLC, Riverbend Holdings, L.L.C., and RIVERBEND HOLDINGS L L C.

Then the mailing address does the same thing in a different key. 123 N Main St Apt 4 and 123 North Main Street #4 and 123 N MAIN ST UNIT 4 are one mailbox. A five-digit ZIP in one file, ZIP+4 in another, and a stray leading apostrophe in the third because someone fought Excel over dropping the leading zero.

None of this is anyone’s mistake. Three files typed by three people across five years will disagree, and the job is to build the pipeline that resolves the disagreement rather than to wish the files were cleaner.

Normalize before you compare

The first move is to stop treating the raw string as the identity. Derive a key from it, let the database compute that key, and put the uniqueness rule on the key.

-- The database derives the key. The importer never gets a vote.
create table owners (
  id            bigserial primary key,
  owner_name    text not null,
  mailing_line1 text not null,
  mailing_zip   text not null,
  match_key     text generated always as (
    upper(
      regexp_replace(
        owner_name || '|' || mailing_line1 || '|' || left(mailing_zip, 5),
        '[^A-Za-z0-9|]', '', 'g'
      )
    )
  ) stored
);

create unique index owners_match_key on owners (match_key);

One trap to know before you run it. A generated column expression has to be immutable, and upper and regexp_replace both qualify. unaccent does not, because it depends on a dictionary that can change, so dropping it into the expression fails at CREATE TABLE rather than at insert time. If you need accent folding, wrap it in your own function declared immutable and accept that you now own that promise.

The unique index is the part that keeps working after everyone forgets this conversation. Application code checks one path at a time, and a year from now someone adds a bulk importer, an admin form, or an API endpoint that skips the check. The constraint does not skip. This is the same argument as making double-booking impossible rather than unlikely: put the rule where every writer has to pass through it.

Normalization gets you further than people expect. In practice it collapses case differences, punctuation, spacing, and the entity-suffix chaos, which is the bulk of what looks like a matching problem. What it will never catch is a genuine typo. RIVERBEND and RIVERBEDN normalize to two different keys, correctly, and you need a different tool.

Addresses need a parser, not a regular expression

Mailing addresses are where hand-rolled cleanup goes to die, because the variation is combinatorial and the standards are real.

USPS Publication 28, Postal Addressing Standards, revised October 2024, is the actual reference. Appendix C1 defines the approved street suffix abbreviations, so STREET, Str, and St. all resolve to ST. Appendix C2 defines secondary unit designators, which is how Apartment, Apt, and # become one thing. Your client’s file follows none of it, and that is exactly why the standard is useful: it gives you a target to normalize toward instead of a set of preferences to argue about.

For parsing, use something trained on real addresses. libpostal is a C library with bindings everywhere, built on conditional random fields and trained on over a billion addresses drawn from OpenStreetMap and OpenAddresses, with normalizations covering 60 languages. It returns structured components, so 123 N Main St Apt 4 comes back as a house number, a directional, a street name, a suffix, and a unit. If you want a pure-Python option for United States addresses specifically, usaddress uses the same statistical approach at a smaller scope.

The rule underneath both: parse into fields, normalize each field against the standard, then rebuild the key from the fields. Never write a regular expression that tries to do all three at once.

How do you compare 9,000 records without 41 million comparisons?

You do not compare every row to every other row. The pair count is n times n minus one, over two, which for 9,099 records is 41,391,351 comparisons. Every one of those runs a string similarity function. That is why naive fuzzy matching scripts run overnight and still finish wrong.

Blocking fixes it. You pick a cheap key that any true duplicate must share, compare only inside those blocks, and the arithmetic collapses. ZIP code is the usual choice for property data, because two records for the same owner almost always carry the same five digits, and a ZIP with 40 owners in it produces 780 pairs rather than 41 million.

create extension if not exists pg_trgm;
create index owners_name_trgm on owners using gin (owner_name gin_trgm_ops);

-- Compare inside a block, not across the whole table.
-- The % operator uses the trigram index. a.id < b.id stops each pair twice.
select a.id, b.id, a.owner_name, b.owner_name,
       similarity(a.owner_name, b.owner_name) as score
  from owners a
  join owners b
    on left(a.mailing_zip, 5) = left(b.mailing_zip, 5)
   and a.id < b.id
   and a.owner_name % b.owner_name
 where similarity(a.owner_name, b.owner_name) >= 0.55
 order by score desc;

PostgreSQL’s pg_trgm module breaks each string into groups of three consecutive characters and measures overlap. similarity() returns a real number from zero, meaning completely dissimilar, to one, meaning identical. The % operator returns true above pg_trgm.similarity_threshold, which defaults to 0.3, and it is index-backed through either a GiST or a GIN operator class. That default is far too loose for owner names, where it will happily pair two unrelated Smiths, so raise the working floor and tune it against real pairs from the client’s own files.

Blocking has one honest cost. Any duplicate whose block key disagrees is invisible, so a couple who moved and changed ZIP will not be compared. The standard answer is to run several blocking passes with different keys, ZIP in one, last name plus street number in another, and take the union.

When do you need probabilistic record linkage?

When no single field is decisive and several weak signals have to vote together.

That idea has a name and a paper. Fellegi and Sunter published A Theory for Record Linkage in the Journal of the American Statistical Association in 1969, and the model still runs national statistics offices. It weights each field by how much agreement or disagreement on that field should move your belief, so agreeing on a rare surname counts for far more than agreeing on a common one, and disagreeing on a middle initial counts for very little.

Splink, built and maintained by the UK Ministry of Justice, is the open-source option worth starting with. It is MIT licensed, runs on DuckDB by default with Spark and PostgreSQL backends available, needs no labelled training data because it estimates its parameters with expectation maximisation, and its own documentation puts it at linking a million records on a laptop in around a minute.

Technique	What it catches	What it misses	What it costs
Exact match	Byte-identical rows	Everything a human typed	Nothing
Normalized key plus unique index	Case, punctuation, spacing, suffix order	Genuine typos	One generated column and one index
Trigram similarity	Typos, transpositions, truncated names	Nicknames, initials, name order swaps	A GIN index and a tuned threshold
Phonetic coding	Sound-alike spellings, Smyth against Smith	Names that look alike but sound apart	Cheap, and noisy used alone
Probabilistic linkage	Weighted agreement across many weak fields	Nothing structural, but still needs blocking	A model you have to estimate and explain

Most property projects need the first three. Reach for the fourth and fifth when the files carry partial phone numbers, partial addresses, and nothing authoritative anywhere.

Which value wins when two rows merge?

Matching finds the pairs. It does not tell you what the surviving record should say, and that decision is where quiet data loss happens.

• Never delete the loser. Keep both source rows, mark one as merged into the other, and store the merge decision with a timestamp. Every merge is reversible or you will regret the first bad one.
• Prefer the most recent source for volatile fields. Phone numbers and mailing addresses go stale. Assessed values do not travel backwards.
• Prefer the most complete value, not the longest. A field with content beats a null. A 90-character owner string is usually two owners jammed together, not better data.
• Never merge on the strength of one weak field. Two records sharing only a common surname in the same ZIP is a coincidence, not a match.
• Send anything between your thresholds to a person. A clear-match band and a clear-no-match band, with a review queue between them, beats one cutoff pretending to be certain.
• Keep the original file. When you find a normalization bug in month four, replaying the raw imports costs an afternoon. Without them it costs the client’s history.

The constraint is the only part that cannot forget

Everything above is a pipeline, and pipelines get edited. The one piece that holds regardless of who writes the next importer is the unique index on the derived key, because it is enforced at the point of write rather than at the point of good intentions.

Get the geometry fast, get the records clean, and a land team stops arguing about whose spreadsheet is right. If the map side is what is hurting, that is a different fix entirely, and I put the same thinking into the parcel and owner model behind a land acquisition system where one owner holding four parcels has to read as one person with four holdings.

Normalize, block, score, then let the database say no.

Frequently asked questions

How do I deduplicate spreadsheets when the same name is spelled differently?

Stop comparing the raw strings. Derive a normalized key by upper-casing, stripping punctuation and spacing, and folding the entity suffixes, then put a unique index on that key so the second copy is refused at write time. Normalization handles the bulk of the variation. For genuine typos, add a trigram similarity pass over blocked candidate pairs and route the uncertain middle band to a person.

What is blocking in record deduplication and why does it matter?

Blocking limits comparisons to records that share a cheap key, such as the five-digit ZIP or the street number. Without it you compare every row to every other row, which for 9,099 records is 41,391,351 pairs, and every pair runs a string similarity function. Blocking cuts that to thousands. The trade-off is that a true duplicate whose block key disagrees never gets compared, so run several passes with different keys and combine the results.

What similarity threshold should I use for fuzzy name matching?

PostgreSQL’s pg_trgm defaults pg_trgm.similarity_threshold to 0.3, which is far too permissive for owner names and will pair unrelated people who share a common surname. Start nearer 0.55 for names, then tune against real pairs pulled from the client’s own files rather than against a general benchmark. Use two thresholds, not one: an auto-merge floor and a lower review floor, with human eyes on the band between them.

Should I use fuzzy matching or probabilistic record linkage?

Fuzzy matching scores one field at a time and suits data with one strong identifier plus some typos. Probabilistic linkage, based on the Fellegi-Sunter model published in 1969, weights agreement across many fields by how informative each one is, which is what you want when no single field decides anything. Splink from the UK Ministry of Justice is the practical open starting point, MIT licensed and unsupervised, and its documentation reports linking a million records on a laptop in around a minute.

How should I parse and standardize mailing addresses?

Parse into structured components with a trained parser rather than regular expressions. libpostal is built on conditional random fields and trained on over a billion addresses from OpenStreetMap and OpenAddresses, and it returns house number, street, suffix, and unit as separate fields. Then normalize each field toward USPS Publication 28, whose Appendix C1 defines street suffix abbreviations and Appendix C2 defines secondary unit designators, and rebuild the match key from the standardized parts.