DEV Community

Tae Kim
Tae Kim

Posted on Originally published at hannune.ai

What Splink actually runs on DuckDB when it scores entity pairs

#entityresolution #duckdb #dataengineering #python

When I added Splink to the ER API, the registry was at 44,798 entities. Before touching any configuration knobs, I looked at the SQL it was generating. I expected opaque query strings. What came out was readable enough that I could follow the logic directly.

The blocking step generates something like this:

SELECT
    l.entity_id AS entity_id_l,
    r.entity_id AS entity_id_r,
    l.name_normalized AS name_l,
    r.name_normalized AS name_r
FROM entities AS l
INNER JOIN entities AS r
    ON LEFT(l.name_normalized, 4) = LEFT(r.name_normalized, 4)
WHERE l.entity_id < r.entity_id
Enter fullscreen mode Exit fullscreen mode

LEFT(name_normalized, 4) is the blocking key. Entities that don't share those first four characters don't get compared at all. At 44,798 entities this brought candidate pairs from roughly 2 billion down to about 400,000.

The cross-join on the full entity table was what was slow. I caught this by looking at the DuckDB query plan while the blocking step ran — it was sitting there doing a lot more work than I wanted. Adding an index on the blocking key column fixed it immediately. Not complicated once I knew where to look, but I wouldn't have looked there without reading what was actually running.

What the EM pass is doing

For each candidate pair, Splink builds comparison features before the EM loop:

SELECT
    entity_id_l, entity_id_r,
    CASE WHEN name_l = name_r THEN 1 ELSE 0 END AS exact_match,
    jaro_winkler_similarity(name_l, name_r) AS jaro_winkler_sim,
    CASE WHEN country_l = country_r THEN 1 ELSE 0 END AS same_country
FROM candidate_pairs
Enter fullscreen mode Exit fullscreen mode

The EM loop ran fast once I had the candidate table. DuckDB handles this in-process, so there's no serialization overhead between iterations. On my machine, 400,000 pairs, the whole convergence took a few seconds. That part of the pipeline wasn't a problem.

What I spent a disproportionate amount of time on was preprocessing. Name normalization for Korean, Japanese, and Chinese inputs has a lot of edge cases, and the blocking key construction amplifies whatever noise gets through. Japanese company name suffixes alone took an afternoon to handle consistently — 株式会社 shows up at the front in some sources and the back in others, abbreviated in a few more. Once I had a normalization that wasn't silently introducing variance into the blocking key, I'd spent more hours there than on everything inside Splink's own configuration combined.

None of that is a Splink problem. The library doesn't know what language your inputs are in. But if you're running entity resolution over multilingual corporate names, the normalization you build before Splink touches the data will probably be where you spend most of your time. The EM loop will be fine.

I build er-api, a multilingual entity resolution service for Korean, Japanese, Chinese, and English corporate data. More at hannune.ai.

Top comments (0)