What the existing flat file integration actually guarantees

Before anything is rewritten, the existing flat file integration needs to be described properly. In most organisations it is not documented anywhere beyond a scheduled task, an SFTP credential and a folder that someone checks when a figure looks wrong. The file itself has an implicit contract: a fixed column order, a particular date format, a convention about whether the export is a full snapshot or only the records that changed since the last run, and a rule about what happens when the job fails. None of this is written down, but all of it is depended upon downstream. Replacing the mechanism without first capturing the contract is how a project ends up producing a faster version of the wrong behaviour.

Read a fortnight of real files, not a sample. Line counts tell you whether the export is a snapshot or a delta. Repeated primary keys across consecutive days tell you whether the receiving system is expected to upsert or insert. Blank columns that are populated in one file out of twenty usually mark an edge case nobody has mentioned: a credit note, a drop-ship line, a manual adjustment made by finance at month end. Where a Sage 200 import template or a Mintsoft order feed is involved, the file may also carry fields that exist purely to satisfy a validation rule, with no business meaning at all.

The most valuable thing a nightly batch provides, and the thing most often lost in a move to real time, is a clean boundary. Everything that happened yesterday is in one file. If the figures disagree, you have a single artefact to point at. Real-time processing dissolves that boundary into a stream of individual events, and unless you deliberately recreate the boundary somewhere, your finance team loses the only reconciliation tool they had. That is a design requirement, not a nice-to-have, and it should appear in the scope document.

  • Snapshot or delta — Does each file contain the complete current state, or only records changed since the previous run? Deltas silently drop records when a run is missed; snapshots do not, but they are expensive and hide deletions unless the receiver reconciles the full set.
  • Failure behaviour — If last night's job did not run, does today's file cover two days or one? The answer determines whether a replacement can be event-driven at all without a separate catch-up path.
  • Ordering assumption — Batch files are usually sorted, so a header line arrives before its detail lines. Events arrive in whatever order the network allows.

Deciding what real time needs to mean, field by field

Real time is not a single setting. Different data in the same feed has different tolerance for staleness, and treating it uniformly produces either an expensive system or an unsatisfying one. Stock availability presented on a Shopify storefront typically needs to be within a minute or two, because the cost of a stale figure is an oversell and a customer apology. Sales order headers posted into an ERP for picking need to arrive in minutes. Nominal postings into Sage 200 almost never need to be instantaneous; they need to be complete and correctly dated, and a fifteen-minute cycle is usually indistinguishable from instant to everyone who reads the ledger.

Write the latency budget down per entity, with the business consequence of breaching it. This has two practical effects. It keeps the build proportionate, because you are not engineering sub-second delivery for data that is read once a day. It also gives acceptance testing something measurable: an auditable statement that order creation to warehouse visibility is under three minutes at the ninety-fifth percentile is a test, whereas 'real time' is an argument waiting to happen six weeks after go-live.

The budget also exposes dependencies that the nightly file was quietly absorbing. If a customer record must exist in Microsoft Dynamics NAV before an order line can be posted, and customer creation is still on a daily cycle while orders have moved to minutes, you have manufactured a race condition that the batch never had, because the batch processed both in a fixed sequence. Either both move together, or the order path needs an explicit create-if-missing step with its own rules about which fields are authoritative.

Finding a change signal to replace the overnight sweep

A nightly export usually works by sweeping a table for rows modified since a watermark, or by dumping everything. Moving to real time means finding a signal that fires when something changes. In order of preference: a native webhook from the source platform, a change-data-capture mechanism or trigger-populated outbox table in the source database, or frequent polling against a reliable modified-timestamp index. Shopify and Mintsoft both offer webhook delivery for the common events. An ageing on-premise application may offer none of this, in which case polling at short intervals against an indexed timestamp column is a legitimate answer, provided the index exists and the query does not degrade under load.

Webhooks are a notification, not a payload you should trust. The sensible pattern is to treat the webhook as a prompt to fetch the current state of the entity by its identifier, then process that. This removes the entire class of problems caused by out-of-order delivery, because a late event simply triggers another fetch of the same, now current, record. It also means a missed webhook is recoverable: a low-frequency reconciliation sweep, perhaps hourly, catches anything the notification layer dropped.

Where the source cannot emit anything useful, an outbox table is the most durable option. A database trigger writes the entity type, the identifier and a sequence number into a small table; the integration reads it, processes, and marks the row complete. The source application is untouched, the change signal is transactional, and the pending row count becomes a monitoring metric that anyone in operations can understand. The cost is a database change in a legacy system, which needs to be agreed with whoever supports it before it reaches the fixed scope.

