Nightly reporting pipeline for an agricultural supply co-operative

The co-operative aggregated purchases and deliveries from around sixty member farms, and the daily operating report was assembled by one person each morning from four system exports and two spreadsheets. It took her until about eleven, which meant the buying team made the day's decisions on yesterday's numbers, and when she was on leave the report simply did not exist. There was also no history worth the name — each day's spreadsheet overwrote the last, so nobody could answer a question about last quarter without a recovery request.

Data engineer, the only one on the project, working with an analyst and the operations manager. I owned the pipeline end to end and the modelling decisions behind it.

I built the ingestion from the four sources — two databases, one CSV drop and one system that could only be scraped through a report export — plus the transformation layer and the storage model. I owned the data quality checks, which is the part I would defend hardest: every run asserts row counts against the prior day, checks for member farms that reported nothing, and refuses to publish rather than publishing something wrong. I built the report itself and the alerting when a run fails, and I wrote the runbook for the operations team.

I made the pipeline fail loudly and publish nothing rather than publish partial data with a warning. The operations manager wanted a partial report with gaps marked, and I argued that a report people have to interrogate is a report people stop trusting — we compromised on failing hard plus a message that says exactly which source is missing and who to call. I also modelled everything as append-only daily snapshots rather than mutating a current-state table, which uses more storage and means any historical question is answerable without a restore. I kept the whole thing in PostgreSQL instead of reaching for a warehouse, because sixty farms is not big data and one database is one thing to operate.

One source system had no API and no database access — the vendor's position was that the export button is the interface — so that leg is a scripted export on a schedule and it is the most fragile part of the pipeline. Several member farms submit by email attachment on their own rhythm, so late data is normal rather than exceptional and the model had to accept restatements. The co-operative has no engineers, so anything I built had to be operable by someone whose job is not this.

The report is ready at 5:40am instead of eleven, and it has run every day for about fourteen months including the two weeks the analyst was on leave. The buying team moved their decision meeting to 7am, which was the actual point of the project. Because everything is snapshotted, the co-operative answered a member dispute about last season's deliveries in an afternoon rather than not at all. Three quality checks have fired for real so far, each one catching a source that had silently stopped.

The analyst who used to build the report by hand defined every figure and caught two places where my numbers were subtly different from hers — both times she was right. The operations manager tested the runbook by deliberately breaking a source while I watched, which found a gap in my alerting.