Design a pipeline that ingests files from partners via SFTP

Treat every file as untrusted and possibly repeated. The design keeps the original file, records every file it sees, validates before loading, loads each file idempotently, and checks that expected files arrived.

SFTP -> landing bucket (original files, immutable)
     -> control table (file name, size, checksum, status)
     -> validate -> load to raw table (by file id)  | reject -> quarantine + notice

Pull and keep the original

A scheduled job (or an event) copies new files from the SFTP server to object storage, under a path that includes the partner and the received date. Never edit or delete the original. It is your evidence if a partner disputes what they sent, and it lets you reprocess.

The control table

For each file, record the partner, name, size, a checksum (SHA-256), the received time, the business date it covers, and a status (received, validated, loaded, rejected). Before processing a file, look up its checksum. If you have seen the same content before, mark it as a duplicate and skip it. This handles "sometimes twice". If the same name arrives with different content, treat it as a corrected file, and decide the rule: usually replace the earlier load for that business date.

Validation

Check the header and column count, data types, mandatory fields, date ranges, and row counts against a trailer line if the partner supplies one. A file that fails a structural check is quarantined as a whole, and a file with a few bad rows follows your threshold rule (load the good rows, quarantine the bad ones, fail if the bad share is above, say, 1 percent).

Idempotent load

Load by file id: delete or overwrite rows previously loaded from that file id (or business date), then insert. Running it twice leaves the same result. Add the file id and load time as columns on every row, which helps tracing.

Late and missing files

Know what you expect: each partner's schedule and cutoff time. A job runs at the cutoff and alerts if the expected file has not arrived ("Partner X file for 2025-03-01 missing at 08:00"). Late arrivals are processed when they come, and downstream tables for that date are rebuilt.

Communication

Send partners an automatic notice for rejects, with the reason and the row numbers. Many "broken file" issues are fixed faster when the partner gets a clear message the same day.

Support tooling

A reprocess command that takes a file id, and a dashboard of files by status and partner. Keep the file retention period in line with contracts.