Joinless

Record linkage when two datasets share no join key. Four matchers, token overlap to quantized neural, measured on identical data, so you know what accuracy actually costs on the machine at hand.

Comment

Inspiration

Imagine you are building a directory of local places from two public sources.

OpenStreetMap tells you that Riverside Hotel Ltd exists at a precise point on the map, but it may not have a phone number or price. A travel guide lists Riverside Hotel with a phone number, room price and an approximate location.

To a person, those records probably describe the same hotel. To a database, they are two unrelated rows:

Source Name Location Useful facts
Map Riverside Hotel Ltd Known category
Travel guide Riverside Hotel Approximate phone and price

There is no shared hotel ID. The names are not exactly equal. An ordinary database join cannot connect them.

If we match them correctly, we get one much more useful record: the map location plus the guide's contact details. If we match them incorrectly, we may attach one business's phone number to another business. A listing with no usable coordinates cannot yet be linked by Joinless, but it is kept as an unmatched record with a reason instead of being discarded.

This was not a hypothetical problem. I encountered it while building a local data system over roughly twenty messy public-record sources. The system cleans each source once and makes the results available to several projects, including merchant, credit-scoring and market- intelligence work. That shared data system is what “lakehouse” means here: one place where raw files are collected, cleaned, stored and made queryable instead of every product rebuilding its own copy.

The original resolver used simple text comparison and geography. It was small, fast and useful, and I later extracted it into the open-source entity-resolution-no-keys reference implementation.

Its weakest point was name variation. Simple word matching can connect Riverside Hotel Ltd and Riverside Hotel, but it struggles with misspellings, abbreviations and names that mean the same thing without sharing the same words.

Modern AI models can turn a name into a list of numbers representing its meaning—an embedding, or, in simpler terms, a semantic fingerprint. Research such as LinkTransformer shows that these models can outperform traditional string matching on record-linkage tasks.

That led me to a different question:

When is an AI matcher worth carrying on the device, and when is a simpler method already good enough?

That question matters because the model is not free. It adds a download, memory use, startup time and processing cost. Record linkage can also involve sensitive information about people and businesses, making a hosted matching API unacceptable. Joinless therefore runs locally: the records do not need to leave the machine to be compared.

What Joinless does

Track: Mobile AI. Joinless runs CPU-only on an Arm64 client, keeps records on-device, and measures whether local AI earns its deployment cost.

Joinless is an open-source Python library and command-line tool for combining records when the sources do not share a common ID. Its source and reproducible evidence are available in the raheebwa/joinless repository.

Given two record sets, it:

  1. finds records that are geographically close enough to be plausible matches;
  2. compares their names using one of four interchangeable methods;
  3. selects the best match above a controlled threshold;
  4. combines complementary facts and records which input sources contributed to the merge; and
  5. keeps every record it cannot match, with an explanation, instead of silently dropping it.

The main commands are:

  • joinless resolve — combine two JSONL record sets;
  • joinless compare — see how two names score under a selected method;
  • joinless doctor — show the machine, runtime and offline state;
  • joinless benchmark — run the controlled experiment and save its evidence; and
  • joinless report — render a saved record without measuring anything again.

How this improves the original lakehouse

The original lakehouse already solved the basic engineering problem: collect messy public data, clean it once, and give several products one shared source instead of several inconsistent copies. Joinless improves the point where those sources become one entity.

Return to the Riverside Hotel example. The map record supplies the location; the travel-guide record supplies the phone and price. Linking them correctly gives every downstream product a more complete hotel. Missing the match leaves useful facts fragmented across two rows. Making a false match is worse: a merchant product might display the wrong phone number, a market-intelligence report might count two businesses as one, and a credit or compliance workflow might associate facts with the wrong organization.

