Property data that is never clean

Five identities, no key

Everyone who works with property records eventually asks the same question, which is what exactly counts as a property. It sounds like a definitional quibble and it is the entire problem. A parcel is a tax and legal object defined by a county assessor. An address is a mail delivery point. A building is a physical structure. A unit is a leasable space. An owner is a legal entity, frequently one formed for this property alone. These five things cross in every direction: one parcel with three buildings, one building spanning two parcels, one address serving two hundred units, one owner entity holding twelve parcels under names that do not match.

There is no national identifier that resolves this. Assessor parcel numbers are assigned county by county, formatted differently in each, reformatted again by every vendor who touches them, and reassigned when parcels split or merge. Addresses are standardized by delivery convention, not by geography. So every property dataset you will ever build is a linkage exercise, and the quality of the linkage — not the richness of the attributes — is what determines whether the analysis on top of it means anything.

Addresses are a delivery convention, not a location

Address standardization is a mature, boring, well-solved problem right up to the point where it is not. Parsing and normalizing a street address is straightforward. Deciding whether two normalized addresses refer to the same thing is not.

The specific traps recur everywhere. Ranged addresses, where a building is recorded as 100-110 Main Street in one file and 104 Main Street in another. Unit designators that appear as Suite 200, Ste 200, #200, or in a second address line, or nowhere. Corner buildings with two legitimate street addresses and a lobby that uses the prestigious one. Vanity addresses that no delivery database recognizes. New construction, which can take a year or more to appear in any address reference, so your newest assets are the ones your pipeline cannot match. And rural addressing, where a physical location and a mailing address are routinely different places entirely.

Two practical rules save a great deal of pain. Keep the original string forever, alongside the normalized form, because you will need to re-normalize when your rules improve. And never treat a normalized address as an identifier — treat it as evidence, one of several, feeding a match decision that also considers geometry, owner name and attributes.

Geocode quality is a field, not a footnote

A coordinate pair looks equally authoritative regardless of how it was produced, and this is one of the more consequential quiet failures in the whole domain. There are four common derivations and they are not comparable.

The failure is not that fallbacks exist. It is that the match type gets dropped somewhere in the pipeline and every downstream consumer treats all four the same. If a distance-to-water or distance-to-brush calculation runs on a ZIP centroid, the answer is off by kilometres and it will still populate a field, print on a report, and drive a decision. Carry the match type all the way through, and make any calculation whose validity depends on precision refuse to run below a stated quality level rather than producing a number.

Owners are entities, and entities are deliberately opaque

Ownership resolution is the hardest linkage in property data and the one with the most value hiding behind it. Single-asset limited liability companies are the norm in commercial real estate, so a common owner appears as a hundred unrelated names. Individual owners appear as SMITH JOHN A & MARY B in one file and John and Mary Smith in another. Trusts change trustees. Entities are renamed after a merger and the assessor file catches up on its own schedule.

The single most useful signal is unglamorous: the mailing address on the tax bill. A hundred differently named entities that all send correspondence to the same suite are almost certainly one operator, and that one feature does more work than any name-matching algorithm. Registered agent and officer information from state business registries adds another layer where it is available and machine-readable, which varies enormously by state.

Be honest about what this produces. Ownership resolution yields a graph with confidence, not a fact. Store the evidence for every link — shared mailing address, shared officer, sequential entity names, a common deed transaction — and design so that any merge can be reversed. A merged owner group that turns out to be two different families is a mistake you must be able to undo without rebuilding the dataset.

Vendors disagree because they are measuring different things

Buy three property datasets and you will get three different square footages for the same building, sometimes twenty or thirty percent apart. The instinct is to treat this as a quality difference and pick the best vendor. It is usually not a quality difference at all.

Gross building area, rentable area, and assessor-recorded living area are three different measurements with three different definitions. Year built may be original construction or the most recent substantial renovation. Unit count may exclude non-conforming units. The assessor's record may be accurate as of a permit filed in 1994. Every one of those numbers can be correct for its own definition and useless for yours.

So the design rule is: never average vendor values, and never silently pick. Store every source value with its source and as-of date, define which measurement your business actually needs, and write an explicit precedence order — this source for this field, falling back to that one, with a recorded reason. When two high-precedence sources disagree beyond a tolerance you set, that is a flag for review, not an input to a mean. An averaged square footage is a number that no source would defend and no reviewer can check.

Two thresholds and a review queue

The matching machinery itself is not exotic and does not need to be. Block candidates by something cheap and reliable — postal code plus street number, or a spatial index over parcel geometry. Score each candidate pair on a handful of features: normalized street similarity, unit agreement, distance between geocodes, owner name similarity, attribute agreement on year built and area.

Then set two thresholds rather than one. Above the upper threshold, accept automatically. Below the lower, reject. Everything in between goes to a review queue with the evidence laid out side by side. The size of that middle band is a business decision, and it is the honest place to trade cost against error.

