Tinkering · Home energy
My utility draws me a chart. I wanted the data behind it.
So I built a pipeline that collects my home's electricity use every half hour, lines it up against the weather hour by hour, and keeps all of it somewhere I can ask questions of. It runs on its own in AWS, deploys through GitHub Actions, and is analysed with DuckDB.
It's a proof of concept for a bigger question: what architecture turns raw utility data into insight that's actually useful about one specific home? Most of what I learned came from the parts that fought back.
Pipeline figures generated from the live archive on September 25, 2026. Household numbers are deliberately left off this page.
Two independent feeds, one archive, analysed in place. Nothing here needs a server running.
30-minute interval usage. Behind 2FA and a Cloudflare rule that fingerprints TLS handshakes.
Hourly temperature and humidity, requested in UTC.
Weather at 07:00, usage at 09:00 Eastern, and a session keepalive every 20 minutes.
One function, four modes: probe, fetch, keepalive, weather. Browser-TLS session via curl_cffi.
The session tokens, rotated and written back on every refresh.
Every pull kept whole and partitioned by date, so overlapping pulls can reveal a restated history.
Reads S3 directly. One SQL model dedupes pulls, strips padding and joins weather. No warehouse.
Pipeline health, always-on load, weather sensitivity, weather-adjusted anomalies.
Alarms for a failed pull, a stale usage feed and a stale weather feed, tracked separately.
Plan on every pull request that touches the pipeline, apply on merge. OIDC into AWS; no stored keys.
All of the above as code, with remote state and native S3 locking.
Each of these looked fine until it was tested against the real thing.
Every request from Python got a Cloudflare 403, including unauthenticated ones. The rule keys on the shape of the TLS handshake, so for my own authenticated requests I present a browser's fingerprint with curl_cffi.
That same fact ruled out a JS-only serverless platform: Node.js got the identical block page, so no JavaScript runtime could make this request. I tested from AWS with a one-call probe before building anything on it.
The client library I built on couldn't complete email or SMS 2FA. I traced it to the protocol — a discarded verification token and a stale assertion — and carried a small, reviewed fix rather than installing an unvetted fork of the code that handles my credentials.
Sessions died after about an hour and a half idle. A once-a-day job would have failed every single run. A 20-minute keepalive holds the session open, and because recovery takes a human with a phone, the alarms carry real weight.
The API only ever returns a rolling ~68-day window, whatever you ask for, so anything not captured is gone. Every pull is stored whole in S3, which also means a quietly revised reading would show up rather than overwrite the past.
The export also pads the current day with zeros. It arrives looking complete, so counting rows can't catch it.
DuckDB read offset timestamps as naive UTC, and treating them as Eastern shifted every report four hours. The Lambda computed "today" on a UTC clock, caught by ruff's datetime rules before it bit. And weather is requested in UTC, because local labels are ambiguous in the repeated hour each November.
The government weather API keeps three days of history, so historical weather comes from Open-Meteo. With it, "unusual" means unusual for how hot it was, not different from the neighbours. That changed which days get flagged: the old method flagged days for being hot or cool, which isn't news.
Standing up GitHub Actions surfaced a run of silent failures: a failed plan reporting green, OIDC subjects in a format the docs don't show, a stale state lock that blocked every run, and a Terraform quirk that would have failed every merge touching Lambda code.
It now can't rewrite its own permissions, and the Lambda zip is pinned to the lockfile and byte-identical whether built on my laptop or in CI.