Daily Variance Reconciliation

Daily variance reconciliation is the nightly batch that proves the money moved the way the fare engine said it would. Each night it compares the expected fare revenue the calculation engines priced against the settled totals that land in acquirer and vendor files, grouped per operator and fare product, computes the signed variance in Decimal, applies tolerance thresholds, and emits the report that auditors and revenue ops open first thing every morning. It is the control point inside Revenue Reconciliation & Settlement where a silent pricing regression, a dropped batch, or a misconfigured acquirer fee stops being invisible. This page covers building that reconciler as a deterministic, replayable job rather than a spreadsheet someone runs by hand.

The scope here is the intra-agency close: one agency’s own expected ledger against the settlement files its acquirers and gateway vendors return, bucketed by (operator_id, fare_product). The harder split — dividing shared-corridor revenue between separate operators — is a settlement problem, not a variance one, and lives in Inter-Agency Settlement Clearing. What this batch produces is the trusted delta that everything downstream trusts: if expected and settled agree to the cent within tolerance, the day closes clean; if they do not, this report is the first artifact an investigation reads.

Architecture: The Nightly Reconciliation Flow

Two independent streams converge at the reconciler. The first is the expected side: every priced fare the calculation engines emitted during the service day, already written to the append-only ledger. The second is the settled side: the acquirer settlement files, gateway payout reports, and cash-vault deposits that arrive on their own schedules, often hours after the service day closes. The reconciler aligns both to a canonical business date, aggregates each into per-bucket totals, subtracts, and grades the result against tolerance before anyone downstream acts on it.

The flow below traces a business date from the two source streams through aggregation, differencing, and tolerance grading to the published report:

The single arithmetic the whole batch turns on is one subtraction per bucket. For a bucket $b$ with expected total $E_b$ and settled total $S_b$, the signed variance and its relative form are:

$$v_b = E_b - S_b \qquad r_b = \frac{v_b}{E_b} \;\; (E_b \neq 0)$$

A positive $v_b$ means the engine expected more than settled — under-collection, a dropped batch, or refunds not yet netted. A negative $v_b$ means more money settled than the engine priced — a double-capture, a duplicated file, or a fee credited in the wrong bucket. Both directions are defects; the tolerance test decides which ones a human has to look at tonight.

Prerequisites & Environment

This reconciler targets Python 3.11+ for datetime.UTC and zoneinfo, and keeps the hot path on the standard library — decimal, datetime, collections, dataclasses — so it runs identically on a nightly cron worker and inside a replay harness. Settlement files are parsed with pandas>=2.1 when they arrive as fixed-width or CSV extracts; the aggregation itself never touches float, so parsed amounts are converted to Decimal at the boundary and stay there.

The assumptions the batch makes about its two input streams:

Tolerance thresholds are not compiled in. They are supplied per bucket — an absolute cent floor plus a relative percentage — so revenue analysts can loosen a noisy card-not-present product or tighten a high-volume flagship fare without a redeploy, exactly as window thresholds are externalized elsewhere in the platform.

Core Implementation

The reconciler below takes two aggregates keyed by bucket, computes a signed Decimal variance per bucket, grades each against a per-bucket tolerance with an absolute-and-relative rule, and returns a structured result the report layer serializes. Every monetary value is Decimal, every bucket present on either side is reported (not just the intersection), and the grading is pure so it replays identically months later.

from __future__ import annotations

import logging
from dataclasses import dataclass
from decimal import Decimal, ROUND_HALF_UP
from enum import Enum
from typing import Mapping

logger = logging.getLogger("transit.variance")

CENTS = Decimal("1")  # amounts are integer minor units


class VarianceGrade(Enum):
    CLEAN = "clean"
    WATCH = "watch"          # inside hard tolerance, outside soft warning band
    EXCEPTION = "exception"  # beyond tolerance, requires investigation


@dataclass(frozen=True)
class Bucket:
    operator_id: str
    fare_product: str

    def key(self) -> str:
        return f"{self.operator_id}::{self.fare_product}"


@dataclass(frozen=True)
class Tolerance:
    """Beyond tolerance when BOTH the absolute and relative floors are breached.

    Requiring both prevents a tiny high-percentage blip on a near-zero bucket
    from paging an analyst, while still catching a large flat variance on a
    product whose daily volume makes the percentage look small.
    """
    abs_cents: Decimal
    rel_fraction: Decimal   # e.g. Decimal("0.005") == 0.5%
    warn_fraction: Decimal  # soft band for the WATCH grade


@dataclass(frozen=True)
class BucketVariance:
    bucket: Bucket
    expected: Decimal
    settled: Decimal
    variance: Decimal        # expected - settled, signed
    variance_pct: Decimal    # relative to expected, 0 if expected == 0
    grade: VarianceGrade