Joinless gives the lakehouse five concrete improvements:

  1. Better name matching without rebuilding the pipeline. The original resolver's geography, IDs, merge rules and preservation of coordinate-less rows remain intact. Only the component judging two names is replaceable.
  2. A safer choice of matcher. Instead of adding AI because it sounds more capable, the lakehouse can choose according to measured errors. A high-precision neural arm may be worth its cost when a false merge is dangerous; a classical matcher may be better when memory, speed and overall balance matter more.
  3. More trustworthy downstream records. Stable input-row identities prevent distinct listings from collapsing accidentally, and a merged record names the sources involved in the merge. Unmatched records remain visible instead of disappearing from merchant, credit or market-intelligence views.
  4. No new cloud-data exposure. Matching remains on the same local machine as the data. The lakehouse does not need to send business records to an external AI API to gain semantic matching.
  5. A repeatable upgrade process. When the source mixture changes, the benchmark can be rerun on labelled examples. The choice is no longer a permanent guess hidden inside pipeline code.

The current synthetic result makes this connection especially useful. It does not tell me to rush an AI model into the lakehouse. It identifies RapidFuzz as the strongest first upgrade to validate on a private labelled sample: higher aggregate F1 than both neural arms, minimal memory, and no model download. The FP32 arm remains a candidate for workflows where its avoidance of near-miss false matches matters enough to accept a lower aggregate F1 and substantially greater memory.

No private lakehouse record was copied into this public project. Before changing the production pipeline, I would run Joinless's same protocol locally on a labelled sample from the lakehouse. That final validation is what turns a benchmark finding into an operational decision.

The four contestants

Joinless compares four approaches under the same rules:

Name In plain language What it carries
overlap Checks how many whole words the names share Python only
fuzzy Recognizes character edits, reordered words and similar spelling RapidFuzz
embed-fp32 Uses an AI model to compare semantic fingerprints Full-precision ONNX model
embed-int8 Uses a compressed version of the same AI model Quantized ONNX model

FP32 is the ordinary full-precision model. INT8 stores many model weights using smaller numbers. This process is called quantization. It is similar to compressing an image: the result should occupy less space, but we must measure whether it also changes speed or quality.

Two classical methods are included deliberately. Comparing AI only with basic word overlap would be an unfair race. For example, BRIGHTWATR and BRIGHTWATER share no complete word, but a good non-AI fuzzy matcher can still recognize the spelling error. RapidFuzz gives the AI model a credible classical competitor.

How I built it

Step 1: eliminate impossible comparisons early

Suppose two hotel directories contain 10,000 records each. Comparing every row in one directory with every row in the other would require 100 million comparisons—even though most places are nowhere near each other.

Joinless first groups records into small geographic grid cells. Each record is compared only with candidates in its own cell and the eight surrounding cells. Checking adjacent cells prevents a valid match from being missed simply because two nearby coordinates fall on opposite sides of a grid boundary.

This technique is called spatial blocking. Formally, brute-force comparison costs \(O(nm)\), while blocking approaches \(O(nk)\). Here, \(k\) is simply the number of candidates in the searched neighborhood. Joinless also reports cell occupancy because an unusually crowded area can still produce many comparisons; the benchmark should expose that cost rather than hide it.

Step 2: compare the names

The simplest method converts each name into a set of words. Its calculation is:

$$ \mathrm{overlap}(A,B)=\frac{|A\cap B|}{\min(|A|,|B|)} $$

In plain language: overlap score = shared words ÷ words in the shorter name. It asks, “What fraction of the shorter name's words also appear in the longer name?”

The fuzzy method looks at both characters and word sets. The AI methods create embeddings with sentence-transformers/all-MiniLM-L6-v2 running through ONNX Runtime on the CPU, then compare those semantic fingerprints.

Every method is allowed to change only the name score. It cannot change which records are considered nearby, how IDs are formed, how distance breaks a tie or how fields are merged. This makes the benchmark a comparison of name matchers rather than four subtly different applications.

Step 3: merge without losing information

When two records match, Joinless keeps every field that appears on only one side. Where both records contain different values for the same field, the more-populated record wins. Coordinates come from whichever record has them; if both have coordinates, the more-populated record supplies them. The merged output also names both contributing sources. It does not currently preserve a licence or source attribution for each individual field.

