Contents
Problem and boundaries
This is an illustrative learning case study, not a deployed system or a client engagement. The input is synthetic transaction data. There are no customers, credentials, production repositories, measured performance results, or business impact claims.
The design question is concrete: how can a daily batch accept late transactions, reject malformed rows, and produce the same model after a retry? Assume an input row carries a transaction ID, event timestamp, amount, currency, and source batch ID. Monetary amounts use exact decimal representation. The batch contract requires a declared currency; amounts in different currencies must never be added together without an explicit conversion policy.
Architecture
Keep original input immutable, with a batch manifest and checksum. A quality gate checks required identifiers, timestamp parsing, declared currency, amount precision, and conflicting transaction IDs. Valid rows enter a staging table. Invalid rows retain their source reference and reason code in quarantine; they are never silently discarded. Schema drift fails the batch for review rather than guessing new field meanings.
The analytical output has one row per event day and currency. Staging uniqueness uses the transaction ID plus the source contract's version policy. A replay replaces the affected day's output atomically from validated staging. A batch ledger records input checksum, rule version, and completion state, so a retry can distinguish the same input from a corrected input.
Role, tools, and approach
The role here is authoring an educational design exercise. Python is the proposed ingestion and validation tool; SQL describes staging constraints and daily aggregation. Object storage and a relational warehouse are architectural roles, with no vendor or paid service selected. These tools are proposed components, not a claim that a runnable repository exists.
Start with deterministic fixtures: a clean batch, a duplicate ID, a missing timestamp, an invalid decimal, a late row, and a corrected batch. Specify the expected quarantine reason and output for each. The core acceptance check is equality of output after running an identical batch twice. A failure between staging and publishing must leave the last complete daily model available.
Results and limitations
The result is this documented design and its invariants; no implementation benchmark or test run is claimed. Suggested evaluation checks include row reconciliation between accepted and quarantined records, uniqueness in staging, and daily totals partitioned by currency. These remain proposed checks for a future implementation.
A late event requires rebuilding its event-day partition, not merely processing today's ingestion date. Refund handling and changing source records need an agreed version policy before implementation. Concurrent writers also require a lock or transactional isolation policy; a ledger alone does not prevent overlapping publication.
Lessons and next experiment
Idempotency belongs to the input contract, the keys, and the publication boundary together. Saving rejected rows makes recovery inspectable. Next, implement a tiny local fixture and simulate a crash before publication, then compare the output with a clean replay. The example does not establish production readiness.