Settlement Data Ingestion and Normalisation Across Multiple PSPs

Scope note. This article is about the pipeline problem created by running settlement data through more than one provider — not the anatomy of any single provider's file. The Stripe vs Adyen settlement file reference owns the universal settlement object model, gross-to-net treatment, and the field-level mapping for those two providers; this piece does not repeat it. The PSP reconciliation failure runbook owns break taxonomy, triage, and escalation; this piece covers where breaks originate in the pipeline, not how to resolve them once found. What's left, and what this article covers, is the layer underneath both: how you take settlement data from N providers with N shapes, on N schedules, and turn it into one dataset you can reconcile and report from at all.

What Changes When You Add a Second Provider

A single-PSP pipeline is mostly a parsing problem: read one file format, on one schedule, map it into your ledger. Add a second provider and the problem changes shape, not just size. The same economic event — a payment, a refund, a chargeback — now has to be recognised as the same event when it shows up in a different file format, on a different delivery schedule, keyed by a different identifier system, denominated with a different fee-line granularity, and cut off at a different time in a different time zone.

None of that is solved by writing one more parser. A parser reads a file. A pipeline has to decide, for every incoming file, whether it's been seen before, whether it supersedes something already ingested, whether it's missing pieces, and whether the period it covers can be considered closed — questions that don't exist when there's exactly one source of truth to trust. This article covers those decisions roughly in the order an operator has to make them: what shape to normalise into, what to join transactions on, what controls the ingestion layer needs to survive real-world provider behaviour, and what breaks when you get any of it wrong.

Canonical Schema Design Across Providers

The core decision is not what fields to store — the universal settlement object model already defines those: identifiers, the gross-to-net money chain, and the distinct date fields. The decision specific to a multi-provider pipeline is architectural: build one provider-neutral internal representation every provider's file maps into, and keep the mapping layer strictly separate from the canonical layer itself.

Each provider gets its own adapter — logic whose only job is translating that provider's field names, record types, and sign conventions into your canonical schema's vocabulary. The canonical schema never learns a provider's vocabulary, and a provider's vocabulary never leaks past its own adapter. When a provider changes a report format, adds a column, or gets replaced entirely, only that one adapter changes; everything downstream — reconciliation, reporting, the general ledger feed — doesn't move.

That separation trades upfront design cost for long-run stability. Skipping the canonical layer and reconciling directly against each provider's native format ships faster for one or two providers, but every downstream consumer ends up coupled to every provider's field names simultaneously — adding a third provider means touching every consumer, not writing one adapter. The canonical-schema approach inverts that: the fixed cost is a mapping layer per provider, and the payoff is that adding, removing, or replacing a provider becomes a bounded, one-adapter change.

A second decision sits inside the canonical schema: how granular to make the record-type taxonomy. Collapse too aggressively — one generic "adjustment" bucket for every provider's version of a reserve movement, a fee true-up, and a manual correction — and you lose the ability to route different types to different ledger accounts or review queues. Split too finely, mirroring every provider's own vocabulary one-for-one, and you've rebuilt N schemas with extra steps. The taxonomy should be exhaustive enough that every provider's record types map onto it without a lossy catch-all, and no finer than that.

Reconciliation Keys: Stable, Provider-Scoped, Network-Level, Synthesised

Which identifier you join on determines what you can and cannot match, and not every identifier in a settlement file plays the same role.

Provider-scoped identifiers — a provider's own transaction ID, its batch or payout ID — are the correct join key inside that provider's own data, and should always be persisted as a foreign reference. But they're meaningless outside that system: they don't appear on a bank statement, don't survive a provider migration, and a second provider's identifier of the same conceptual type shares no relationship with the first provider's.

Network-level identifiers — the Acquirer Reference Number, or where a card network doesn't support ARNs, the System Trace Audit Number or Retrieval Reference Number — sit above any single provider, assigned by the card network or acquiring bank rather than the PSP. That makes them, in principle, the most portable key available: the same scheme reference exists whichever PSP processed the transaction. In practice they come with real constraints. Stripe's own documentation states it can take up to seven business days after a refund is initiated before the ARN is available from downstream banking partners, and that no ARN is produced when a refund posts as a reversal rather than a full refund cycle. A network-level reference is a strong match key once it exists — it isn't one you can rely on being present at ingestion time, and a pipeline that requires it upfront will stall on every transaction that hasn't reached that stage yet.

Your own merchant or order reference is the identifier you control, and has to be the backbone of cross-provider joining, because it's the only one guaranteed to exist consistently regardless of provider. Generate it once, at your own system, before the transaction reaches any PSP, and pass it through as the merchant-reference field every provider's API and reports expose. That single design choice is what makes a canonical schema joinable at all — without it, the pipeline is reduced to hoping providers' own IDs and settlement timing happen to line up, which is exactly the fragile matching that produces false breaks.

