Where did this value come from?

Batch integration, transaction boundaries and the history a business system needs to keep.

Someone questions a number in yesterday’s valuation. One input was an exchange rate supplied by another system, and today’s rate is different. Yesterday’s import finished successfully. Can we find the value the calculation actually used and trace it back to the supplied record?

Now suppose a counterparty questions the totals in a file we sent. Can we show exactly what left our system, which data contributed to it and whether the recipient accepted it? Running the export again against today’s database may produce a different answer.

A scheduled import has an identity, purpose, inputs, decisions and effects. So does an export. Both deserve the accountability we expect when somebody changes business data through a screen. Their transaction boundaries and history belong in the design.

In the systems I have worked on, much integration means files arriving, bulk extracts requested and collections sent elsewhere. For a modest feed, ordinary tables and a small program may be enough: a batch record, retained input, validation results, links from committed changes to their source, and an explicit application transaction.

Knowing what arrived

The batch identity ties together its source, receipt time, supplier identifier, effective date and processing attempts. Its first useful distinction is between a delivery still in progress and one ready to inspect.

I have seen two familiar ways of making that distinction for files. One writes under a temporary name and renames the file when complete, using an atomic rename where supported. The other waits for a separate control file published after the data file has finished. It must itself arrive complete and identify the batch it describes.

A control file can also carry the expected record count, business date and totals. Compare these with the received contents and store both in the batch history. Count parsed records: quoted CSV fields can contain newlines, and headers or trailers may need excluding. The convention belongs to the interface agreement.

Keep the original payload for the agreed retention period. For CSV, I would usually stage incoming fields as text, with typed identifiers linking each record to its batch. An impossible date can then be inspected alongside everything else that arrived. A separate schema can give this material and its processing records a clear boundary within the database.

Checking records and collections

Agree how to interpret character encoding, dates, decimal separators and columns. Check the header before interpreting rows, so added, renamed or reordered columns are either supported or rejected as structural errors. Bulk APIs need corresponding checks. When an extract spans pages, the supplier must provide a stable collection, perhaps through a snapshot token, as-of parameter or fixed extract identifier. Without one, record that its consistency is unknown.

We can then convert values, check their meaning and, where useful, place valid rows in typed staging tables with keys and constraints. Validation should explain problems someone can act upon. If one record has an invalid date, unsupported currency and missing identifier, I want all three reported together. Some checks must wait for conversion, but independent checks can still run.

Oracle’s LOG ERRORS INTO can record supported insert failures while processing continues within a REJECT LIMIT. It reports one error per rejected row; collecting all validation failures still needs explicit checks.

The collection needs checking too. I have been asked to detect files containing only a CSV header, or columns containing nothing but zeros. Zero may be valid in one record while a whole column of zeros makes the file unsuitable. Similarly, allowing nulls in individual records need not mean accepting an entirely empty column.

No individual row failure is needed for the collection to be wrong.

Unusual record counts can matter too. If a feed normally contains 10,000 to 50,000 records, receiving 100 or 500,000 deserves investigation. Store counts, totals and proportions of zero or null values for comparison over time. An unusual volume may be legitimate, but investigate it before it becomes business data.

Deciding what to accept

Acceptance therefore needs an explicit policy. Some feeds permit independent records to succeed; others require related records or the whole batch to remain together. Accounting entries may have to balance. A complete reference-data replacement may need every required currency. If two rates fail validation, do we reject the batch, publish an incomplete set, or retain yesterday’s values? Each choice changes subsequent calculations.

We also need to know whether a feed supplies replacements, incremental changes or corrections. Absence may mean deletion in a complete replacement and no change in an incremental feed. Where the supplier promises a sequence, receiving batch 42 without 41 is a separate failure. A delayed earlier batch needs an explicit rule about whether it can supersede newer data.

The policy can include an error threshold or a hold for review. Record the measurements, rules and thresholds used, and any authorised release decision with its actor and reason. Distinguish valid records withheld by a rejected batch from those that failed their own checks.

Where the transactions go

Those decisions determine the transaction boundaries. Receiving a batch records what arrived; validation records what we found; application changes the business state. The first two can commit even when the third is refused, so rejection leaves its evidence available. For a modest batch, the final transaction can apply all accepted changes and their provenance together. A larger replacement might be prepared as an inactive version and published through one atomic change to which version is current. Readers then see a deliberate boundary instead of an accidental consequence of processing in chunks.

A delivered file and its control file pass into three dashed transaction
boundaries in turn. Receive records the batch identity and source, the receipt time,
the original payload and the counts against the control file. Validate records every
problem for each record, measurements of the whole collection, the rules and
thresholds used, and the decision to accept, hold or reject. Only if the batch is
accepted does apply run, committing the business changes, the links to their source
records and the mark that the batch was applied, all together. A bracket under the
first two notes that they commit even when applying is refused, so the evidence of
what arrived survives. — Receiving and validating commit on their own, so a refused batch still leaves the evidence of what arrived and what was found. Applying commits the business effect together with the record of where it came from.

Within one database, the business effect and the record that it was applied must commit together. Effects outside that transaction boundary need their own stable identity so the receiver can recognise retries.

