Batch Cost Reporting Automation

Within the reporting, CI/CD and delivery pipelines domain, this guide isolates one sub-problem: turning a validated cost-and-variance snapshot into a scheduled, reproducible margin report without ever recomputing the underlying numbers. The failure this eliminates is the “which spreadsheet is right?” problem — an operations team running three different notebooks that each aggregate the ledger slightly differently and produce three different food-cost percentages for the same week. A batch report is correct only if it is a pure, versioned function of a frozen input, and this page defines that function, the data contract it consumes, and the three-phase build that renders it.

Concept Definition and Data Contract

A batch report consumes exactly one input: a frozen snapshot of the variance layer’s output. “Frozen” is the load-bearing word. The report does not query the live cost tables; it queries a specific run_version that was materialized once and will never change. The input contract is a row per (location_id, menu_item_id) carrying units_sold, theoretical_cost, actual_cost, and net_revenue, with all monetary columns typed as Decimal (or a NUMERIC column at the database boundary). Those figures come from the theoretical vs actual food cost calculation layer, specifically the variance mapping methodologies that already attributed each dollar of variance to a cause.

The output contract is a rendered artifact plus a metadata record. The artifact is an HTML or PDF report; the record captures report_id, run_version, period, the set of location_ids included, a content hash, and a generated-at timestamp. Nothing about the report is derived from mutable state — given the same run_version and the same template, the artifact is byte-reproducible, which is the property that makes it auditable.

Architecture Decision Rationale

Freeze the snapshot rather than query live tables. The tempting shortcut is to point the report at the current cost tables and filter by date. That couples the report to whatever corrections have landed since — a supplier credit posted on Tuesday silently rewrites Monday’s report if it is regenerated. Pinning run_version decouples the report from time: the same week always renders the same numbers, and a correction produces a new run_version and a visibly new report rather than a silent edit.

Aggregate with vectorized pandas, not per-location loops. A national estate is hundreds of locations times hundreds of menu items. Iterating locations to build a report is both slow and a common source of subtle bugs where one location’s frame leaks a stale variable into the next. A single groupby over the whole snapshot is faster and structurally guarantees each location is computed from its own rows only.

Render from a frame, never inline arithmetic in the template. All computation happens in the aggregation step and lands in a fully-formed frame; the template only formats. A template that does math (NaN) reintroduces the divergence problem — now the report has cost logic in two places. The template’s only numeric responsibility is choosing display precision at the very last step.

Phase 1 — Load and Validate the Frozen Snapshot

The first phase pins the snapshot and asserts completeness before a single figure is aggregated. Missing locations or stale versions are caught here, loudly, rather than surfacing as blank cells in a delivered PDF.

from __future__ import annotations

from dataclasses import dataclass
from decimal import Decimal

import pandas as pd


@dataclass(frozen=True)
class SnapshotContract:
    run_version: str
    period: str
    expected_locations: frozenset[str]


def load_snapshot(conn, contract: SnapshotContract) -> pd.DataFrame:
    """Load a single pinned run_version and assert it is complete."""
    df = pd.read_sql(
        "SELECT location_id, menu_item_id, units_sold, theoretical_cost,"
        "       actual_cost, net_revenue"
        "  FROM variance_snapshot WHERE run_version = %(rv)s",
        conn, params={"rv": contract.run_version},
    )
    for col in ("theoretical_cost", "actual_cost", "net_revenue"):
        df[col] = df[col].map(lambda v: Decimal(str(v)))

    present = frozenset(df["location_id"].unique())
    missing = contract.expected_locations - present
    if missing:
        raise SnapshotIncomplete(f"snapshot {contract.run_version} missing {sorted(missing)}")
    return df


class SnapshotIncomplete(RuntimeError):
    """Raised when a pinned snapshot is missing expected locations."""

Casting money through Decimal(str(v)) at load time keeps a float column in the database from injecting binary-rounding error into a figure that will be printed and reconciled. The SnapshotIncomplete exception is deliberately fatal for the affected report: a margin report that omits a location is not a smaller report, it is a wrong one.

Phase 2 — Deterministic Aggregation and Error Routing

The validated snapshot aggregates to the reporting grain — usually per location, sometimes per location per category. This is the pure-function heart of the report. Any row that cannot participate (a zero-revenue location that would divide by zero) is routed, not silently coerced.

from decimal import Decimal

import pandas as pd


