Why your data joins are failing (and how I audit them)

Question

​When you start scaling your real estate business, you eventually have to move past manual entry. You start pulling in data from tax records, skip tracing, and other sources. To make it useful, you have to join that data together.

​Since we usually have to use the property address as the primary key for these joins, things get messy fast.

​Data systems are literal. If one list has "123 Main St" and the other has "123 Main Street," a standard join is going to treat those as two different houses. If you are running an inner join, that record is just gone. If you’ve noticed that your final counts aren't measuring up to what you started with, check your joins first.

​One thing I always do as a system builder is never trust the join. I always log the dropped records or export them to a separate file so I can actually see what’s being left out.

​When you can see the dropped records in a CSV, you start to see the patterns. Maybe one source uses "Apt" and the other uses "Unit." Once you see the pattern, you can clean the data and stop leaving potential deals on the table. Reliable data is the only way to have a real foundation for your business.

​How are you all handling address normalization when you're merging different lists? I would love to hear how others are keeping their data clean.

Ryan Stomel · Answer

For me it’s a mix of confidence thresholds and an exception queue. If the fuzzy match clears a high-confidence threshold and the secondary fields agree — ZIP, city, owner name, prior alias, sometimes parcel geometry if I have it — I’ll auto-resolve it and write that crosswalk so the same mismatch doesn’t come back next cycle.

If those checks don’t line up, it stays unresolved and gets reviewed instead of forcing a bad parcel match. The expensive mistakes usually come from a match that feels “probably right” but gets pushed into production anyway, so I’d rather carry a slightly bigger exception list than create false positives in the core table.

Estervelle Bennett · Answer

@Ryan D You’re right, this definitely turns into a data engineering problem once you scale it.I’m a data engineer working with property data, so I deal with this a lot.

I standardize addresses upfront and use that as the common layer before anything gets merged. I also run a fuzzy match on anything that doesn’t join cleanly. If I can resolve it, I map it back to the parcel. If not, I keep it flagged so I can track patterns.

On parcel IDs, I agree they’re the best key when available. The issue I run into is a lot of datasets don’t include them, so I can’t rely on them consistently.

Even when they are there, I still validate them. If the match rate is off, something’s wrong.

Estervelle Bennett · Answer

@Ryan Stomel like your point about an exceptions table. I’ve been doing something similar, but I actually try to resolve them in the same flow.

When records don’t match, I run a fuzzy pass on them. If I can confidently match them, I assign the parcel ID and resolve it there. If not, they stay in the unresolved list.

So it ends up being less about just tracking exceptions and more about reducing them as part of the pipeline

How are you resolving those exceptions?

Ryan D. · Answer

@Estervelle Bennett Good post Estervelle. Worth calling out that this is really a data engineering problem that wandered into a BP thread — which is a good thing, because most people dont realize thats what theyre dealing with.

The scale question matters a lot here. If youre processing hundreds of records a week from a couple sources, honestly just throw it at an LLM. Feed it the messy addresses, tell it to normalize to USPS format, done. GPT-4o handles "123 Main St" vs "123 Main Street" vs "123 Main St NW" without breaking a sweat and the cost is negligible.

But if youre doing this at volume — thousands of records, multiple ingestion sources, automated pipelines — then the rabbit hole is worth it. Thats where you want a proper normalization layer: USPS standardization API or something like SmartyStreets, a fuzzy match step (Levenshtein distance works fine for address strings), and exactly what you described — logging the dropped records so you can see what the join missed. The patterns reveal themselves fast. Apt vs Unit, Ave vs Avenue, directional prefixes — once you catalog them you can handle them systematically.

The bigger thing I'd add: make the address your secondary key, not your primary. If you can get parcel ID or APN from the county assessor and use that as your join key, youre in much better shape. Address strings are human-entered garbage. Parcel IDs are assigned by governments and they dont change.

Are you working on more than 1000 unique property addresses at the same time that this becomes an reccurring bottleneck?

Ryan Stomel · Answer

This is a real problem. I’ve run into it just syncing lease data, tenant names, and QuickBooks records across systems — the join doesn’t just fail, it quietly creates bad reporting. The way I handle it is standardizing addresses before they ever touch the main table: USPS formatting, directional cleanup, unit normalization, and then a second check using parcel/APN when I can get it because addresses are too messy to trust by themselves. I also keep an exceptions table instead of just a dropped-record export, so recurring mismatches get solved once instead of rediscovered every month. In property data, the bad join usually shows up later as a wrong owner report, duplicate tenant, or lease rolling to the wrong suite, so catching it upstream is huge.

Why your data joins are failing (and how I audit them)

5 Replies