Data Ingestion Recipe Parsing Workflows

Inventory Snapshot Reconciliation

A physical inventory count is the ground truth that every theoretical model is eventually measured against — and it is also one of the noisiest feeds a food-cost system ingests. This guide, part of the data ingestion and recipe parsing workflows domain, isolates the sub-problem of reconciling a counted snapshot against expected on-hand: computing what inventory should be after opening stock, receipts, and sales-driven depletion, comparing it to what was counted, and attributing the difference. The failure this eliminates is the mystery variance — a shrinkage number with no cause, which operators either ignore or over-react to, because the reconciliation never separated a miscount from real loss.

Concept Definition and Data Contract

Reconciliation consumes three inputs and emits a signed shrinkage record per SKU. The inputs are the opening snapshot (on-hand at period start), the movement feed (receipts in, plus expected depletion out), and the closing count (the physical count at period end). Expected depletion is computed from POS sales expanded through the recipe BOM cost roll-up, in canonical units established by unit conversion and canonicalization. The output contract is, per (location_id, ingredient_sku): expected_on_hand, counted_on_hand, shrinkage (signed), and a status.

The signed convention matters. A negative shrinkage (counted less than expected) is loss — waste, over-portioning, or theft. A positive shrinkage (counted more than expected) is usually a data problem — an unrecorded receipt or a depletion overstated by a bad recipe. Both are surfaced; neither is clamped to zero.

Architecture Decision Rationale

Reconcile in canonical units, not receiving units. A count in cases, a recipe in grams, and receipts in pounds cannot be subtracted until they share a unit. Reconciliation operates entirely on canonical quantities, so the arithmetic is dimensionally sound and a “case” never accidentally cancels a “gram.”

Attribute, don’t just total. A single shrinkage number per SKU is nearly useless. Splitting expected depletion into its drivers — sales-driven usage, known waste, sanctioned substitutions — lets the residual be a real signal. The attribution itself is deepened downstream in cost variance attribution models; here the job is to produce a clean, signed residual for it to work on.

Quarantine impossible counts. A negative count, or one an order of magnitude off the expected value, is almost always a miscount or a unit error, not a genuine result. Reconciling it would inject a false variance; quarantining it triggers a recount instead. The end-to-end depletion computation is worked through in reconciling theoretical vs actual inventory depletion.

Phase 1 — Validate the Counted Snapshot

The counted snapshot is ingested through a strict contract; impossible counts are quarantined before any arithmetic.

from __future__ import annotations

from decimal import Decimal

from pydantic import BaseModel, ConfigDict, Field, field_validator


class InventoryCount(BaseModel):
    model_config = ConfigDict(frozen=True, extra="forbid")

    location_id: str
    ingredient_sku: str
    counted_qty_canonical: Decimal = Field(ge=0)      # negative count -> reject
    counted_at: str

    @field_validator("counted_qty_canonical", mode="before")
    @classmethod
    def _as_decimal(cls, v: object) -> Decimal:
        return Decimal(str(v))

The ge=0 bound rejects a negative count at parse time — you cannot have less than nothing on the shelf, and a negative value is a scanner or sign error that must not enter the reconciliation.

Phase 2 — Compute Expected On-Hand and the Signed Difference

Expected on-hand is opening plus receipts minus expected depletion; the difference against the count is the signed shrinkage. All vectorized, all Decimal.

from decimal import Decimal

import pandas as pd


def reconcile(opening: pd.DataFrame, receipts: pd.DataFrame,
              depletion: pd.DataFrame, counts: pd.DataFrame) -> pd.DataFrame:
    """Return signed shrinkage per (location_id, ingredient_sku)."""
    keys = ["location_id", "ingredient_sku"]
    df = (
        opening.merge(receipts, on=keys, how="outer")
        .merge(depletion, on=keys, how="outer")
        .merge(counts, on=keys, how="outer")
        .fillna(Decimal("0"))
    )
    df["expected_on_hand"] = (
        df["opening_qty"] + df["received_qty"] - df["expected_depletion_qty"]
    )
    df["shrinkage"] = df["counted_qty_canonical"] - df["expected_on_hand"]
    return df[keys + ["expected_on_hand", "counted_qty_canonical", "shrinkage"]]

An outer join on every feed means a SKU that appears in only one source (received but never counted, counted but never opened) still surfaces with the others zero-filled, so nothing silently drops out of the reconciliation.

