Part 5: The hourly loop · Finance ETL

Scratch cut · 1:41 Screen recording of the timer firing, the log lines, the row count, and the reconciliation line. Then the sync_runs table. Demo data, scratch voice, real pipeline. The live recording takes over once it is shot.

The script

set -euo pipefail
trap 'rm -f /tmp/ynab_response_*.json' EXIT
cursor=$(psql ... "SELECT server_knowledge FROM ynab.sync_state WHERE endpoint='budget_detail'")
fetch budget_detail with last_knowledge_of_server=$cursor   # 429 -> sleep 60, retry
INSERT INTO ynab_raw.api_responses ...
SELECT ynab.apply_response(:id);
INSERT INTO ynab.sync_runs (mode, status, rows_upserted) ...
SELECT COUNT(*) FROM ynab.v_balance_check WHERE ABS(drift) > 0.01;

Running it

A systemd service with an EnvironmentFile at /etc/ynab-sync/env.conf, mode 600. The token never appears in the process list or the logs.
A systemd timer, hourly. The first run with a cursor of zero is the backfill and takes a few minutes.
Every run writes a row to sync_runs with status, rows, and any error.

The hourly loop: clock, fetch, land, apply, and back to the clock.

The outage

Transaction ids were typed as uuid. The documentation calls them ids; most of them look like uuids. Then the first scheduled transaction occurred, and its ledger row carried an id shaped like <uuid>_<date>. The cast failed, the transaction rolled back, and every hourly run failed the same way for four days.

Skipping those rows was not an option either. One skipped occurrence produced exactly 30.59 dollars of drift on checking. Thirty dollars is a small amount to lose and a large amount to be wrong about. The balance check view caught it. A migration retyped the id columns as text, rebuilt the dependent views, and the replay from raw landed the missing rows.

Audio · 3 minThe four-day outage, told properly

Scratch narration How it was noticed, the wrong first theory, the 30.59 dollars, and what changed in the checklist.

The hourly loop: timer, fetch with the cursor, land, apply, and reconcile with the figure 30.59. — Plate V · The hourly loop
Free to borrow for your feed · Square edition

Where you are: a pipeline that runs and checks its own math. Next, you finally get to look at it.

A script, a timer, and a four-day outage.

The script

Running it

The outage

Is there a 30.59 hiding in your books?