External engines

Reading the history from Spark, Trino, DuckDB or PyIceberg with MeterStore out of the data path — and the version-resolution trap that silently double-counts if you skip one step.

The default answer for external engines is the Iceberg catalogue, not a MeterStore endpoint. Spark, Trino, DuckDB, PyIceberg and Snowflake read the history directly from object storage. MeterStore is then in the metadata path only — never the data path — so readers scale in parallel and MeterStore is neither a bottleneck nor a single point of failure.

Two engines read the output in the test suite:

Field ids matter because Iceberg resolves columns by id, not by name: a writer that assigned them differently produces a table that opens and returns the wrong column. PyIceberg is also the engine this documentation recommends for the maintenance MeterStore cannot do itself, so it is checked rather than assumed.

Resolving corrections

readings_versions is a versioned table: a correction is a new row with a higher version, never an update. Take the highest per reading.

SELECT malo_id, obis_code, "from", value, … FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY malo_id, obis_code, "from", version_scope
    ORDER BY version DESC, recorded_at DESC
  ) AS _meterstore_rank
  FROM readings_versions
) AS _meterstore_resolved WHERE _meterstore_rank = 1

Do not retype it. system.resolution carries this exact text for your table, merge key and all, and store.resolution_sql() returns the same string — both from the one definition the store itself plans against, so a pasted query and the store cannot drift.

Three parts of it are not guessable:

• version_scope is in the PARTITION BY. MSCONS assigns versions per network operator per month, so versions from different scopes are not comparable and must not be ranked against each other.
• The merge key may be wider than those three. Declared identity columns join it, and so does melo_id on a table that identifies a reading by its Messlokation — which a Zählerstandsgang does by default. Omitting one resolves across it: with a tenant column one tenant’s correction supersedes another’s reading, and without melo_id on a point table one meter’s register supersedes the meter next door’s. This is the part not to retype from memory — take it from system.resolution, which carries your table’s own key.
• recorded_at DESC breaks ties. MeterStore’s own write paths refuse to store two rows at one (merge key, version), so a tie should not arise — but a warehouse is a shared surface, and ROW_NUMBER with a partial ordering returns a different row on a different plan. A settlement that reproduces only sometimes is not reproducible.

Gas is not on the calendar day

Every row carries a balancing_day column — the Berlin calendar day for electricity, heat and water, the Gastag (06:00 to 06:00 local, GaBi Gas, Art. 3 Nr. 6 VO (EU) 312/2014) for gas. Group by it:

SELECT balancing_day, SUM(value) FROM readings GROUP BY 1;

date_trunc('day', "from") is wrong twice over: "from" is UTC, so the Berlin day boundary sits at 22:00 or 23:00 UTC; and gas is not balanced on the calendar day at all. Neither error raises anything — both produce a plausible daily curve.

The column is computed at write time because SQL dialects differ on timestamp arithmetic, so no single published expression is right everywhere.

The same boundary carries up to the month: EDI@Energy Allgemeine Festlegungen v6.1c, Kap. 3.1 defines the gas Bilanzierungsmonat as 01.06 06:00 to 01.07 06:00, so version_scope’s YYYY-MM for a gas row is cut at 06:00 too. A row at 02:00 local on 1 March carries February’s scope. Reading it as a calendar month splits one operator-month in two.

And the stored column already answers that too. A Bilanzierungsmonat is a whole number of balancing days, so its first day is the first of the month balancing_day falls in — which makes the monthly roll-up a DATE operation with no zone conversion, no DST reasoning and no dialect:

-- Right for gas and for electricity, on every engine.
SELECT date_trunc('month', balancing_day) AS bilanzierungsmonat, SUM(value)
FROM readings
GROUP BY 1;

date_trunc('month', "from") is not the same expression, and is wrong for the same two reasons the daily one is. MeterStore’s own SQL has meter_balancing_month("from", sparte); the test suite pins the two to agree.

At a DST transition the long and short gas days are the ones named after the Saturday, because the clocks change before the 06:00 boundary:

The DuckDB suite groups a gas workload across that transition by the stored column, matches MeterStore’s own histogram, and checks that the naive UTC grouping disagrees.

Decimals cross as strings

QueryResult::to_json renders value, version and any SUM over them as "123.456789", not 123.456789. That is the one shape in which JSON carries an exact decimal.

