Contents
  1. Background: the graph
  2. The core problem: canonicalization
    1. Start with ground truth
  3. The NCAA co-athlete pipeline: direct database resolution
    1. Blocking: generating candidate pairs
    2. A tiered query strategy
    3. Discovering school IDs
    4. Scoring
    5. Resolution logic
    6. Results
    7. Why OpenSearch didn’t work
  4. The pubnet co-authorship pipeline: dimension-based resolution
    1. Resolve dimensions, not facts
    2. The bronze, silver, and gold stages
    3. Cross-source deduplication with canonical_work_id
    4. Scoring co-authorship edges
    5. Results
  5. Lessons learned
    1. What worked
    2. What didn’t work
    3. Report discard rates as findings
  6. Production database safety
  7. Why the scoring denominator stays wrong
  8. Conclusion

Entity resolution sounds like a matching problem. At scale, it’s a search problem, and the size of the search space is what makes it hard.

At Vieu (product: seeqe.com), I build data pipelines that enrich a B2B relationship intelligence graph. The graph has one job: when a salesperson wants to reach someone at Company X, find a warm introduction path through their existing network. It holds about 900 million person nodes sourced from LinkedIn, connected by precomputed edges that represent real-world relationships: co-worker, co-student, co-athlete, and co-author.

Every new edge type starts with the same problem. An external data source, such as NCAA rosters, academic publications, or nonprofit boards, contains entity mentions like “Aman Jain, MIT, 2023,” and each mention has to be linked to the correct person node in a 900-million-row Postgres table. A correct link adds a warm introduction path that didn’t exist before. A wrong link fabricates a relationship that could embarrass someone on a sales call.

This post covers two entity resolution pipelines I built on the same graph:

  • The NCAA co-athlete pipeline links college athletes to person nodes. It resolves about 48% of athletes across five schools, with 97.5% accuracy on resolved matches at Harvard.
  • The pubnet co-authorship pipeline links academic authors to person nodes. It resolves against author dimensions instead of author slots, a 4.678:1 reduction in work, and produced 6.34 million co-authorship edges on a single-university slice.

The core problem is the same, but the data is very different, and the contrast is where the useful lessons are.

Background: the graph#

Vieu’s graph spans three databases:

  • Postgres is primary and handles about 90% of all data work. It holds about 900 million person nodes in public.person (921 million rows, 1.3 TB) and 384 million education records in public.school_degree (292 GB).
  • OpenSearch indexes about 46 million documents, roughly 5% of Postgres, skewed toward well-hydrated senior professionals.
  • MongoDB is rarely used.

Each person node carries a person_id (format PERS-<uuid>), person_name, title, LinkedIn work experience (company plus start and end dates), and education (school plus start and end years). Companies are also nodes. Scheduled Lambda jobs precompute edges and store them bidirectionally in pathing.edge_table; the application reads precomputed paths and never calculates connections at query time.

Before my work, the graph had two edge types: co-worker (same company, overlapping employment dates) and co-student (same school, overlapping enrollment). I added co-athlete (same NCAA team, overlapping years) and designed co-author (academic co-authorship).

Co-authorship differs from the other three edge types in a way that matters for the product. Co-worker, co-student, and co-athlete edges are same-institution by construction: both endpoints sit inside one company or one school. Co-authorship is the only edge type that is natively cross-organizational. A warm introduction into a target account needs an edge that crosses the account boundary, and co-authorship produces those at scale.

The core problem: canonicalization#

Given a scraped entity, such as “Jane Smith, Harvard, 2019” from an NCAA roster or “J. Smith, MIT CSAIL” from an OpenAlex author record, the task is to find the correct person_id in a 900-million-row table. This is canonicalization: mapping a noisy external identity to a canonical node in the graph.

One asymmetry dominates every design decision: false positives are worse than misses. If I link an NCAA athlete to the wrong person, the graph now contains a fabricated relationship. A salesperson who trusts the graph mentions the wrong connection in cold outreach, and their credibility is gone. A miss only means we don’t surface that path, and the salesperson never knows it could have existed.

This asymmetry pushes every threshold, scoring function, and resolution rule toward precision. The system is designed to under-resolve rather than mis-resolve.

Start with ground truth#

