Park Data Ingest
Real-Time Theme Park ETL

Every write uses ON CONFLICT (entity_id) DO UPDATE or composite key variants. This makes every run idempotent — a job that runs twice at the same timestamp produces identical state rather than duplicate rows. For a 10-minute cron that has no deduplication budget, idempotency is more valuable than the slight write amplification from checking for existing rows first.

The exception is park_live_status_logs_v2, which is designed as an append log — each 10-minute snapshot is a distinct row even for the same entity. The upsert key here is (entity_id, status_timestamp), so re-runs at the same timestamp still converge to one row per entity per timestamp.

Sellout and downtime event detection compares the current API response against the previous known state for each entity. Rather than querying PostgreSQL on every iteration to find the last known status, the cache is loaded once at the start of each run and updated in-place as the live data phase processes each entity. This reduces per-entity queries from O(n) database calls to one bulk load.

The cache is module-level (PREVIOUS_STATES_CACHE = {}), so it resets on each container invocation. Container App Jobs start fresh for each scheduled execution — there's no warm instance carrying stale cache between runs. This is intentional: it avoids a ghost state problem where a cache entry from a prior run suppresses a real state transition that happens to look like the previous value.

A Lightning Lane sellout that lasts 90 minutes means something very different if the park was closed for 45 of those minutes. clip_to_park_hours() subtracts CLOSED intervals from the release-to-sellout window to compute the number of minutes the attraction was actually open and accessible during that period. This makes operating_minutes the correct feature for demand analysis — a 45-minute operating window that sold out is more significant than a 90-minute raw window that included park closure.

This field directly feeds the downstream content pipeline's analysis of which attractions have the most aggressive sellout patterns relative to their actual operational window.

The original decision to use Cosmos DB was reasonable at the time: the hot partition problem wasn't visible until waitSnapshot accumulated millions of documents. Once it was visible, the planned solution was a hierarchical partition key migration — creating park_ops_v2 with [/doctype, /date] keys and backfilling 5.9M documents. That plan was designed, documented, and ready to execute.

The decision to abandon Cosmos entirely instead came from a simpler observation: every aggregate document in Cosmos was computed from data that already lived in PostgreSQL. The publisher read from PostgreSQL, computed GROUP BY rollups, and wrote them to Cosmos — which was then re-read by downstream consumers. This is a pure round-trip with no benefit. PostgreSQL can compute the same aggregates at query time or via materialized views at a fraction of the cost, with one fewer hop in the data path and no per-RU billing.

The v6 "split architecture" was always meant to be temporary — a transition state between the old monolith and a cleaner design. The split was introduced to provide fault isolation while the Cosmos migration was planned. Once the decision to drop Cosmos was made, the split job was no longer necessary and was eliminated.

When an attraction closes permanently (Rock 'n' Roller Coaster, DINOSAUR), the ThemeParks.wiki API may still return the entity in the roster with stale data for some time. Writing zero-wait or null status records for a closed attraction adds noise to the historical dataset that downstream models have to filter. The blocklist prevents writes for specific entity IDs entirely, while preserving all historical rows already in the database.

A soft-delete approach (marking the entity as inactive in park_entities_v2) would still require every downstream consumer to filter on the inactive flag. The blocklist is enforced at the ingest layer, keeping downstream consumers clean without extra filter logic.