Files, inboxes, sheets and APIs

Four shapes, four sets of problems

Almost every automation reads from one of four places, and each fails differently.

A file that lands somewhere. SFTP drop, a shared folder, an email attachment. Easy to read, and the problems are ordering and duplication: the same file arriving twice, a file arriving half-written while you read it, yesterday’s file being replaced silently. Read only files whose modification time has been stable for a minute, and record a hash of every file you have already processed.

An API. Auth, token refresh, pagination, rate limits, and their idea of an error. Everything a vendor’s quickstart leaves out is what you will spend the week on.

A database you are allowed to read. The best case by a distance: types, constraints, transactions, and a schema you can query. Ask for a read-only replica before you build anything cleverer.

A spreadsheet a team maintains by hand. The most common source in the real world and the worst-behaved. Merged cells, a header row that moves down when somebody adds a title, numbers stored as text, dates that Excel has helpfully reinterpreted, and a “Notes” column that contains business rules nobody has written anywhere else.

For that last one, one habit prevents most of the pain: match columns by header name, never by position, and fail loudly when an expected header is missing. Position-based readers keep working after somebody inserts a column, and quietly read the wrong field into your database for a month.

Land the raw thing first

The most useful architectural habit in this whole subject: store the source exactly as received, before any parsing, with a fetch timestamp and a hash. A folder of original files, or a table with one text column holding the raw payload.

Everything after that is derived and can be recomputed. When you discover in March that your parser has been dropping the second line of every address since January, you re-run the parser over stored raw data and you are finished in an hour. Without it, the data is gone and you are writing apologies.

This costs almost nothing. Raw text compresses hard, and disk is the cheapest thing in the system.

Incremental sync, and the two traps

You do not want to pull everything every time. So you keep a cursor (the highest updated_at you have seen) and ask for rows newer than that.

Trap one: the boundary. Several rows can share a timestamp, clocks skew between servers, and a transaction can commit after a row with a later timestamp is already visible. Ask for updated_at >= last_seen - 5 minutes and deduplicate by primary key on your side. Overlap is cheap; a missed row is invisible forever.

Trap two: deletions are silent. A deleted row does not have a new updated_at. It stops existing, and an incremental sync will never mention it, so your copy keeps a customer who was removed in February for as long as the system runs. The fix is a periodic full reconcile (weekly, compare the complete set of ids and mark what has vanished) or a source that gives you soft deletes with a deleted_at you can read.

Advance the cursor over the data, never over the clock. A job that asks for “yesterday’s rows” and skips a day when the machine is down has lost that day permanently.

Schema drift

Upstream systems change without telling you. A column is renamed, a field that was always a string becomes an object, an enum gains a seventh value that your code maps to null.

Assert on every run. Check the set of columns you expect is present, check that the types are what you assumed, check the row count is in a plausible range against the last run. Fail the run rather than write a table full of nulls: a job that stops is an inconvenience, and a job that carries on wrongly is a month of bad data.

The free path

You need less than you think. DuckDB reads CSV, Parquet, JSON and Excel with SQL, runs anywhere, and will comfortably handle files far larger than memory: it is the fastest way to look at a strange file and find out what is actually in it. SQLite is an excellent landing store for anything under a few million rows and needs no server. pandas or Polars for transformation, openpyxl for the awkward spreadsheets, curl and jq for poking at an API before you write any code.

Start by loading one real file and printing the distinct values of every column. Ten minutes of that tells you more about the data than any documentation the source system has, and it is where you find that 4 per cent of the “country” column says “n/a”.