Some of your sources will never have an API and the design has to say so
Two or three systems in every estate will only ever send you a file. Designing for that means recording arrivals as facts, because this breaks by going quiet.
The pipeline was green, the dashboard was up, and the number on the front page had not moved since Thursday. Nobody noticed until the Tuesday, because a flat line for a few days is not obviously wrong in a business that has quiet weeks. The cause was a file. A partner's system had written its nightly extract into a slightly different directory after a server rebuild, and the job that read the old directory found nothing, did nothing, and reported success.
Almost every estate has two or three sources like this. A nightly drop on SFTP. A CSV that a regulator's portal will only give up to a human with a login. An export that arrives as an email attachment from someone at a haulier, because that is how it has arrived since before anyone currently employed there joined. Teams tend to treat these as a temporary embarrassment, a gap that will close as soon as the vendor ships something modern or procurement finishes a replacement.
They are not temporary. Some of them will outlast the warehouse, the BI tool and the team. Once you accept that, the design changes, and it changes in a specific direction: the arrival of the file becomes a fact you record and reason about, rather than an event you passively wait for.
Nobody is going to build you that API
It is worth being honest about why these sources stay the way they are, because the reasons are structural rather than lazy. The system may be a licensed product whose roadmap you have no influence over. The counterparty may be a public body, where the file format is defined in a circular and changing it requires a process rather than a decision. The data may cross an organisational boundary, in which case the file is not a technical inconvenience, it is the contract: both sides can point at it, archive it and argue about it. And in a fair number of cases the system is twenty years old, it works, and the operator has no commercial reason to touch it.
There is a subtler version too. An API exists, but it is paginated, rate limited and priced per call, so pulling three years of history through it is impractical and the vendor's own answer is a bulk export. In that situation you have an API on the brochure and a file in production, which is the worst arrangement of all if the design pretends otherwise.
The failure mode is silence
An API integration fails loudly. You get a 401, a 500, a timeout, a schema validation error, and something in the stack raises its hand. A file source fails by absence, and absence emits nothing. There is no error to catch because no attempt was made by anyone.
Orchestrators make this worse by accident. A task that lists a directory, finds no matching file and exits cleanly is a successful task. A sensor that waits and then times out often lands in a state that shows up as amber on a screen nobody has open at 3am. And for many real sources, zero rows is a legitimate business answer: a plant that did not run a batch that day, a depot with no despatches on a bank holiday. So the pipeline genuinely cannot tell the difference between nothing happened and nothing arrived, unless you give it a way to know what it was supposed to receive.
An API failure tells you it failed. A missing file tells you nothing at all, which is why the arrival has to be a fact you record rather than an event you wait for.
The pattern, stated plainly
Land the file first, then decide what it is
The first move is to stop parsing on receipt. Copy the file, unchanged, into a landing area, and keep it. The original bytes, the original filename, partitioned by the date you received it. Everything after that point reads from your copy, never from the source's directory, so a re-run three weeks later reprocesses exactly what arrived rather than whatever is there now. This is what the bronze layer in a medallion arrangement is actually for, and it is the part teams most often shortcut. In Antvia the landing area is deliberately dumb for the same reason: the value of a raw copy is that nothing clever has happened to it yet.
Alongside the copy, write a row. A small manifest table in Postgres, one row per file, is the single highest-value object in this whole pattern, because it turns a filesystem into something you can query and alert on.
- source and feed which counterparty and which named feed, because one partner often sends several unrelated files down the same channel.
- original filename and path kept verbatim, since the naming convention is frequently the only place the business date appears.
- received_at when it landed in your estate, in UTC, set by you and not by the sender.
- business date what period the contents claim to cover, parsed from the name or the header, recorded separately from received_at.
- size and checksum a SHA of the bytes, which is how you detect a redelivery, a truncated transfer and a silently changed extract.
- row count and status counted after parsing, with a status that moves from landed to parsed to loaded to rejected, and stays wherever it stopped.
Expectation is a row, not a schedule
Watching the folder is not enough, because a folder cannot tell you about a file that does not exist. The fix is to write down what you expect before it arrives. For each feed, generate expectation rows on a calendar: this source, this business date, expected between 22:00 and 02:00, tolerate late until 06:00, and here is who is called when it does not show. Then a single check compares expectations against the manifest and reports the gaps.
- 01Declare the feedCadence, window, expected filename pattern, expected schema, the calendar it follows, and what a legitimate empty file looks like.
- 02Generate expectationsAhead of time, one row per date the feed should deliver, so absence becomes a queryable state rather than a non-event.
- 03Land and stampCopy the bytes untouched, write the manifest row with received_at and checksum, match it to an open expectation.
- 04Parse and validateSchema, row count against the recent range, and the sanity checks that catch a header-only file or a truncated transfer.
- 05Close or escalateMark the expectation met, or, once the tolerance window passes, raise it against the feed with the counterparty's contact attached.
The same date will arrive twice
Once you have checksums, the awkward cases become tractable. An identical file redelivered because someone re-ran a job on their side is a no-op: same checksum, same business date, ignore and log. A different file for a business date you have already loaded is a restatement, and it needs a version number, a load that supersedes rather than duplicates, and a visible note wherever that date is reported. Without the checksum you are guessing, and most estates guess by trusting the filename, which is exactly the field a counterparty changes without telling anyone.
Connectors help with the transport. Airbyte, an SFTP operator in Airflow, a managed file-drop service, all of them will move bytes reliably. What none of them will do for you is decide what a late file means for a signed-off report, or who gets phoned. That part is the data contract, and for file sources the contract is mostly about time rather than schema.
Mailboxes and portals are production systems
The email feed deserves saying out loud, because it is usually undocumented. If a report depends on an attachment sent to a named person, that person's mailbox is production infrastructure, and it will fail the week they change roles. Move it to a shared mailbox owned by a function, have a rule write the attachment straight into the landing area, and record the sender and subject in the manifest alongside the checksum. The same discipline applies to a portal that only a browser can reach. Scripted downloads against a portal are brittle work and there is no elegant version of it, but the brittleness is contained if what comes out the other end lands in the same place with the same stamps as everything else.
When not to do any of this
Not every file deserves the apparatus. A reference table of twelve rows that changes twice a year is fine as a spreadsheet a named person updates, and building an arrival contract around it is work you will maintain forever for no benefit. Be honest about frequency and consequence before you build.
The harder case is when engineering becomes avoidance. If a counterparty has been sending a broken or late file for two years, a well-built alert that fires into your own team's channel does not fix the feed, it just relocates the cost onto you and makes the problem quiet enough to survive. The monitoring is worth having, but the escalation should reach the person who can actually change the behaviour, and somebody should be having the conversation about the file itself. A design that absorbs a counterparty's unreliability indefinitely is not resilience, it is a subsidy.
The test for whether you have got this right is not the architecture diagram. It is whether, on a Monday morning, someone can open one page and see every feed the business depends on, when each last arrived, when the next is due, which ones are outside their window, and who is being contacted about them. If that page exists and is boring, the sources without APIs have stopped being an embarrassment and started being ordinary infrastructure.