The Data Layer, And Why I Don't Trust A Number Until It Matches A Bill

• $0.00$0.00difference between four invoices and my own data store
• 614,000price-forecast snapshot rows captured over five months
• 72 hleft out of any tight analysis while prices settle

Everything in this series rests on one claim: that the numbers I quote are real. Not “the app says so”. Real, in the sense that if you asked the retailer for an invoice for the same period, the totals would match, and not approximately. To the cent.

This is the least exciting post in the series and the most important. In a wholesale-exposed home energy system, the difference between a number that feels right and one that is right is usually a sign, a timezone, or a stack of charges nobody told you about.

The setup

The data layer is three things stacked up:

1. Home Assistant on a small server in the house, ingesting everything the inverter, the retailer, the hot water unit, the HVAC and the weather station will hand over.
2. A nightly sync that pulls sensor history out of Home Assistant and billing data out of Amber’s API, and writes both into Parquet files.
3. A master table with one row per half-hour, fifty-five columns wide, joining the lot on a single time axis.

The whole store is about 200 MB. It fits in memory on a laptop. It is not big data. It is honest data, which is the thing that matters.

Why the apps aren’t enough

The Sigenergy app gives you daily and monthly totals. Amber’s app gives you the live price and a forecast. Neither lets you ask the question I wanted to ask: at this exact half-hour, what was the price, what was the household doing, what was the battery doing, what was the weather doing, and did the automation make the right call?

The inverter knows energy and not price. The retailer knows price and not energy. The weather station knows neither. To audit decisions after the fact you have to merge them at half-hour resolution and keep the result long enough to see seasons.

There is also what I think of as the 90-day cliff. These systems show the current state and quietly age out the rest. Home Assistant’s recorder defaults to ten days of history. Amber’s forecast sensor is overwritten every eleven minutes. By the time you want three months of forecast snapshots, you have ten days of state changes and no forecast history. I raised the recorder retention to 365 days the week I installed it, and started capturing every forecast snapshot in late November. That second decision, made almost as an afterthought, is why Post 5 exists.

The billing corroboration

A pipeline you cannot check will eventually lie to you. My check is simple: every claim about exports, imports, costs or revenue has to match Amber’s invoices. Not roughly. Exactly.

For each of the four post-solar invoices I took two numbers off the credits page, total kilowatt-hours exported and total dollars earned, and asked my own data store for the same billing windows. Here is how they landed:

Source	Exported	Earned
Retailer invoices (4×)	2,069.81 kWh	$157.47
Parquet master	2,069.81 kWh	$157.47
Difference	0.00 kWh	$0.00

To the cent, and to the hundredth of a kilowatt-hour.

That is the point at which the data layer stops being a hobby and becomes load-bearing. If the totals didn’t match, I would be debugging, not writing.

The sign-convention bear trap

This was the biggest mistake I made on the project, and every homemade data pipeline will make one like it, and most will never notice.

When I started writing about the feed-in tariff, I read the retailer’s feed-in field at face value. It is mostly negative. I assumed negative meant “you’re paying to export”, the negative-price story everyone has heard, and I built a whole draft of Post 2 around it. “The six-cent lie” was in a heading. I had charts.

It was wrong. The field uses an accounting sign from the retailer’s side: negative means money flowing to the household. From the homeowner’s side you have to negate it before doing anything else.

I caught it because the totals didn’t reconcile. The export side came out negative when the bills said it was a credit. The fix was one minus sign in one helper function. The lesson is never publish a number until it has cleared a third-party check. Without the invoices I would have shipped a post arguing I was paying to export power while my bank account filled with export credits. That is a funnier story to read than to live.

So the first thing the master table does is multiply the feed-in column by minus one and rename it. The accounting sign stays inside the ingest layer.

Timezone, billing periods, settlement lag

Three smaller traps, each of which cost me an hour.

