Normalizing SKU Hierarchies Across Retailers

Trade promotion reconciliation collapses when trading partners operate on divergent item masters: a single vendor-manufactured unit is logged as a GTIN-14 at the distributor level, a retailer-specific identifier (DPCI, TCIN, or PLU) at point-of-sale, and a temporary promotional variant during scan-based trading events — and unless those are resolved into one canonical hierarchy before trade terms are applied, off-invoice deductions, rebate accruals, and promotional lift drift into unreconciled variance. This page documents the exact procedure for normalizing those hierarchies into a single, auditable SKU graph so that a case-level retailer deduction and an each-level vendor ERP record match instead of flagging as a discrepancy. It is the implementation-level companion to the field mapping strategies cluster, which frames the canonical schema this resolver writes into, inside the broader data ingestion normalization pipelines discipline.

Tiered SKU normalization: divergent identifiers resolved into one canonical node Three source systems supply divergent identifiers: a distributor feed carries case-level GTIN-14, a retailer point-of-sale carries each-level DPCI, TCIN, or PLU codes, and a promotional event carries a temporary promo_variant. All three funnel into a tiered resolver that runs three ordered stages — stage one is an exact GTIN match via a deterministic cross-reference join, stage two is unit-of-measure normalization using an integer pack matrix, and stage three is a probabilistic fallback scored on brand, pack size, and net weight. Records that score at or above the 0.90 confidence threshold emit one canonical SKU node holding the write-once canonical_item, a checksum-valid gtin14, and a pinned agreement_version. The canonical node links to the vendor ERP item as a write-once anchor, carries an additive promo lineage tag, and preserves the full pack hierarchy of each, inner, case, and pallet with integer multipliers. Records that score below the threshold are routed to a manual-review quarantine and held out of accrual. SKU NORMALIZATION · DIVERGENT IDENTIFIERS → ONE CANONICAL NODE Distributor feed GTIN-14 case level Retailer POS DPCI · TCIN · PLU each level Promo event temp promo_variant scan-based trading TIERED RESOLVER · STRICT PRECEDENCE 1 Exact GTIN match deterministic xref join · short-circuit 2 UOM normalization integer pack matrix · Decimal 3 Probabilistic fallback brand · pack · net weight Manual-review quarantine score < 0.90 · held out of accrual below threshold score ≥ 0.90 Canonical SKU node canonical_item · gtin14 ✓ check digit base_pack_level · units_per_parent agreement_version pinned (never “latest”) Vendor ERP item write-once anchor Promo lineage additive tag PRESERVED PACK HIERARCHY · INTEGER MULTIPLIERS each ×1 inner ×6 case ×24 pallet ×1344

Prerequisites

Before resolving retailer identifiers into a canonical hierarchy, confirm the following are in place:

  • Parsed, schema-tagged source records. Raw vendor files must already be flattened and validated by the CSV & EDI parsing workflows, with every incoming row carrying source_system_id, raw_sku, and unit_of_measure before it reaches the resolver. This page consumes parsed records, never raw X12 segments.
  • A loaded GTIN cross-reference. A pre-compiled mapping of standardized identifiers (UPC-A, EAN-13, GTIN-14) to the canonical vendor ERP item number, plus a per-vendor pack-size matrix for unit-of-measure conversion.
  • A pack-hierarchy contract. Each canonical item declares its packaging levels (each → inner → case → pallet) and the integer multipliers between them, version-pinned so a mid-cycle pack change does not silently reprice history.
  • Python packages: pydantic>=2.6 for the canonical model and validators, polars>=0.20 (or pandas) for vectorized joins, and the standard-library decimal module. Every monetary and rate field uses decimal.Decimal; never float.
  • Access role: read access to vendor and retailer master data and write access to the canonical SKU graph (typically the reconciliation_etl service role).

For authoritative guidance on standardizing product identifiers and packaging hierarchies across trading partners, refer to the GS1 GDSN data standards.

Step-by-step implementation

Step 1 — Model the canonical SKU node