Phase 3 — Route Outliers and Write the Ledger

Reconciled rows within a plausible band are written to the ledger; outliers are flagged for recount. The write is idempotent on the period key.

from decimal import Decimal

import pandas as pd


def route_and_stage(recon: pd.DataFrame, period: str,
                    tolerance_pct: Decimal = Decimal("0.5")) -> tuple[pd.DataFrame, pd.DataFrame]:
    recon = recon.copy()
    denom = recon["expected_on_hand"].where(recon["expected_on_hand"] != 0, Decimal("1"))
    rel = (recon["shrinkage"] / denom).abs()
    outlier = rel > tolerance_pct
    recon["period"] = period
    recon["status"] = "RECONCILED"
    recon.loc[outlier, "status"] = "RECOUNT"
    return recon[~outlier], recon[outlier]

INSERT INTO reconciliation_ledger (period, location_id, ingredient_sku,
       expected_on_hand, counted_on_hand, shrinkage, computed_at)
VALUES (:period, :loc, :sku, :exp, :cnt, :shrink, NOW())
ON CONFLICT (period, location_id, ingredient_sku) DO UPDATE
   SET expected_on_hand = EXCLUDED.expected_on_hand,
       counted_on_hand  = EXCLUDED.counted_on_hand,
       shrinkage        = EXCLUDED.shrinkage,
       computed_at      = EXCLUDED.computed_at;

The ON CONFLICT upsert keyed on (period, location_id, ingredient_sku) makes a re-run after a recount overwrite exactly the affected rows, never duplicate the period.

Production Hardening

Canonical units throughout. Reconcile only on canonical quantities so receiving units and recipe units never cancel each other incorrectly.
Signed shrinkage. Preserve the sign — loss and data-error point in opposite directions and demand different responses.
Quarantine impossible counts. Reject negative counts at ingestion and route implausible ones to recount before they distort the ledger.
Idempotent period writes. Upsert on the period key so a recount re-run is safe and never double-counts.
Attribution-ready residual. Subtract known waste and substitutions from expected depletion so the residual shrinkage is a genuine signal for the attribution layer, not a bucket of everything unexplained.

Failure Modes and Troubleshooting

Symptom	Likely cause	Detection / fix
Every SKU shows shrinkage	Expected depletion computed in wrong units	Reconcile in canonical units only; verify the depletion feed matches.
Shrinkage sign looks inverted	Count minus expected written backwards	Define `shrinkage = counted − expected`; negative is loss.
A recount doubled a period’s rows	Insert without an idempotency key	Upsert on `(period, location_id, ingredient_sku)`.
A huge false variance on one SKU	Impossible count reconciled instead of quarantined	Route outliers beyond the band to `RECOUNT`; reject negatives at ingest.
Shrinkage never resolves to a cause	Known waste/substitution not subtracted	Net known movements out of expected depletion before differencing.

FAQ

Why reconcile in canonical units instead of the units the count came in?

Because opening stock, receipts, sales-driven depletion, and the physical count almost never arrive in the same unit. Subtracting a count in cases from an expected value in grams is meaningless. Converting every feed to a canonical base unit at ingestion makes the reconciliation arithmetic dimensionally sound, so a difference reflects real movement rather than a unit mismatch.

What does a positive shrinkage (more counted than expected) mean?

Usually a data problem rather than a windfall — an unrecorded receipt, a recipe that overstates depletion, or a prior miscount. Preserving the sign is what lets you tell it apart from genuine loss (negative shrinkage). Clamping both to a magnitude would hide the most useful diagnostic signal in the reconciliation.

When should a count be quarantined instead of reconciled?

When it is physically impossible (negative) or implausibly far from expected on-hand relative to a tolerance band. Those are almost always miscounts or unit errors, and reconciling them injects a false variance. Routing them to a recount queue keeps the ledger clean and gets a human to re-verify the shelf before the number is trusted.

How does reconciliation relate to cost-variance attribution?

Reconciliation produces a clean, signed residual shrinkage per SKU in canonical units, with known waste and substitutions already netted out. The attribution layer then explains that residual — how much is prep error, portioning drift, or supplier quality. Reconciliation is the measurement; attribution is the diagnosis, and the quality of the diagnosis depends on the residual being clean.

For library specifics, see the official pandas documentation and Pydantic documentation.