class DailyVarianceReconciler:
    """Nightly per-bucket variance close for one agency's own settlement."""

    def __init__(
        self,
        tolerances: Mapping[str, Tolerance],
        default_tolerance: Tolerance,
    ) -> None:
        self._tolerances = tolerances
        self._default = default_tolerance

    def _tolerance_for(self, bucket: Bucket) -> Tolerance:
        return self._tolerances.get(bucket.key(), self._default)

    @staticmethod
    def _pct(variance: Decimal, expected: Decimal) -> Decimal:
        if expected == 0:
            return Decimal("0")
        return (variance / expected).quantize(Decimal("0.0001"), rounding=ROUND_HALF_UP)

    def _grade(self, variance: Decimal, expected: Decimal, tol: Tolerance) -> VarianceGrade:
        magnitude = abs(variance)
        rel = abs(self._pct(variance, expected))
        # A bucket with no expected revenue but real settled money is always
        # an exception: it cannot be explained by rounding.
        if expected == 0 and magnitude > 0:
            return VarianceGrade.EXCEPTION
        beyond_abs = magnitude > tol.abs_cents
        beyond_rel = rel > tol.rel_fraction
        if beyond_abs and beyond_rel:
            return VarianceGrade.EXCEPTION
        if magnitude > tol.abs_cents and rel > tol.warn_fraction:
            return VarianceGrade.WATCH
        return VarianceGrade.CLEAN

    def reconcile(
        self,
        expected: Mapping[Bucket, Decimal],
        settled: Mapping[Bucket, Decimal],
    ) -> list[BucketVariance]:
        """Difference every bucket present on either side and grade it."""
        results: list[BucketVariance] = []
        all_buckets = set(expected) | set(settled)
        for bucket in sorted(all_buckets, key=lambda b: b.key()):
            exp = Decimal(expected.get(bucket, Decimal("0"))).quantize(CENTS)
            settle = Decimal(settled.get(bucket, Decimal("0"))).quantize(CENTS)
            variance = exp - settle
            tol = self._tolerance_for(bucket)
            grade = self._grade(variance, exp, tol)
            result = BucketVariance(
                bucket=bucket,
                expected=exp,
                settled=settle,
                variance=variance,
                variance_pct=self._pct(variance, exp),
                grade=grade,
            )
            if grade is VarianceGrade.EXCEPTION:
                logger.warning(
                    "variance exception %s: expected=%s settled=%s delta=%s",
                    bucket.key(), exp, settle, variance,
                )
            results.append(result)
        return results

Three decisions carry the correctness of this batch. First, the reconciler ranges over the union of buckets, not the intersection — a product that priced revenue but settled nothing, or settled money against a product the engine never priced, are the two failures a naive inner join hides. Second, tolerance is an absolute-and-relative rule: a half-percent swing on a bucket that turns over eight dollars is noise, but a flat fifty-dollar gap on a high-volume flagship product is real, and requiring both floors to break keeps analysts off the noise. Third, an expected == 0 bucket with settled money is unconditionally an exception, because there is no rounding story that explains cash arriving for a fare nobody priced.

A worked bucket makes the grading concrete. Suppose the (MUNI_BUS, adult_single) bucket priced Decimal("1250000") cents ($12,500.00) and settled Decimal("1249850") — a 150-cent shortfall. Against a tolerance of abs_cents=Decimal("500") and rel_fraction=Decimal("0.005"), the relative variance is 150 / 1_250_000 = 0.00012, two orders of magnitude under the 0.5% floor, and the absolute variance is under the five-dollar floor, so the bucket grades CLEAN — the shortfall is almost certainly a single rounding-boundary tap and no human needs to see it. Now hold the same 150-cent gap on a low-volume concession that priced only Decimal("9000") cents ($90.00): the relative variance is 0.0167, well past the floor, but the absolute variance is still under five dollars, so it grades CLEAN too — the absolute floor deliberately vetoes a high-percentage blip on a tiny bucket. Only when both floors break — say a 900-cent gap at 1.2% on that flagship product — does the bucket escalate to EXCEPTION and land in tonight’s queue. That asymmetry is the entire point of the two-part rule: it is tuned to page an analyst on money that matters, not on arithmetic that does not.

Schema Validation & Edge Cases

The batch inherits aggregates, but “aggregated” does not mean “aligned.” The edges that corrupt a variance report are all boundary and identity edges, and each has a defined behavior rather than a silent guess.

• Cutoff timezone. The service day is an agency-local business date, but timestamps arrive in UTC. A tap at 23:58 local on a America/Chicago system is a UTC record after midnight; bucketing it by UTC date drops it into the wrong day and manufactures a matched over/under pair across the boundary. Both streams normalize to the same zoneinfo cutoff before aggregation, following the Python zoneinfo documentation, and the cutoff is a configuration value, not a literal.
• Late and partial files. Acquirer files land hours after close, and sometimes a day’s settlement arrives split across two deliveries. A bucket whose settled side is still incomplete must be graded WATCH and re-run, never closed as a real under-collection. The batch keys on each file’s declared control total and record count so a truncated or still-arriving file is detectable before it pollutes the report; the sibling guide on matching settlement files to tap ledgers owns that row-level completeness check.
• Refunds and chargebacks. A refund settles as a negative amount, often on a business date after the original sale. Netted into the wrong day it reads as a phantom under-collection on the refund date and an over-collection on the sale date. Refunds are bucketed to the original sale’s business date via the settlement file’s reference to the original transaction, so the variance nets to zero where the money actually belongs.
• Idempotent re-runs. The whole batch is re-runnable. Aggregation dedupes on file_id so an acquirer redelivery does not double the settled side, and the grading function is pure, so a replay of last Tuesday produces byte-identical output for an audit.
• Fee and interchange lines. Acquirers report gross sales and deduct interchange and scheme fees separately. The expected side prices gross fare; reconciling against a net payout invents a variance equal to the fee. The settled aggregate is built from the gross settlement line, and fees are reconciled as their own bucket rather than smeared across fare products.