Encode the canonical node as a Pydantic v2 model so an invalid GTIN checksum, a missing pack level, or a float multiplier is rejected at load time rather than discovered during accrual. The node captures resolved identity; the raw source identifiers are retained for audit but never used for arithmetic.

python
from decimal import Decimal
from enum import Enum
from pydantic import BaseModel, field_validator


class PackLevel(str, Enum):
    each = "each"
    inner = "inner"
    case = "case"
    pallet = "pallet"


class CanonicalSku(BaseModel):
    canonical_item: str            # vendor ERP item number, write-once
    gtin14: str                    # GS1 GTIN-14, check-digit valid
    base_pack_level: PackLevel     # the level this node represents
    units_per_parent: int          # integer multiplier to the next level up
    promo_variant_id: str | None = None
    agreement_version: int         # pinned, never "latest"

    @field_validator("gtin14")
    @classmethod
    def gtin_checksum(cls, v: str) -> str:
        if len(v) != 14 or not v.isdigit():
            raise ValueError("GTIN-14 must be 14 digits")
        digits = [int(c) for c in v]
        check = (10 - (sum(d * (3 if i % 2 == 0 else 1)
                           for i, d in enumerate(digits[:13])) % 10)) % 10
        if check != digits[13]:
            raise ValueError("invalid GTIN-14 check digit")
        return v

Validation check: load a fixture with a transposed-digit GTIN and assert CanonicalSku(**fixture) raises ValidationError. An identifier that fails its own check digit must never anchor a match.

Step 2 — Anchor matching to standardized identifiers first

Resolve in strict precedence: exact GTIN match before any retailer-assigned code. A deterministic primary join against the pre-compiled cross-reference is both faster and audit-defensible than fuzzy logic, so it must run first and short-circuit on success.

python
def resolve_primary(raw: dict, gtin_xref: dict[str, str]) -> str | None:
    # gtin_xref maps any standardized identifier -> canonical_item
    for field in ("gtin14", "ean13", "upca"):
        code = raw.get(field)
        if code and code in gtin_xref:
            return gtin_xref[code]
    return None   # fall through to UOM + probabilistic resolution

Validation check: feed a record whose gtin14 is present in the cross-reference and assert it resolves without touching the fallback path. Standardized identifiers must always win over retailer codes.

Step 3 — Normalize the unit of measure before comparison

The dominant failure is a case-level retailer deduction matched against an each-level ERP record. Convert every quantity to the canonical base level using the integer pack matrix so quantities are commensurable before any financial line is built.

python
from decimal import Decimal

PACK_MATRIX = {                       # canonical_item -> units per level
    "ERP-00471": {"each": 1, "inner": 6, "case": 24, "pallet": 1344},
}

def to_base_units(canonical_item: str, qty: Decimal, level: str) -> Decimal:
    multiplier = PACK_MATRIX[canonical_item][level]
    return (qty * Decimal(multiplier))   # exact; no float division

Validation check: convert 2 cases of ERP-00471 and assert the result is exactly Decimal("48") eaches. Use Decimal end to end — a float multiplier reintroduces the drift this step exists to remove.

Step 4 — Preserve promotional lineage

When a retailer introduces a temporary promotional SKU, the resolver must link it to the base item while tagging the temporary attributes, so accruals are not double-counted and a deduction dispute can be traced to the exact promotional window.

python
def attach_promo_lineage(node: CanonicalSku, raw: dict) -> CanonicalSku:
    if raw.get("is_promo"):
        node = node.model_copy(update={
            "promo_variant_id": raw["promo_variant_id"],
        })
    return node   # base canonical_item is preserved; variant is additive

Validation check: resolve a promotional record and assert canonical_item still points at the base ERP item while promo_variant_id is populated. Lineage must be additive, never a replacement of base identity.

Step 5 — Apply probabilistic fallback, then quarantine the rest

Records that survive without a GTIN match enter a bounded fuzzy pass on brand + pack_size + net_weight. Anything below the confidence threshold is not guessed — it is quarantined for manual review so a weak match never silently mis-attributes a rebate.