def aggregate_report(df: pd.DataFrame) -> tuple[pd.DataFrame, pd.DataFrame]:
    """Return (report_frame, held_frame). Held rows never render as zero."""
    df = df.copy()
    df["cost_variance"] = df["actual_cost"] - df["theoretical_cost"]

    grouped = df.groupby("location_id", as_index=False).agg(
        units_sold=("units_sold", "sum"),
        actual_cost=("actual_cost", "sum"),
        net_revenue=("net_revenue", "sum"),
        cost_variance=("cost_variance", "sum"),
    )

    zero_rev = grouped["net_revenue"] <= 0
    held = grouped[zero_rev].assign(reason="zero_or_negative_revenue")
    report = grouped[~zero_rev].copy()
    report["food_cost_pct"] = report.apply(
        lambda r: (r["actual_cost"] / r["net_revenue"]).quantize(Decimal("0.0001")),
        axis=1,
    )
    return report.sort_values("cost_variance", ascending=False), held

Splitting held off rather than letting a zero-revenue location render food_cost_pct = 0 is the same quarantine discipline the ingestion layer applies. A location with a broken sales feed and a genuine zero-sales day are indistinguishable in the output of a naive division; separating them lets the report footnote “1 location pending sales data” instead of publishing a fabricated 0% food cost.

Phase 3 — Render and Persist the Artifact

The report frame renders through a template and the artifact is stored idempotently. Rendering is where display precision is finally applied — the only place rounding for presentation is allowed.

from __future__ import annotations

import hashlib

import pandas as pd
from jinja2 import Environment, select_autoescape


def render_report(report: pd.DataFrame, period: str, env: Environment) -> tuple[str, str]:
    """Render the report frame to HTML and return (html, content_hash)."""
    display = report.copy()
    display["food_cost_pct"] = (display["food_cost_pct"] * 100).map(lambda d: f"{d:.2f}%")
    display["cost_variance"] = display["cost_variance"].map(lambda d: f"${d:,.2f}")

    template = env.get_template("margin_report.html")
    html = template.render(period=period, rows=display.to_dict("records"))
    content_hash = hashlib.sha256(html.encode("utf-8")).hexdigest()
    return html, content_hash

The content hash is what makes storage idempotent: the artifact store upserts on (report_type, period, location_scope) and, if the incoming hash matches the stored one, the write is a no-op. A retried nightly job therefore never produces a duplicate report. The concrete weekly implementation — including trend columns that compare against prior periods — is worked through in generating weekly margin reports with pandas, and the upstream mechanics of keeping the snapshot itself fresh are covered in refreshing materialized cost views on a schedule.

Production Hardening

• Snapshot pinning. Every report records the run_version it consumed. Regenerating a period reads the same version and produces an identical artifact; a data correction mints a new version rather than mutating history.
• Idempotency keys. Upsert artifacts on (report_type, period, location_scope) and short-circuit on a matching content hash, so retries and double-triggers converge to one artifact.
• Memory discipline. Aggregate the whole estate in one vectorized pass, cast wide identifier columns to category dtype, and chunk only if a single snapshot genuinely exceeds worker memory — most do not once aggregated.
• Decimal at the boundary. Carry Decimal through aggregation; apply presentation rounding only in the render step. The stored figure and the printed figure must reconcile to the cent.
• Completeness gating. Assert the expected location set before rendering, and footnote held rows explicitly rather than omitting them, so a missing feed is visible instead of masquerading as zero sales.

Failure Modes and Troubleshooting

FAQ

Why freeze a snapshot instead of querying the current tables?

Because the current tables change. A supplier credit, a late invoice, or a corrected yield lands after the reporting period closes and would silently rewrite an already-sent report if it were regenerated against live data. Pinning a run_version makes the report a function of an immutable input, so the same period always renders identically and a correction produces a new, visibly different report rather than a silent edit.

Should the template do any calculation?

No. All arithmetic happens in the aggregation phase and lands in a fully-formed frame; the template only formats strings and chooses display precision. Any math in the template duplicates cost logic and reintroduces the divergence the whole pipeline exists to prevent.

How do I stop a retried job from sending two reports?

Store artifacts under an idempotency key of (report_type, period, location_scope) and compute a content hash of the rendered output. If the key exists with the same hash, the write and the send are no-ops. Because generation is a pure function of the pinned snapshot, the retry produces the identical hash and is correctly recognized as a duplicate.

What belongs in the report versus the alert channel?

Batch reports are periodic, comprehensive, and pull-based — a full margin picture an operator reviews on a cadence. Alerts are event-driven and narrow — one dish crossed a band and someone must act now. They read the same snapshot but serve different urgency; the alerting logic lives in threshold tuning, and distribution routes each to its own channel.

Related

• Up one level: Reporting, CI/CD & Delivery Pipelines
• Generating Weekly Margin Reports with pandas
• Refreshing Materialized Cost Views on a Schedule
• Variance Mapping Methodologies
• Scheduled Report Distribution

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