Deduplicating Overlapping Vendor Claims

A vendor can claim the same sell-through more than once without ever submitting a byte-identical line: a rebate is claimed on a case-level GTIN in one file and on that case’s each-level children in another, or the same units surface under two internal SKU codes that both resolve to one canonical product. Left unchecked, these overlaps inflate accrued liability, corrupt volume-threshold math, and surface as unexplained variance at close. This page gives the exact procedure for collapsing them: normalize every claim SKU onto the canonical hierarchy, fingerprint each claim at its true node, detect parent/child and exact overlap, and reconcile the group down to one decimal-exact survivor while quarantining anything genuinely ambiguous. It is the implementation-level companion to the SKU Mapping & Deduplication topic area, which owns the broader crosswalk and identity-resolution model inside claim validation rule engine configuration. The related task of catching duplicate retailer deduction claims lives in duplicate claim detection; here the concern is strictly overlap across the product hierarchy.

Deduplicating vendor claims that overlap across the SKU hierarchy Three raw vendor claims enter the pipeline: one on a case-level GTIN for 100 cases, and two on that case's each-level child SKUs for 600 eaches each. A normalize stage maps every claim SKU onto canonical hierarchy nodes and converts units to a common each-level base, expanding the 100-case claim into 1,200 eaches. A fingerprint stage keys each claim on vendor, promotion, period, canonical node, and normalized quantity. An overlap detector finds two conditions: exact duplicates sharing a fingerprint, and parent/child overlap where a case claim and its expanded each claims cover the same sell-through. Resolvable groups collapse to one survivor whose amount is reconciled with decimal precision and which retains an audit link to every absorbed claim. Groups that cannot be reconciled deterministically branch to a quarantine queue for manual review rather than being merged or dropped. Raw overlapping claims CLM-1 · case GTIN 108847 · 100 cases CLM-2 · each SKU-A · 600 eaches CLM-3 · each SKU-B · 600 eaches Normalize to canonical nodes case → 12 eaches · CLM-1 becomes 1,200 eaches Build canonical fingerprint vendor · promotion · period · node · base qty ! Detect overlap exact dupe · parent/child double-claim ambiguous Quarantine queue manual review · nothing merged reconcilable 1 Resolve to one survivor Decimal reconcile · retains audit link Survivor: one claim at the settled node, 1,200 eaches absorbs CLM-1, CLM-2, CLM-3 · superseded rows retained with a link to the survivor

Prerequisites

Before running dedup over a claim batch, confirm the following are in place:

  • A canonical SKU hierarchy. Every claim SKU must resolve to a single canonical node in a graph that records parent/child roll-up relationships and the pack factor between levels — for example, a case that contains twelve eaches. This graph is the output of normalizing SKU hierarchies across retailers; dedup consumes it and never invents mappings on its own.
  • Normalized claim facts. Claims arrive from the data ingestion normalization pipelines already reduced to typed records with a resolved vendor, promotion, fiscal period, quantity, unit-of-measure, and a monetary amount. A “same units under two SKUs” overlap is only detectable once both codes point at one canonical node.
  • A defined base unit of measure. Overlap comparison happens at one level — typically the each. The pack factor between each hierarchy level must be known and decimal-exact so a case claim expands to the same base quantity a bundle of each claims represents.
  • Python 3.11+ with pydantic>=2.6. All validation below uses Pydantic v2 models. Every monetary field uses decimal.Decimal; never float.
  • Access roles. The dedup job needs read access to the canonical hierarchy and the claim store, and write access to the survivor table and the quarantine queue. Releasing a quarantined overlap or overriding a survivor requires a separate financial-review role — the ETL service role runs the collapse but cannot adjudicate ambiguity.

Step-by-Step Implementation

Step 1 — Normalize claim SKUs to canonical hierarchy nodes

Resolve every claim’s SKU to its canonical node and convert its quantity to the base unit of measure. Until this is done, a case claim and its each claims look unrelated because they carry different codes and different units. Expansion by the pack factor is what makes the two comparable.

python
from decimal import Decimal
from pydantic import BaseModel, field_validator

class CanonicalClaim(BaseModel):
    claim_id: str
    vendor_id: str
    promotion_id: str
    period: str                 # ISO 'YYYY-MM'
    node_id: str                # canonical hierarchy node
    ancestor_ids: tuple[str, ...]  # node_id's parents, root-last
    base_qty: Decimal           # quantity in base UoM (eaches)
    amount: Decimal             # claimed rebate, decimal-exact

    @field_validator("base_qty", "amount", mode="before")
    @classmethod
    def _no_floats(cls, v):
        if isinstance(v, float):
            raise ValueError("quantities and amounts must be str/Decimal, not float")
        return v