Integration Pattern

This batch is a control point between the pricing engines upstream and the settlement and audit machinery downstream, and its output is a dependency for both.

The expected side is not recomputed here — it is read from the append-only fare ledger described in Ledger & Audit Trail Design. That ledger is the single source of truth for what the engines priced, and reconciling against it (rather than against a live re-pricing) is what makes tonight’s variance reproducible during a dispute six months from now: the exact expected total that fed the report is hash-anchored and immutable. When a bucket grades EXCEPTION, the investigation reads back through that ledger to the individual priced fares, not through a re-run of the calculation engine.

Downstream, a clean intra-agency close is the precondition for cross-agency work. There is no point prorating a shared corridor’s revenue between operators until each operator’s own books balance to settlement, so Inter-Agency Settlement Clearing consumes this batch’s CLEAN verdict as a gate — a day with open exceptions is held out of the netting run rather than clearing on numbers that do not yet foot. The exception queue, meanwhile, feeds the same investigation surface that fraud and dispute workflows use, so an under-collection that turns out to be evasion rather than a data defect crosses cleanly into Fraud Detection & Revenue Protection.

Performance & Scale Considerations

At metro scale the reconciler grades tens of thousands of buckets against a service day of millions of taps, and the cost lives almost entirely in the two aggregations, not the differencing.

• Aggregate at the source. Both totals should be summed in the database or the columnar extract, not in Python. The reconciler operates on per-bucket sums — a map of a few tens of thousands of entries — so the object it holds in memory is bounded by the product catalog times the operator count, never by daily tap volume.
• Decimal at the boundary only. Convert parsed amounts to Decimal once, at the parse edge, then sum in Decimal. Converting repeatedly inside a hot loop is the common performance regression; the type is exact but not cheap, so touch it as few times as possible per record.
• Partition by business date. Run one reconciler instance per business date so a late file for Monday re-runs Monday alone without disturbing Tuesday’s closed books. Dates are independent, so the batch parallelizes across them trivially, and a replay of a historical week fans out with no shared state.
• Bounded exception fan-out. The report writes every bucket, but only exceptions carry a drill-down payload. Materializing the underlying rows for clean buckets is wasted I/O; defer the row-level pull to the investigation step so a clean night is cheap.
• Stable ordering. Sort buckets by key before emitting so two runs of the same day diff cleanly. Deterministic ordering is what lets a reviewer confirm a re-run changed nothing by comparing report artifacts directly.

Operational Checklist

1. Pin the cutoff. Confirm both streams normalize to the same agency-local business-date cutoff from configuration, and alert on any source that emits records outside its declared date window.
2. Verify control totals. Reject or hold any settlement file whose declared record count or control total does not match what was parsed, before it reaches aggregation.
3. Externalize tolerances. Confirm per-bucket absolute and relative thresholds load at runtime and that a threshold change takes effect without a redeploy.
4. Gate the clean signal. Ensure a day with open exceptions is held out of inter-agency clearing rather than silently clearing on numbers that do not foot.
5. Dedupe on file_id. Validate that reprocessing an acquirer redelivery leaves the settled aggregate unchanged.
6. Bucket refunds to the sale date. Confirm negative settlement lines net against the original business date, not the refund date.
7. Retain the report artifact. Store every night’s report immutably alongside the ledger hash it reconciled against, so a re-run is provably reproducible for audit.
8. Watch variance drift. Trend the daily aggregate variance per product against baseline; a slow creep inside tolerance is an early pricing regression the pass/fail grade will not catch until it breaks the threshold.

Related deep-dive guides

• Automating Daily Fare Variance Reports — generating the grouped, tolerance-graded report and its CSV-ready rows from the reconciler output.
• Matching Settlement Files to Tap Ledgers — the row-level match that classifies each settlement line against the internal tap ledger before aggregation.

Related

• Inter-Agency Settlement Clearing — consumes the clean-close signal before prorating shared-corridor revenue.
• Ledger & Audit Trail Design — the append-only fare ledger this batch reads its expected side from.
• Fraud Detection & Revenue Protection — where an under-collection that turns out to be evasion rather than a data defect is routed.
• Transfer Window Logic — the calculation-engine stage that prices the transfers this batch later reconciles.
• Threshold Tuning Frameworks — how variance tolerances, like fare thresholds, are versioned and tuned against history.

Part of Revenue Reconciliation & Settlement.