ERCOT Net Load Forecast and Actuals

The below examples show the SQL for calculating Actual and Forecasted Net Load in ERCOT using tables available our Snowflake Marketplace Listing.

Net Load Actual

ERCOT net load shows the hourly net load across ERCOT. Net load is calculated by combining solar and wind generation and subtracting that from load. Net load is a clear metric that helps illustrate how much load is met by thermal and other dispatchable resources.

WITH solar_agg AS (
  SELECT
    ercot_solar_actual_and_forecast_by_geo_region_hourly.interval_start_local,
    -- actuals are repeated across multiple reports, 
    -- so we just select one using max()
    max(
      ercot_solar_actual_and_forecast_by_geo_region_hourly.gen_system_wide
    ) AS gen_system_wide
  FROM
    ercot_solar_actual_and_forecast_by_geo_region_hourly
  WHERE
    ercot_solar_actual_and_forecast_by_geo_region_hourly.gen_system_wide IS NOT NULL
  GROUP BY
    ercot_solar_actual_and_forecast_by_geo_region_hourly.interval_start_local
),
wind_agg AS (
  SELECT
    ercot_wind_actual_and_forecast_by_geo_region_hourly.interval_start_local,
    max(
      ercot_wind_actual_and_forecast_by_geo_region_hourly.gen_system_wide
    ) AS gen_system_wide
  FROM
    ercot_wind_actual_and_forecast_by_geo_region_hourly
  WHERE
    ercot_wind_actual_and_forecast_by_geo_region_hourly.gen_system_wide IS NOT NULL
  GROUP BY
    ercot_wind_actual_and_forecast_by_geo_region_hourly.interval_start_local
)
SELECT
  l.interval_start_local,
  l.interval_end_local,
  l.total AS load,
  s.gen_system_wide AS solar,
  w.gen_system_wide AS wind,
  l.total - s.gen_system_wide - w.gen_system_wide AS net_load
FROM
  ercot_load_by_forecast_zone l
  LEFT JOIN solar_agg s ON l.interval_start_local = s.interval_start_local
  LEFT JOIN wind_agg w ON l.interval_start_local = w.interval_start_local
WHERE
  l.total IS NOT NULL
ORDER BY
  l.interval_start_local DESC
LIMIT 1000;

Net Load Forecast

ERCOT net load forecast is the forecasted net load levels for the current day and next six days in ERCOT. This is calculated by combining forecasted wind and solar generation and subtracting that from load forecasts. Net load is a clear metric that helps illustrate how much load is met by thermal and other dispatchable resources, which is particularly valuable as the net load peak has grown increasingly volatile.

WITH load AS (
  SELECT
    ercot_load_forecast_by_forecast_zone.interval_start_local,
    ercot_load_forecast_by_forecast_zone.system_total AS load_forecast,
    ercot_load_forecast_by_forecast_zone.publish_time_local AS load_publish_time_local
  FROM
    ercot_load_forecast_by_forecast_zone
  WHERE
    ercot_load_forecast_by_forecast_zone.system_total IS NOT NULL
),
renewables AS (
  SELECT
    s.interval_start_local,
    s.interval_end_local,
    s.stppf_system_wide AS solar_forecast,
    w.stwpf_system_wide AS wind_forecast,
    s.publish_time_local AS solar_publish_time_local,
    w.publish_time_local AS wind_publish_time_local
  FROM
    ercot_solar_actual_and_forecast_by_geo_region_hourly s
    JOIN ercot_wind_actual_and_forecast_by_geo_region_hourly w ON s.interval_start_local = w.interval_start_local
    -- Round to the nearest minute to ensure matching intervals
    AND date_trunc('minute', s.publish_time_local) = date_trunc('minute', w.publish_time_local)
  WHERE
    s.stppf_system_wide IS NOT NULL
    AND w.stwpf_system_wide IS NOT NULL
)
SELECT
  r.interval_start_local,
  r.interval_end_local,
  l.load_forecast,
  r.solar_forecast,
  r.wind_forecast,
  l.load_forecast - r.solar_forecast - r.wind_forecast AS net_load_forecast,
  GREATEST(
    l.load_publish_time_local,
    r.solar_publish_time_local,
    r.wind_publish_time_local
  ) AS publish_time_local,
  l.load_publish_time_local,
  r.solar_publish_time_local,
  r.wind_publish_time_local
FROM
  renewables r
  JOIN load l ON r.interval_start_local = l.interval_start_local
  -- Solar and wind forecasts are published at XX:55, so get the load forecast from XX:30
  AND date_trunc('minute', l.load_publish_time_local) = (
    date_trunc('minute', r.solar_publish_time_local) - interval '25 minutes'
  )
WHERE
  l.load_forecast IS NOT NULL
ORDER BY
  r.interval_start_local DESC
LIMIT 1000;