Duplicate Claim Detection

In vendor rebate and trade promotion reconciliation, the most expensive failure is not a claim that is wrong — it is a claim that is right, paid, and then paid again. A vendor re-submits the same quarterly rebate under a slightly different reference; a retailer takes a deduction off an invoice and later takes it a second time off a statement; an ETL replay re-ingests an already-settled batch after an outage. Each of these is a duplicate, and none of them looks like fraud — they look like ordinary traffic until the money leaves twice. Within the broader claim validation rule engine discipline, this page owns the single control that stands between two identical assertions and two identical payments: detecting that an incoming claim or deduction is the same economic event as one the system has already accepted, and doing so before an accrual is posted or a short-pay is honoured.

This is the canonical reference for that control. It specifies the claim-fingerprint record the detector emits, the deterministic canonical hash that catches exact duplicates in constant time, the blocking and near-duplicate scoring that catches the fuzzy ones a hash will miss, the decimal.Decimal discipline that keeps amount comparison exact, and the idempotent quarantine that collapses a duplicate group down to a single survivor without ever destroying evidence. It sits between the SKU-level deduplication of the sku mapping deduplication area — which resolves what was claimed — and the recovery routing of the settlement deduction financial close area, which acts on what should be clawed back. The audience is concrete: Python ETL developers who own the detector, trade finance analysts who defend the survivor, and vendor managers who answer for the quarantined twin.

Positioning Within the Reconciliation Architecture

Duplicate detection sits inside the validation engine but occupies a distinct seat from the other checks around it. Where temporal and volume checks ask “is this claim correct”, duplicate detection asks the orthogonal question “is this claim new” — and it must answer before the amount is committed, because a duplicate that reaches the ledger becomes a phantom liability that only surfaces at close. It runs after ingestion has produced clean, typed records and after SKU resolution has decided what was actually claimed, but before the payout math runs, so a confirmed twin never consumes an accrual line in the first place.

Two design commitments keep this seat clean. First, detection never mutates the claim it inspects — a suspected duplicate is tagged and grouped, never overwritten, so the forensic trail from both twins back to their source envelopes survives. Second, detection separates matching from disposition: the detector’s job is to assert “these two records are the same economic event” with a confidence and a reason; the decision to block, recover, or adjudicate belongs to the routing layer downstream. That separation is what lets the detector be reused across two superficially different problems — a vendor re-claiming a rebate and a retailer double-deducting a promotion — because both reduce to the same fingerprint comparison once normalized. The upstream contract comes from the agreement schema design registry, which supplies the promotion references a fingerprint is keyed on, and the whole flow is one node in the broader core architecture and promotion mapping reconciliation model. Confirmed duplicate deductions, once grouped, hand off to the recovery workflow in the settlement deduction financial close area rather than being resolved here.

Entity Topology and Schema Specification

Detection is only deterministic if the record it reasons over is stable and hashable. Each incoming claim or deduction resolves to exactly one claim fingerprint, and every fingerprint that is judged a duplicate is bound to a dup group — a set of fingerprints representing one economic event, of which precisely one is the designated survivor. The keys binding these must be assigned at detection time, never inferred later at query time. The table below specifies the minimum schema the detector emits; treat it as a versioned interface, where adding a nullable field is backward-compatible while renaming or retyping one is a breaking change that bumps the detector version.

The fingerprint_hash is what makes exact-duplicate detection a constant-time lookup rather than a scan: because it is a deterministic hash over the normalized identifying tuple, two records describing the same event collapse to the same key, and an idempotent insert becomes a no-op instead of a second accrual. The blocking_key is deliberately coarser — it exists to bound the near-duplicate search so scoring runs against a handful of candidates rather than the whole ledger. Monetary values are held as Decimal from the moment the fingerprint is built — never float — so amount comparison and tolerance arithmetic stay exact.

Conditional Logic and Rule Integration

Duplicate detection is a two-stage cascade, and the ordering is not incidental — the cheap deterministic test runs first, and only what survives it pays the cost of scoring.

