Clinical Metadata Normalization Across Trial Systems

The same site is 0042 in the CTMS, SITE-0042-US in the EDC export, and referenced by its institution name alone in the IRB correspondence. The same investigator is “Dr. Robert J. Smith, MD” on a delegation log, “Smith, Robert” in the CTMS contact record, and “R. Smith” on a signed 1572. Dates arrive as 03/04/2026, 2026-03-04, and “4 March 2026” depending on which system exported them, and a country shows up as USA, US, and United States across three different feeds for the same site. None of these systems is wrong on its own terms — each is internally consistent with its own conventions — but a pipeline that has to reconcile a site’s readiness across CTMS, EDC, and IRB records needs one canonical answer for what the site number, the investigator, the date, and the country actually are.

This guide covers building that canonical layer: a small, typed model that every heterogeneous source maps onto, a set of deterministic coercion rules for dates and country codes, and explicit tie-break rules for when two systems disagree about the same field. It sits inside Automated Document Ingestion & Validation Workflows, consuming documents that have already been classified by Document Classification & Routing and cleared through Schema Validation & Error Categorization. Validation confirms a single document is well-formed; normalization is what makes three well-formed documents from three different systems agree with each other.

Why normalization is a separate concern from validation #

A CTMS export with site_no: "42" and an EDC export with SiteNumber: "0042" can both pass their own schema validation perfectly — each is a syntactically valid string in its own system — and still refer to the same site under two different string representations. Schema validation checks that a value has the right shape; normalization decides what a value means across systems and reconciles it to one identity. Skipping this step does not eliminate the problem, it just relocates it: the mismatch surfaces later, as two “different” sites in a readiness report, or as a submission that cites an investigator name that does not match what the IRB has on file.

The reconciliation itself has to be deterministic and traceable for the same reason document classification is rules-first: an inspector or a data manager asking “why does this canonical record say the site activated on this date” needs an answer that names a source system and a rule, not a best-effort guess. Fuzzy matching and similarity scores can suggest candidate matches to a human reviewer, but the actual merge decision is a fixed precedence rule over a fixed set of source systems, applied the same way every time.

Decision flow #

Every raw field passes through the same four stages regardless of which source system it came from or which canonical entity — site or investigator — it belongs to: map the source-specific field name onto its canonical name, coerce the raw value into a strict type, resolve the entity’s identity by matching keys across systems, and apply a fixed precedence rule wherever two systems disagree.

Some of the raw values entering this pipeline originate from PDF-derived metadata captured during earlier ingestion — an IRB approval letter’s issue date read from its document properties, or a delegation log’s site identifier pulled from an AcroForm field, both covered in the OCR pipeline and the classification stage. Any extraction step reading those PDF structures should use the maintained pypdf library; the legacy PyPDF2 package is unmaintained and should not be the source of a value that later feeds a canonical record.

Library and tooling landscape #

Date and country handling look trivial until a pipeline meets its first ambiguous format or its first legacy timezone dependency. The choices below favor strictness over convenience everywhere a wrong guess would be silent.

Deprecated dependency: pytz predates the standard library’s zoneinfo module and is no longer the recommended way to attach a timezone to a clinical timestamp. New code should read from zoneinfo import ZoneInfo and drop the pytz dependency entirely; existing pytz-based code is safe to leave running but should not be the pattern copied into new normalization logic.

Step-by-step implementation #

The normalizer decomposes into the same stages the decision flow shows: model the canonical entities and the raw records that feed them, map fields declaratively, coerce types strictly, and resolve identity with a fixed precedence rule.

1. Model the canonical entities and the raw source record #

A SourceRecord captures one field value exactly as it arrived, before any interpretation; the canonical dataclasses are frozen so a later stage cannot mutate a resolved record in place.

"""Canonical clinical entities and raw per-system source records."""
from __future__ import annotations

from dataclasses import dataclass, field
from datetime import date
from enum import Enum


class SourceSystem(str, Enum):
    CTMS = "ctms"
    EDC = "edc"
    IRB = "irb"


@dataclass(frozen=True)
class SourceRecord:
    """One raw field value as it arrived from a source system, pre-normalization."""

    system: SourceSystem
    entity_key: str   # the source system's own identifier for this entity
    field_name: str   # canonical field name this value maps to
    raw_value: str
    captured_at: date


@dataclass(frozen=True)
class CanonicalSite:
    """One site, keyed by protocol number and a single canonical site number."""

    canonical_site_id: str
    protocol_no: str
    site_number: str
    country: str  # ISO 3166-1 alpha-2
    activation_date: date | None
    source_ids: dict[SourceSystem, str] = field(default_factory=dict)