Timezone. Amber’s API returns UTC. Home Assistant runs in local time. The master stores local time, so the conversion has to happen at the right point. Get it wrong and a half-hour lands in the wrong day; badly wrong and a whole day lands in the wrong month. I once put a half-hour, about 5 kWh, that belonged to 31 December into 1 January because the conversion ran in the wrong order in the join. Nothing economic hung on it, but it is the class of error that quietly accumulates in a sloppy pipeline.

Billing periods. Bills run 13 Nov to 12 Dec, 13 Dec to 12 Jan, and so on. Reconcile against calendar months and every total will be off by a day at each end. You will assume the data is broken and lose an evening.

Settlement lag. Half-hours are settled against the wholesale market one to three days after the fact. Until Amber’s quality field says billable, the numbers can still move. Compute yesterday’s cost at midnight and you get a number, just not necessarily the one on the bill. I ignore the trailing 72 hours for any analysis that has to be tight.

None of these is exotic. A professional would spot them in five minutes and a homeowner with a Python script will fall into each at least once. The only way to know you’ve fallen in is an external source of truth to compare against.

What lives in the Parquet store

The master table sits on a few granular tables, each covering one domain at five-minute resolution.

The energy table holds inverter telemetry: solar per string, battery charge and discharge, grid flows, household consumption. It is where you go when half-hours aren’t fine enough, such as the four minutes after an automation fired.

The weather table comes from the rooftop station: temperature, humidity, wind, and the most useful field in the store, measured solar irradiance. That is what the sun is actually doing, as opposed to what a commercial forecast thinks it should be doing, and the gap between the two is the subject of Post 5. On cloudy days the forecasting service overestimates output by 117 to 414 per cent. I only know that because I hold both the forecast history and the irradiance ground truth on the same time axis.

The hot water table records tank temperatures at top and bottom probes, compressor and element states, and power draw. It earned its keep the night I found the unit’s thermostat reading the top probe and reporting “tank hot” while the bottom probe sat at twenty-five degrees after a mains refill. From the app the system was fine. From the data the bottom half of the tank was cold. The fix was a watchdog that catches the stratification and runs a top-up cycle before morning showers. It took two probes and a Parquet file.

The HVAC table covers all six Rinnai zones. In summer it is mostly idle. From June, when six zones run for hours and solar drops, it is the one I expect to live in.

And the forecast history, the most valuable single dataset in the store: about 614,000 price-forecast snapshot rows, captured every eleven minutes for five months. Each snapshot is the full forward curve when it was issued, out to roughly twenty-seven hours. Joined back to the billing actuals, you can ask exactly how accurate Amber’s forecast is at any lead time from thirty minutes to a day. That is Post 5, and none of it would exist if I hadn’t started capturing on a hunch.

The sync, and why it is boring on purpose

The sync is a Python script of about six hundred lines that runs early every morning. It pulls what is new from Home Assistant and Amber, checks the two against each other on the overlap, appends to the Parquet files and rebuilds the master. No streaming, no cloud, no message queue, no Kubernetes.

It is boring on purpose. The pipeline does not have to be clever. It has to be checkable. Each morning a five-line reconciliation runs against the latest invoice, or the latest settled day if there is no new one. If the totals don’t match, the script raises and I find out at breakfast. It has been green every day since the four-invoice match.

You could build this differently: InfluxDB and Grafana (I run those too, for live monitoring), DuckDB, or with patience a spreadsheet. The technology matters far less than the principle. Capture the data, keep it, make it queryable, and reconcile it against the bills.

Why this matters for the rest of the series

You will read “the battery earned X” or “the curtailment automation cost Y” and reasonably take it on trust. I am willing to write those numbers because each is a slice of the same Parquet, computed the same way, and the totals reconcile to four invoices, exact to the cent.

It sounds small. It is the entire foundation. Without it the forecast work in Post 5 is a hunch, the automation review in Post 6 is wishful thinking, and the economics in Post 7 are app screenshots. With it, every claim can be audited against a piece of paper from a third party.

Measurement is the first optimisation. Reconciliation is the first measurement that survives contact with reality.