web_docs/docs/dbt/entity-resolution-mdm.md

---
sidebar_position: 12
---

# Master Data Management: Person & Address Entity Resolution

Design for normalizing names and addresses across heterogeneous sources so that
name search and address-map search resolve **similar entities to one canonical
record**. Links persons and addresses from OpenStates, GivingTuesday / 990s,
campaign contributions, `bronze_locations`, and the AI-extracted `event_person`
/ `event_place` / `event_organization` marts.

**Status:** partially built. Addresses resolve deterministically and serve from
`mdm_address`; organizations serve from `mdm_organization`. The person pool
(`int_persons__unioned`) is built and now serves at **source-occurrence grain**
from `mdm_person` (PK `person_uid`) — the canonical public person table that
replaced the legacy `contacts_search_ai` model. Probabilistic person clustering
(Splink → `int_persons__clustered` → a deduplicated `master_person_id`) is the
remaining piece; until it lands, `mdm_person` serves the conformed occurrences.
Serving models use the `mdm_` prefix (the `dim_`/`fact_` star-schema naming is
disallowed). Build incrementally in the order in [Build order](#build-order).

## Division of labor

dbt owns the **keys and serving**; Splink owns the **fuzzy matching and
clustering**. The two halves talk through exactly two tables, so neither reaches
into the other's internals.

```
dbt (keys + conformance)          Python / Splink (matching)         dbt (serving)
──────────────────────────        ──────────────────────────         ─────────────────
Layer 0  extensions               Layer 3  Splink model              Layer 5  mdm_person/address
Layer 1  normalization macros  ►  Layer 4  predict + cluster    ►            mdm_bridge_*
Layer 2  conformed stagings       writes clusters → bronze              (reads cluster table)
```

- **dbt → Splink contract:** `int_persons__unioned` / `int_addresses__unioned`
  (the conformed input Splink reads).
- **Splink → dbt contract:** `bronze.entity_person_clusters` /
  `bronze.entity_address_clusters`
  (`source_system, source_pk, master_person_id, match_probability`).

## Why this shape

- Reuses the record-linkage pattern already proven in
  `int_master__crosswalk` (one CTE per strategy, `match_method` +
  `match_confidence`), rather than a bespoke MDM tool.
- Normalization keys live **upstream in dbt** where they are shared and tested —
  Splink's `block_on(...)` clauses reference those same columns.
- Cross-reference **bridge tables** link sources without mutating source data.
- Every step is independently shippable (exact-match first, fuzzy later).

## The sources

| Source | Table | Name field(s) | Address field(s) | Strong keys |
|---|---|---|---|---|
| OpenStates persons | `bronze_persons_scraped` via `stg_bronze_persons_scraped` (filtered `is_usable_person`) | `name_clean` | `mailing_address` | `ocd_id`, `email`, `phone` |
| Election candidates | `bronze_persons_osf_ledb` (OSF LEDB) | `full_name`, `firstname`, `lastname` | `geo_name`, `state_abb` | `ledb_candid` |
| GivingTuesday / 990 | `bronze.bronze_organizations_nonprofits_nccs` | `org_name_current` | `f990_org_addr_street/city/state/zip`, `org_addr_full`, lat/long | `ein` |
| Campaign contributions | `bronze.bronze_campaigns_contributions` | `contributor_name` | `contributor_city/state/zip` | `contributor_employer`, `committee_id` |
| Locations | `bronze.bronze_locations` | `name` | `address`, `city`, `state`, `zip`, `county`, lat/long | `telephone`, `website` |
| Parcel / property records | `bronze.bronze_addresses` (~598k) | `owner_name` | `street_line1` (number embedded), `street_line2`, `city`, `state_abbr`, `postal_code` | `parcel_number`, `jurisdiction_id` |
| AI persons | `bronze.bronze_persons_from_ai` | `full_name`, `appeared_as` | — | `person_id`, `wikidata_qid` |
| AI places | `bronze.bronze_places_from_ai` | — | `normalized_address`, `street_address`, `city`, `state_code`, lat/long | `geocode_status` (no strong id) |
| AI organizations | `bronze.bronze_organizations_from_ai` | `org_name`, `org_name_normalized` | — | `ein`, `ntee_code`, `wikidata_qid` |

**Read AI sources at bronze, not at the marts.** `event_person` / `event_place` /
`event_organization` are medallion *outputs* (`bronze_*_from_ai` + event/
jurisdiction joins + monthly partitioning). Feeding a mart back into an
intermediate model is a backwards DAG dependency; MDM also doesn't need the event
context to resolve an entity. So conform from `bronze_persons_from_ai`,
`bronze_places_from_ai`, `bronze_organizations_from_ai` directly.

`bronze.bronze_addresses` is the largest address source (~598k) but is **not**
pre-parsed despite its column names: `street_number` is empty (the number is
embedded in `street_line1`) and `situs_full` carries a noisy source prefix, so
the street/number split is done in `stg_parcels__address`. Its `owner_name` is a
parcel owner (person *or* business), so it feeds the name pipeline too under the
right `entity_type`.

**Reference / gazetteer (not resolution inputs):** `bronze.bronze_geo_places`
(Census place gazetteer: name → geoid → state) and
`bronze.bronze_osf_places_geocoded` (place → lat/long → population) are lookups
used to **validate and enrich** `city_norm` / place names and attach geoids —
they are a reference dimension, not entities to dedup.

:::warning Grain mismatch
`contributor_name` and 990 `org_name_current` are **organizations**, not people.
Route them to an organization resolution pool (or at minimum a separate
`entity_type` partition) — do not link them against `event_person` / OpenStates
humans. Connect person ↔ org via an affiliation edge instead.
:::

## Layer 0 — extensions

One migration / `dbt run-operation`. Used by Splink's Postgres comparison
functions and by the API search trigram indexes.

```sql
create extension if not exists unaccent;
create extension if not exists pg_trgm;
create extension if not exists fuzzystrmatch;
```

## Layer 1 — normalization macros (dbt)

In `dbt_project/macros/`. These produce the blocking keys Splink consumes, so
they must live here, shared, not inside the Splink script. Keep the existing
`normalize_name` for the generic case and add:

- `normalize_person_name(col)` — `unaccent` → lowercase → flip "Last, First" →
  strip titles (`mr|mrs|dr|hon|councilmember|commissioner`) and suffixes
  (`jr|sr|iii`) → collapse whitespace.
- `name_phonetic_key(col)` — `dmetaphone(family_name)` (Double Metaphone, via
  `fuzzystrmatch`) for the fuzzy blocking key; Splink also uses `dmetaphone` as a
  name comparison level.
- `normalize_address(col)` — `unaccent` → lowercase → expand USPS abbreviations
  (`st→street`, `ave→avenue`, `n→north`…) → strip unit/suite → collapse.
- `zip5(col)` — `left(regexp_replace(zip, '\D', '', 'g'), 5)`.
- `address_match_key(...)` —
  `md5(normalized_street || '|' || city_norm || '|' || state_code || '|' || zip5)`.

Address **parsing** (street vs unit vs city) is the one task worth doing outside
dbt — a small `usaddress` / `libpostal` enrichment in `packages/ingestion` that
writes parsed components back to bronze. Start SQL-only (abbreviation expansion
covers ~80%); add the parser only if match recall is poor.

## Layer 2 — conformance staging (dbt)

`stg_<source>__person` and `stg_<source>__address` for each resolution source
(OpenStates, 990/NCCS, campaign contributions, `bronze_locations`,
`bronze_addresses`, and the `event_*` marts — not every source feeds both
pipelines: contributions and `event_person` are name-only, `bronze_addresses`
feeds both),
all emitting one uniform contract, then unioned into
`int_persons__unioned` / `int_addresses__unioned`:

```
source_system, source_pk, entity_type,
-- names
raw_name, name_norm, given_name_norm, family_name_norm,
family_name_initial, name_phonetic_key,   -- initial = left(family_name_norm, 1), for blocking
-- addresses: PARSED INTO COLUMNS, not one string (Splink matches far better this way)
raw_address, address_norm, address_match_key,
street_number, street_name, city_norm, state_code, zip5,
lat, lon,
-- strong keys, nullable
email, phone, ein, external_id
```

:::tip Parse addresses into columns
Splink performs significantly better when an address is split into distinct
columns than when fed one long string — each component gets its own comparison
and term-frequency weighting. Break every source address into `street_number`,
`street_name`, `city_norm`, `state_code`, `zip5` during conformance. SQL
abbreviation expansion covers the easy cases; the `usaddress` / `libpostal`
enrichment (Layer 1) fills `street_number` / `street_name` for the messy tail.
:::

Standardization happens here too, before anything reaches Splink — **garbage in,
garbage out**: lowercase every string, strip leading/trailing and double
whitespace (`normalize_name` / `normalize_address` already do this), `unaccent`,
and null out empties so Splink sees real nulls rather than `''`.

This is the highest-value artifact. **Validate row counts and key null-rates
here before going further** — most of the value and most of the bugs live in
conformance.

## Layer 3–4 — resolution

**Split by data shape (decided after a first Splink run over-merged addresses):**

- **Addresses → deterministic (dbt, not Splink).** Addresses are structured, so
  they resolve on the exact normalized-address key. Probabilistic clustering
  chained whole ZIPs together (a 13,699- then 4,469-member blob of *different*
  houses) because fuzzy `street_number` scoring is too weak against exact
  zip+city+state agreement. `int_addresses__clustered` sets
  `master_address_id = coalesce(address_match_key, address_uid)` — identical
  addresses merge, streetless singletons stay separate. Result: 442,579 →
  **376,337 master addresses**, largest cluster a real shared PO box (551), built
  in seconds, zero chaining. `mdm_address` + `mdm_bridge_address_xref` serve it.
- **Persons → Splink (probabilistic).** Names have no clean key and genuinely
  need fuzzy + phonetic matching; this is where Splink earns its keep.

The Splink design below now applies to the **person** pool.

### Splink (Python, `packages/`)

New Python is a proper library, not a script (`packages/` rule). Record linkage
is explicitly allowed outside dbt (ML / ingestion).

- **Location:** `packages/ingestion/src/ingestion/mdm/` — `person_linker.py`,
  `address_linker.py`, shared `settings.py`, and a CLI so it runs as
  `python -m ingestion.mdm.person_linker` (mirrors `llm.governance`,
  `ingestion.youtube`). Add `splink` to that package via `uv`.
- **Backend:** Splink Postgres backend on `localhost:5433/open_navigator`,
  reading the `intermediate.int_*__unioned` tables; results write back to
  `bronze`. No data leaves the warehouse. (DuckDB backend is the fallback if
  Postgres perf bites.) Two backend constraints, caught via `--dry-run` and
  handled in `ingestion.mdm`: the Postgres dialect has **no Jaro-Winkler** (use
  Levenshtein via `fuzzystrmatch`), and it resolves input tables by **bare name
  only** (engine sets `search_path=intermediate,bronze,public`; schema-qualified
  names fail).
- **Blocking rules — run several in sequence** so a typo in one field doesn't
  drop the pair (a record blocked out by a bad zip is still caught by a
  name-based rule). Avoids the `O(N²)` all-pairs blowup. Use the dbt keys:
  - `block_on("zip5")` — exact zip.
  - `block_on("family_name_initial", "state_code")` — first initial of surname + state.
  - `block_on("street_name")` — shared street name.
  - `block_on("email")` / `block_on("external_id")` — strong-key shortcuts.

  Keep `entity_type` as a partition so org rows never block against people.

- **Comparison levels — ordered, most-to-least certain per column.** Mix string
  metrics (Jaro-Winkler / Levenshtein) with phonetics (Double Metaphone).

  **Person name** (`given_name_norm`, `family_name_norm`):
  1. Exact match (`john bowyer` == `john bowyer`).
  2. **Double Metaphone** match — catches `jon`/`john`, `smith`/`smyth`.
  3. **Jaro-Winkler ≥ 0.88** — catches typos `jhon`, `bowyre`.
  4. Else / null.

  **Address** (parsed columns):
  - `street_number` — **exact-or-null only**. A wrong number is a different
    house; do not fuzzy-match it.
  - `street_name`, `city_norm` — Jaro-Winkler / Levenshtein levels (`main st`
    vs `maen st`).
  - `zip5` — **partial credit**: exact = strong; first-3-digits match = a
    positive (weaker) level; total mismatch = penalty. Don't treat zip as
    all-or-nothing.
  - lat/long distance band as a corroborating level when both present.

- **Term-frequency (TF) adjustments — turn these on.** A match on a rare name or
  rare street name is heavier evidence than a match on `Smith` or `Main`. Splink
  down-weights common values and boosts rare ones automatically; enable TF on
  `family_name_norm`, `given_name_norm`, `street_name`, and `city_norm`.

- **Training:** `estimate_u_using_random_sampling` + EM; persist the trained
  model JSON in the package (versioned) for reproducible runs.
- **Clustering:** `cluster_pairwise_predictions_at_threshold(df, threshold=0.9)`
  → `cluster_id` becomes `master_person_id` / `master_address_id`. Tune the
  threshold from Splink's match-weight waterfall charts; log precision/recall on
  a hand-labeled sample.

**Survivorship (golden record) stays in dbt** — pick best-source / most-complete
per cluster in reviewable SQL, not in Python.

## Layer 5 — serving (dbt marts)

- `mdm_person`, `mdm_address` — golden records, built by joining `int_*__unioned`
  to the cluster table plus survivorship logic. **Addresses** are live
  (deterministic clustering). **Persons** currently serve at source-occurrence
  grain (PK `person_uid`) directly off `int_persons__unioned`; the upstream `ref`
  swaps to `int_persons__clustered` once Splink person clustering lands, adding a
  deduplicated `master_person_id` — same evolution `mdm_organization` followed.
- `mdm_bridge_person_address`, `mdm_person_source_link`, `mdm_bridge_address_xref`
  — link the masters to `source_system + source_pk` (and to each other).
- API name-search and address-map-search query the `mdm_` marts; every source
  reaches them through the bridge.
- `GIN (name_norm gin_trgm_ops)` and `GIN (address_norm gin_trgm_ops)` indexes
  keep `similarity()` / `%` searches fast.

```
bronze sources ─┐
                ├─► stg_<src>__person/address (conform) ─► int_*__unioned
event_* marts ──┘                                              │
                                                               ▼
                                          Splink: predict + cluster (packages/ingestion)
                                                               │
                                                               ▼
                                          bronze.entity_*_clusters (master_id)
                                                               │
                                                               ▼
                          mdm_person / mdm_address + mdm_bridge_* ◄── API search
```

## Build order

Each step is shippable on its own.

1. Extensions + the five normalization macros.
2. Conformed stagings → `int_persons__unioned` / `int_addresses__unioned`.
   **Validate row counts + key null-rates before continuing.**
3. Splink linker on **addresses first** (smaller comparison space, lat/long is a
   strong signal) as the template.
4. `mdm_address` + `mdm_bridge_address_xref` + API trigram index. Prove search
   end-to-end on one entity. ✅ done.
5. Repeat 3–4 for persons. `mdm_person` already serves at occurrence grain;
   remaining work is the Splink person linker → `int_persons__clustered` →
   `master_person_id`, then repoint `mdm_person`'s upstream `ref`.

## Watch-outs

- **Organizations vs people:** `contributor_name` and 990 `org_name_current` go
  to an organization master, not the person pool.
- **AI-extracted sources are lowest trust:** `bronze_persons_from_ai` /
  `bronze_places_from_ai` have no strong keys and known dirtiness (channel-name
  collisions, promotion gaps).
  Use a conservative match threshold and rank them last in survivorship so a
  hallucinated name never becomes a golden record.
- **Name token order differs across sources** (found via `audit_mdm_keys`):
  campaign `contributor_name` is `LAST, FIRST` (comma-delimited, which
  `normalize_person_name` flips), but parcel `bronze_addresses.owner_name` is
  `LAST FIRST` with **no comma** (`DORROUGH JESSE`) — the macro can't detect
  order, so it does not flip, and a surname-as-last-token phonetic key keys on
  the given name instead. Owner names also carry estate/trust noise
  (`... 1/2 INT & ... TRUST`). **Decision:** do not rely on a single "surname"
  phonetic key. Emit phonetic keys for *both* the first and last token and let
  Splink's name comparison + multiple blocking rules resolve order, rather than
  hard-coding a per-source flip. Low match weight handles the trust/estate noise
  gracefully. See [Layer 1](#layer-1--normalization-macros-dbt) /
  [Layer 3](#layer-34--splink-python-packages).
- **Streetless rows + PO boxes need NULL keys** (found building
  `int_addresses__unioned`): `address_match_key` already returns NULL when the
  street is blank — without that, ~11.7k streetless Selma AL parcels collapsed
  onto one hash. A shared **PO box** is the same trap (`po box 412` = 551
  distinct owners): treat PO-box-only situs as not-a-unique-address and exclude
  it from the exact key too, leaving those rows to Splink's fuzzy comparison.
- **High-volume sources must dedup to distinct entities, not occurrences**
  (found building `int_persons__unioned`): `bronze_campaigns_contributions` has
  **~24.5M transaction rows** — a donor who gave 500 times is one entity, not 500
  pool rows. `stg_contributions__person` collapses to one row per distinct
  contributor identity (`name_norm` + city + state + zip) before the union, with
  `source_pk` = a hash of that identity; the transaction-level link belongs in a
  separate xref, not the person pool. Apply the same rule to any future
  transaction-grained source. Result: 24.5M txns → **1,885,888 distinct
  contributors** (13× collapse); full person pool `int_persons__unioned` =
  2,362,852 rows.
- **Materialize high-volume staging as a table, not a view** (perf, found
  building the above): `stg_contributions__person` is a view, so the regex-heavy
  `normalize_person_name` + `distinct on` over 24.5M rows re-runs on every read —
  the union build took ~21 min, almost all of it here. Switch this one staging
  model to `materialized='table'` (or an incremental int model keyed on the
  append-only contributions) so the normalization runs once. The view default is
  fine for the small sources.
- **Person bridges are FK-bound to `mdm_person` and filtered to real people**
  (resolved after measuring orphan keys). `mdm_person` filters to
  `entity_type='person' and is_probable_person`; the bridges
  (`mdm_bridge_person_address`, `mdm_person_source_link`) now apply the **same
  filter** so every `person_uid` resolves to an `mdm_person` row — enforced as a
  `person_uid → mdm_person` FK. Before this, `mdm_bridge_person_address` pulled
  every `bronze_addresses` owner occurrence unfiltered: 35,773 of 111,075 distinct
  keys (32%) had no `mdm_person` row — **24,467 `entity_type='organization'`**
  (business parcel owners) and 11,306 low-quality person names. The filter drops
  those (bridge: 111,075 → 75,302 keys, 0 orphans). The excluded business parcel
  owners are not lost: `stg_parcels__org` routes the org-shaped owner names
  (`classify_name_entity_type = 'organization'`) into the org pool, and
  `mdm_bridge_org_address` now carries them as `parcel_owner` links (≈8,086 distinct
  org↔address pairs, 0 address-FK orphans) alongside the `located_at` facility links.
- **Filter non-names at the source, flag the rest** (found via `full_name`/
  `is_probable_person`): scraped pages yield ~12k non-name strings (titles, dates,
  "hours of operation", UI chrome). `stg_openstates__person` reuses the existing
  `is_usable_person` gate from `stg_bronze_persons_scraped` so they never enter
  the pool. For residual junk from other sources, `int_persons__unioned` exposes
  `is_probable_person` (false for orgs, names with digits, 1- or 6+-token strings)
  and `full_name` = `initcap(name_norm)` for display. Filter with
  `where is_probable_person` before serving.