Making an SFTP file drop integration survive a badly timed upload

The overnight load reported success at 03:04 and imported 10,374 registrations. The vendor's support desk says the extract contained 18,442. Nobody has been paged, nothing turned red, and the discrepancy surfaces four days later when somebody notices the weekly total looks light.

The file on disk is 10,374 complete rows and one truncated line. The loader read a file that was still being uploaded, stopped when the stream ran out, and did exactly what it was built to do. This is the characteristic failure of an SFTP file drop integration, and it is worth solving properly because plenty of event vendors still deliver by scheduled file rather than an API, and they will still be doing so in five years.

What happens when the loader opens a file that is still being written?

Work the timing. The vendor's export job starts at 02:58. The full extract is 640 megabytes, and the link between their host and your server sustains around 3 megabytes per second, so the upload takes about 213 seconds and finishes at 03:01:33.

Your loader runs at 03:00:00. At that instant roughly 120 seconds of writing has happened, so around 360 megabytes of 640 are present, which is 56.3 per cent of the file. Applied to 18,442 rows that is about 10,374 rows plus a partial one, which matches what the load reported.

Nothing here is a bug. Both jobs did their work correctly and the schedule was the only thing wrong. That is why the failure recurs: it depends on file size, link speed and load, so it appears the week the extract grows past some threshold, and it disappears again when somebody re-runs the load by hand at 09:00 and gets a complete file.

The response most teams reach for first is to move the cron to 04:00. That buys a season. Extracts grow, links get congested, and the vendor moves their job to accommodate their own maintenance window, and none of those changes will be announced to you.

Why does a rename fix it, and what does atomic mean here?

The durable fix is for the sender to write the file under a temporary name and then rename it to the name your watcher is looking for.

The property that makes this work is specified rather than folklore. The Open Group Base Specifications Issue 8, IEEE Std 1003.1-2024, requires that when a rename replaces an existing entry, "a directory entry named new shall remain visible to other threads throughout the renaming operation and refer either to the file referred to by new or old before the operation began" (The Open Group, 2024). A reader therefore sees one complete file or the other complete file. There is no interval in which it sees half of one.

Two conditions attach to that. The rename must happen within the same filesystem, because a rename across filesystems degrades into a copy followed by a delete, which reintroduces exactly the window you were closing. And the temporary name must be one your watcher ignores, which is usually done with a dot prefix or a distinct extension, so a directory listing during the upload does not pick up the partial file under a name that looks eligible.

The practical instruction to give a vendor is one sentence: write to .incoming/filename.csv.tmp in the same directory as the target and rename to filename.csv when the write completes.

The rename trap in the protocol itself

There is a catch specific to SFTP that catches people who have only done this on local filesystems.

The SSH File Transfer Protocol specification states, of the rename operation, that "It is an error if there already exists a file with the name specified by newpath", and adds that "The server may also fail rename requests in other situations, for example if 'oldpath' and 'newpath' point to different file systems on the server" (Ylonen and Lehtinen, 2001). A rename onto an existing path is therefore an error under that version of the protocol, not an overwrite, and the behaviour on your vendor's server is a question worth asking rather than assuming.

Two consequences follow. First, a drop pattern that reuses one fixed filename every night will fail on the rename the moment yesterday's file is still sitting there, and the vendor's job will either error or, worse, fall back to a direct write. Second, the same-filesystem constraint that POSIX implies is written into the protocol as a permitted failure, so a chroot layout where the staging directory and the watched directory sit on different mounts will fail in a way that looks intermittent.

Both are solved by the same convention. Use unique filenames carrying the business date and a sequence number, keep the staging directory inside the same mount as the target, and have the consumer move the file into an archive directory after a successful load so the drop directory never accumulates.

The manifest, and the arithmetic it lets you do

Atomic delivery closes the partial-read window. It does nothing about a vendor whose extract itself was short, which happens when their source query times out or a filter is misconfigured, and that failure produces a complete, well-formed file containing the wrong number of rows.

The answer is a manifest written after the data file and named to pair with it. Six fields are enough: data filename, row count excluding the header, byte count, checksum, business date, and a sequence number that increments every run.

Now the load can check itself. Read the manifest, load the data, count what you loaded, compare. In the case above the manifest would say 18,442 and the loader would count 10,374, a shortfall of 8,068 rows or 43.7 per cent, and the job would fail loudly at 03:04 rather than four days later. Even a 12 row shortfall gets caught, which matters because small shortfalls are the ones that never get noticed and always turn out to be the same 12 records.

The sequence number earns its place separately. A file that never arrives leaves no evidence at all: no error, no partial file, nothing but an absence. If last night was sequence 214 and tonight is 216, you know 215 is missing before anybody asks. Without it, a skipped night is invisible until a monthly total comes in low, and by then the vendor's own retention window may have closed.

There is a cheaper version of the same idea for vendors who will not write a manifest, and it is worth knowing because plenty of them will not. Ask for a zero-byte sentinel file, dropped after the data file and named to match it, such as registrations-2026-10-04.csv.done. Your watcher looks for the sentinel and only then reads the data file. It gives you the completion signal without the counts, so it closes the partial-read window and leaves the short-extract problem open. That is roughly half the protection for about ten minutes of the vendor's engineering time, which makes it the right ask when the alternative is nothing at all.

What else belongs in the drop contract

A file for every scheduled run, including empty ones. A zero-row file with a manifest saying zero is a positive statement that nothing happened. Silence is ambiguous.

A checksum. Cheap to produce, cheap to verify, and it catches the corruption that a row count cannot.

A stated encoding and a stated line ending. Those cause a different class of problem once the file loads, and encoding failures in an exhibitor or registration file deserve their own attention.

Notice of column changes. A file drop has no version negotiation, so a new column arriving without warning is a schema change delivered by surprise, and surviving that mid season needs a detection layer of its own.

A right to the data in the first place. Portability sets a floor worth quoting in negotiation. The GDPR gives a data subject the right to receive their personal data in "a structured, commonly used and machine-readable format" and to have it transmitted to another controller where technically feasible (EU, 2016). That is a right held by individuals rather than by you, and it is still a useful reference point when a supplier suggests that a nightly extract is a premium feature. The terms you actually need go further, and writing export rights into the contract is the place to settle them.

Where this stops

None of this makes the file correct. Atomic delivery and a manifest tell you that you received everything the sender intended to send, which is a smaller claim than it sounds and the only one this design can enforce. A vendor whose extract query silently excludes cancelled registrations will produce a perfectly delivered, perfectly counted file that is wrong in the same way every night, and the manifest will agree with it.

The second limit is that a manifest is only as trustworthy as the process that writes it. If the vendor generates the manifest from the same query that generated the file, rather than by counting the file after writing it, the two will agree even when the query returned half the data. Ask which of those two things their job does. It is a five word question and the answer changes how much the check is worth.

This week, look at one existing file drop. Check whether the sender renames into place or writes directly, and check whether anything downstream compares a declared row count against a loaded row count. If either answer is no, the request to the vendor is short, it costs them almost nothing, and it converts a silent failure into a loud one, which is the trade you want on every feed in the integration layer.