SC ReconciliationReconciliation Automation
  1. Home
  2. Exceptions
  3. Discrepancy Root-Cause Classification
  4. Building a Failure-Reason Taxonomy for Reconciliation

Building a Failure-Reason Taxonomy for Reconciliation

↑ Part of Discrepancy Root-Cause Classification.

A reason-code enumeration is only useful for as long as it stays closed. The moment one engineer adds "price mismatch" to a queue-resolution form while another adds "pricing discrepancy" to a retry handler, the codes stop being groupable, and every dashboard built on top of them silently under-counts. This page is narrower than picking a classifier architecture — it is the taxonomy design problem itself: a fixed, two-level category → code enumeration with naming rules that prevent duplication, a version number that lets codes evolve without breaking historical joins, and a deterministic mapper that converts years of accumulated legacy free text into that closed set instead of discarding it.

Operational Trigger Signals

Build or overhaul the taxonomy when the following conditions show up in the exception store, because each one is a measurable sign that the reason field has drifted into an ungoverned free-text graveyard rather than a chartable dimension:

  1. Distinct free-text count exceeds the real cause count by an order of magnitude. SELECT COUNT(DISTINCT reason_text) returning 60+ values against an underlying failure surface of 8–12 real causes means the field is capturing phrasing, not cause.
  2. The same underlying failure produces different strings across services. If the matching engine logs "fx rate not found" and the manual-review UI lets an analyst type "missing exchange rate" for the identical condition, no GROUP BY will ever unify them.
  3. A new reason string appears in the backlog every week with no review gate. Ad hoc strings added directly in application code, with no pull request against a shared enumeration, is the leading indicator that governance does not exist yet.
  4. Analysts cannot answer “what changed since last month” without reading rows. If a lead has to eyeball a sample of the backlog to guess whether vendor drift or FX gaps are trending up, the reason field is not serving its only job.
  5. A prior code list exists but has no version and no deprecation path. Codes get silently renamed or removed, and last quarter’s dashboards can no longer be reproduced against this quarter’s code set.

When two or more of these are true, the fix is not “add more codes” — it is designing the enumeration properly, once, with the naming and versioning discipline below.

Step-by-Step Implementation

Design the taxonomy as a two-level hierarchy: a small, fixed set of categories (the axis operations reports on — which team or system owns the fix) and, within each category, a set of codes (the specific, machine-testable condition that fired). Category is coarse and stable; codes are more numerous and allowed to grow, but only through a governed process.

  1. Fix the category axis first. Pick 4–6 categories that map to distinct remediation owners, not to arbitrary groupings — DATA_QUALITY, TOLERANCE, DEPENDENCY, TIMING, and a residual SYSTEM bucket for anything no rule yet claims. A category should never need to change once teams are aligned on it.
  2. Enumerate codes per category from real failure signatures, not hypothetical ones — pull the actual distinct conditions your matcher or classifier already distinguishes, such as the rule chain in Discrepancy Root-Cause Classification, and assign each a category.
  3. Apply naming rules before anything ships: UPPER_SNAKE_CASE, a condition or noun phrase (never a team name or an action like SEND_TO_PROCUREMENT), no category prefix stutter (VENDOR_DRIFT under DATA_QUALITY, not DATA_QUALITY_VENDOR_DRIFT), and a hard cap around 30 characters so the code stays readable in a dashboard legend.
  4. Version the enumeration as a whole, not per-code. Bump the minor version when adding a new active code; bump the major version only when a code is deprecated or its meaning changes, since that is the class of change that breaks a consumer keyed on the string.
  5. Never delete or repurpose a retired code. Mark it DEPRECATED, point replaced_by at its successor, and keep it queryable forever — historical exceptions keep the code that was active when they were raised.
  6. Build the legacy mapper last, once the enumeration is stable, so historical free text has a fixed target to be normalized against rather than a moving one.

The following module defines the taxonomy as typed data — an Enum for the two closed vocabularies plus a dataclass catalog entry — and a mapper that resolves legacy free text against it, logging every miss so the alias table itself becomes a living backlog of what still needs coverage:

PYTHON
import logging
import re
from dataclasses import dataclass
from enum import Enum
from typing import Optional

logger = logging.getLogger("recon.taxonomy")

TAXONOMY_VERSION = "2.1"  # bump minor for new codes, major for deprecations


class ReasonCategory(str, Enum):
    """The fixed, coarse axis operations reports on. Never repurpose a member."""
    DATA_QUALITY = "DATA_QUALITY"
    TOLERANCE = "TOLERANCE"
    DEPENDENCY = "DEPENDENCY"
    TIMING = "TIMING"
    SYSTEM = "SYSTEM"


class CodeStatus(str, Enum):
    ACTIVE = "ACTIVE"
    DEPRECATED = "DEPRECATED"


@dataclass(frozen=True)
class ReasonCodeDef:
    """One catalog entry: a specific, testable condition inside a category."""
    code: str
    category: ReasonCategory
    description: str
    added_in: str
    status: CodeStatus = CodeStatus.ACTIVE
    replaced_by: Optional[str] = None


# The closed catalog. Adding a row is a minor version bump; deprecating one
# is a major version bump. Nothing is ever deleted from this dict.
TAXONOMY: dict[str, ReasonCodeDef] = {
    "VENDOR_DRIFT": ReasonCodeDef(
        "VENDOR_DRIFT", ReasonCategory.DATA_QUALITY,
        "Vendor identifier, name, or remit-to address deviates from history.", "1.0"),
    "SKU_MISMATCH": ReasonCodeDef(
        "SKU_MISMATCH", ReasonCategory.DATA_QUALITY,
        "Line-item SKU absent from the canonical catalog crosswalk.", "1.0"),
    "DUPLICATE_RECORD": ReasonCodeDef(
        "DUPLICATE_RECORD", ReasonCategory.DATA_QUALITY,
        "Incoming record matches an already-settled record on natural key.", "1.2"),
    "PRICE_TOLERANCE_EXCEEDED": ReasonCodeDef(
        "PRICE_TOLERANCE_EXCEEDED", ReasonCategory.TOLERANCE,
        "Unit price delta exceeds the configured tolerance band.", "1.0"),
    "QTY_VARIANCE": ReasonCodeDef(
        "QTY_VARIANCE", ReasonCategory.TOLERANCE,
        "Quantity delta exceeds the configured tolerance band.", "1.0"),
    "FX_RATE_MISSING": ReasonCodeDef(
        "FX_RATE_MISSING", ReasonCategory.DEPENDENCY,
        "No validated exchange rate for the currency pair at the effective date.", "1.0"),
    "MISSING_COUNTERPART": ReasonCodeDef(
        "MISSING_COUNTERPART", ReasonCategory.DEPENDENCY,
        "No matching document found in the canonical ledger within the lookback window.", "1.0"),
    "LATE_ARRIVAL": ReasonCodeDef(
        "LATE_ARRIVAL", ReasonCategory.TIMING,
        "Record arrived after its watermark closed and the batch already committed.", "1.1"),
    "OUT_OF_SEQUENCE": ReasonCodeDef(
        "OUT_OF_SEQUENCE", ReasonCategory.TIMING,
        "Record arrived before the counterpart it depends on was ingested.", "2.0"),
    "AMBIGUOUS_CAUSE": ReasonCodeDef(
        "AMBIGUOUS_CAUSE", ReasonCategory.SYSTEM,
        "Deprecated: two or more rules matched with no precedence to break the tie.",
        "1.1", status=CodeStatus.DEPRECATED, replaced_by="UNCLASSIFIED"),
    "UNCLASSIFIED": ReasonCodeDef(
        "UNCLASSIFIED", ReasonCategory.SYSTEM,
        "No rule or alias matched; residual bucket routed to manual labeling.", "1.0"),
}

