Experiment record

Weather-history collection: formula failure and direct-API redesign

An observed failure in a formula-based hourly weather collection workflow, followed by a privacy-preserving direct-API redesign.

Record metadata

Experiment ID: OPENMETEO-MSM-001
Date: 2026-08-09
Generated by: AI-assisted investigation and implementation workflow
Human intervention: The human authorised access to the existing private spreadsheet and approved a redesign. Private identifiers and credentials are excluded.
Raw data available: false
Source code available: false
Last verified: 2026-08-09

Conclusion

The formula-based collection path was not sufficiently reliable for the intended history archive: when its fetch formula returned an error, new hourly records stopped. The redesign changed the collection path to a direct API client with time-keyed writes and bounded backfill. This record does not yet demonstrate long-term reliability.

Scope and privacy boundary

The source workflow was a private spreadsheet used with a local energy-data project. Its location, spreadsheet identifiers, credentials, values, and source code are intentionally not published. Consequently, there is no public raw dataset for this record.

Verified observations

A spreadsheet formula used as the upstream weather-data collection path returned a fetch error.
Hourly history had previously been written as values; the existing rows remained intact when the fetch error occurred.
New expected hourly records were absent while the formula path was failing.
The workflow was subsequently redesigned to request the weather API directly rather than depending on the spreadsheet formula.
The redesign specification uses a timestamp as the record key, writes only missing time keys, and permits bounded backfill of recent missing times.

What is not verified

The exact retry count, backoff behaviour, and persistent state mechanism could not be independently inspected through the available interface.
The direct-API workflow’s long-term operation and recovery from future provider failures have not yet been demonstrated.
No inference is made about the reliability or availability of any provider outside this specific observed event.

Reusable design pattern

Treat a display formula as a display or comparison layer, not as the only historical-ingestion path.
Fetch structured data in an authorised scheduled client.
Use an unambiguous timestamp as the deduplication key.
Preserve existing history; append only missing records.
On failure, record the error and retry later without overwriting prior values.
Recheck a limited recent window to repair temporary gaps.

Reproduction conditions

No public reproduction is supplied because the original environment is private. A future public reproduction should provide an independently accessible endpoint, a non-sensitive location or synthetic dataset, exact request parameters, the scheduler configuration, raw responses, and the complete analysis code.

The structured record is under experiments/OPENMETEO-MSM-001/. The CSV has a schema only, and the Python file is intentionally inert.