Field Mapping Strategies for Vendor Rebate Reconciliation

Once a vendor file has been parsed into typed facts, every promotion event, deduction line, and accrual still wears the source system’s vocabulary: one retailer calls it promo_cd, another DealID, an ERP export labels the same money disc_amt and a POS dump calls it scan_allow. Field mapping is the layer that translates that heterogeneity into one canonical, auditable schema the rest of the pipeline can reason over without re-reading raw bytes. Within the broader data ingestion normalization pipelines discipline, this page owns one specific sub-problem: how a parsed-but-unaligned record becomes a deterministic canonical fact — the right field names, the right types, the right semantics — before any rate is applied or any claim is validated. Get mapping wrong and the failures are financial and silent: a scan_allowance mapped to the wrong rebate bucket double-counts a deduction, a category-level UPC that never resolves to a financial line leaks an entire promotion, a timezone-naive effective_date shifts a claim into the wrong reconciliation period.

This is the canonical reference for that mapping stage. It describes the mapping registry and canonical schema the layer emits, how conditional and hierarchical rules encode vendor-specific business logic, how monetary and temporal fields are coerced without precision loss, the Pydantic-enforced ETL patterns that make mapping safe to replay, and the drift, dispute, and access controls that let trade finance defend every mapped value. The audience is concrete: Python ETL developers who own the mapping registry, trade finance analysts who reconcile the resulting accrual, and vendor managers who answer for a contested claim. Mapping consumes the output of CSV & EDI parsing workflows and hands a clean canonical record to the sync and validation layers downstream.

Positioning Within the Reconciliation Architecture

The mapping layer sits between parsing and settlement, downstream of the byte-level work and upstream of everything financial. It receives structure without interpretation — the parser deliberately emits a vendor’s PROMO_CD as an opaque string rather than deciding what it means — and its single responsibility is semantic alignment: deciding that this vendor’s PROMO_CD is the canonical promotion_id, that this column of money is off_invoice_amount and not scan_allowance, and that this date is the promotion’s effective_date in UTC. Everything after this stage — the rating engine that applies tiers, the claim validation rule engine that scores a deduction, the general-ledger reconciliation — treats the canonical fact as a stable contract. If mapping is wrong, every downstream number inherits the error, and because the canonical record looks perfectly well-formed, the failure surfaces weeks later as an unexplained variance rather than a parse exception.

Two design commitments keep this safe. First, mapping is declarative and externalized: rules live in a version-controlled registry, never hard-coded into ingestion logic, so the same parsed payload reprocessed under a new mapping version produces a deterministic, diffable result. Second, mapping is non-destructive: the source field name and value are preserved alongside the canonical projection, so any mapped figure can be traced back to the exact raw label it came from. Clean separation here is what lets the POS & ERP sync patterns layer join and deduplicate mapped facts against the ledger, and what lets async batch processing replay an out-of-order vendor batch without re-deriving any semantics. Where parsing aligns a vendor’s product codes structurally, the deeper semantic problem of resolving case packs, inner units, and individual UPCs to one financial line is owned by normalizing SKU hierarchies across retailers.

Entity Topology and Schema Specification

The mapping layer is governed by two artifacts: the canonical schema, which defines the target fields every record must resolve to, and the mapping registry, which defines how each source system’s vocabulary projects onto that target. The canonical schema is the contract the rest of the reconciliation pipeline depends on; the registry is the per-vendor, per-format translation that satisfies it.

The canonical promotion fact standardizes the dimensions reconciliation actually joins and rates on:

Every mapping rule in the registry carries its own metadata so the projection is reproducible and auditable: a stable rule_id, the source_system and source_format it applies to, the version and effective_from date that scope it, and a confidence flag distinguishing a deterministic one-to-one mapping from a heuristic match that should be reviewed. The registry is stored as configuration-as-code — versioned YAML or JSON dictionaries deployed through CI — and never edited live, so a mapping change is a reviewable commit with an author, a timestamp, and a list of the reconciliation periods it affects.

Conditional Logic and Rule Integration

Direct one-to-one mappings — disc_amt → discount_amount — are the easy majority, but trade finance rarely stays that clean. The registry supports three rule classes, evaluated in precedence order so the most specific rule wins.

Direct mappings rename and coerce a single source field to a single canonical field. Conditional mappings encode vendor- or channel-specific business logic: a value’s canonical destination depends on a predicate over other fields in the same record. Hierarchical mappings resolve nested or multi-level source structures — EDI loop groups, JSON product trees, category-rollup exports — into the flat canonical fact, traversing levels until a financial line resolves.

Conditional rules are where eligibility scoping and channel logic enter the mapping layer. A scan allowance from one retailer is a scan_back; an off-invoice line from the same vendor under a different deal is an off_invoice deduction, and the two settle differently downstream. Encoding that as data rather than code keeps it reviewable:

{
  "rule_id": "map.allowance.walmart.scan_back",
  "source_system": "WALMART_POS",
  "source_format": "csv.v3",
  "version": "2026.02",
  "effective_from": "2026-02-01",
  "target_field": "discount_amount",
  "when": {
    "all": [
      {"field": "deal_type", "op": "eq", "value": "SCAN"},
      {"field": "channel", "op": "in", "value": ["RETAIL", "CLUB"]}
    ]
  },
  "from_field": "scan_allow",
  "set": {"allowance_type": "scan_back"},
  "confidence": "deterministic"
}

The temporal predicates here are deliberately aligned with the same window semantics the eligibility rule framework uses downstream, so a date that maps inside a promotion window at this stage does not get rejected as out-of-window later. Mapping does not own the rate math, but it must emit fields the payout structure modeling layer can rate without re-interpretation — which is why allowance_type and a normalized rebate_rate are resolved here rather than inferred later.

Financial Settlement Layer

Mapping is where raw monetary strings become typed money, and the single most consequential rule on this page is that every amount is parsed straight from its string form into decimal.Decimal — never through float. A vendor file that carries 1234.56 as text, or an EDI element with an implied two-place decimal, must arrive in the canonical fact as Decimal("1234.56") with an explicit scale, because float introduces platform-dependent rounding drift that is invisible per line and material across a quarter-end run.

from decimal import Decimal, ROUND_HALF_UP

CENTS = Decimal("0.01")

def to_money(raw: str, implied_decimals: int = 0) -> Decimal:
    """Coerce a source amount string to a 2-place Decimal without float."""
    value = Decimal(raw.strip())
    if implied_decimals:
        value = value / (Decimal(10) ** implied_decimals)
    return value.quantize(CENTS, rounding=ROUND_HALF_UP)

Rates are normalized to a fraction at scale 6, so a source "5%", "0.05", or "5.0" (per-hundred) all resolve to Decimal("0.050000") under an explicit per-source rule — ambiguity in rate units is a top cause of disputed accruals. Currency is preserved, never converted, at this stage: the canonical fact carries the source currency code and the unconverted amount, leaving FX to a dedicated, separately auditable step. Sign convention is fixed here too — a deduction reduces what the retailer owes and an allowance is owed to the vendor — so that the direction of every dollar is unambiguous before it reaches the ledger. The tier-boundary arithmetic that turns these mapped amounts into accruals belongs to the rating engine, but it can only be correct if the money and rate fields it consumes are decimal-exact and unit-normalized from this layer.

ETL Implementation Patterns

Mapping is enforced with Pydantic v2 models that make an invalid canonical fact unconstructable: float amounts are rejected, required fields cannot be null, and the date window is validated at model-build time. The mapping engine applies registry rules to produce a candidate dict, then the canonical model validates it — so a rule that mis-types a field fails loudly at map time rather than silently downstream.

from datetime import date
from decimal import Decimal
from enum import Enum
from pydantic import BaseModel, model_validator, field_validator

class AllowanceType(str, Enum):
    off_invoice = "off_invoice"
    scan_back = "scan_back"
    bill_back = "bill_back"
    lump_sum = "lump_sum"

class CanonicalPromotionFact(BaseModel):
    promotion_id: str
    sku_upc: str
    retailer_location_id: str
    transaction_date: date
    effective_date: date
    expiration_date: date
    units_sold: int
    gross_sales: Decimal
    discount_amount: Decimal
    rebate_rate: Decimal
    allowance_type: AllowanceType
    source_field_map: dict[str, str]

    @field_validator("gross_sales", "discount_amount", "rebate_rate", mode="before")
    @classmethod
    def reject_float(cls, v):
        if isinstance(v, float):
            raise TypeError("monetary/rate values must be Decimal or str, never float")
        return Decimal(str(v))

    @model_validator(mode="after")
    def window_ordered(self):
        if self.effective_date > self.expiration_date:
            raise ValueError("effective_date after expiration_date")
        return self

Persistence is an idempotent upsert keyed on a deterministic record_key — a hash of source_system, the source record’s natural key, and the mapping registry version. Because the key folds in the mapping version, replaying an unchanged file under the same registry overwrites rather than inserts (a true no-op), while a registry change produces a new key and a clean, diffable re-projection rather than a silent mutation of history. Schema evolution follows the same discipline: new canonical fields are added as optional with a default and a effective_from registry version, never as a breaking rename, so older reconciliation periods continue to project correctly under the rules that were live when they ran.

Drift Detection and Validation

A mapping registry is only as trustworthy as its monitoring, because the most dangerous failures are the ones that still produce a well-formed record. Retailers change export formats without notice, a renamed column starts arriving as null, a new deal type appears that no rule covers, and the canonical fact looks fine while quietly losing money. Validation therefore runs at two levels: per-record structural checks (the Pydantic gate above, plus referential integrity against the geography and vendor masters), and statistical drift monitoring across a batch.