The foundational rule, inherited from Boro’s knowledge transfer before he left the team, is to build a ground-truth dataset first and iterate against it. Never tune heuristics on probabilistic matches without a fixed reference.

In practice, that means manually verifying about 50 matches against real LinkedIn profiles before running canonicalization at scale, using them to set confidence thresholds, and only then scaling up. For NCAA, I manually verified the first 20 resolved matches. For pubnet, we hand-labeled 100 edges across the decision boundary.

This is widely recommended and rarely done. The temptation is to tune thresholds until the numbers look right, without a fixed definition of “right.” Starting from ground truth caught systematic errors early. For example, inferred graduation years were off by 2–4 years for about 43% of Harvard athletes, because of how class_year labels mapped to actual graduation dates. Without ground truth, that error would have silently inflated false positives.

The NCAA co-athlete pipeline: direct database resolution#

The NCAA pipeline links college athletes to person nodes. The source data is NCAA rosters: athlete name, school, sport, and graduation year. The challenge is matching these against 900 million person records with enough confidence to write an edge.

Blocking: generating candidate pairs#

The first version used Google Custom Search (SERP) to find LinkedIn profiles. I ran a proof of concept on 98 tennis athletes at two deliberately contrasting schools: Harvard, with high LinkedIn density, and Norfolk State, a Division I HBCU with low LinkedIn density.

The result was a 74% discard rate: of 98 SERP queries, only 25 athletes were worth passing to the scoring step. The two schools failed for different reasons:

  • Norfolk State failed through silence (70% zero results). The athletes weren’t on LinkedIn. Many were international athletes from Brazil, Eastern Europe, and Latin America who returned home without building US-facing professional profiles.
  • Harvard failed through noise (48.3% “fixed spelling”). Google “corrected” athlete names and returned results for the wrong person. The root cause was the inferred graduation years being off by 2–4 years, so the query constraints didn’t narrow the results.

The discard rate ruled out SERP for production. Precision on the surviving candidates was fine, but Google Custom Search costs money at scale and gives us no control over the matching logic.

We switched to querying Postgres directly. The public.person table has a B-tree index on person_name, so exact-match lookups are fast. The query pattern that works is:

  1. Look up the name in public.person with an exact = match on the B-tree index.
  2. Verify school_degree for the returned person_id values.

The reverse order (school first, then name) always times out. school_degree has 384 million rows, and a school-first query without a person_id filter triggers a sequential scan that never finishes under production traffic.

A tiered query strategy#

The pipeline queries in tiers, relaxing one constraint at each step:

graph TD
    A["Tier 1\nExact name + school + exact grad year"] -->|miss| B["Tier 2\nExact name + school + grad year ±1"]
    B -->|miss| C["Tier 3\nExact name + school (no year)"]
    C -->|miss| D["Tier 4\nFuzzy name variant matching"]
    D -->|miss| E["Tier 6\nOpenSearch rescue"]
    E -->|miss| F["NOT_FOUND"]

    A -->|hit| G["Score candidates"]
    B -->|hit| G
    C -->|hit| G
    D -->|hit| G
    E -->|hit| G

    class A green
    class B,C,D amber
    class E,F red
    class G blue
Figure 1: The tiered query cascade. Each tier relaxes one constraint. Tier 5 (LIKE patterns) was removed after it caused sequential scans on 900 million rows. The OpenSearch rescue tier adds only about 0.5% incremental lift.

The missing tier is worth explaining. Tier 5 used ILIKE with leading wildcards, which caused full table scans across the 900-million-row person table, so we removed it. Any query pattern that bypasses the B-tree index on a table this size is a production incident waiting to happen.

Discovering school IDs#

Schools in Vieu’s Postgres are denormalized. Harvard alone has 33,724 rows in public.school, one for every sub-entity: Harvard Business School, Harvard Law, Harvard Medical, Harvard Extension, Harvard Divinity, and so on. Most sub-entity rows have linked_in_url = NULL, which hides them from URL-based discovery.

I built a discovery strategy with four probes:

  1. Exact LinkedIn URL matching through the B-tree index on public.school.linked_in_url
  2. Exact school_name matching
  3. Co-occurrence queries against person IDs resolved in earlier pipeline runs
  4. Sibling ID scanning

