SFTP to API: why file feeds outlive migrations

The nightly file has run for eleven years. It lands on an SFTP server at 2 a.m., a script loads it, and finance reads the result at 8. Nobody who built it still works at the company. When the migration to an API finally starts, the team learns the file’s most important rule from an incident: a customer missing from the file means the customer was deleted.

Moving file feeds onto APIs is the second step of data integration. Every company has these feeds, between the ERP and the bank, the HR system and payroll, the billing system and the warehouse. They outlive every migration plan because nobody knows exactly what they do.

What is an SFTP to API migration?

An SFTP to API migration replaces a scheduled file transfer with direct API calls between two systems. Before the migration, one system writes a file, usually a CSV, to an SFTP server on a schedule, and the other picks it up and loads it. After it, records move through the receiving system’s API as they change, and the rules the file carried by convention, such as what a missing row means, are written into the integration instead.

Why batch file feeds outlive every migration plan

1. The file is a contract nobody wrote down. Column order, date formats, what an empty field means and what a missing row means were all agreed once, in 2014, and never written anywhere.
2. Absence means deletion. A full-file replace says “these are all the records”. No sequence of API calls says that, so a record deleted in the source lives on in the target.
3. The cutover happens all at once. The team switches the whole feed on a weekend, finds a difference on Monday, and switches back. The feed then runs for another three years.
4. Month end behaves differently. Adjustments, reversals and late entries only appear at month end, so a migration that matched for three weeks breaks in the fourth.
5. A truncated file looks valid. An upload that stopped halfway is a smaller file with the right columns, and it loads without an error.
6. Nobody owns it. The feed sits between two teams, and each assumes the other is responsible.

What it costs

The feed is a single point of failure that nobody watches. When it breaks, finance finds out at 8 a.m. from a report that is wrong, and someone spends the morning working out why.

It also holds back everything around it. Data that moves once a night can’t support anything that needs to happen during the day, so the systems on either side work around it with their own exports and spreadsheets.

What to fix first

1. Recover the file’s rules before designing anything. Read the loading script, compare a month of files, and write down what each column, empty value and missing row means.
2. Decide how deletes travel. If absence means deletion, the API feed needs an explicit delete, or a reconciliation that compares full lists.
3. Run both paths in parallel. Load the file and the API feed side by side, and compare the results every night. The difference report is the specification you never had.
4. Cut over one record type at a time. Move customers, then invoices, then payments, each when its comparison has been clean.
5. Wait for a clean month end. Don’t switch off the file until the comparison has been empty through at least one month end.
6. Check the file is complete before loading it. Use a row count, a trailer record or a checksum, and refuse a partial file.

The guide to migrating an SFTP batch feed to an API has the prompts for each step.

Where it fits

A file feed is often the last piece of an older integration platform. Moving it is part of the same job as moving off a legacy iPaaS, and the parallel run works the same way. Once the data moves through an API, changes can travel as they happen, as webhooks or a change data capture stream.

For all seven steps, see the data integration process.