What breaks when the nightly reset disappears

Batch processing is forgiving in ways that are easy to overlook. A duplicate row in a file is usually removed by a de-duplication step or overwritten by the last occurrence. A transient failure means the whole file is reprocessed tomorrow. A wrong value gets corrected in the next full snapshot. Event-driven processing has none of these safety nets, so the properties that batch supplied by accident must now be supplied deliberately: idempotency keys so the same event processed twice produces one outcome, explicit handling of out-of-order arrival, and a dead letter path so a single poison record does not stall the queue behind it.

Volume profile changes too. A nightly job hits the receiving system once, in a controlled window, often at a time when nothing else is competing for it. Real-time delivery spreads the same volume across the working day, which is usually gentler, except at the peaks. A promotional hour on a Shopify store can generate more order events in twenty minutes than the previous batch moved in a day, and the receiving ERP will have its own throughput ceiling. Queue the work, rate-limit the writes to match what the destination tolerates, and accept that latency will stretch during a spike. That is a far better outcome than a half-posted batch.

Finally, think about deletions and cancellations. A full-snapshot export handles them implicitly, because a record that disappears from the file is understood to be gone. Event streams need an explicit cancellation or void event, and if the source does not emit one, you need a reconciliation sweep that compares identifiers and acts on the difference. This is the single most common gap we find in migrations away from snapshot files, and it usually surfaces in finance as orders that never close.

Running the file and the stream side by side

Do not cut over in one step. Keep the nightly export running and send the real-time path to a staging area or a shadow table for a defined parallel period, typically two to four weeks and always spanning a month end. Each morning, compare the two sets: record counts, totals by day, and a field-level diff on a sample. Disagreements in that window are cheap. Disagreements after cutover are incidents.

The comparison will find things neither side expected. Timezone handling is a frequent offender: a batch job that runs at 02:00 and selects 'yesterday' may be using server local time while the API returns UTC, which moves an hour of orders across a date boundary and misstates a daily total. Rounding differences on tax or discount lines show up here too, as do records the file silently excluded because of a WHERE clause nobody remembered writing. Each finding should be resolved and recorded against the mapping document rather than patched in code and forgotten.

When the parallel run is clean, the file does not have to be retired. Demoting it from a transport mechanism to a daily control file is often the right answer: the same export, written to an archive, used solely for reconciliation and dispute resolution. It costs very little to keep, it gives finance the end-of-day boundary that real time removed, and it provides a recovery path if the event pipeline is ever unavailable for an extended period.

  • Parallel period — Long enough to include a month end, a weekend and at least one failure of something.
  • Comparison artefacts — Daily counts and totals by entity, plus a field-level diff on a random sample. Store the results; they are the acceptance evidence.
  • Abort criteria — Agree in advance what level of discrepancy delays cutover, so the decision is made against a threshold rather than under pressure.

Scoping and sequencing the work so it stays fixed

The discovery stage for this kind of migration has a specific output: the documented file contract, the latency budget per entity, the chosen change signal for each source, and the reconciliation design. If those four things are not settled, an estimate is guesswork, because the difference between a platform with reliable webhooks and a legacy database needing an outbox table is substantial. We would rather spend the time proving the change signal works on real data than discover in week three that the modified-timestamp column is not updated by one of the three applications that write to the table.

Sequence the migration by entity rather than attempting everything at once. Reference data first, because masters must exist before transactions reference them. Then the highest-value transactional flow, usually orders or stock. Then the accounting postings, which are the most sensitive to correctness and the least sensitive to latency. Each entity gets its own parallel run and its own sign-off, and the nightly file continues to carry anything not yet migrated. The feed shrinks one column group at a time until what remains is the reconciliation copy.

On completion, the handover should include the code, the mapping document with every exception found during the parallel run, the runbook for replaying a failed event, and the monitoring thresholds. A real-time pipeline needs someone to notice when it stops, which a nightly file never really did, because a missing file was obvious by lunchtime. Defining the alert, the recipient and the expected response is part of the delivery, not an afterthought. If you want to discuss a specific export and what it would take to move it, our number is 01303 883111 and the address is hello@api-integrations.co.uk.

Key points

  • Document the existing file contract first: snapshot or delta, failure behaviour, ordering assumptions and the exceptions hiding in rarely populated columns.
  • Set a latency budget per entity rather than treating real time as one setting, and replace the batch's accidental safety nets with explicit idempotency, ordering and dead letter handling.
  • Run the file and the event pipeline in parallel across a month end, then demote the export to a daily reconciliation control rather than deleting it.