The practical hierarchy: join on your own merchant reference first — it should resolve almost every match. Fall back to the provider's own transaction ID for investigation inside a single provider's data when the merchant reference is missing or mangled. Reserve the network-level reference for what the other two can't resolve — cross-provider volume checks, provider migrations, a dispute where the bank-side reference is the only thing the cardholder's bank will accept — and build ingestion to accept it asynchronously, days after the original record, rather than requiring it upfront.

Duplicate Delivery and Idempotent Ingestion

Both settlement files and the webhook or API events layered alongside them can arrive more than once, and provider documentation treats this as expected behaviour, not an edge case. Stripe's webhook documentation states directly that "webhook endpoints might occasionally receive the same event more than once," recommending deduplication by logging processed event IDs — and notes that in some cases two separate Event objects are generated for what is conceptually one change, so a robust check also keys off the underlying object's ID plus the event type. Adyen's documentation describes the same pattern from its side: a webhook is placed in a retry queue if Adyen doesn't receive a response within 10 seconds, and duplicate events are identified because they carry the same eventCode and pspReference while other fields, including the event date, can differ — Adyen's guidance is to use the details from the latest event received.

File delivery carries the same risk through a different mechanism: an export job retries after a timeout and delivers the same period's file twice, an SFTP pickup runs against a directory a second time before the first run's processed-marker is written, or a manual re-run during an investigation resubmits a file nobody flagged as a duplicate. None of these are malicious or rare — they're the ordinary failure modes of file delivery over an unreliable network, the same category the webhook documentation above describes for events.

The fix is the same shape in both cases: make ingestion idempotent by keying off content, not delivery. For a file, compute a stable identity for it — a hash of its content, or the provider's own report/batch identifier if that reliably stays constant across re-deliveries — and check that identity against what's already ingested before processing a row. For events, track processed event IDs and treat a second delivery of an already-processed ID as a no-op. Neither requires trusting a provider to send a file or event exactly once, which is the assumption that breaks first once volume grows and something upstream inevitably retries.

Corrected and Restated Files: Append-Only Beats Overwrite

A provider re-issuing a report for a period you've already closed needs a different response than a duplicate delivery. A duplicate is the same file twice; a corrected file is a genuinely different file — different totals, different rows, sometimes a different record count — claiming to describe a period your ledger already closed against.

Overwriting the old data with the new is the wrong instinct, for the same reason accounting systems generally reject overwrite-in-place corrections: the moment you overwrite, you lose the ability to answer what your books actually said on the day you closed that period. That matters because downstream artefacts — a monthly close, a tax filing, a report already sent to a board or investor — were built against the original numbers, and a silent overwrite makes it impossible to reconstruct why a later figure moved.

The alternative is append-only ingestion with explicit supersession: the corrected file is ingested as a new, distinct version linked to the original it corrects, and the original is marked superseded, not deleted. Your canonical schema needs a version or as-of field on every settlement record for this to work, not just a period field, so a query can ask either "what does the corrected record show" or "what did we believe on the day we closed it." The delta between versions is worth computing and storing explicitly — it's what you actually explain when someone asks why last month's numbers changed.