python
def resolve_fuzzy(raw: dict, candidates: list[dict],
                  threshold: Decimal = Decimal("0.90")) -> str | None:
    best, score = None, Decimal("0")
    for cand in candidates:
        s = score_candidate(raw, cand)        # returns Decimal in [0, 1]
        if s > score:
            best, score = cand["canonical_item"], s
    return best if score >= threshold else None   # else -> quarantine

Validation check: feed an ambiguous record whose best score is 0.84 and assert it returns None and is routed to the review queue, not auto-matched. Below threshold must mean quarantine, not a coin-flip.

Step 6 — Resolve at scale with vectorized joins

Drive the whole batch through a single vectorized join against the pre-compiled cross-reference rather than per-row Python loops. polars lazy evaluation (or pandas categorical dtypes) keeps millions of line items inside one pass while preserving referential integrity per vendor-retailer pair.

python
import polars as pl

def resolve_batch(records: pl.LazyFrame, xref: pl.LazyFrame) -> pl.LazyFrame:
    return (
        records
        .join(xref, on="gtin14", how="left")           # primary anchor
        .with_columns(
            pl.col("canonical_item").is_null().alias("needs_fallback")
        )
    )

Validation check: run the join on a fixture where 5 of 100 rows lack a GTIN and assert exactly 5 rows carry needs_fallback = true. The vectorized pass must isolate, not silently drop, the unresolved tail.

Common failure modes and fixes

  1. Unit-of-measure mismatch (UOM_MISMATCH). A retailer deducts at the case level while the vendor ERP records eaches, so a legitimate transaction flags as a discrepancy. Convert both sides to canonical base units with the integer pack matrix (Step 3) before comparison — never compare raw quantities across levels.
  2. Floating-point drift in attribution. Computing rebate amounts or pack conversions with float lets rounding error compound across millions of lines and breaks ledger ties. Keep every quantity and rate in decimal.Decimal, use integer pack multipliers, and quantize monetary results explicitly with ROUND_HALF_UP.
  3. Missing promotional variant (MISSING_PROMO_VARIANT). A scan-based promo SKU with no link to its base item double-counts during accrual. Attach lineage additively (Step 4) so the base canonical_item is preserved and the variant is a tag, then escalate orphaned variants to vendor managers before the accrual cycle closes.
  4. Invalid GTIN checksum (INVALID_GTIN_CHECKSUM). A transposed or truncated identifier matches the wrong canonical item. Validate the check digit at the model boundary (Step 1) so a malformed GTIN is rejected at ingest rather than anchoring a false match.
  5. Hierarchy drift (HIERARCHY_DRIFT). A retailer reorganizes its pack configuration mid-cycle and the resolver silently reprices history. Pin agreement_version and the pack matrix per record, classify the mismatch as HIERARCHY_DRIFT, and route it to master-data correction through fallback routing logic rather than auto-resolving it.

Operational checklist

Frequently asked questions

Why anchor to GTIN before retailer codes instead of the reverse? Standardized identifiers are stable across trading partners and carry a self-validating check digit, so an exact GTIN match is both deterministic and audit-defensible. Retailer-assigned codes (DPCI, TCIN, PLU) are namespaced per retailer and can collide or be reused, so they are only a fallback once the standardized anchor misses.

What happens to a record the resolver cannot confidently match? It is quarantined, not guessed. Any record whose best fuzzy score falls below the confidence threshold routes to a manual-review queue and is held out of accrual until a vendor manager corrects master data — a weak match that mis-attributes a rebate is more expensive than a held line.

How are temporary promotional SKUs kept from double-counting? Lineage is additive: the promo variant links back to the base canonical_item and carries its own promo_variant_id, effective dates, and rebate tier as tags. Accruals roll up against the base item while the variant attributes preserve traceability to the exact promotional window.

Where do resolved SKUs go next? The canonical graph feeds the rest of field mapping strategies, and resolved line items synchronize to the general ledger through POS & ERP sync patterns on the retailer’s fiscal cutoff, carrying the canonical item number so each entry traces back to its originating sales line.

Back to