CAISO Like-Day Analysis — Morning Briefing

1. Run every query, read every result. Do not fabricate prices or generation numbers. If a query returns empty, say so. Traders make real-money decisions from this.
2. No training-data prices. Forward curves, spot prices, gas prices change hourly. Only use numbers from query results in this conversation.
3. Always state the flow date. CAISO trades on PPT flow days. Be explicit about which flow date you're analyzing.

Workflow

Execute these steps in order. Run independent queries in parallel where possible.

Step 1: Determine Today's Flow Date and Timezone Offset

today = current date (user's local or system date)

Determine DST vs standard time:

• DST (2nd Sunday in March through 1st Sunday in November): PPT = UTC - 7
• Standard (November through March): PPT = UTC - 8

Set TZ_OFFSET accordingly (use INTERVAL '7' HOUR for DST, INTERVAL '8' HOUR for standard).

HE1 PPT in UTC = today at 08:00 UTC (DST) or 09:00 UTC (standard). Full flow day in UTC = today at HE1_UTC through tomorrow at HE1_UTC.

Step 2: Query Today's Fundamentals

Run these queries in parallel:

2a. DA and RT Prices (NP15 + SP15)

SELECT
  DATE(datetime - INTERVAL '7' HOUR) AS flow_date,
  name, datatype,
  CAST(EXTRACT(HOUR FROM (datetime - INTERVAL '7' HOUR)) + 1 AS INTEGER) AS he_ppt,
  avgvalue
FROM rfo_analytics.rfo_prices_enriched
WHERE iso = 'CAISO'
  AND name IN ('TH_NP15_GEN-APND', 'TH_SP15_GEN-APND')
  AND datatype IN ('DALMP', 'RTLMP')
  AND datetime BETWEEN TIMESTAMP '{today_utc_he1}' AND TIMESTAMP '{tomorrow_utc_he1}'
ORDER BY name, datatype, he_ppt

Compute from results:

• On-peak (HE7-22): avg DA, avg RT, avg DART spread
• Off-peak (HE1-6, HE23-24): avg DA, avg RT, avg DART spread
• Midday (HE10-16): avg DA, min DA, avg RT, avg DART spread
• Flat (all hours): avg DA

2b. Wind Generation

SELECT DATE(datetime - INTERVAL '7' HOUR) AS flow_date,
  AVG(avgvalue) AS wind_avg, MAX(avgvalue) AS wind_max
FROM rfo_analytics.rfo_generation_enriched
WHERE iso = 'CAISO' AND name = 'CAISO'
  AND datatype = 'GENERATION_WIND_CURRENT'
  AND datetime BETWEEN TIMESTAMP '{yesterday_utc_he1}' AND TIMESTAMP '{tomorrow_utc_he1}'
GROUP BY DATE(datetime - INTERVAL '7' HOUR)
ORDER BY 1

2c. Solar Generation

Same pattern as wind but datatype = 'GENERATION_SOLAR_CURRENT'.

2d. Gas Prices

SELECT DATE(datetime - INTERVAL '7' HOUR) AS flow_date, name, AVG(avgvalue) AS gas_price
FROM rfo_analytics.rfo_prices_enriched
WHERE iso = 'CAISO'
  AND name IN ('Socal-Citygate', 'PG&E - Citygate')
  AND datatype = 'NGI_GASPRICE_AVG'
  AND datetime BETWEEN TIMESTAMP '{week_ago_utc}' AND TIMESTAMP '{tomorrow_utc_he1}'
GROUP BY DATE(datetime - INTERVAL '7' HOUR), name
ORDER BY name, flow_date

2e. CAISO Imports

SELECT DATE(datetime - INTERVAL '7' HOUR) AS flow_date, datatype, AVG(avgvalue) AS avg_val
FROM rfo_analytics.rfo_flows_enriched
WHERE iso = 'CAISO' AND name = 'Caiso_Totals'
  AND datetime BETWEEN TIMESTAMP '{week_ago_utc}' AND TIMESTAMP '{tomorrow_utc_he1}'
GROUP BY DATE(datetime - INTERVAL '7' HOUR), datatype
ORDER BY flow_date, datatype

2f. Check if Tomorrow's DA is Published

SELECT name, CAST(EXTRACT(HOUR FROM (datetime - INTERVAL '7' HOUR)) + 1 AS INTEGER) AS he_ppt, avgvalue
FROM rfo_analytics.rfo_prices_enriched
WHERE iso = 'CAISO'
  AND name IN ('TH_NP15_GEN-APND', 'TH_SP15_GEN-APND')
  AND datatype = 'DALMP'
  AND datetime BETWEEN TIMESTAMP '{tomorrow_utc_he1}' AND TIMESTAMP '{day_after_utc_he1}'
ORDER BY name, he_ppt

If tomorrow's DA is already published, report actual prices alongside the estimate.

Step 3: Present Today's Fundamental Snapshot

Format as a table:

Step 4: Historical Multi-Signal Search

Query all fundamental metrics for the trailing ~13 months (go back to previous year's spring). The lookback start should be approximately today - 400 days but at minimum cover the prior year's March.

-- Single large CTE query joining wind, solar, DA metrics, RT-DA spreads, gas
-- See the full query pattern in Step 2 but for the historical range
-- Filter: wind_avg > 2000 AND sp15_midday_min < -5
-- Return: flow_date, wind_avg, solar_avg, np_offpk, np_onpk, sp_offpk, sp_onpk,
--         sp_mid_min, sp_mid_avg, np_mid_min, np_mid_avg, np_flat, sp_flat,
--         np_rtda_offpk, sp_rtda_offpk, pge_gas, scg_gas

Use the same CTE-based query structure that joins wind_daily, solar_daily, da_metrics, rt_da_spread, and gas_daily. Filter for:

• wind_avg > 2000
• sp_midday_min < -5
• DA data completeness: HAVING COUNT(*) >= 20 in the DA metrics CTE

Step 5: Score and Rank

For each historical day, compute a multi-signal distance score:

Formula: raw_score = SUM(weight * |actual - today| / normalization)

Apply recency weighting — divide raw score by the recency multiplier:

adj_score = raw_score / recency_multiplier

Present top 10 single-day matches in a table.

Step 6: Find Two-Day Pairs

For each scored day D, check if D+1 exists in the dataset. Compute:

• Whether D+1 had higher total renewables (wind + solar) than D
• Combined score = adj_score + (0 if renewables_up else 3)

Sort by combined score. Take top 5-8 pairs.

For each pair, show:

1. D1 and D2 fundamentals side by side
2. The delta (D2 - D1) for each metric
3. What happened to prices on D2

Step 7: Nodal Congestion Analysis

For the D+1 dates from the top 5 pairs, query node-vs-hub LMP spreads during midday (HE10-16):

-- Join node LMP against SP15/NP15 hub LMP for interval_num 10-16
-- Target these specific nodes:

Premium nodes (consistently above hub during oversupply):

• SLAP_PGCC-APND (Moss Landing / battery hub)
• MOSSLD_2_PSP1-APND, ELKHRN_1_EESX3-APND (Moss Landing units)
• SLUISP_2_UNITS-APND (San Luis Obispo area)
• SLAP_PGFG-APND (Geysers geothermal)
• SLAP_PGHB-APND (Humboldt Bay)
• DLAP_PGAE-APND (PG&E load)

Discount nodes (depressed below hub — solar injection bottlenecks):

• KETTLEMN_6_N001 (Kettleman Hills — Kern County solar belt)
• AVENAL_6_GN001 (Avenal — same area)
• POD_MNDOTA_1_SOLAR1-APND (Mendota solar — Fresno area)
• DC_ADLNTO-APND (Adelanto DC tie)

DLAPs for load spread:

• DLAP_PGAE-APND, DLAP_SCE-APND, DLAP_SDGE-APND, TH_ZP26_GEN-APND

Present as a pivot table: nodes as rows, D+1 dates as columns, values = spread vs SP15 hub.

Step 8: DA-RT Opportunity Analysis

For the top 5 D+1 dates, pull full 24-hour DA and RT from rfo_prices_enriched:

SELECT flow_date, name, he_ppt,
  MAX(CASE WHEN datatype='DALMP' THEN avgvalue END) AS da,
  MAX(CASE WHEN datatype='RTLMP' THEN avgvalue END) AS rt,
  MAX(CASE WHEN datatype='RTLMP' THEN avgvalue END) - MAX(CASE WHEN datatype='DALMP' THEN avgvalue END) AS dart
FROM ... GROUP BY flow_date, name, he_ppt

For each D+1 date, compute:

• Off-peak, on-peak, midday average DART
• Hourly DART profile (identify extreme hours)
• Evening ramp DART (HE19-22)

Summarize patterns:

• Which periods did RT consistently outperform DA? (virtual demand opportunities)
• Which periods did DA consistently outperform RT? (virtual supply opportunities)
• Was the evening ramp consistently underpriced in DA?

Step 9: Price Estimate

Using the D+1 outcomes from the top 5 pairs, weighted by recency:

1. Compute the D1→D2 delta for each metric across all pairs
2. Weight by recency multiplier
3. Apply the weighted-average delta to today's values

Present:

Include a short rationale paragraph explaining:

• Which analogue pairs weigh most heavily and why
• Key bullish/bearish signals
• Tail risk scenarios

Step 10: Final Briefing Format

Structure the output as:

1. Date & Context — flow date, DST/standard, day of week
2. Today's Fundamental Snapshot — the table from Step 3
3. Tomorrow's OASIS Forecast & Import Schedule — load/wind/solar forecast + NW/DSW import schedule vs today
4. Top 5 Single-Day Matches — recency-weighted, with scores
5. Top 5 Two-Day Pairs — D1 vs D2 fundamentals, deltas, D2 outcomes (matched on today→tomorrow forecast profile)
6. Regional Import Breakdown — NW vs DSW paths, daily trend, comparison to historical
7. Nodal Congestion Patterns — pivot table across D+1 dates
8. DA-RT Opportunities — which periods/nodes showed virtual bid edge
9. Tomorrow's Price Estimate — central estimate + range + rationale incorporating forecast data
10. Trading Signals — high-confidence patterns vs conditional patterns

Athena Query Execution

Execute via AWS CLI:

aws athena start-query-execution \
  --query-string "<SQL>" \
  --query-execution-context Database=rfo_analytics \
  --result-configuration OutputLocation=s3://aws-athena-query-results-767397821604-us-east-1/claude-queries/ \
  --region us-east-1

Poll results:

aws athena get-query-results --query-execution-id <id> --region us-east-1

Run independent queries in parallel. The historical search (Step 4) is the heaviest query — submit it early.

Key Data References

These are exact column values — do not guess or abbreviate:

All rfo_*_enriched timestamps are UTC. Convert to PPT using datetime - INTERVAL '7' HOUR (DST) or INTERVAL '8' HOUR (standard).

DA market in caiso_nodal_prices: interval_num 1-24 = HE1-24 PPT. RTPD in caiso_nodal_prices: varies (may be sparse for historical dates).

Regional Import Paths (rfo_flows_enriched)

These are the exact intertie name values for datatype = 'TRANS_USAGE_ENE_IMPORT_MW':

Northwest / MidC Paths

NW Total = MALIN500_ISL + NOB_ITC + COTPISO_ITC

Desert Southwest Paths

DSW Total = PALOVRDE + ELDORADO + MEAD + NORTHGILA + PARKER + WSTWGMEAD + MEADMKTPC

Other Key Paths

Scheduled vs Actual

• Tomorrow's scheduled imports are already visible in the database as forward-looking TRANS_USAGE_ENE_IMPORT_MW values. Check for the next flow date to see what's scheduled.
• Historical import data uses actual flows.

OASIS 7-Day Forecasts

Load Forecasts (rfo_load_enriched)

Use name = 'CAISO' for system-level.

Wind/Solar Forecasts (rfo_generation_enriched)

Available names: CAISO (system), NP15, SP15, ZP26.

How to Use Forecasts for Two-Day Analysis

Instead of searching for D+1 with "higher renewables" generically:

1. Query tomorrow's OASIS wind/solar/load forecasts.
2. Query tomorrow's scheduled NW/DSW imports (already in the flow data).
3. Compute tomorrow's expected supply profile: wind + solar + imports - load.
4. Search history for D+1 days where the ACTUAL wind, solar, imports, and load matched tomorrow's FORECAST. This finds the right analogue D+1 days based on what the market expects, not just directional moves.

The two-day pair scoring should now incorporate:

• D score: how similar D is to today (existing scoring)
• D+1 forecast match: how similar D+1's actuals are to tomorrow's OASIS forecasts
• Weight both components, with D+1 match weighted slightly higher since we care about the outcome