Shadow IDs (for Harvard, 272101974, 272177857, and 141) have largely disjoint sets of person_id values because they come from different LinkedIn scraping passes. Missing a shadow ID means missing every athlete whose education record points to that scrape.

Scoring#

Each candidate gets a weighted sum of matching signals:

Signal Points Condition
Name exact match +0.30 Case-insensitive, stripped
Name fuzzy match +0.20 Jaro-Winkler ≥ 0.85 (only if not exact)
School in education +0.25 Any education entry contains the school name
Grad year exact +0.20 Education end_year equals grad year
Grad year ±1 +0.12 end_year equals grad year ± 1
Hometown match +0.10 Hometown city appears in LinkedIn location
High school match +0.10 Jaro-Winkler ≥ 0.80 against education entries

One finding shaped the system: the hometown and high school signals never fired. All 497 resolved Norfolk State records scored exactly 0.75 (exact name, school match, and exact grad year). Hometown strings were never normalized in the source data, and LinkedIn profiles rarely list high schools. We kept both signals in the scorer, since they cost nothing to evaluate, but in practice they carried no weight.

Resolution logic#

Each athlete ends in one of five statuses:

Status Condition
RESOLVED One candidate ≥ 0.82 and no other candidate ≥ 0.70
AMBIGUOUS Two or more candidates ≥ 0.70
NEEDS_REVIEW Best score 0.55–0.69: candidates found, but not confidently
NO_DATES School matched, but no education dates in the database
NOT_FOUND No candidates in any tier

The uniqueness condition (no other candidate at or above 0.70) matters as much as the score threshold. A candidate scoring 0.85 is useless if another scores 0.75. You can’t tell them apart, and picking the higher score is a coin flip, not a resolution.

Results#

We chose two schools for contrast:

School Resolved rate Why
Harvard ~31.6% High LinkedIn density; more findable professionals
Norfolk State ~9.3% Low density; many international athletes not on LinkedIn

The 3× gap isn’t a pipeline issue; it reflects data density. Schools with more alumni on LinkedIn resolve at higher rates, which makes the gap calibration data rather than a bug.

Across a five-school proof of concept (Harvard, Norfolk State, Navy, MIT, and Michigan), the pipeline canonicalized about 48% of athletes and produced about 160,000 candidate pairs. Validation on the Harvard H3 set confirmed 97.5% accuracy on resolved matches with zero SCORER_DISAGREED cases. The system under-resolves rather than mis-resolves, as designed.

At 100 schools, we project 2–3 million PLAYED_TOGETHER edge pairs linking 500,000–800,000 unique person nodes.

Why OpenSearch didn’t work#

OpenSearch looked like a natural secondary lookup. Its test-pers-search-22051009 index has fuzzy matching, faceting, and a built-in relevance scorer, so I evaluated it as a rescue tier for athletes that Postgres couldn’t find.

It had a 98.3% false positive rate. Of 761 OpenSearch results, 748 were OS_NO_SCHOOL_MATCH false positives and only 13 were genuine OS_RESOLVED athletes. The root cause is coverage: OpenSearch holds about 46 million documents, 5% of Postgres, so “exactly one match” in OpenSearch doesn’t mean the match is unique in the full graph. The incremental lift over Postgres alone was about 0.5%.

OpenSearch is useful as a person-search product feature. It isn’t useful as a canonicalization backend.

The pubnet co-authorship pipeline: dimension-based resolution#

The pubnet pipeline links academic authors to person nodes to produce co-authorship edges. It draws on two sources:

  • OpenAlex: about 320.9 million works and 120.2 million authors, CC0-licensed and actively updated
  • OAG: about 152.5 million records, frozen since February 2024

Both inherit from Microsoft Academic Graph, which shut down in 2021, so they overlap heavily.

Resolve dimensions, not facts#

The NCAA pipeline resolves mentions one at a time, running a tiered query cascade for each athlete. That works for about 10,000 entities. Our academic sources contain about 69.7 million author slots across three shards, and resolving them one at a time would take months.

The key architectural decision was to resolve against dimensions, never against the fact table. Stage 1 builds an author_dim table with one row per distinct (source, author_id), aggregating institution affiliations across all of that author’s papers. This shrinks the resolution space from about 69.7 million author slots to about 14.9 million distinct authors, a 4.678:1 reduction that holds at full scale. An author who appears on 10 papers is resolved once, not 10 times.