A JSON number cannot. Arrow renders the decimal exactly, and then every ordinary reader — serde_json without arbitrary_precision, every JavaScript engine, Python’s json — parses it into an f64. At the full eighteen digits the last one is simply gone: 123456789012.345678 reads back 123456789012.34567. No error, no warning, and a settlement figure that no longer reconciles.

Enabling arbitrary_precision would fix serde_json and none of the consumers, who are the point of a JSON surface. Timestamps are already strings for the same reason. Non-decimal columns are unchanged: a count is still a number.

Arrow IPC and Flight SQL are unaffected — they carry Decimal128 as itself.

Which catalogue you are on matters

The reason is structural rather than a gap in any engine. A REST catalogue hands an engine the current metadata pointer. The SQL catalogue keeps that pointer in PostgreSQL and writes no version-hint.text beside the files, so an engine pointed at the bare directory must glob and pick what looks newest — which can select a metadata document that was never committed. DuckDB requires unsafe_enable_version_guessing = true to open such a table at all.

The catalogue façade

let router = cold_tier.catalog_facade().router();   // feature = "catalog-facade"

meterstore serve --catalog-addr 127.0.0.1:8181     # the same thing, bound

A read-only Iceberg REST Catalog endpoint implementing the spec’s config, namespace and table-metadata routes. Every Iceberg engine speaks this; nothing MeterStore-specific is needed client-side.

Four decisions it makes explicit:

• It serves one namespace, not the whole catalogue. A SQL catalogue is a table in a database, and a database is a thing organisations share. cold_tier.catalog_facade() is confined to the tier’s own namespace and answers 404 NoSuchNamespaceException for anything else, the listing included. CatalogFacade::new(catalog) serves everything, for a caller that means it.
• There is no write path, rather than a write path that is off. Concurrent external writers would break the tiering invariant, and nothing downstream could detect it: the files would be valid Iceberg, the invariant check only looks at PostgreSQL, and the first symptom would be a figure that does not reconcile. Mutating routes answer 405 with that reason — not 501, which invites a client to retry against a future version.
• It carries no object-store credentials. The response says where the data is, not how to authenticate to it. Engines use their own credentials, which is what keeps the endpoint out of the data path — and means compromising it does not hand over the warehouse.
• It returns a Router, not a bound port, so you wrap it in your own authentication, TLS and tracing. The CLI binds it bare for a deployment that puts its own proxy in front, alongside Flight SQL and under one shutdown.

Flight SQL, and when not to use it

The hot tier lives in PostgreSQL and is not in the catalogue, so the unified view is the one thing external clients cannot assemble themselves. That is Flight’s entire justification here — real but narrow. Routing analytics through it would be worse than reading object storage directly: a proxy hop that serialises parallel reads through one process.

let service = FlightSqlServer::new(store).into_service();     // feature = "flight"
let service = FlightSqlServer::new(catalog).into_service();   // …or every table

• A store or a whole catalogue. The server takes any SqlSurface — the pair of methods serving needs: plan a statement without running it, and run it as a stream. A statement spanning two tables is the second thing an external client cannot assemble for itself, since each has its own watermark and its own hot half.
• Read-only. A write here would bypass the tier routing and the subject- reference check, neither of which is recoverable afterwards, so every mutating call answers PermissionDenied naming both.
• Results carry their boundary over the wire. The watermark, the tiers scanned and the read mode travel as Arrow schema metadata on every response, so a BI tool that keeps the schema keeps the provenance. Over a catalogue, meterstore.watermarks names each table the statement touched and its boundary, beside the conservative minimum — two tables have two boundaries, and one number would claim a figure was settled to a point only half its inputs had reached.
• Rows are streamed, never collected. This is the surface built for BI tools, and a BI tool’s query is the one whose rows genuinely are the answer. Buffering the result to send it would make the server’s peak memory the size of whatever a client asked for, over a socket the client controls. The provenance is available before the first batch, which is what lets the schema go out first.
• The statement handle is the SQL. A server-side cache would need eviction and would leak on a disconnected client, and at metering result sizes it buys nothing — the cost is the scan, not the parse.
• GetFlightInfo plans; DoGet executes. A client learns about a bad statement before it starts rendering a stream, and the rows are produced once. Answering the metadata call by running the query would make a BI tool’s ordinary two-call sequence cost two full scans, on the one surface built for BI tools — and preparing a statement would execute it.
• into_service returns a tonic service, not a bound port, for the same reason the façade returns a router.

information_schema is enabled on the session, so a BI tool can list the catalogue before it queries anything — and the distinction between readings and readings_versions is discoverable rather than folklore.