def to_canonical(raw: dict, graph) -> CanonicalClaim:
    node = graph.resolve(raw["sku"])          # raises on unmapped SKU
    base = Decimal(raw["qty"]) * graph.pack_factor_to_base(node)
    return CanonicalClaim(
        claim_id=raw["claim_id"], vendor_id=raw["vendor_id"],
        promotion_id=raw["promotion_id"], period=raw["period"],
        node_id=node.id, ancestor_ids=graph.ancestors(node.id),
        base_qty=base, amount=Decimal(raw["amount"]),
    )

Validation check: feed the 100-case CLM-1 and assert base_qty == Decimal("1200") (100 × 12), and assert to_canonical raises when raw["sku"] is not in the graph. An unmapped SKU must fail loudly here, not slip through and escape dedup.

Step 2 — Build a canonical claim fingerprint

A fingerprint is the deterministic key that makes exact duplicates collide. Compute it over the fields that define “the same claim” — vendor, promotion, period, and canonical node — plus the normalized quantity, and hash them in a fixed order so two runs produce byte-identical keys.

python
import hashlib

def fingerprint(c: CanonicalClaim) -> str:
    parts = [c.vendor_id, c.promotion_id, c.period, c.node_id, str(c.base_qty)]
    blob = "\x1f".join(parts)          # unit-separator delimits fields
    return hashlib.sha256(blob.encode()).hexdigest()

Validation check: fingerprint two claims that share every keyed field and assert the digests are equal; change only base_qty and assert they differ. The str(base_qty) term must come from a normalized Decimal (Decimal("600"), not Decimal("600.00")) so trailing-zero formatting never splits a true duplicate — normalize with base_qty.normalize() if source formatting varies.

Step 3 — Detect hierarchy overlap and exact dupes

Group claims by (vendor_id, promotion_id, period) and, within each group, apply two tests. Exact duplicates share a fingerprint. Parent/child overlap is subtler: a claim on a node and a claim on one of that node’s ancestors both cover the same sell-through, so their base quantities must be checked against the roll-up. The volume threshold validation stage depends on this because an undetected double-claim inflates the qualifying volume.

python
from itertools import combinations

def find_overlaps(claims: list[CanonicalClaim]) -> dict:
    exact, hierarchy = [], []
    seen: dict[str, str] = {}
    for c in claims:
        fp = fingerprint(c)
        if fp in seen:
            exact.append((seen[fp], c.claim_id))   # same fingerprint
        else:
            seen[fp] = c.claim_id
    for a, b in combinations(claims, 2):
        # one node is an ancestor of the other -> same physical units
        if a.node_id in b.ancestor_ids or b.node_id in a.ancestor_ids:
            hierarchy.append((a.claim_id, b.claim_id))
    return {"exact": exact, "hierarchy": hierarchy}

Validation check: with CLM-1 on the case node and CLM-2/CLM-3 on its each children, assert both (CLM-1, CLM-2) and (CLM-1, CLM-3) appear in hierarchy. Then assert that two claims on sibling nodes with no ancestor relationship are not flagged — siblings are distinct products, not an overlap.

Step 4 — Resolve each group to one survivor with decimal reconciliation

Collapse a detected overlap group to a single survivor. The survivor is chosen deterministically — the most specific node (deepest in the hierarchy) that fully covers the claimed sell-through, or, for exact dupes, the earliest-submitted claim. Reconcile the amount by expanding every member to the base node and summing with Decimal, then assert the group’s economic total is preserved. The confirmed settlement amount belongs to the settlement, deduction & financial close stage; dedup only guarantees the survivor carries the reconciled, non-double-counted quantity and amount.

python
from decimal import ROUND_HALF_UP

def resolve_survivor(group: list[CanonicalClaim]) -> dict:
    # deepest node wins for hierarchy overlap; ties -> earliest claim_id
    survivor = min(group, key=lambda c: (-len(c.ancestor_ids), c.claim_id))
    # expected units = the single covered sell-through, not the sum of members
    expected_qty = max(c.base_qty for c in group)
    per_unit = (survivor.amount / survivor.base_qty)
    amount = (per_unit * expected_qty).quantize(Decimal("0.01"), ROUND_HALF_UP)
    return {
        "survivor_id": survivor.claim_id,
        "node_id": survivor.node_id,
        "base_qty": expected_qty,
        "reconciled_amount": amount,
        "absorbed": [c.claim_id for c in group if c.claim_id != survivor.claim_id],
    }

Validation check: for the case/each example where each claim represents 1,200 base eaches, assert expected_qty == Decimal("1200") (one sell-through, not 1,200 + 600 + 600) and that reconciled_amount equals the per-unit rate times 1,200 quantized to the cent. Summing member quantities here is the classic double-count bug — the group covers one physical movement.