The bronze, silver, and gold stages#

graph LR
    subgraph "Stage 0 · Bronze"
        A["OpenAlex\n320.9M works"] --> B["Normalize to\ncanonical work + work_author"]
        C["OAG\n152.5M records"] --> B
        B --> D["Flattened Parquet\n(work + work_author)"]
        D --> E["Load to Postgres\nUNLOGGED staging"]
    end

    subgraph "Stage 1 · Silver"
        E --> F["author_dim\n14.9M distinct entities"]
        E --> G["institution_dim\nnormalized institutions"]
    end

    subgraph "Stage 2-3 · Resolution"
        G --> H["Institution resolution\nvs company + school tables"]
        F --> I["Author canonicalization\nvs 900M person nodes"]
        H --> I
    end

    subgraph "Stage 4 · Gold"
        I --> J["Pairwise edge join\ncanonical ordering"]
        J --> K["Tier + confidence\nfiltering"]
        K --> L["Edge export\nto ingestion pipeline"]
    end

    class D amber
    class F violet
    class I blue
    class K green
Figure 2: The pubnet pipeline stages. The 4.678:1 reduction from the fact table to the author dimension is the biggest architectural win: resolution happens once per author, not once per author slot.

The pipeline runs in six stages:

Stage 0: bronze. Per-source adapters normalize raw data into canonical work and work_author records in flattened Parquet. We avoided nested arrays because DuckDB aggregation is awkward with nesting. Nothing is filtered at admission: even a paper with 5,104 authors is written in full, and n_authors keeps the raw slot count. Malformed “poison” rows are quarantined to per-shard files with the raw line, line number, and error class. Bronze is immutable and content-addressed by source version.

Stage 0c: load to Postgres. Bronze Parquet is copied into UNLOGGED staging tables with synchronous_commit = off, using chunked INSERT ... ON CONFLICT DO NOTHING, a 120-second statement timeout, and a sleep between chunks. The stage honors dry_run: when it’s set, data goes to a temporary schema and the stage logs would-be counts without touching production.

Stage 1: silver (dimension construction). This stage builds author_dim (one row per distinct author) and institution_dim (one row per normalized institution string) in two phases: Stage 1a writes per-shard partial Parquet files, and Stage 1b merges them at a barrier into global author-space shards. It’s idempotent by overwrite.

Stage 2: institution resolution. Each institution string gets three passes against the company index: normalize and AND, strip leading terms, then OR. The stage is class-aware, keeping company and school tracks separate because downstream affiliation matching uses different tables.

Stage 3: author canonicalization. Each author is resolved, gated by their resolved institution. Affiliation matching has two tracks: work_history.company_id for companies and school_degree.school_id for schools. The stage retrieves candidates, scores them, and assigns a resolution status.

Stage 4: gold (edge materialization). A pairwise join produces edges with canonical ordering through LEAST/GREATEST COLLATE "C", tiered by the paper’s author count. Edge confidence is the lower of the two endpoint confidence scores. There is deliberately no gold edge table: the gold layer feeds edges straight into the existing ingestion pipeline. That’s a defended design decision, not an omission.

Cross-source deduplication with canonical_work_id#

The original design had no cross-source deduplication. Because author_key is (source, author_id), the same person is two entities across OAG and OpenAlex, and the same paper has two publication_id values. Two researchers with 3 shared papers in both sources would aggregate to n_shared_papers = 6, which pushes a genuine strong-tier pair (2–5 authors) into the medium tier (6–15).

The fix is one derived column:

SQL
canonical_work_id = COALESCE('doi:' || normalize_doi(doi), publication_id)

Papers with DOIs collapse across sources for free; papers without DOIs stay self-canonical. The column is computed in bronze with no join and no cross-reference table, and it eliminates a whole class of double-counting bugs. Every downstream aggregate uses COUNT(DISTINCT canonical_work_id) instead of COUNT(*).

A dirty-DOI guard handles issue-level DOIs that are shared across whole journal volumes. Any canonical_work_id that maps to more than N distinct works within one source, or to works whose titles disagree beyond a threshold, is demoted back to self-canonical. The demotion count is an alarm metric.