When a record cannot be matched—especially when it has no coordinates—it remains in the output as an unmatched record. “Could not match” and “deleted” are very different outcomes.

Joinless also gives every physical input row its own stable, source-aware identity. Two listings with the same name and no coordinates remain two distinct rows instead of accidentally collapsing into one.

Step 4: keep the AI local and optional

The AI model is downloaded during an explicit setup step, not while matching. Its identity, version, licence and file checksum are recorded. If the file is missing or has changed, Joinless refuses to run that arm instead of silently fetching a replacement.

Users who need only classical matching do not install or load ONNX Runtime. This matters for both usability and honest measurement: the “cheap” method cannot secretly inherit the AI runtime's memory and startup cost.

Building a fair test

A benchmark can easily produce a misleading winner. If I choose mostly spelling mistakes, one method may dominate. If I choose mostly exact names, every method may look perfect. If I tune a matcher on the same examples used for the final score, I am effectively giving it the answers before the exam.

Joinless uses 1,800 entirely invented name pairs across five repeatable random seeds and eight types of variation:

  • exact names;
  • punctuation and formatting;
  • changed word order;
  • abbreviations;
  • character noise and misspellings;
  • transliteration;
  • different businesses whose names have similar meanings; and
  • deceptive near-matches that must remain separate.

The examples are divided into three groups:

  • development — the practice material I can inspect while building;
  • calibration — used to choose each matcher's cutoff score; and
  • sealed test — the final exam, opened only after the cutoff is frozen.

All four methods use exactly the same cutoff-selection procedure. They can receive different numeric cutoffs because their scores mean different things, but no method receives hand tuning.

Results are reported separately for each type of variation before being summarized. I also wrote down which method I expected to win each category before the run. When the evidence disagrees, the disagreement is saved as a finding rather than explained away.

How to read the results

Three measurements describe matching quality:

  • Precision: Of the matches proposed, how many were correct?
  • Recall: Of all the true matches available, how many were found?
  • F1: One score that balances precision and recall.

A business may value these differently. If a wrong merge could attach a payment or compliance record to the wrong company, precision may matter more. If a human will review suggestions and missing a candidate is expensive, recall may matter more.

The canonical four-arm record, 20260813T151146Z-benchmark.json, identifies an Arm64 Mac with 12 logical CPUs and 32 GB of memory. ONNX Runtime used its automatic, unpinned CPU thread pool; Joinless did not pretend that this was a one-thread run. The benchmark evaluated 470 sealed-test pairs after choosing thresholds from 430 separate calibration pairs. The record does not identify the exact Apple chip, so I do not infer it from the core count.

Method F1 Precision Peak memory Model download
Word overlap 0.721 0.635 22.5 MB None
Fuzzy matching 0.886 0.975 24.0 MB None
Full-precision AI 0.871 1.000 275.3 MB 91.1 MB
Compressed AI 0.866 0.995 208.9 MB 59.3 MB

Precision and recall are the pooled sealed-test aggregates in the same record. Recall runs the other way — 0.833, 0.812, 0.771 and 0.767 in the same order — which is the whole trade-off in two columns: the arm that never accepts a wrong match resolves the fewest true ones.

The most important result is not “AI wins.” On this disclosed synthetic dataset, fuzzy matching achieved the best overall F1 score while using a fraction of the neural arms' memory.

In the deceptive near-miss family, the full-precision AI arm accepted none of the 115 pairs designed to look like matches while describing different businesses. Across the whole sealed set it made no false match at all — precision 1.000 — and its aggregate F1 nonetheless trails fuzzy matching, because recall of 0.771 against fuzzy's 0.812 means that caution also passes on genuine matches elsewhere. That makes the AI arm potentially useful when a false merge is much more dangerous than a missed match—not automatically useful for every workload.