Stage one: the canonical fingerprint. Every incoming claim is reduced to a canonical tuple — (vendor_id, promotion_ref, invoice_ref, period, amount) — where each element is normalized to a fixed representation: identifiers trimmed and upper-cased, period coerced to an ISO date, and amount rendered as a fixed-scale decimal string. That canonical tuple is joined with a stable separator and hashed with SHA-256 to yield fingerprint_hash. Because the hash is deterministic, two byte-different submissions of the same economic event — a re-send with different whitespace, a re-keyed invoice reference with a lower-case suffix — produce the same hash once normalized, and a single indexed lookup answers “have we seen this exact claim before” without comparing a single field pairwise. This catches the largest and most dangerous class of duplicate: the literal re-submission.

Stage two: blocking and near-duplicate scoring. A hash miss does not mean the claim is new — it means it is not identical. Duplicates that differ in a way that survives normalization — a rebate claimed at a marginally different amount, a deduction taken one day into the next period, an invoice reference that was mistyped rather than re-cased — will not collide. For these, the detector generates candidates by blocking_key (all accepted claims from the same vendor in the same period bucket), then scores each candidate pair on two axes: amount proximity within a Decimal tolerance, and temporal overlap between the two claim windows. The composite match_score in [0, 1] drives disposition against two thresholds — above the high threshold is a confirmed duplicate, below the low threshold is unique, and the band between them is ambiguous and routed for review. This scoring layer is deliberately conservative: it flags candidates, it does not silently merge them, because a false merge destroys a legitimate second claim as surely as a false miss pays a duplicate. The thresholds themselves are configuration, versioned alongside the eligibility rule framework so a change to sensitivity is auditable rather than a code edit.

{
  "duplicate_detector": {
    "canonical_fields": ["vendor_id", "promotion_ref", "invoice_ref", "period", "amount"],
    "hash_algorithm": "sha256",
    "blocking_key": "vendor_id + period_bucket",
    "amount_tolerance": "0.01",
    "temporal_overlap_days": 3,
    "score_thresholds": {"confirmed": "0.92", "review_floor": "0.60"}
  }
}

Financial Settlement Layer

Duplicate detection does not compute a payout, but it decides whether a payout happens at all, so its arithmetic must be exact. Amount comparison is where a naive implementation quietly fails: two claims of 1000.00 and 1000.001 are not equal, but under binary floating point the difference can vanish or spuriously appear, so every amount is compared as decimal.Decimal and every tolerance is itself a Decimal, never a float epsilon. A near-duplicate amount test asks whether the absolute difference between two amounts is within a configured tolerance — a genuine test of economic sameness — and both operands and the bound are decimals quantized to the same scale, so the comparison is reproducible to the cent across every environment.

The tolerance is not a license to treat close amounts as identical for payment; it is a signal that lowers confidence and raises a candidate for scoring. A confirmed exact-hash duplicate contributes zero to any accrual — the survivor already carries the full amount — while a scored near-duplicate is held out of settlement until its group is adjudicated, so no amount is ever committed twice and none is dropped without a decision. The idiom below is the only acceptable amount-proximity test.

from decimal import Decimal

def within_amount_tolerance(a: Decimal, b: Decimal, tolerance: Decimal = Decimal("0.01")) -> bool:
    """Compare two claim amounts for near-equality. Decimal only — never float epsilon."""
    if not isinstance(a, Decimal) or not isinstance(b, Decimal):
        raise TypeError("amounts must be decimal.Decimal, never float")
    scale = Decimal("0.01")
    delta = (a.quantize(scale) - b.quantize(scale)).copy_abs()
    return delta <= tolerance

ETL Implementation Patterns

Enforcement begins at the fingerprint boundary. Model the record with Pydantic v2 so a float amount, an unnormalized identifier, or an out-of-range score fails fast at detection time instead of surfacing as a duplicated accrual weeks later. The model below rejects the two most common silent corruptions — a monetary value that arrived as float, and a fingerprint whose canonical hash was not computed over the normalized tuple — and it computes the canonical hash itself so the hash can never drift from the fields it claims to summarize.

