Resolving Funder Alias Collisions with Deterministic Lookup Tables

This guide is part of the Field Mapping & Normalization section within the broader Data Ingestion & Grant Parsing Workflows framework, and it solves one narrow problem: the raw label Total means cumulative award for one funder and current-period request for another, so a single global alias map silently resolves both to the same canonical field and corrupts the ledger.

A flat {"total": "award_total"} dictionary looks harmless until the second funder arrives. Now two funders legitimately disagree about what Total means, and whichever mapping loaded last wins — nondeterministically, with no record of the override. This guide builds a lookup that scopes every alias by funder_id, breaks any remaining in-funder ambiguity with an explicit precedence order, refuses to load a table that maps one key to two canonical targets, and writes a lineage entry for every single resolution so an auditor can replay how Total became award_total on this row and period_request on that one.

When to Use This Approach #

Reach for a funder-scoped deterministic lookup when all three of these hold:

• The same raw label means different things across funders. Total, Amount, Budget, and Cost are the usual offenders — each funder’s template author chose them independently, so no global meaning exists. If your labels were already globally unique you would not need namespacing; you would need only the canonical rename covered by Standardizing Grant Field Names Across Multiple Portals.
• Wrong mappings are a financial-records defect, not a cosmetic one. A Total that lands in the wrong canonical field misstates the award amount that reconciles against 2 CFR §200.302 financial-management records. That regulation requires records that identify the source and application of funds, which is impossible if a resolution can silently flip meaning between runs.
• You need the mapping to be replayable and reviewable. Auditors and grant managers must be able to answer “why did this cell become award_total?” months later. That answer lives in a lineage entry, not in tribal knowledge about which dictionary loaded last.

What is out of scope here: the tables you resolve are produced upstream — coordinate extraction of budget grids belongs to PDF Grant Application Parsing, and the downstream canonical rename plus dtype coercion belong to the parent Field Mapping & Normalization stage. This guide covers only the alias-to-canonical resolution decision and its audit surface. The lookup itself is data, not code: it lives in a versioned YAML file so a grant analyst can amend a funder’s aliases without a deployment.

Step-by-Step Implementation #

The reference implementation targets Python 3.10+ and depends only on the standard library plus PyYAML for reading the data file:

python>=3.10
PyYAML==6.0.1

Step 1: Namespace the lookup by funder_id #

The whole defect comes from a key that is too small. Widen it: the resolution key is the pair (funder_id, normalized_label), never the label alone. Normalize the label to a stable form — trimmed, lowercased, internal whitespace collapsed — so " Total " and total hit the same entry, but keep the funder scope so funder A’s Total and funder B’s Total are different keys entirely.

import logging
from typing import Dict, List, Optional
from dataclasses import dataclass

logger = logging.getLogger("grant.field_mapping.alias")


def normalize_label(label: str) -> str:
    """Collapse a raw column label to a stable lookup token."""
    return " ".join(label.strip().lower().split())


@dataclass(frozen=True)
class AliasCandidate:
    """One admissible mapping for a (funder, label) key."""
    canonical: str          # e.g. "award_total"
    section: str            # table section this applies to, or "*" for any
    rank: int               # lower wins when several candidates match
    rule_id: str            # stable id of the source row, for lineage

AliasCandidate is frozen because a resolved mapping must be immutable once loaded. funder_id is not a field on the candidate — it is the namespace the candidate lives under, which the index in Step 3 makes explicit.

Step 2: Define deterministic precedence for ambiguous labels #

Scoping by funder removes cross-funder collisions but not in-funder ambiguity: within one funder, Total on the personnel section and Total on the summary section can legitimately mean different fields. Resolve that with an explicit, total ordering rather than insertion order. The rule: a candidate whose section exactly matches the current table section beats a "*" wildcard, and ties break on the lower rank. Because the sort key is total, the outcome is identical on every run.

def resolve_with_precedence(
    candidates: List[AliasCandidate], section: str
) -> Optional[AliasCandidate]:
    """Pick exactly one candidate by section specificity, then rank."""
    scoped = [c for c in candidates if c.section in (section, "*")]
    if not scoped:
        return None
    # exact section (0) sorts before wildcard (1); rank breaks the tie
    scoped.sort(key=lambda c: (0 if c.section == section else 1, c.rank))
    return scoped[0]

The section argument is supplied by the extractor that produced the table (for example, the heading above the grid). Passing "*" as the section forces wildcard-only resolution, which is the correct behavior for a funder whose template has no sections.

Step 3: Detect collisions at table-load time #

The failure you must never ship is two candidates that are indistinguishable — same funder_id, same normalized label, same section, same rank — but different canonical targets. That is an unresolvable tie, and it must fail when the table loads, not when a row happens to hit it in production. Build the namespaced index from the YAML rows and raise a structured error the moment two rows contend for one slot.

import yaml
from pathlib import Path

AliasKey = tuple  # (funder_id: str, normalized_label: str)


class AliasCollisionError(Exception):
    """Two distinct canonical targets claim one (funder, label, section, rank)."""

    def __init__(self, funder_id: str, label: str, section: str, targets: List[str]) -> None:
        self.funder_id = funder_id
        self.label = label
        self.section = section
        self.targets = sorted(targets)
        super().__init__(
            f"Alias collision for funder={funder_id!r} label={label!r} "
            f"section={section!r}: {self.targets}"
        )