The error counts show that trade-off more clearly than the aggregate scores. All 115 deceptive near-miss examples were different businesses. Word overlap incorrectly accepted all 115, fuzzy matching accepted 5, full-precision AI accepted none, and compressed AI accepted 1. Conversely, character noise contained 40 true matches, and every arm missed all 40 under its single calibrated cutoff. Taken together, the full-precision arm's pattern looks like cautious abstention: it avoids false matches in the near-miss family but does not close the aggregate F1 gap with fuzzy matching, which means it leaves more true matches unresolved elsewhere.

For context, the complete generated corpus is exactly half positive. A matcher that answers “match” to everything would score about 0.667 F1 across that complete corpus; on the 470-pair sealed split, which contains 240 positives and 230 negatives, it would score about 0.676. Word overlap's 0.721 is only modestly better and comes with those 115 near-miss false positives.

Quantization reduced the model file by 34.9% and peak memory by 24.1%, while aggregate F1 fell by 0.005. Relative to FP32, INT8 missed one additional abbreviation match and made one additional false match in the near-miss family. Inspection of the model showed why “INT8” needs qualification: 36 of 48 relevant matrix-multiplication operations were converted, while 12 remained unquantized.

Quantization did not materially improve warm prepared-vector scoring: the median was 24.87 microseconds for FP32 and 24.71 microseconds for INT8. It did reduce median batched preparation from 11.270 ms to 7.630 ms, or 32.3%, on this run. These are separate stages, so I do not combine them into a single end-to-end speedup claim.

Cold start is also decomposed rather than collapsed into one opaque number, and until this record the neural arms' load-bearing phases were never timed at all — they were reported as inapplicable using a reason written for the classical arms. That defect is now fixed. FP32 measured 115.6 ms for imports, 55.5 ms for session creation and 9.6 ms for tokenizer loading, for a cold start total of 202.7 ms; INT8 measured 99.1 ms, 41.2 ms and 9.5 ms respectively, for a cold start total of 171.8 ms—15.2% cheaper than FP32. First inference is timed separately again: 3.80 ms for FP32 against 2.11 ms for INT8. Interpreter start is recorded but marked not attributable to the matcher, since every arm pays it. The classical arms carry no session or tokenizer to load: overlap's cold start is 25.1 ms, dominated by an 8.9 ms import; fuzzy's is 30.1 ms, dominated by a 13.3 ms import. Phase decomposition is what surfaces INT8's cold-start advantage; a single combined number would have hidden it.

These results apply to this model, machine, test set and threshold policy. They are evidence for a decision, not a universal leaderboard.

The primary optimization

Suppose one hotel is compared with twenty nearby candidates. A naive AI implementation may recreate the same hotel's semantic fingerprint twenty times.

Joinless is designed to prepare each distinct name once, process names in batches, and reuse the result wherever the name appears. For \(C\) pair comparisons, the naive path may perform \(2C\) encodes even though only \(n\) distinct names exist. Hoisting the work reduces that toward \(n\) encodes.

The naive path remains available as the control. Both paths run over the same sample — 20 records producing 30 candidate comparisons — so the two figures differ only in call pattern. After five warm-ups and across twenty repetitions, FP32 preparation fell from 67.611 ms naive to 11.270 ms hoisted—a 6.00× ratio. INT8 fell from 39.884 ms to 7.630 ms, a 5.23× ratio. Classical preparation was already measured in tens of microseconds, so the practical value of the hoist is concentrated in the neural arms.

Quantization is the supporting optimization. It answers a separate question: can the same model occupy less storage and memory without losing too much matching quality?

Challenges I faced

A green test suite was giving false confidence

One of the most important tests was supposed to prove that classical matching never loaded the AI runtime. It could not fail because Python had already cached the module it was “testing.” The fix was to start a completely fresh Python process and inspect what that process loaded.

