Ugandan public-record data is a mess of a specific kind. Business names arrive
as "RITAH""S BAR" — an apostrophe, quote-escaped by a CSV writer that couldn’t
decide on a convention. N/A is not a null; it is a load-bearing sentinel that
means three different things depending on the column. A phone number turns to
scientific notation the moment a multi-megabyte CSV touches a synced folder —
256…479 becomes 2.56E+11, and the loss is silent. And the same hotel exists
twice: once as a map point with coordinates but no phone, and once as a
travel-guide listing with a phone but no coordinates — with no shared key to
join the two.
I needed one typed, documented SQL surface over roughly twenty of these sources, feeding three real products. The reflex answer is a workflow orchestrator and a cloud warehouse. That is a cluster-scale bill for a laptop-scale problem. What I built instead is a small medallion lakehouse — DuckDB and Parquet, four refinement layers, a query catalog — and two problems in it were genuinely hard. This is the first one.
No key, no cross product, no dropped rows
Two sources describe the same places and share no identifier. Three traps sink
the naive implementation. Comparing every map point against every listing is
O(n²). Matching “only rows that both have coordinates” silently drops exactly
the coordless listings that carry the facts the map source lacks — you delete
the reason you added the second source. And hashing an id from name + coords
collides two same-named coordless rows into one — two Nakato Guesthouses in
different villages become a single phantom.
The resolution is unglamorous and fast: bucket the located rows into a ~110 m coordinate grid, so matching a listing means scanning its own cell and the eight neighbours instead of the whole country. Match names with a token-overlap coefficient after stripping business suffixes — dirty names, not exact strings. Pick the nearest candidate by haversine within 100 m. On a hit, the row with more fields filled wins as merge-primary and the map source’s coordinates always win. And the coordless rows are kept, keyed by a second hashing scheme — name + phone + source article instead of name + coordinates — so same-named strangers stay distinct.
resolver.py view on GitHub ↗
for w in listings:
if w["lat"] is None or w["lon"] is None:
unified.append(w) # coordless: keep the facts, don't geo-match
continue
ci, cj = int(w["lat"] // GRID_DEG), int(w["lon"] // GRID_DEG)
best, best_d = None, DEDUP_RADIUS_M
for di in (-1, 0, 1):
for dj in (-1, 0, 1): # this cell + 8 neighbours — O(n), not O(n²)
for o in grid.get((ci + di, cj + dj), ()):
if id(o) in merged_geo or not name_match(w["_tokens"], o["_tokens"]):
continue
d = haversine_m(w["lat"], w["lon"], o["lat"], o["lon"])
if d <= best_d:
best, best_d = o, d
The whole algorithm is about two hundred lines of standard library. Most engineers reach for an entity-resolution framework here; the framework would have been most of the deploy and none of the insight.
The measurement that redesigned the model
The plan was one canonical entity table. A trial run measured 4% overlap between the two populations — the sources describe mostly different places, sharing only a thin seam — so the model split in two: anchor entities with a stable spine, and venue listings resolved against them at the seam. The decision record was revised backwards from the measurement, not defended against it. That loop — propose in writing, measure, revise the record, attach a “when to revisit” trigger — is the part of this build I would defend hardest.
Making a generative step boring
One layer runs a language model to synthesise descriptions. A model is not deterministic, and the pipeline must be — re-running it should never re-bill or rewrite content whose inputs didn’t change. So the generated output carries a provenance tuple, keyed by a hash of its source text: unchanged source, no regeneration; changed source, exactly one. Idempotent on its inputs even though the model is not. Every generated number is then grep-verified against the source before promotion, and anything unverifiable goes to a review queue rather than into the lake.
What I’d stand behind
This is single-author, personal-scale infrastructure — no SLA, no concurrency pressure, and DuckDB on a laptop is deliberately unglamorous. But the right answer to twenty messy sources and three hungry products was not a cluster. It was a well-typed folder, a query engine with no server, and an algorithm I can hold in my head.
The resolver is published as a clone-and-run reference implementation — entity-resolution-no-keys — and the snippets behind this piece are collected as a public gist. The full breakdown, with the decision records and the rejected alternatives, is the architecture case study →
Second in a series taking apart the systems behind the work.