Measure by sampling, and sample all three bands. Pull a couple of hundred pairs from the auto-accept band and check them by hand; that gives you a defensible precision figure. Pull from the auto-reject band too, because the false negatives are invisible otherwise and they are what make your portfolio count come up short. Publish both numbers with the dataset. A match rate quoted without a sampled precision figure is a claim, not a measurement.

Realistic expectations, stated plainly: matching a reasonably clean commercial address list against good county parcel data commonly lands somewhere in the mid-eighties to mid-nineties percent on first pass. Pushing past that costs disproportionately and the last few points usually come from accepting looser matches, which trades a visible gap for an invisible error. Frequently the better answer is to leave the residual unmatched, show it, and let a person work the list.

Which errors are expensive

Not all mismatches cost the same, and the design should follow the cost rather than the count. Attaching the wrong roof age or construction class to a property changes a risk assessment. Attaching the wrong owner merges two customers or splits one. Attaching the wrong parcel to a loan misstates collateral. Attaching a slightly wrong geocode to a marketing list costs nothing.

Work out which of your consumers can tolerate a bad match and which cannot, then let them read the confidence rather than hiding it behind a single table. In practice this means the resolved record exposes its own quality — match type, score, evidence, source and as-of date — and each consumer sets its own floor. A pricing model can insist on rooftop-quality geocodes and high-confidence parcel links. A prospecting list can take everything.

Everything moves, and the pipeline has to survive it

Assessor rolls are republished on an annual cycle in most counties, with supplemental updates in between. Ownership changes appear when a deed is recorded, which lags the transaction by days to weeks. Parcels split and merge when land is subdivided or assembled, and when that happens the identifier you keyed on may simply cease to exist.

Three requirements follow. Your internal property identifier must be yours, stable, and never a vendor's key or a parcel number, because both will change under you. Every record needs an as-of date so a stale value is distinguishable from a current one. And the refresh has to handle disappearance explicitly: a parcel that is no longer in the roll needs a resolution — split into these three, merged into that one, or genuinely unknown — rather than silently vanishing from a count that someone reconciles at quarter end.

Diffing successive refreshes is the cheapest quality control available and almost nobody does it. A county whose parcel count moved four percent between rolls has either had real subdivision activity or changed its export format. A batch of properties whose owner changed on the same day is either a portfolio trade worth knowing about or a parsing regression. Both are visible in a diff and invisible in a snapshot.

When you do not need to build this

Some honest limits.

Small, stable footprints. If you touch a few hundred properties a year and they change slowly, buy a vendor file and have a person reconcile it. A linkage pipeline for four hundred properties is a hobby with a maintenance burden.

One geography, one source. If your business is in a single county and that county publishes decent parcel data, most of this article is over-engineering. Load the county file, key on their parcel number, accept the maintenance when it changes.

Nobody will own the review queue. The middle band only works if a human works it. If no one is accountable for a queue, set your thresholds so that everything is either accepted or rejected, and report the resulting error honestly. A review queue nobody empties is worse than no queue, because it becomes a place where problems are stored rather than solved.

A sequence that works

Step three is the one that gets skipped and the one that makes every later number meaningful. Hand-labelling a few hundred pairs is a couple of days of tedious work, and without it there is no way to say whether the pipeline is right, no way to tune the thresholds, and no way to answer the question a risk committee will eventually ask.

The mistakes we get called in to fix

• A vendor key used as the primary identifier, and a re-key event that broke every downstream join
• Geocode match type dropped at ingest, so hazard distances were computed from postal centroids
• Square footage averaged across three vendors, producing a number no source would defend
• Match rate reported with no sampled precision, so nobody knew what the matches were worth
• Owner merges with no evidence stored and no way to reverse one that was wrong
• A review queue with no owner, holding eleven thousand pairs and growing

Before you build

• The anchor identity is chosen and written down: parcel, address, building or unit
• Your internal property id belongs to you and is never a vendor or parcel number
• Original strings are retained beside every normalized value
• Geocode match type travels with the coordinate to every consumer
• Precedence per field is explicit; no vendor values are averaged
• A hand-labelled ground truth set exists before any threshold is tuned
• Precision is sampled in all three bands and published with the dataset
• Every merge stores its evidence and can be reversed
• Refresh handles splits, merges and disappearances explicitly

Bottom line

Property data resists cleaning because the underlying objects genuinely are ambiguous, not because anyone has been careless. The productive move is to stop chasing a clean dataset and start building one that reports its own quality — match type, confidence, evidence, source, as-of date — so that a consumer who needs precision can demand it and a consumer who does not can proceed. That reframing costs a little more engineering up front and it is the difference between a dataset people trust conditionally and one they abandon the first time it embarrasses them.

Frequently asked questions