# Legacy free-text -> code. Keys are pre-normalized (lowercase, alnum + spaces only).
LEGACY_ALIAS_MAP: dict[str, str] = {
    "price off again": "PRICE_TOLERANCE_EXCEEDED",
    "pricing discrepancy": "PRICE_TOLERANCE_EXCEEDED",
    "unit cost mismatch": "PRICE_TOLERANCE_EXCEEDED",
    "qty mismatch": "QTY_VARIANCE",
    "quantity does not match": "QTY_VARIANCE",
    "vendor name changed": "VENDOR_DRIFT",
    "remit to changed": "VENDOR_DRIFT",
    "sku not in catalog": "SKU_MISMATCH",
    "unknown part number": "SKU_MISMATCH",
    "rate not found": "FX_RATE_MISSING",
    "missing exchange rate": "FX_RATE_MISSING",
    "no po found": "MISSING_COUNTERPART",
    "cannot locate original po": "MISSING_COUNTERPART",
    "received late": "LATE_ARRIVAL",
    "shipment arrived after close": "LATE_ARRIVAL",
    "possible duplicate invoice": "DUPLICATE_RECORD",
}


def _normalize(raw: str) -> str:
    """Lower-case, collapse whitespace, drop punctuation for alias lookup."""
    cleaned = re.sub(r"[^a-z0-9 ]", "", raw.strip().lower())
    return re.sub(r"\s+", " ", cleaned)


def map_legacy_reason(raw_text: str, exception_id: str) -> ReasonCodeDef:
    """Resolve a legacy free-text reason to a catalog entry, logging misses.

    Unmapped strings fall back to UNCLASSIFIED rather than raising, so a
    backfill job never halts on one bad row -- but every miss is logged so
    the alias table can absorb it in the next taxonomy revision.
    """
    key = _normalize(raw_text)
    code = LEGACY_ALIAS_MAP.get(key)
    if code is None:
        logger.warning(
            "unmapped legacy reason exception=%s normalized=%r taxonomy_version=%s",
            exception_id, key, TAXONOMY_VERSION,
        )
        return TAXONOMY["UNCLASSIFIED"]

    entry = TAXONOMY[code]
    if entry.status is CodeStatus.DEPRECATED:
        logger.info(
            "legacy reason mapped to deprecated code exception=%s code=%s replaced_by=%s",
            exception_id, entry.code, entry.replaced_by,
        )
        entry = TAXONOMY[entry.replaced_by] if entry.replaced_by else entry

    logger.info("mapped legacy reason exception=%s code=%s", exception_id, entry.code)
    return entry

Run map_legacy_reason as a one-time backfill over historical free-text rows, then keep the logger.warning line wired to the same alerting path used for the classifier’s UNCLASSIFIED rate — a spike in unmapped legacy strings during a backfill is the signal that the alias table, not the taxonomy, needs another pass.

Two-level failure-reason taxonomy: category to code A root taxonomy node, versioned, branches into five fixed categories: DATA QUALITY, TOLERANCE, DEPENDENCY, TIMING, and SYSTEM. Each category branches into its specific reason codes. DATA QUALITY holds VENDOR_DRIFT, SKU_MISMATCH, and DUPLICATE_RECORD. TOLERANCE holds PRICE_TOLERANCE_EXCEEDED and QTY_VARIANCE. DEPENDENCY holds FX_RATE_MISSING and MISSING_COUNTERPART. TIMING holds LATE_ARRIVAL and OUT_OF_SEQUENCE. SYSTEM holds UNCLASSIFIED, the residual bucket for anything no rule or alias matched. Reason taxonomy v2.1 DATA_QUALITY source data is wrong TOLERANCE band breached DEPENDENCY required input missing TIMING arrived off-window SYSTEM no rule matched VENDOR_DRIFT SKU_MISMATCH DUPLICATE_RECORD PRICE_TOLERANCE_EXCEEDED QTY_VARIANCE FX_RATE_MISSING MISSING_COUNTERPART LATE_ARRIVAL OUT_OF_SEQUENCE UNCLASSIFIED residual, not a dumping ground

Configuration Reference

The catalog itself is the configuration surface — every code below is versioned, categorized, and either active or deprecated-with-successor. Treat additions and deprecations as pull requests against this table, not as a code change buried inside a matcher:

Category Code Status Added in Description
DATA_QUALITY VENDOR_DRIFT ACTIVE 1.0 Vendor identifier, name, or remit-to deviates from history
DATA_QUALITY SKU_MISMATCH ACTIVE 1.0 Line-item SKU absent from the catalog crosswalk
DATA_QUALITY DUPLICATE_RECORD ACTIVE 1.2 Incoming record matches an already-settled natural key
TOLERANCE PRICE_TOLERANCE_EXCEEDED ACTIVE 1.0 Unit price delta exceeds the configured band
TOLERANCE QTY_VARIANCE ACTIVE 1.0 Quantity delta exceeds the configured band
DEPENDENCY FX_RATE_MISSING ACTIVE 1.0 No validated rate for the currency pair at the effective date
DEPENDENCY MISSING_COUNTERPART ACTIVE 1.0 No matching document within the lookback window
TIMING LATE_ARRIVAL ACTIVE 1.1 Record arrived after its watermark closed
TIMING OUT_OF_SEQUENCE ACTIVE 2.0 Record arrived before its dependency was ingested
SYSTEM AMBIGUOUS_CAUSE DEPRECATED → UNCLASSIFIED 1.1 Two rules matched with no precedence; folded into the residual code
SYSTEM UNCLASSIFIED ACTIVE 1.0 No rule or alias matched; residual, routed to manual labeling

Debugging & Recovery

Most taxonomy failures are governance failures, not code failures — the enumeration itself is simple; keeping it closed under pressure is the hard part:

  • A near-duplicate code request lands in review. Before approving a new code, check whether an existing one already covers the condition under a different name. AMBIGUOUS_CAUSE in the catalog above exists as a cautionary example — it was deprecated in favor of UNCLASSIFIED once the team recognized the two meant the same thing operationally.
  • The LEGACY_ALIAS_MAP misses a common phrasing. Log every miss (as map_legacy_reason does above) and review the miss log weekly during a backfill; a single normalized string appearing dozens of times is worth an alias entry, not a one-off manual fix.
  • A code needs to change meaning. It does not — deprecate it and add a new one with a replaced_by pointer. Rewriting a code’s meaning in place silently corrupts every historical dashboard that filtered on it before the change, and nothing signals that a join has gone stale.
  • UNCLASSIFIED volume climbs after a backfill. That is expected during the first pass over legacy data; treat it as a punch list, not a failure. Route the residual to the same manual-labeling queue the classifier already feeds, and feed the resolved labels back into LEGACY_ALIAS_MAP and TAXONOMY together, in one review, so the vocabulary and the alias table never drift apart.
  • Downstream consumers break after a taxonomy version bump. This is the reason major/minor semantics exist — a dashboard or alert built against Monitoring and Alerting for Reconciliation Pipelines that hard-codes a deprecated code needs to be updated in lockstep with a major bump, so pin TAXONOMY_VERSION in any alert query that filters by code.

FAQ

How many categories should the top level of the taxonomy have?

Keep it to 4–6. Each category should correspond to a distinct remediation owner or system boundary — data quality, tolerance, an external dependency, timing, and a residual bucket cover most reconciliation pipelines. More than six categories usually means two of them overlap in practice, which reintroduces the ambiguity a closed taxonomy is supposed to remove; fewer than four means codes with genuinely different owners are being grouped together, which defeats routing.

Why version the whole taxonomy instead of versioning each code independently?

Because consumers — dashboards, alert rules, the exception queue’s triage router — read the catalog as a set, not one code at a time. A single taxonomy version lets any historical exception be re-associated with the exact catalog snapshot that was active when it was classified, which is what makes trend charts across quarters trustworthy instead of silently comparing incompatible code sets.

Should the mapper ever guess at a code using fuzzy string matching instead of an exact alias lookup?

No. Reason-code assignment is an audit-relevant classification, and a fuzzy guess introduces exactly the kind of unreviewable probabilistic step this taxonomy exists to avoid — that tradeoff is explored for matching records, not classifying causes, in When to Use Fuzzy Matching Over Exact PO Matching. Keep the legacy mapper as an exact, logged lookup and grow the alias table deliberately from its miss log instead.