def build_index(table_path: Path) -> Dict[AliasKey, List[AliasCandidate]]:
    """Load the alias data file and fail fast on any unresolvable collision."""
    raw = yaml.safe_load(table_path.read_text()) or {}
    index: Dict[AliasKey, List[AliasCandidate]] = {}
    # slot -> canonical, to detect (section, rank) contention within a key
    seen: Dict[tuple, str] = {}

    for funder_id, rows in raw.items():
        for row in rows:
            label = normalize_label(row["label"])
            cand = AliasCandidate(
                canonical=row["canonical"],
                section=row.get("section", "*"),
                rank=int(row.get("rank", 100)),
                rule_id=row["rule_id"],
            )
            slot = (funder_id, label, cand.section, cand.rank)
            prior = seen.get(slot)
            if prior is not None and prior != cand.canonical:
                raise AliasCollisionError(funder_id, label, cand.section, [prior, cand.canonical])
            seen[slot] = cand.canonical
            index.setdefault((funder_id, label), []).append(cand)

    logger.info("Loaded alias index: %d keys across %d funders", len(index), len(raw))
    return index

The corresponding data file is deliberately readable — a grant analyst edits it, not an engineer:

NIH:
  - {label: "Total", canonical: "award_total", section: "summary", rank: 10, rule_id: "nih-001"}
  - {label: "Total", canonical: "personnel_total", section: "personnel", rank: 10, rule_id: "nih-002"}
FORD_FOUNDATION:
  - {label: "Total", canonical: "period_request", section: "*", rank: 10, rule_id: "ford-001"}

Because collision detection keys on (funder_id, label, section, rank), the two NIH rows above coexist happily — they differ by section — while a second ford-001 row pointing Total at a different canonical would abort the load.

Step 4: Record a lineage entry for every resolution #

Every resolution, not just the surprising ones, produces an immutable lineage record binding the raw input to the canonical output and the exact rule that decided it. This is the artifact that lets an auditor replay the decision under 2 CFR §200.302. Emit it to the audit logger and return it alongside the resolved field.

from datetime import datetime, timezone


@dataclass(frozen=True)
class LineageEntry:
    funder_id: str
    raw_label: str
    section: str
    canonical: str
    rule_id: str
    resolved_at: str


class AliasResolver:
    def __init__(self, index: Dict[AliasKey, List[AliasCandidate]]) -> None:
        self._index = index
        self.unmapped: Dict[tuple, int] = {}

    def resolve(self, funder_id: str, label: str, section: str = "*") -> Optional[LineageEntry]:
        norm = normalize_label(label)
        candidates = self._index.get((funder_id, norm), [])
        chosen = resolve_with_precedence(candidates, section)
        if chosen is None:
            self.unmapped[(funder_id, norm)] = self.unmapped.get((funder_id, norm), 0) + 1
            logger.warning("Unmapped alias funder=%s label=%r section=%s", funder_id, norm, section)
            return None
        entry = LineageEntry(
            funder_id=funder_id, raw_label=norm, section=section,
            canonical=chosen.canonical, rule_id=chosen.rule_id,
            resolved_at=datetime.now(timezone.utc).isoformat(),
        )
        logger.info("Resolved %s/%r -> %s via %s", funder_id, norm, chosen.canonical, chosen.rule_id)
        return entry

Recording rule_id rather than only the canonical name is what makes the trail reproducible: it points at the exact data-file row, so a later table edit is visible as a different rule_id in the lineage rather than a silent change of meaning.

Step 5: Expose an unmapped-label report #

A None from resolve means the funder-scoped index has no candidate — that key must surface, not vanish. Step 4 already accumulated misses; now render them as a deterministic, aggregated report an analyst uses to extend the data file. Never guess a canonical target for an unmapped label; guessing is exactly the nondeterminism this design removes.

def unmapped_report(resolver: AliasResolver) -> List[dict]:
    """Deterministic, ordered report of every unresolved (funder, label)."""
    rows = [
        {"funder_id": fid, "label": lbl, "occurrences": count}
        for (fid, lbl), count in resolver.unmapped.items()
    ]
    rows.sort(key=lambda r: (-r["occurrences"], r["funder_id"], r["label"]))
    logger.info("Unmapped-label report: %d distinct keys", len(rows))
    return rows

Sorting by descending occurrence then funder and label keeps the report stable across runs and puts the highest-impact gaps first, so the analyst extends the YAML in priority order.

Verification #

Confirm the resolver is deterministic and audit-complete with four checks:

1. Namespacing keeps identical labels apart. Resolve Total for NIH with section summary and for FORD_FOUNDATION with section *, and assert the canonical outputs are award_total and period_request respectively — proof that the funder scope, not load order, decides.
2. Precedence is total and stable. Resolve Total for NIH with section personnel and assert personnel_total; shuffle the candidate list and assert the result is unchanged, proving the sort key fully orders the candidates.
3. Collisions fail at load, not at runtime. Feed build_index a data file with two NIH/Total/summary/rank 10 rows pointing at different canonicals and assert it raises AliasCollisionError with both targets — before any row is ever resolved.
4. Every resolution and miss is recorded. Assert a successful resolve returns a LineageEntry carrying the rule_id, and that an unknown label increments resolver.unmapped and appears in unmapped_report.

index = build_index(Path("aliases.yaml"))
resolver = AliasResolver(index)

assert resolver.resolve("NIH", "Total", "summary").canonical == "award_total"
assert resolver.resolve("FORD_FOUNDATION", " total ", "*").canonical == "period_request"
assert resolver.resolve("NIH", "Indirect", "summary") is None
assert unmapped_report(resolver)[0]["label"] == "indirect"

A compliant run emits one INFO line per resolution carrying the funder, normalized label, canonical target, and rule_id, plus one WARNING per unmapped key. Ship those logs to a write-once tier so the resolution trail is retained for review alongside the financial records governed by 2 CFR §200.302.

Common Errors & Fixes #

Related #

• Parent section: Field Mapping & Normalization
• The canonical rename these resolutions feed: Standardizing Grant Field Names Across Multiple Portals
• Where the raw funder labels come from: PDF Grant Application Parsing