This is a design principle, not a specific provider's documented behaviour — providers handle corrections through different mechanisms, and some route a correction through a later period's true-up line rather than reissuing the original file (a pattern the Stripe vs Adyen reference covers for Adyen's monthly invoice true-up). The architectural point holds regardless of mechanism: whatever changes a closed period's numbers lands in your ledger as a new, dated, linked entry, never a silent replacement.

Missing Reports, Partial Files, and Schema Drift

Detecting a bad file is comparatively easy — it leaves evidence: a row that fails a sanity check, a total that doesn't tie out, a null where one shouldn't be. Detecting an absent file is harder, because absence leaves nothing to validate. If a provider's export silently fails, an SFTP job gets misconfigured after a routine credential rotation, or a provider quietly changes its delivery schedule, the result looks identical to a quiet day with genuinely low volume.

The only defence is a positive expectation, checked independently of what shows up: maintain your own record of what you expect from each provider and when, and alert on the absence of an expected file, not just on errors in files that arrived. That expectation has to come from operational knowledge of each provider's cadence, not be inferred from historical patterns — an inferred expectation degrades exactly when it's needed most, right after a provider silently changes its schedule.

Partial files are a related failure: the file arrives on schedule, but a network interruption or a timed-out export leaves it incomplete. A partial CSV can look valid — parseable, well-formed rows — right up until it stops. Cross-check row counts and control totals where the report includes them, and treat a failed cross-check as not-yet-ingested rather than partially ingested; a half-loaded period is worse than one you're still waiting on, because it looks complete to anyone downstream who doesn't check.

Schema drift is the quietest failure, most likely to corrupt data silently rather than block ingestion. A provider adds a column, renames one, or changes an enum's valid values, and a pipeline built to read positionally keeps running — just wrong. Make the adapter layer strict: validate the expected column set and enum values on every file, and fail loudly on anything unrecognised rather than passing it through. A loud failure is solvable and visible; a silent pass-through is a data-quality issue nobody notices until a downstream total stops tying out.

Batching Cadence and Cut-Off Time Zones Break Naive Daily Reconciliation

A reconciliation process built around "today's file covers today's transactions" works only if every provider settles on a strict daily cycle. That assumption doesn't hold in practice — settlement frequency varies by provider and contract, and at least one major provider consolidates several calendar days into a single batch on a recurring basis, as the reconciliation runbook documents. A naive daily join will show a break every time a provider's batching cadence doesn't line up with a calendar day, even though nothing is actually wrong — and in a multi-provider pipeline that effect compounds, because different providers batch on different cycles simultaneously.

A related but distinct problem sits underneath cadence: where the boundary of "today" falls at all. A provider's settlement day is defined by its own cut-off, in its own configured time zone, which rarely lines up with midnight in your own ledger's time zone. For an operator running providers across regions, transactions captured at the same instant can land in "Tuesday's" file for one provider and "Wednesday's" for another, because each provider's cut-off falls at a different point relative to it — a transaction at 11pm can land on opposite sides of two providers' cut-offs, producing two files that each look internally correct while disagreeing about which day the same transaction belongs to.

The fix for both: reconcile against the provider's actual batch or payout identifier, never against calendar date, and store every date field as a full timestamp with an explicit time zone, normalising to a single reporting time zone only at report-generation time — never earlier, and never by discarding the original provider time zone on ingest. Hold each provider's batching cadence and cut-off as explicit, per-provider configuration your ingestion and reconciliation logic both read from, not an assumption baked into the join logic — a provider changing its cadence should be a configuration update, not a code change, and discarding time zone early makes a cut-off discrepancy invisible exactly when you need to explain it.

Event-Stream vs Settlement-File Reconciliation: Two Sources of Truth

A modern PSP integration usually produces two independent signals for the same event: the webhook or API event stream, firing near real time as the PSP's system state changes, and the settlement file, reporting what actually processed and paid out on the provider's batch schedule. Treating these as the same signal arriving twice, rather than as two sources with different guarantees, is where a lot of ingestion design goes wrong.

The event stream's value is speed — often the earliest signal something happened, useful for fulfilment or flagging an anomaly quickly. Its cost is that provider documentation is explicit it doesn't offer strong delivery or ordering guarantees: Stripe states it does not guarantee webhook events arrive in the order generated, and duplicate delivery is expected behaviour to handle, not an occasional bug. Adyen's documentation describes the equivalent from its side — a retry queue that can redeliver, with duplicates distinguishable only by comparing event code and reference fields against what's already processed.

The settlement file's value is the opposite trade-off: it's the provider's actual accounting record, and the one you close a period against. Its cost is latency — it arrives on a batch schedule, by construction behind the event stream, sometimes by hours, sometimes by the multi-day cycles above.

The practical resolution: use the event stream to detect and start investigating quickly; use the settlement file as the record you reconcile and close against. A transaction the event stream reports as succeeded, but the settlement file never confirms within the provider's expected window, is a break — the event stream doesn't override an absent settlement record regardless of how confident the earlier signal looked. Store both signals against the same canonical transaction record rather than letting one overwrite the other; a mismatch between them is exactly the exception the pipeline exists to surface, not a nuisance resolved by picking whichever arrived first.

Fee and FX Line Normalisation Across Providers

Providers report fees at meaningfully different granularity, and normalising that gap is a canonical-schema decision distinct from the field-level provider mapping already covered in the Stripe vs Adyen reference — that piece maps each provider's actual field names; the decision here is what your schema does with fee lines once every provider's breakdown has been read.

Normalise too aggressively — collapsing every provider's fee detail into a single net figure at ingestion — and you lose the ability to validate a fee against a rate card or explain a variance to finance, for any provider whose native report was more granular than your schema kept. Preserve too little structure the other way — storing each provider's breakdown under that provider's own field names — and cross-provider fee comparison becomes a manual exercise every time. The workable middle: a canonical fee-component taxonomy generous enough to hold the most granular provider's breakdown without a lossy catch-all, where a provider reporting only a single net figure simply populates fewer components than one that itemises interchange, scheme fees, and markup separately. FX line items follow the same pattern — the FX mechanics themselves, where conversion happens in the chain and who bears the spread, are covered in the FX and cross-border settlement architecture reference; this pipeline should treat an FX line as just another canonical fee component, not a special case.

Break Management at the Data Layer

The reconciliation runbook owns break taxonomy and resolution; the data layer's job sits upstream: deciding which unmatched items need a human today, which should age silently, and which are small enough to clear automatically.

Ageing matters because a fresh unmatched item and a month-old one are different problems even with an identical underlying cause. A transaction not yet in any settlement file one day after capture is very likely just timing; the same transaction unmatched sixty days later is a different situation — carry that as an explicit ageing bucket per provider, set against that provider's own typical cadence rather than one blanket threshold across the whole pipeline.

Materiality thresholds serve a similar purpose in a different dimension: a few currency units of variance on volume in the millions is very likely FX or rounding, not a genuine break. An explicit tolerance band, auto-cleared to a dedicated rounding account rather than left open, keeps the exception queue reserved for items that actually need judgment.

What the data layer must never auto-clear, regardless of amount: anything touching a reserve, a chargeback, or an unexplained deduction with no corresponding transaction. Those carry information — a reserve schedule, a dispute deadline, a possible fraud signal — a materiality threshold has no way to evaluate.

Data Retention, Restatement Windows, and Provider Migration

How long to retain raw files, and how far back a restatement can reach, are questions this piece deliberately doesn't answer with a number. Retention obligations and any restatement window a provider contract permits are set by your regulatory environment, your auditors, and each provider's specific terms — they vary by jurisdiction and provider, and a number invented here would be wrong for most readers. Derive both from your own provider contracts and compliance function, and store the answer as an explicit, per-provider policy value the pipeline reads, not an assumption embedded in code.

What the design does need, independent of that number: keep raw provider files — not just the parsed output — for at least as long as the longest restatement window any provider you use can invoke. A corrected file arriving after the raw original is discarded leaves you unable to compute the delta the supersession model above depends on, quietly breaking append-only design exactly when it matters.

Provider migration is the same retention question from another angle. Adding a new acquirer is, in a well-designed pipeline, one new adapter — the payoff described in the canonical-schema section. Leaving a provider is the harder direction: its historical data doesn't disappear from your obligations because it stops flowing in. It has to remain queryable in canonical form, under its original provider attribution, for as long as retention policy requires — a durable provider-identity field on every historical record, and an ingestion pipeline that keeps working as a read path after a provider's write path is decommissioned.

Controls and Monitoring for the Pipeline

The pipeline itself needs monitoring as a system, not just the transactions flowing through it. Track file-delivery timeliness per provider against the expected-delivery record above, not just whether a file eventually arrived — a provider consistently three hours late is a different risk than one usually on time that missed once, even though both technically "arrived."

Track match rate as a first-class metric, broken out by provider rather than blended into one site-wide number. A blended rate can look healthy while one provider's data quietly degrades, masked by strong performance elsewhere — the same structural failure multi-MID chargeback-ratio pooling produces, covered for a different metric in the multi-entity, multi-MID architecture reference: a blended number hides a problem in one segment behind good performance in the others.

Alert on schema-validation failures and missing-file detection as distinct classes from ordinary unmatched-transaction exceptions — they represent a broken adapter or delivery channel, not a genuine reconciliation break. Version every adapter change with a changelog entry tied to its go-live date, so a sudden shift in match rate can be correlated back to the change that caused it rather than treated as a mystery.

Common Failure Modes

Joining on a provider's own transaction ID as if it were universal. Correct inside that provider's data and nowhere else; a pipeline that treats it as portable across providers or a migration will silently fail to match once a second provider enters the picture.

Overwriting a closed period when a corrected file arrives. Destroys the ability to explain why a number changed, and breaks any downstream artefact built against the original figures.

Treating "no file received" as "no activity" without an independent expectation to check against. A silently failed delivery and a genuinely quiet period look identical unless the pipeline maintains its own record of what should have arrived.

Reconciling by calendar date instead of batch or payout ID. Breaks the moment any provider batches across more than one day, and multiplies across providers batching on different cycles.

Letting the event stream override an absent settlement file. It's a fast signal, not a settlement instruction; a transaction with no confirming settlement record is a break regardless of what the webhook reported.

Discarding provider time zone at ingestion. Once normalised away, a cut-off discrepancy between providers becomes invisible, with no way to explain after the fact why a transaction landed in what looks like the wrong period.

Skipping raw-file retention because the parsed output looks complete. A restatement or delta calculation against a superseded version depends on still having the original file.

What to Read Next

• Stripe vs Adyen settlement file reference — the universal settlement object model and field-level mapping this article deliberately does not repeat.
• PSP reconciliation failure runbook — break taxonomy, per-PSP matching-ID chains, and the escalation process once a break is found.
• FX and cross-border settlement architecture — where currency conversion actually happens in the transaction chain and who bears the spread.
• Multi-entity, multi-MID payment architecture — how MID count multiplies the number of settlement feeds a pipeline has to ingest in the first place.
• Multi-acquirer routing — the routing-layer decision that determines how many providers' settlement data this pipeline has to ingest at all.