import hashlib
from decimal import Decimal
from pydantic import BaseModel, field_validator, model_validator

class ClaimFingerprint(BaseModel):
    claim_id: str
    vendor_id: str
    promotion_ref: str
    invoice_ref: str
    amount: Decimal
    currency: str
    period: str
    fingerprint_hash: str | None = None

    @field_validator("amount", mode="before")
    @classmethod
    def _amount_is_decimal(cls, v: object) -> Decimal:
        if isinstance(v, float):
            raise ValueError("amount must arrive as str or Decimal, never float")
        return Decimal(str(v))

    @field_validator("vendor_id", "promotion_ref", "invoice_ref")
    @classmethod
    def _normalize_id(cls, v: str) -> str:
        return v.strip().upper()

    @model_validator(mode="after")
    def _canonical_hash(self) -> "ClaimFingerprint":
        canonical = "|".join([
            self.vendor_id, self.promotion_ref, self.invoice_ref,
            self.period, f"{self.amount.quantize(Decimal('0.01'))}",
        ])
        computed = hashlib.sha256(canonical.encode("utf-8")).hexdigest()
        if self.fingerprint_hash and self.fingerprint_hash != computed:
            raise ValueError("fingerprint_hash does not match canonical tuple")
        object.__setattr__(self, "fingerprint_hash", computed)
        return self

Persistence uses idempotent upserts keyed on fingerprint_hash, so re-ingesting an unchanged claim overwrites cleanly instead of inserting a twin — the single cheapest place to stop a duplicate is a unique constraint on the canonical hash. Quarantine is likewise idempotent: when a group is formed, exactly one fingerprint is promoted to survivor and the rest are marked confirmed_dup with the shared dup_group_id; re-running detection on the same batch reproduces the identical group and survivor rather than creating a new one, because survivor selection is a deterministic rule (earliest accepted claim_id wins by default). Detector evolution follows semantic versioning: a change to normalization or thresholds ships behind a new detector_version so historical fingerprints remain reproducible under the exact detector that first computed them. The deduction-specific mechanics — matching a retailer short-pay against an already-recovered deduction — are worked end to end in detecting duplicate deduction claims.

Drift Detection and Validation

A detector is only trustworthy if its own output stays healthy over time, and the most important signal is the duplicate rate itself. A vendor whose duplicate rate suddenly climbs is either changing how it submits — a new EDI feed re-sending confirmations, a portal that double-posts — or is being tested for a control gap. Drift detection monitors the rate per vendor and per promotion against a rolling baseline, and a breach holds the vendor’s batch for review rather than silently absorbing the twins. Every held record keeps its match reason and score, so nothing is discarded and nothing is silently blocked.

Catching these before they compound is what preserves margin integrity: a duplicate-rate spike caught at detection time is a vendor-feed configuration conversation, while the same spike discovered after settlement is a restated accrual and a recovery campaign. When a signal breaches tolerance the detector triggers an automated exception workflow that routes the batch to operations with the offending fingerprints and their scores attached, so the review starts from evidence rather than suspicion.

Fallback and Dispute Routing

Ambiguity is inevitable when claims are near rather than identical, so the detector degrades into review rather than guessing. Disposition is categorized deterministically and routed by outcome, never dropped. A confirmed duplicate — an exact hash hit, or a near-duplicate scoring above the high threshold — is blocked from settlement and, when it is a retailer deduction, routed into the recovery workflow so the money can be clawed back; the survivor carries the single legitimate amount forward. An ambiguous claim — one whose match_score lands in the band between the review floor and the confirmed threshold — is routed to manual adjudication, where a trade finance analyst inspects both records and either confirms the group (choosing or accepting the survivor) or releases the claim as genuinely distinct. A unique claim — no hash hit and a score below the floor — passes cleanly to accrual.

