Build a normalized multi-ISO LMP data pipeline: node mapping, intervals, DST handling

Verified steps

1. Establish a canonical schema for LMP records: {iso, node_id, node_type (bus/zone/hub), interval_start_utc (ISO 8601 with UTC offset), interval_end_utc, lmp_mwh, energy_component, congestion_component, loss_component, data_vintage (real-time/day-ahead/final)}.
2. Build per-ISO adapters that normalize timestamps to UTC on ingestion: CAISO uses Pacific Prevailing Time with hour-beginning intervals; PJM uses Eastern Prevailing Time with hour-ending intervals; NYISO and ISO-NE use Eastern Prevailing Time with hour-ending intervals; MISO uses Central Prevailing Time; AEMO uses AEST (no DST) with 5-minute and 30-minute intervals.
3. During DST fall-back transitions, the repeated local hour (e.g., 1:00–2:00 AM appears twice) must be disambiguated using the UTC offset embedded in the raw timestamp or a pre-built DST transition table — never store ambiguous local timestamps.
4. Maintain a node mapping table with columns {iso, native_node_id, node_name, balancing_area, latitude, longitude, voltage_kv, effective_date, end_date} to handle node renaming and topology changes over time.
5. Implement idempotent upserts keyed on (iso, node_id, interval_start_utc, data_vintage) to safely re-run backfills when ISOs publish restated data after settlement finalization.
6. Build a data completeness monitor that checks for expected interval count per ISO per day and alerts when gaps exceed the ISO-specific maximum expected latency (e.g., CAISO OASIS is typically available within 15 minutes of interval close).