Scoring co-authorship edges#

Co-authorship edges are tiered by the paper’s author count:

Tier Author count Meaning
Strong 2–5 Direct, intentional collaboration
Medium 6–15 Lab or group collaboration
Weak 16–50 Large collaboration
Excluded More than 50 Mega-collaboration noise

Excluding papers with more than 50 authors removes about 71% of raw edges, but those edges are noise that would dominate the graph. A single paper with 2,963 authors would fabricate about 4.4 million pairs, more than most entire data sources produce. Those aren’t real relationships: nobody on a 2,963-author paper knows the other 2,962 co-authors.

All four policy knobs live in one versioned edge policy config:

  • Tier thresholds, as shown in the preceding table
  • Degree caps: the top 250 edges per person, and both endpoints must survive
  • Confidence floors: 0.55 minimum
  • Admission rules: which resolution statuses are eligible for edges

A policy change means bumping the config version and rerunning Stages 4 and 5 only, without re-acquiring, re-parsing, or re-resolving anything.

Results#

Resolution rates. OpenAlex canonicalized at 16.5% and OAG at 17.9%. The joint Tier 1 edge resolution rate, where both endpoints resolve, was about 3.5–3.8%. That figure is indicative, measured on an induced subgraph with only 1.19% institutional coverage, but it ruled out the optimistic end of our earlier yield estimate.

Yale slice (measurement run). The run produced 6.34 million unique co-authorship edges, 5.4 million of them strong-tier and cross-organizational. Single-author papers, which produce no edges, made up 38.32% of papers. 18.26% of author pairs shared two or more papers, a repeat-collaboration signal that strengthens the relationship.

Volume funnel. Edges narrow at each stage:

  1. 141.4 million raw author-pair slots
  2. 89.9 million after structural filtering (removing single-author papers and papers with more than 50 authors)
  3. 14.7 million after resolution
  4. 5.4 million after confidence and tier filtering

Each step is defensible. Resolution, not data, is the bottleneck by a wide margin: we hold 90 million potential edges, and between 1 million and 8 million are writable at current resolution rates.

Lessons learned#

What worked#

Postgres primary, OpenSearch secondary. Direct Postgres queries using the B-tree index on person_name cover 80–90% of cases. OpenSearch’s 98.3% false positive rate and 5% coverage make it a rescue tier at best.

Querying by name first. Query person by exact name, then verify school_degree by person_id. The reverse order never completes at production scale.

Ground truth before iteration. Manually verifying 50 matches before scaling caught graduation-year errors that aggregate metrics would have hidden.

Precision over recall. The 0.82 threshold plus the uniqueness condition means the system under-resolves rather than mis-resolves: 97.5% accuracy on resolved Harvard H3 matches with zero scorer disagreements.

Dimension-based resolution. Resolving 14.9 million authors instead of 69.7 million author slots, a 4.678:1 reduction, was the biggest architectural win in the pubnet pipeline.

canonical_work_id. One derived column, with no join and no cross-reference table, eliminated a whole class of double-counting bugs. The simplest deduplication fixes are often the ones that hold up.

What didn’t work#

SERP as the primary canonicalization method. Google Custom Search is fine for prototyping but doesn’t scale. Direct database queries are the production approach; SERP is a data-repair tool.

OpenSearch as the primary lookup. With 46 million of 900 million documents, “exactly one match” doesn’t mean unique in the full graph.

Inferred graduation years. Harvard’s class_year to grad-year mapping was off by 2–4 years for about 43% of clean matches, so the +0.20 exact-grad-year signal actively hurt those athletes.

Hometown and high school signals. Neither ever fired. Hometown strings were never normalized, and LinkedIn profiles rarely include high schools. The signals looked good in design and contributed nothing in production.

ILIKE with leading wildcards. It caused full table scans on 900 million rows, so we removed it from the pipeline. Any query pattern that bypasses the B-tree index is a production incident.

Report discard rates as findings#

Three numbers that look like failures were among the most useful results:

The 74% SERP discard rate ruled out SERP and led to the direct-database architecture. Reporting it was more valuable than hiding it: it’s a precision-first gate working, not a pipeline failing.