Each disposition carries a deterministic reason code, the dup_group_id where one was formed, and the match_score that drove the decision, so a vendor challenging a blocked claim can be shown exactly which prior claim it collided with and why. This is where duplicate detection connects to the wider fallback routing logic discipline: an unresolvable case is never left in limbo, it is assigned an owner and an SLA. The adjudication and recovery mechanics themselves — the queue, the clawback posting, the default policy — are owned downstream in settlement, but the detector’s obligation is to name the correct lane, group the twins, and emit an audit entry recording why a claim was blocked, released, or held, so reconciliation stays continuous and every duplicate decision is traceable.

Security and Access Boundaries

Claim and deduction data carries negotiated pricing and party detail, so duplicate detection is governed as a financial control, not a utility script. A quarantined duplicate group is an assertion that money should not move, which makes the power to release one as sensitive as the power to approve a payment. Role-based access control (RBAC) tags travel with every fingerprint and group so authorization is enforced per action: ETL developers can deploy detector versions and replay batches but cannot alter a persisted fingerprint or hand-pick a survivor; vendor managers can review quarantined groups and supply context but cannot release a confirmed duplicate into settlement; trade finance analysts can adjudicate ambiguous groups, choose survivors, and export audit trails but cannot edit the source amounts a fingerprint was built from. Survivor selection and group release are the two most privileged operations and both require a second reviewer above a materiality threshold.

Immutable, hash-chained audit logs capture every fingerprint computation, match, quarantine, and adjudication decision — and because each fingerprint is anchored by its canonical hash, any post-hoc tampering with the identifying fields surfaces as a hash mismatch rather than passing unnoticed, aligning with SOX and internal-control requirements. Every release of a suspected duplicate records who released it, against which survivor, and on what evidence. Treating duplicate detection as a versioned, access-governed control is what turns an ordinary stream of resubmissions and double-deductions into defensible reconciliation: fewer phantom liabilities, faster recovery of unauthorized deductions, and a clean trail from two identical assertions to a single honest payment.

Frequently Asked Questions

Why hash a canonical tuple instead of comparing claim fields directly? Because direct comparison is quadratic and fragile. Reducing the identifying fields to one normalized tuple and hashing it with SHA-256 turns exact-duplicate detection into a single indexed lookup, and it absorbs cosmetic differences — whitespace, casing, re-keyed references — that would otherwise defeat a field-by-field equality test. Normalization happens before hashing so two byte-different submissions of the same economic event collapse to the same key.

If the fingerprint hash catches duplicates, why also run near-duplicate scoring? A hash only catches records that are identical after normalization. Duplicates that differ in a surviving way — a marginally different amount, a claim taken one day into the next period, a genuinely mistyped invoice reference — will not collide, so they are invisible to the hash. Near-duplicate scoring generates candidates by blocking key and scores them on amount proximity and temporal overlap to catch exactly the twins that are the same event but not the same bytes.

How does the detector avoid destroying a legitimate second claim? By separating matching from disposition and never silently merging. A near-duplicate is flagged with a score and a reason, not overwritten. Confirmed duplicates above the high threshold are quarantined into a group with one survivor while every twin is preserved, and ambiguous mid-band scores are routed to a human adjudicator who can release the claim as genuinely distinct. Nothing is dropped; every record keeps its group id and score for audit.

Why must amount comparison use decimal.Decimal rather than a float tolerance? Because floating-point comparison of money is not reproducible. Two amounts that differ by a fraction of a cent can compare equal or unequal depending on platform, so an amount-proximity test built on float epsilons is non-deterministic. Both operands and the tolerance are held as Decimal, quantized to the same scale, so the near-equality test is exact and reproducible to the cent everywhere it runs.

Related

• SKU Mapping & Deduplication — resolves what was claimed at the line level before fingerprints are compared at the claim level.
• Date-Window Alignment Checks — the temporal alignment that near-duplicate scoring reuses when measuring window overlap.
• Scoring & Confidence Models — the broader confidence framework the match score plugs into.
• Volume-Threshold Validation — the sibling check that validates claimed quantity against source sales.
• Detecting Duplicate Deduction Claims — the deduction-specific workflow for catching a retailer short-pay taken twice.

Up one level: Claim Validation & Rule Engine Configuration