@dataclass(frozen=True)
class CanonicalInvestigator:
    """One principal investigator, keyed by a stable cross-system identifier."""

    canonical_pi_id: str
    family_name: str
    given_name: str
    npi: str | None
    source_ids: dict[SourceSystem, str] = field(default_factory=dict)

2. Map each source system’s raw field names onto canonical names #

The mapping is data, not code, so adding a fourth source system or renaming a field a vendor changed is a table edit rather than a new branch of conditional logic.

"""Declarative mapping from each source system's raw field name to the canonical field."""
from __future__ import annotations

FIELD_MAP: dict[SourceSystem, dict[str, str]] = {
    SourceSystem.CTMS: {
        "site_no": "site_number",
        "ctry_cd": "country",
        "activation_dt": "activation_date",
    },
    SourceSystem.EDC: {
        "SiteNumber": "site_number",
        "Country": "country",
        "SiteActiveDate": "activation_date",
    },
    SourceSystem.IRB: {
        "site_identifier": "site_number",
        "site_country": "country",
        "approval_effective_date": "activation_date",
    },
}


def canonical_field_name(system: SourceSystem, raw_field: str) -> str | None:
    """Return the canonical field name for a raw field, or None if unmapped."""
    return FIELD_MAP.get(system, {}).get(raw_field)

3. Coerce dates and country values strictly #

Both coercion functions refuse to guess. A date that is not already ISO 8601 must be converted by a system-specific adapter before it reaches this function, not inferred here — that keeps the ambiguity resolution explicit and reviewable rather than buried in a permissive parser.

"""Strict coercion of raw date and country values onto canonical types."""
from __future__ import annotations

from datetime import date, datetime

import pycountry

_COUNTRY_ALIASES = {
    "USA": "US",
    "UNITED STATES": "US",
    "UK": "GB",
}


def to_iso_date(raw_value: str) -> date:
    """Parse a source date string strictly as ISO 8601 (YYYY-MM-DD).

    Raises ValueError on anything else rather than guessing an ambiguous
    format like 03/04/2026; a system-specific adapter must convert to
    ISO 8601 upstream of this call.
    """
    return datetime.strptime(raw_value, "%Y-%m-%d").date()


def to_iso_country(raw_value: str) -> str:
    """Return the ISO 3166-1 alpha-2 code for a raw country value."""
    normalized = raw_value.strip().upper()
    if normalized in _COUNTRY_ALIASES:
        return _COUNTRY_ALIASES[normalized]
    if len(normalized) == 2:
        match = pycountry.countries.get(alpha_2=normalized)
        if match:
            return match.alpha_2
    match = pycountry.countries.get(name=raw_value.strip())
    if not match:
        raise ValueError(f"Unrecognized country value: {raw_value!r}")
    return match.alpha_2

4. Resolve identity with a fixed precedence rule #

When two source systems disagree on the same field for the same entity, the winner is decided by a fixed, documented order of systems — not by whichever record happened to arrive last.

"""Deterministic identity resolution across CTMS, EDC, and IRB site records."""
from __future__ import annotations

from collections import defaultdict

# System-of-record precedence when two sources disagree on the same field.
_PRECEDENCE = (SourceSystem.CTMS, SourceSystem.EDC, SourceSystem.IRB)


def resolve_site(protocol_no: str, records: list[SourceRecord]) -> CanonicalSite:
    """Merge per-system records for one site into a canonical record.

    The matching key is (protocol_no, mapped site_number); records that
    fail to converge on the same site_number after field mapping are not
    merged here — they surface as a conflict for the caller to route to
    manual review rather than silently picking a winner.
    """
    by_field: dict[str, dict[SourceSystem, str]] = defaultdict(dict)
    source_ids: dict[SourceSystem, str] = {}
    for record in records:
        by_field[record.field_name][record.system] = record.raw_value
        source_ids[record.system] = record.entity_key

    def pick(field_name: str) -> str | None:
        values = by_field.get(field_name, {})
        for system in _PRECEDENCE:
            if system in values:
                return values[system]
        return None

    site_number = pick("site_number")
    country_raw = pick("country")
    activation_raw = pick("activation_date")
    if site_number is None:
        raise ValueError(f"No source supplied a site_number for protocol {protocol_no!r}")

    return CanonicalSite(
        canonical_site_id=f"{protocol_no}:{site_number}",
        protocol_no=protocol_no,
        site_number=site_number,
        country=to_iso_country(country_raw) if country_raw else "",
        activation_date=to_iso_date(activation_raw) if activation_raw else None,
        source_ids=source_ids,
    )

5. Record provenance for every field that fed the canonical value #

A canonical record is only as trustworthy as its ability to answer “where did this specific value come from,” so the audit event carries the full set of contributing source records, not just the final result.

"""Emit an audit event capturing per-field provenance for a resolved site."""
from __future__ import annotations