I then deliberately broke completed behavior to see whether the tests noticed. That exercise found that:

  • seven of the nine neighboring map cells could be removed without a test failing;
  • every explanation for an unmatched record could be replaced with nonsense; and
  • the function that compared predictions with expectations was thoroughly unit-tested but never called by the real benchmark.

The project enforces 100% line and branch coverage, but I learned that coverage only proves code was visited. It does not prove the tests would recognize incorrect behavior. For important rules, I now break the rule deliberately and verify that the test turns red—like testing a fire alarm with safe artificial smoke instead of trusting its power light.

The same adversarial approach found two measurement defects before publication. The preparation worker had copied the optimized and naive loops instead of timing the implementations the resolver actually ships; I refactored both callers onto one implementation and added a test that fails if measurement bypasses it. Preparation was also timed once, which produced ratios noisy enough to reverse the apparent winner. It now discards five warm-ups, measures twenty repetitions, and reports the median and 99th percentile. The final figures are therefore not merely more flattering numbers—they come from a procedure designed to catch its own mistakes.

One cutoff can hide an entire category

The single global threshold selected by each matcher worked well on easy categories but caused every arm to propose no matches at all for the character-noise category. The overall score still looked respectable because the easy cases outweighed that failure.

Rather than quietly tune a special threshold after seeing the result, I recorded the problem as an open issue. The final report must either show different operating points or state clearly what the global threshold fails to serve.

Keeping the race fair

The model, tokenizer, pooling method, inputs, runtime, execution provider, automatic threading configuration and measurement procedure all had to remain the same between FP32 and INT8. Only the graph's numerical representation was allowed to change. Otherwise a faster or more accurate result could not be attributed to quantization.

Resisting the attractive extras

Fine-tuning, more models, GPU/NPU execution and a polished web interface would all be interesting. They would also make it much harder to finish the controlled experiment. Joinless stays focused on one model, one device class and one question.

What I learned

AI deployment is a product decision, not an accuracy score. A model can be more cautious about false matches while losing overall to a strong classical method and using much more memory.

The best method depends on the cost of each kind of mistake. Precision and recall matter more than declaring one generic winner.

Write down your expectation before seeing the answer. I expected fuzzy matching to win the transliteration category. The actual winners were word overlap and both embedding arms. Because the expectation was stored before the result, that contradiction became something to investigate rather than something to rationalize.

Undefined is not zero. “Tried and got every answer wrong,” “made no prediction,” “there were no positive examples,” “the method could not start” and “the test was invalid” are different facts. Joinless preserves those differences instead of forcing each into a convenient zero.

The person who chooses the test cases can choose the winner. That is why the result is shown by category, the data roles are separated, the random seeds are recorded and every conclusion is limited to the disclosed test distribution.

An optimization needs a before case. Without retaining and measuring the naive path, a claimed speed improvement would be a story rather than evidence.

What I am proud of

Joinless does not hide inconvenient results. The best aggregate matcher in the current run is classical. The compressed model is much smaller but slightly less accurate. A prediction I wrote down was wrong. One category exposed a weakness in the threshold policy.

Every published number comes from a non-overwriting JSON record containing the machine, software, model identity and procedure that produced it. The useful artifact is therefore not only a matcher; it is a repeatable way for another developer to decide whether on-device AI is worth using for their own record-linkage problem.

What's next

The preparation measurement, per-seed variation, per-family report and accuracy-cost frontier are complete and generated from the canonical run record. The next product step is to let users run the same governed protocol on their own labelled CSV or JSONL pairs. A read-only viewer may follow, but the CLI and evidence record will remain the source of truth.

Built With

  • all-minilm-l6-v2
  • apple-silicon
  • arm
  • arm64
  • embeddings
  • github-jobs
  • hatchling
  • huggingface
  • hypothesis
  • int8
  • macos
  • mypy
  • onnx
  • onnxruntime
  • optimum
  • pytest
  • python
  • pytorch
  • quantization
  • rapidfuzz
  • ruff
  • sentence-transformers
  • tokenizers
  • transformers
  • uv
Share this project:

Updates