Build a weather observation table that can admit it is stale

Description and use

Build a weather observation table that explicitly marks stale data by tracking source and ingestion timestamps, preserving raw METAR, and labeling units.

Problem

A station dashboard may display outdated temperature data because the ingestion pipeline does not track data freshness, leading to stale observations being treated as new.

Outcome

The workflow ensures that stale weather observations are not mistakenly treated as fresh, preserving data integrity and providing clear ingestion status.

Workflow

• 1. Define a request payload with stationIds, hours=0, maxObservations, and maxAgeMinutes to fetch the latest report per station.
• 2. Use stationId and observedAt as a composite key for upserts, storing checkedAt separately as ingestion evidence.
• 3. Preserve the full raw METAR report for analyst comparison.
• 4. Label units (Celsius, knots, statute miles, hectopascals) in the data model to avoid unit confusion.
• 5. Store qualifiers (e.g., visibility 10+) as source values and document any transformation.
• 6. Create separate routes for fresh, stale, and source-failure observations, updating ingestion status without erasing previous data.
• 7. Test the consumer with three cases: fresh report, stale report, and source error, ensuring only fresh data appears in the current-observations view while all cases remain in the ingestion log.
• 8. Verify pricing and limits for the free tier and applicable platform tiers before scheduling the Actor.

Evidence

A station dashboard can display a plausible temperature long after its source stopped updating.

Source

Use stationId together with observedAt as an observation key. Store checkedAt separately as ingestion evidence.

Source

Create separate routes for fresh observations, stale observations and source failures.

Source

The current FREE-tier result price is $0.0005 for the documented unit: One complete METAR observation for one station and observation time.

Source