Step 5 — Quarantine ambiguous overlaps instead of merging them

Not every overlap is safely resolvable. When members disagree on per-unit rate beyond a tolerance, when the pack factor is unknown, or when two claims overlap partially (a case claim plus an each claim for a quantity the case cannot contain), collapsing them would fabricate a number nobody can defend. Route these to a quarantine queue with the full group preserved, exactly as unresolved gaps fall through to fallback routing logic rather than auto-settling.

python
RATE_TOLERANCE = Decimal("0.005")   # 0.5% per-unit rate spread

def reconcile_or_quarantine(group: list[CanonicalClaim], registry) -> dict:
    rates = [(c.amount / c.base_qty) for c in group if c.base_qty > 0]
    spread = max(rates) - min(rates)
    child_qty = sum(c.base_qty for c in group[1:])
    if spread > RATE_TOLERANCE or child_qty > group[0].base_qty:
        record = {"status": "quarantine", "reason": "rate_spread_or_qty_excess",
                  "members": [c.claim_id for c in group]}
        registry.write_quarantine(record)      # nothing merged, nothing dropped
        return record
    result = resolve_survivor(group)
    result["status"] = "resolved"
    registry.write_survivor(result)
    return result

Validation check: build a group whose per-unit rates differ by more than RATE_TOLERANCE and assert status == "quarantine"; build a group whose child quantities exceed the parent’s capacity and assert it also quarantines. Then assert a clean group returns status == "resolved" and that no quarantined group ever writes a survivor row.

Common Failure Modes and Fixes

  1. Parent/child double-claim slips through. Comparing only fingerprints catches exact dupes but misses a case claim overlapping its each children, because the nodes and quantities differ. Fix: run the ancestor test in Step 3 in addition to the fingerprint test; a claim on any node in another claim’s ancestor_ids is an overlap regardless of quantity.
  2. Float amount comparison. Computing per-unit rates or rate spread with float lets rounding error push a genuine duplicate past the tolerance, or merge two claims that should have quarantined. Fix: keep every amount and quantity in decimal.Decimal from ingestion and quantize survivor amounts explicitly with ROUND_HALF_UP.
  3. Unnormalized SKU escapes dedup. A claim whose SKU never resolved to a canonical node carries a raw code, so its fingerprint can never collide with the canonical duplicate. Fix: make to_canonical in Step 1 raise on unmapped SKUs and route those to the crosswalk backlog; an un-resolved claim is a mapping gap, not a unique claim.
  4. Over-aggressive merge. Treating two claims on sibling nodes, or two legitimately separate shipments in the same period, as a single overlap destroys real liability. Fix: require an ancestor relationship (not mere shared vendor/period) for hierarchy overlap, and reconcile to the single covered sell-through only when the roll-up quantities agree; otherwise quarantine.
  5. Lost audit link on the absorbed claims. Deleting the superseded rows once a survivor is written makes the dedup irreversible and unauditable. Fix: never delete — mark absorbed claims superseded with a pointer to the survivor_id, so a reviewer can reconstruct which raw claims collapsed into the settled one.

Operational Checklist

Frequently Asked Questions

How is a parent/child overlap different from an exact duplicate? An exact duplicate is two claims that key to the identical fingerprint — same vendor, promotion, period, canonical node, and normalized quantity. A parent/child overlap is two claims on different hierarchy levels — a case GTIN and its each children — that nonetheless cover the same physical sell-through once the case is expanded by its pack factor. Fingerprints alone catch the first; the ancestor test in Step 3 is required for the second.

Why expand everything to a base unit of measure before comparing? Because a case claim and an each claim describe the same movement in different units. Without expanding the case by its pack factor, a case-level claim for 100 cases and an each-level claim for 1,200 eaches look like unrelated quantities, so the double-count goes undetected. Normalizing both to eaches with a decimal-exact factor is what makes the overlap visible and the reconciliation defensible.

When should an overlap be quarantined instead of merged? Quarantine whenever a deterministic collapse would fabricate a number: when member per-unit rates disagree beyond tolerance, when the pack factor is unknown, or when child quantities exceed what the parent node can contain (a partial or contradictory overlap). Merging in those cases guesses a survivor amount nobody can defend in an audit, so the group is held for financial review with every member preserved.

How do I keep the deduplication auditable and reversible? Never delete absorbed claims. Mark each superseded claim with a pointer to the surviving claim’s identifier and record the reconciled quantity and amount on the survivor. A reviewer can then reconstruct exactly which raw claims collapsed, confirm the sell-through was counted once, and reverse the merge if a mapping later proves wrong.

Up one level: SKU Mapping & Deduplication

Back to