Short answer: Generate each monthly statement from one immutable, versioned data snapshot; when its numbers differ from a dashboard snapshot or live query, compare the cutoff, filters, timezone, metric definition, and revision before touching the PDF template.
That is the result worth optimizing for: repeatable explanations, not accidental visual agreement. The evaluation constraint is equally important for a media reporting pipeline. A fix passes only when the same input snapshot produces the same statement totals while concurrent live activity continues, and when batch throughput remains high enough to finish the monthly archive window.
The tempting approach is to execute the reporting query again whenever someone opens a statement. It is simple. It also turns a historical artifact into a moving target because late corrections, timezone boundaries, mutable dimensions, and newly arrived events can change the answer between runs. PDF is the delivery format here, not the source of truth.
Why do monthly statement numbers not match a dashboard snapshot or live query?
The three surfaces usually answer subtly different questions. A live query asks what the database knows now. A dashboard snapshot may reflect a cached refresh, a saved filter state, or an aggregate built at an earlier watermark. A monthly statement is expected to preserve what was approved for a closed reporting period. Those semantics can diverge even when every SQL expression is individually valid.
Start with time. Store the reporting interval as a half-open range, such as period_start <= event_time < period_end, and record the timezone used to derive both boundaries. A row at exactly midnight belongs to one period, not both. If the dashboard labels a month in a viewer's local timezone while the statement job groups UTC timestamps, events near the boundary can appear to move. Do not patch that with a one-hour offset; make the business timezone and conversion rule explicit. Next, inspect data freshness. The snapshot needs a watermark or equivalent revision token that says which source changes it includes. The live query will naturally see later inserts and corrections. A dashboard backed by a materialized aggregate can lag behind both. Comparing only the visible date range hides this distinction, so put snapshot_id, source_watermark, metric_version, and normalized filter values into the statement manifest. These are audit fields, not presentation copy. Definitions drift too. net_revenue might mean gross revenue minus refunds in one path, while another path also excludes tax or applies currency conversion on a different date. The right repair is one versioned metric contract shared by snapshot creation, dashboard inspection, and statement rendering. Copying an expression into three services feels quick in a notebook; six months later, it becomes three definitions wearing the same label. One more trap is multiplicity. Joining campaign, placement, and adjustment tables can duplicate a monetary fact before aggregation. Check row counts and stable fact identifiers at each join boundary. A PDF with a correct subtotal layout cannot rescue an upstream one-to-many join.
Capture the comparison before rendering
Treat statement generation as a small data build. The input stage resolves the closed period and freezes a snapshot. The transform stage applies a named metric contract and emits typed statement rows. The render stage consumes those rows without querying operational tables. The archive stage stores the PDF beside a manifest and a digest of the canonical data payload.
The manifest is the fastest route from "these totals differ" to a useful comparison. It should make the query context inspectable without requiring the original worker process: period boundaries, timezone, filters, source watermark, metric version, snapshot identifier, generation timestamp, and a digest. Be careful with the digest. Hash a canonical serialization of the data contract, not the PDF bytes; PDF metadata and producer behavior can vary without changing any statement number.
Here is a focused Python shape for that boundary. It deliberately separates canonical statement data from rendering concerns.
from __future__ import annotations
from dataclasses import asdict, dataclass
from datetime import datetime
from decimal import Decimal
import hashlib
import json
from typing import Iterable
@dataclass(frozen=True)
class StatementRow:
account_id: str
impressions: int
net_revenue: Decimal
@dataclass(frozen=True)
class SnapshotManifest:
snapshot_id: str
source_watermark: str
metric_version: str
period_start: datetime
period_end: datetime
timezone: str
filters: dict[str, str]
def canonical_payload(
manifest: SnapshotManifest, rows: Iterable[StatementRow]
) -> bytes:
ordered_rows = sorted(rows, key=lambda row: row.account_id)
payload = {
"manifest": {
**asdict(manifest),
"period_start": manifest.period_start.isoformat(),
"period_end": manifest.period_end.isoformat(),
},
"rows": [
{
"account_id": row.account_id,
"impressions": row.impressions,
"net_revenue": format(row.net_revenue, "f"),
}
for row in ordered_rows
],
}
return json.dumps(
payload, sort_keys=True, separators=(",", ":")
).encode("utf-8")
def snapshot_digest(
manifest: SnapshotManifest, rows: Iterable[StatementRow]
) -> str:
return hashlib.sha256(canonical_payload(manifest, rows)).hexdigest()
There are two details here that often disappear in a quick prototype. Decimal values become strings instead of binary floating-point numbers, and rows are sorted by a stable business key before hashing. Without canonical ordering, two equivalent result sets can produce different digests merely because the database returned rows in a different order. Don't confuse that with a financial mismatch.
Keep the raw snapshot or a reproducible snapshot reference as well as the digest. A digest proves equality; it cannot explain inequality. When a discrepancy arrives, compare the statement snapshot with a dashboard export at the same watermark and metric version. If they agree, the live query is answering a later question. If the typed records agree but the displayed totals differ, the investigation can finally move downstream to formatting, pagination, or rounding.
A focused reconciliation test
The useful test does not assert only a grand total. It compares keys, counts, and amounts in that order, then reports the smallest meaningful differences. Missing account IDs suggest filter or join behavior. Matching IDs with different impression counts point toward data cutoff or deduplication. Matching counts with different money values narrow the search to metric versions, currency treatment, or rounding.
Use exact decimal arithmetic for statement amounts and define where rounding occurs. Rounding each line and then summing can differ from summing unrounded values and rounding once. Either policy can be valid under a stated contract; mixing them cannot. Preserve the unrounded input needed for audit, then test the selected policy directly.
from dataclasses import dataclass
from decimal import Decimal
@dataclass(frozen=True)
class Difference:
account_id: str
field: str
snapshot_value: object
comparison_value: object
def index_rows(rows: list[StatementRow]) -> dict[str, StatementRow]:
return {row.account_id: row for row in rows}
def reconcile(
snapshot_rows: list[StatementRow],
comparison_rows: list[StatementRow],
) -> list[Difference]:
snapshot = index_rows(snapshot_rows)
comparison = index_rows(comparison_rows)
differences: list[Difference] = []
for account_id in sorted(snapshot.keys() | comparison.keys()):
left = snapshot.get(account_id)
right = comparison.get(account_id)
if left is None or right is None:
differences.append(
Difference(account_id, "row", left, right)
)
continue
for field in ("impressions", "net_revenue"):
left_value = getattr(left, field)
right_value = getattr(right, field)
if left_value != right_value:
differences.append(
Difference(
account_id, field, left_value, right_value
)
)
return differences
def total_revenue(rows: list[StatementRow]) -> Decimal:
return sum((row.net_revenue for row in rows), start=Decimal("0"))
Run this comparison before invoking a PDF renderer. Feed it fixtures for boundary timestamps, zero-activity accounts, late adjustments, deleted dimension labels, and one-to-many joins. For a batch system, also generate multiple statements from the same frozen snapshot in parallel and verify their canonical digests. The goal is not to benchmark the renderer with invented throughput numbers. It is to discover whether concurrency changes data access, ordering, memory pressure, or retry behavior in your own environment.
Retries deserve precision. Snapshot creation should have a stable operation identity, and rendering should be idempotent for a given snapshot and template version. A retry must not silently choose a newer watermark. Write archive objects under a deterministic statement identifier, then publish completion metadata only after the PDF and manifest are durable. This avoids treating a partially completed batch as an approved archive.
The catch is storage and operational complexity. Retaining immutable snapshot inputs consumes more space than rerunning live queries, and versioned metrics require ownership and migration discipline. This approach is not suitable when the document is explicitly a real-time view with no audit or replay requirement. In that case, label the generation time and live semantics clearly, and keep the query path simple. For monthly media statements that may be challenged later, retain the snapshot.
Measure this before copying the design
Batch throughput is not just PDFs per minute. Measure snapshot-read time, transform time, render time, archive-write time, peak worker memory, retry count, and queue age separately. Also record statements completed per fixed worker allocation. That breakdown tells you whether adding renderer workers will help or merely increase contention against the snapshot store.
Use a representative distribution of statements rather than one average fixture. Media accounts with a handful of rows and accounts with thousands of placements stress different parts of the pipeline. I'm not sure a single concurrency setting can cover both tails in every deployment; the deciding evidence is a load run using your row-count distribution, document page distribution, archive latency, and worker memory limit. Bucket the results by input size before setting concurrency.
Keep evaluation gates close to the artifact. A data gate checks reconciliation against the approved snapshot. A document gate checks required pages, labels, and totals extracted from the finished PDF. A batch gate checks that queue age and resource ceilings stay within the publishing window. This is the notebook-to-production transition that matters: the exploratory query can reveal the mismatch, but a repeatable harness prevents it from returning next month.
Small signals help.
Log identifiers and versions, not the full sensitive statement payload. Alert on mismatch counts, missing snapshot manifests, abnormal retries, and archive publication lag. If a statement must be regenerated after a legitimate correction, create a new revision linked to the prior one rather than replacing history invisibly. The reader should be able to tell which revision they received.
Before adopting this design, measure the actual mismatch categories for one reporting cycle. If nearly every discrepancy comes from stale dashboard refreshes, aligning watermarks may solve most of the problem. If discrepancies cluster around month boundaries, focus on timezone fixtures. If typed rows match and only extracted PDF text differs, investigate document formatting and font behavior. Different evidence should lead to different work.
References
- ISO 32000-2, Portable Document Format: https://www.iso.org/standard/75839.html
Top comments (0)