A coverage drop caught at map time is a registry update; the same gap discovered after settlement is a restated accrual and an audit finding. When drift exceeds tolerance the engine quarantines the affected rows with their original source_field_map attached and opens an exception ticket, so the gap is fixed in the registry — as a reviewable commit — rather than patched in flight.

Fallback and Dispute Routing

No registry covers every label a multi-vendor ecosystem will ever emit, so the mapping engine degrades rather than halts. When no specific rule matches a source field, a deterministic fallback rule routes the value to a quarantine projection — it is never dropped and never guessed into a canonical bucket. Failures are categorized and routed by tier, mirroring the discipline the parsing layer applies upstream. Coercion errors (an amount that will not parse to Decimal, a date that fails timezone normalization) are typically a vendor format change and trigger a re-send or a registry fix. Coverage errors (a source label with no active rule, or a deal_type no conditional rule recognizes) open a mapping-gap ticket for the ETL owner. Referential errors (a retailer_location_id absent from the geography master, an unregistered vendor) route to trade-finance review. Each quarantined row carries a deterministic error code, the offending record_key, and its full source-to-canonical provenance, and every fallback application writes an audit entry naming the lane it was routed to. The default-rate policy and the manual adjudication queue that ultimately resolve a contested mapping are owned by the fallback routing logic layer; mapping’s job is to name the correct lane and leave a traceable record, so reconciliation stays continuous and every non-standard label is accounted for.

Security and Access Boundaries

Vendor feeds carry negotiated pricing and party data, so the mapping registry is governed as a financial control, not a config file. Canonical facts hold sensitive money and identifiers, so field-level encryption protects monetary elements (gross_sales, discount_amount, rebate_rate) and party identifiers at rest, and role-based access control (RBAC) tags travel with every field so authorization is enforced per attribute. The separation of duties is explicit: ETL developers can author and deploy registry versions but cannot mutate a persisted canonical fact; vendor managers can review quarantined rows and propose mapping changes but cannot edit an extracted amount; trade-finance analysts can adjudicate exceptions and export audit trails but cannot alter source provenance. Because mapping rules are the same kind of high-trust artifact as the agreement schema design they ultimately feed, every registry change is a signed, reviewed commit, and persistence writes to immutable, hash-chained audit logs so any tampering surfaces as a chain break. Registry deployment credentials and the keys protecting encrypted fields rotate on a fixed schedule. Treating field mapping as a versioned, access-governed control is what turns a chaotic pile of vendor vocabularies into audit-ready financial data: faster settlements, fewer disputes, and a defensible trail from raw source label to posted accrual.

Frequently Asked Questions

Why externalize mappings into a registry instead of writing them in the ETL code? Because a mapping is business logic that changes far more often than the pipeline does, and every change must be reviewable and reversible. A version-controlled registry makes each change a diffable commit with an author, a timestamp, and an effective_from date scoping which reconciliation periods it affects — so reprocessing an old period uses the rules that were live then, and an auditor can see exactly why a field was mapped the way it was.

What happens when a retailer renames a source column without telling us? The renamed column arrives unmatched, so the fallback rule quarantines those rows and the drift monitor fires a coverage-drop signal before any malformed record reaches the ledger. The fix is a registry update — a new mapping rule for the renamed label — deployed as a commit, after which the quarantined batch is replayed and projects cleanly.

Why coerce every amount to decimal.Decimal at the mapping stage? Because float introduces platform-dependent rounding drift that is invisible per line but compounds to a material variance across a quarter-end run, and IFRS 15 / ASC 606 treatment requires figures reproducible to the cent. Amounts are parsed straight from their string form into Decimal with an explicit scale and ROUND_HALF_UP, and the Pydantic model rejects any float outright.

How does mapping avoid double-counting when a vendor re-sends a file? Persistence is an idempotent upsert keyed on a deterministic record_key hashed from the source system, the record’s natural key, and the mapping registry version. Replaying an unchanged file under the same registry overwrites rather than inserts, so it is a no-op; a registry change yields a new key and a clean re-projection rather than a silent edit to history.

Related

• CSV & EDI Parsing Workflows — turns raw vendor submissions into the typed facts this layer maps onto the canonical schema.
• POS & ERP Sync Patterns — joins, deduplicates, and pushes mapped canonical facts to downstream financial systems.
• Async Batch Processing — the queue and worker model that replays out-of-order batches idempotently.
• Normalizing SKU Hierarchies Across Retailers — resolves case packs, inner units, and UPCs to a single financial line for rebate attribution.
• Claim Validation Rule Engine — consumes the canonical fact to score and validate each deduction.

Up one level: Data Ingestion & Normalization Pipelines