import json
import logging

logger = logging.getLogger("ingestion.normalization")


def record_resolution(site: CanonicalSite, records: list[SourceRecord]) -> None:
    event = {
        "action": "site_normalized",
        "canonical_site_id": site.canonical_site_id,
        "protocol_no": site.protocol_no,
        "resolved": {
            "site_number": site.site_number,
            "country": site.country,
            "activation_date": site.activation_date.isoformat() if site.activation_date else None,
        },
        "sources": [
            {"system": r.system.value, "field": r.field_name, "raw_value": r.raw_value}
            for r in records
        ],
    }
    logger.info(json.dumps(event, sort_keys=True))

Validation and audit-trail integration #

Normalization runs after a document has already passed Schema Validation & Error Categorization for its own type, so the raw values entering this pipeline are already structurally sound within their own source system — the work here is reconciling sound values against each other, not re-checking their shape. The canonical field names and permissible values referenced throughout this guide are not defined locally; they are drawn from the shared regulatory data dictionary, so “site_number” means the same thing whether it was populated from a CTMS export or an IRB letter.

Every resolution — a site merged from three systems, an investigator matched by NPI, a date coerced from a source-specific adapter — writes a provenance record to the append-only audit log. That record is what lets a data manager or an inspector answer, months later, exactly which source system supplied the country code on a site that turns out to have been miscoded, and which precedence rule decided the outcome when two systems disagreed.

Error categorization and recovery #

Normalization failures split the same way classification failures do, by whether retrying the same inputs could ever change the outcome.

• Permanent — irreconcilable identity. Two source systems supply site numbers for the same protocol that do not resolve to the same canonical key under any known aliasing rule. This is not a coercion bug; it means either a genuine data entry error in one system or a site the mapping table has never seen. Route to a conflict queue for manual reconciliation, and do not auto-merge on a guess.
• Permanent — unparseable value after adapter. A date or country value that still fails strict coercion after passing through its source-specific adapter is a defect in that adapter or in the source data itself. Fail the specific field, keep the rest of the record’s fields that did coerce, and flag the failing field for correction rather than discarding the whole record.
• Permanent — precedence produces a value that fails cross-field checks. If the picked value for one field is inconsistent with another already-resolved field (for example, an activation date earlier than a recorded IRB approval date), that is a business-rule violation to surface explicitly, not something the precedence rule alone can fix by trying a different source.
• Transient — source system unavailable during resolution. A timeout fetching the CTMS or EDC record needed to complete a merge is retryable with bounded backoff; the mapping and precedence rules themselves do not change between attempts, only the availability of the input.

Compliance checklist #

Before a canonical record is considered final and available to downstream consumers, confirm each of the following holds:

• Every canonical field traces back to a specific source system and raw value in the audit log
• Dates are coerced with strict ISO 8601 parsing, never a permissive or fuzzy parser
• Country values resolve to ISO 3166-1 alpha-2 codes through a deterministic lookup, not free text
• A fixed, documented precedence order decides every conflict between source systems
• Records that cannot be reconciled are routed to manual review rather than merged on a guess
• Canonical field names match the shared regulatory data dictionary
• Fuzzy or similarity-based matching, if used at all, only assists a human reviewer and never decides a merge on its own

FAQ #

Why not just trust whichever system’s record arrived most recently? #

Recency has nothing to do with correctness, and a “most recent wins” rule produces a different answer depending on network timing and batch schedules rather than on which system is actually authoritative for a given field. A fixed precedence order — for example, treating the CTMS as the system of record for a site number — produces the same, explainable answer every time regardless of arrival order.

How does this differ from classifying a document? #

Classification decides what a single document is so it reaches the right validator; normalization reconciles the field values inside already-validated documents from different systems onto one canonical model. A document can be correctly classified and individually valid while its site number still needs reconciling against what the EDC and IRB have on file.

Can fuzzy name matching ever decide an investigator match automatically? #

No. A similarity score can rank candidate matches for a human reviewer to confirm quickly, but the final merge must come from a deterministic key — an NPI match, or an explicit confirmed alias — so the decision is reconstructable from a named rule rather than a similarity threshold that happened to clear on a given run.

What happens when a date fails strict ISO 8601 parsing? #

The specific field fails coercion and is flagged rather than guessed at with a permissive parser. The rest of the record’s fields that did coerce successfully still resolve normally; only the failing field is held for correction, so one bad date does not block an otherwise-reconcilable site record.

Related #

• Up one level: Automated Document Ingestion & Validation Workflows
• Normalizing Site and Investigator Identifiers Across Systems
• Regulatory Data Dictionary Construction
• Document Classification & Routing for Clinical Site Packets
• Schema Validation & Error Categorization