The 3.5–3.8% joint pubnet resolution rate ruled out the optimistic yield estimate. It also showed exactly where the bottleneck is (resolution, not data) and what would improve it: better institution coverage and fuzzy name matching at scale.

The 98.3% OpenSearch false positive rate permanently settled whether to use OpenSearch for canonicalization. It shouldn’t be used, and now we have the data to defend that.

Production database safety#

Every design decision that isn’t about accuracy follows one rule: don’t break the production database. The person table serves live application traffic, and every query I run shares the same Postgres instance.

These are the rules I follow, some learned the hard way:

Set statement_timeout=30000 on every connection. If a query hasn’t returned in 30 seconds, it’s doing something wrong, usually a sequential scan.

Never pass more than 10,000 IDs in an ANY() clause. Anything above 500 is risky. Instead, cache entity IDs as an in-process Python set (about 100 ms, once), narrow to about 50 candidates with an indexed name predicate, and then verify membership with a bounded ANY(...) lookup.

Never join on school_id without filtering on person_id first. Harvard has 33,724 school rows, and an unconstrained join explodes.

Prefer EXTRACT(YEAR FROM dates_to) to range-style date predicates. Query profiling showed it’s 12× cheaper, because the simpler expression keeps the planner from choosing a sequential scan.

Never use ILIKE with a leading wildcard on large tables. It bypasses every index. This is why Tier 5 was removed.

Don’t bulk-fetch from the primary. Never pull large record sets from the primary database. Tier 1 filters on cheap match signals, and expensive analysis runs only on high-confidence survivors.

These rules aren’t performance optimizations; they’re safety boundaries. A runaway query on a 900-million-row table shared with live traffic is a production incident, not a slow notebook cell.

Why the scoring denominator stays wrong#

One small technical detail illustrates a broader principle about production scoring systems.

The pubnet author scorer computes:

Text
score = (name_jw × 4 + affiliation_match × 2 + year_match × 2) / 10

The weights sum to 8, not 10. The /10 is left over from the NCAA five-signal scorer (name 4, school 2, year 2, hometown 1, high school 1). When hometown and high school were dropped for never firing, nobody updated the denominator.

As a result, the maximum score is 0.80, not 1.0. A CONFIRMED threshold of 0.70 really means 7 of 8 achievable points (87.5%), which requires name_jw of at least 0.75 even with perfect affiliation and year matches. That’s genuinely strict.

It’s tempting to “fix” the denominator to 8. Don’t. Changing /10 to /8 without rescaling every threshold would drop the CONFIRMED bar from name_jw ≥ 0.75 to name_jw ≥ 0.40, which would admit garbage. Every threshold, validation metric, and ground-truth label was calibrated against /10, and changing it invalidates all of those measurements.

The general principle: in a production scoring system, the denominator is an arbitrary convention, but the thresholds calibrated against it are empirical. Change the convention only if you’re prepared to recalibrate everything downstream. Usually it isn’t worth it.

Conclusion#

Entity resolution at graph scale isn’t a matching algorithm. It’s a system design problem shaped by constraints that dominate every technical choice: production database safety, the asymmetric cost of false positives, ground-truth methodology, and the fact that an external data source will never cover the full graph.

The pipeline architecture generalizes to four steps:

  1. Blocking reduces the search space.
  2. Scoring weights the signals you have.
  3. Resolution decides using a uniqueness condition, not only a threshold.
  4. Tiered confidence recognizes that matches aren’t equal: some write edges, some need review, and some are discarded.

These findings carried across both pipelines:

  • Build ground truth first. Fifty manually verified matches catch errors that aggregate metrics hide.
  • Favor precision over recall. A missed edge is invisible; a wrong edge is a fabricated relationship.
  • The query pattern matters more than the scoring function. A name-first, index-covered query beats clever fuzzy matching that causes a full table scan.
  • Report discard rates as findings. A 74% discard rate that rules out a bad approach is worth more than a 90% match rate on the wrong method.
  • Treat the scoring denominator as a contract. Calibrate once, and don’t change the denominator without recalibrating everything downstream.

The system under-resolves rather than mis-resolves. That isn’t a limitation; it’s the design.

I build graph enrichment pipelines at Vieu.