Suppose an import commits its changes, then crashes before recording completion. A retry may apply them again. Record completion first, and work can be marked done when its changes never committed. A stable batch or item identity, enforced with suitable uniqueness or concurrency protection, lets a retry recognise committed effects. Concurrent runs must also coordinate so two instances cannot apply the same work. A supplier identifier, and sometimes a content hash, can expose the same delivery under another filename. Corrections get a new batch identity; retries keep the logical identity and add attempt history.

Application-time checks still matter. An account can be open when validation runs and closed before the batch is applied. The applying transaction must check and protect mutable facts on which its decision depends, using an appropriate constraint, row lock or isolation level rather than a separate unprotected read.

Explaining a value

Return to the exchange rate in yesterday’s valuation. A source-batch identifier on today’s rate explains today’s value. Yesterday’s calculation needs the input version it actually used. A calculation should select the published version once, use it throughout and record it with the result; otherwise a version switch can create the same inconsistency we will shortly see in an export. If a mapping or transformation changed the supplied value, its version may matter too. For large reference sets, recording the published version is often enough; provenance need not link every result to thousands of individual rows.

SQL Server’s temporal tables can preserve previous rows automatically. Oracle offers Flashback Query, dependent on retained undo, and Flashback Data Archive for longer retention. These preserve history; we still supply source links and identify the input versions calculations used.

Effective date, receipt time and publication time answer different questions: a rate effective yesterday may arrive today and be accepted tomorrow. Preserve those distinctions for as long as the business needs to explain its results; database history timestamps should not be mistaken for publication time. Raw payloads, especially those containing personal data, also belong in the retention and deletion policy alongside business records.

Four boxes linked left to right. Yesterday's valuation, run on 25 September,
recorded that it used rates version 17. Rates version 17 was published at 09:30 that
morning from batch 2291. Batch 2291 holds the rate's effective date of 24 September,
its receipt at 06:10 on 25 September, its validation results and its release
decision. The original payload, kept as received, holds the supplied record. Below, a
dashed grey path shows the tempting shortcut: looking up today's rate, version 18
from batch 2304, which explains today's value but not yesterday's result. — The question is answered by links recorded at the time, not by looking up the value as it is today.

What we sent

Now follow the valuation into the file sent to the counterparty. Suppose we query its header total, then its detail records, then a summary. Another transaction commits a correction between those queries. Under read committed, the header can describe the earlier state while the details describe the later one. Every query succeeds, yet the file disagrees with itself.

The extraction needs one consistent view. In SQL Server, I would normally use SNAPSHOT, enabled through ALLOW_SNAPSHOT_ISOLATION, to keep that view across queries without shared data locks. Oracle’s SET TRANSACTION READ ONLY and PostgreSQL’s REPEATABLE READ also provide a transaction-wide snapshot.

Above, read committed: the export queries its header total, then its detail
records, then a summary. A correction commits between the header and the details. The
header reflects the state before the correction and the details and summary the state
after it, so the file shows a header total of 1,000,000 against details summing to
1,002,500. Every query succeeded. Below, one snapshot: the same three queries sit
inside one dashed boundary whose view is fixed when it begins. The correction commits
during the export but is not seen, and all three parts of the file agree. The
captured result is retained and the boundary closes before the file is sent, so
transmission, acknowledgement and any resend happen outside it, using the retained
file. — Under read committed every query succeeds and the file still contradicts itself. One snapshot fixes the view for all three; closing it before transmission keeps a slow send from holding it open.

PostgreSQL’s SERIALIZABLE READ ONLY DEFERRABLE adds a stronger guarantee when the relevant writers also use SERIALIZABLE: it may wait at the first data query for a safe snapshot, then run without serialization failures.

A consistent database snapshot can still catch a business process between separately committed steps. The export may therefore need to select a completed batch or published business version as well. Long-lived snapshots also consume versioning resources or delay cleanup, another reason not to hold one open during transmission.

Capture the export’s values and provenance in a durable result associated with its identity: perhaps immutable export rows rendered into a file, or a retained file carrying that identity. Apply the same collection checks used on imports: unexpected volumes, empty output, inappropriate all-zero or all-null columns, and business totals. Retain the measurements and review decisions. When capture is complete, close the source transaction before transmission. Generate control metadata from the finished output and publish the completion signal only after the data file is fully available. The retained payload then shows exactly what we sent.

Delivery has its own history. Resending the retained payload and generating a fresh export are different operations. A lost connection can leave receipt uncertain; the receiving system’s acknowledgement and duplicate-handling rules govern a safe retry. Record separately what was prepared, transmitted and acknowledged.

The history has users

This history has users. They need to follow a rate into the calculations that used it, inspect rejected records, compare batch measurements and investigate work held for review. The screens are familiar: batch lists, record inspection, validation results, source links and authorised actions for retrying or replacing work.

Expected arrivals belong here too. Every completed import can be successful while today’s essential feed is missing. Since no event occurs when a file fails to arrive, a scheduled check must compare expected deliveries with actual receipts, allowing for the supplier’s delivery calendar. A useful batch view shows what should have arrived, when it was due and how stale the usable data has become. The business decides whether dependent calculations may proceed. Receiving yesterday’s file again should not make its contents count as today’s data.

A practical test

Pick a value in a result produced last month. Find the input version, source record and batch, and explain the checks and decisions that made it eligible for use. Then choose an export and show exactly what left the system and the evidence of its receipt. If processing stopped just after a commit, could a retry safely determine what remained?

If answering those questions requires finding the developer who remembers how the job worked, some of the business system’s essential history is still being stored in the wrong place.