A solo-built finance data platform

The business runs on separate systems: job management, accounting, HR, time-tracking. None of them talk to each other. Before this existed, one number depended on a person doing manual, repetitive work in the middle: every cycle, someone on the finance team copied timesheet entries out of Clockify and re-entered them into AroFlo by hand, so a job's cost would include its labour at all. That is what "leadership needs one honest view of jobs, costs and labour" meant on the ground, multiplied across every system boundary in the business.

The obvious build is "copy everything into one database every night." I did not build that, for a finance reason rather than a technical one:

• A nightly full copy means the numbers are up to a day old. In job costing, a purchase order approved this morning changes a job's margin this morning.
• A full copy that fails halfway leaves you with a partially updated warehouse and no way to tell. Half-loaded finance data is worse than no data, because it still renders a report.

So the requirement I designed to was not "get the data across". It was:

Every number in the warehouse must be traceable to a source record, reproducible on demand, and provably complete, or the system must refuse to serve it.

Everything downstream follows from that sentence. This is the part that is normally an architect's job, and it is the part that decides whether the rest is worth building.

Act one Full loading

The first version was Python, started mid-2025. Every request signed with an HMAC-SHA512 signature built from the method, timestamp and query string. A pagination engine that keeps asking for the next page until the source says there isn't one: the API caps at 500 records per page, and no endpoint is small enough to trust unpaginated. A flattener turning deeply nested JSON into something tabular. Credentials in environment variables, never in source.

It pulled everything, every run. It was slower than what replaced it, and that was the correct trade at the time, because writing it is how I learned the source system's real behaviour:

• field casing that changes between endpoints
• records returned under one name in the change feed and a different name in the data API
• endpoints that quietly under-return
• line-level tables that are actually at line × job grain, so a naive SUM() over them overstates by millions

None of the later work would have been safe without that. You cannot design a reliable pipeline for a system you have only read the documentation for.

Act two Incremental loading

About a year later, the rebuild inverted the question. Instead of "give me all your data", it asks "what changed since I last succeeded?"

• A watermark records the last successful run. A typical run now touches a few hundred changed records instead of tens of thousands, ~99% fewer records processed per run.
• Fifteen data zones fan out in parallel rather than queueing behind each other. Runtime went from 25–31 minutes to under ten.
• Every raw API page is saved before anything is processed, through a three-stage audit trail. If a run dies mid-way, nothing is lost and I can prove what arrived.
• Loads are idempotent: each record is deleted then re-inserted by key, so running the same batch once or ten times produces the same result.
• The watermark advances only if the whole run succeeded. A failed run simply re-fetches from the last good point next time.

The finance reason for idempotency, since this is the decision people skip: if a re-run doubles a labour line, a job's cost is silently overstated. Nobody gets an error. The report renders. Someone quotes the wrong margin to a client, and it surfaces weeks later as an invoice dispute. Making every load safe to re-run is not engineering elegance: it is the thing standing between a retry and a wrong number in front of a client.

This is the stage most portfolio projects never reach, and it is the strongest evidence for owning something rather than merely building it:

• It runs on a schedule, hourly through the working day, without anyone starting it.
• A watchdog restarts the loader if the machine reboots or the process dies.
• Nine automated health checks run every morning and email me on failure, not a dashboard I have to remember to open. Failure has to come and find me.
• Every stage logs what it did, so a question about last Tuesday is answerable from evidence rather than memory.
• When the network dropped for a day and every scheduled run failed, the system kept the last good copy and refused to load rather than overwriting good data with nothing. Without that guard, eight consecutive failed runs would have replaced the warehouse with zero rows.

That last one is the point of the whole stage. The system's default behaviour when something goes wrong is to stop and shout, not to continue quietly.

I did not switch off the original Python pipeline when the new one went live. I kept it running and gave it a different job.

It now runs hourly, takes a complete snapshot of the source, and rebuilds a second, independent database from scratch. So the same source is loaded two different ways, by two pipelines built on two different technologies, into two databases, and those two get compared.

A pipeline that checks itself against a copy of itself is not checking anything. The old build is not legacy I failed to retire: it is the control group.

On top of that:

• Record counts are reconciled at every stage: raw equals routed equals loaded. A mismatch is a hard failure, not a warning.
• The new warehouse was reconciled against the existing production report and matched to the dollar: two independently built pipelines arriving at the same total is the strongest evidence available that both are right.
• Row-level security and a read-only reporting login, so the reporting layer physically cannot write to the warehouse.

Why a finance person insists on this and an engineer might not: in accounting you never trust a single source for a material number: you reconcile it against an independent one. Bank to ledger. Ledger to statement. I built the same habit into the platform, because a data pipeline that cannot be reconciled is just a confident opinion.

My first diagnosis was wrong. I initially concluded the source system wasn't reporting status-only changes. It was. The audit trail I'd built in stage two held the correct data all along: the loss was mine, not the vendor's. I record that because it is the actual lesson: I had evidence on disk that settled it in minutes, and I nearly blamed an external system instead of reading it.

The repair. Replayed in strict date order, then re-applied every later batch. A partial replay is worse than none, which I found out by doing it and watching a second set of records regress. Verified back to zero drift the same morning.

The permanent fix. The processing queue was rebuilt so ordering is guaranteed regardless of the order files arrive in, plus a settling delay so a backlog is fully assembled before any of it is applied.

Counts lie. Comparing keys lies about state. Reconciliation has to compare what a record says, not merely that it exists.

And the harder one: the check you never see fail is the one to distrust first. Mine had been passing for months because it wasn't looking at the right thing.