ERCOT historical backfills need a different pipeline design

A historical backfill is not a real-time collector with a larger date range.

Treating it that way is one of the easiest ways to hit rate limits, create duplicates, restart enormous jobs unnecessarily, and end up unsure whether the resulting history is complete. Backfills deserve their own execution strategy.

Where this guide sits in the pipeline

1. Source
2. Fetch
3. Raw copy
4. Validate
5. Verify completeness
6. Normalize
7. Promote
8. Client system

Live ingestion and backfills serve different goals

Break history into deterministic windows

Turn “load everything since 2023” into a finite list of windows, generated once and stored:

2026-01-01
2026-01-02
2026-01-03
...

Size the windows by data volume, not convenience. A day of a daily report is one small artifact; a day of nodal SCED prices is large enough that ERCOT staff advised requesting hours rather than full days when bandwidth-based limits arrived in March 2026 (Discussion #132). For high-volume products, split each day into hour-sized or multi-hour windows.

Persist backfill state

CREATE TABLE backfill_window (
    dataset        text        NOT NULL,
    window_start   timestamptz NOT NULL,
    window_end     timestamptz NOT NULL,
    status         text        NOT NULL DEFAULT 'pending',  -- pending | complete | failed
    attempts       integer     NOT NULL DEFAULT 0,
    rows           integer,
    checksum       text,         -- of the raw artifact
    completed_at   timestamptz,
    PRIMARY KEY (dataset, window_start)
);

The checksum belongs to the raw artifact. When a window is downloaded again, it tells you whether the upstream data changed or you simply received the same thing twice.

Make loading idempotent

Rerunning a window must not create duplicate records. Either replace the window's rows inside one transaction, or upsert on the dataset's natural key:

INSERT INTO rt_lmp (sced_timestamp, settlement_point, lmp, source_window)
VALUES (%(sced_timestamp)s, %(settlement_point)s, %(lmp)s, %(source_window)s)
ON CONFLICT (sced_timestamp, settlement_point)
DO UPDATE SET lmp = EXCLUDED.lmp,
              source_window = EXCLUDED.source_window;

Choose the key carefully. In ERCOT's local time, the repeated hour in November produces two sets of rows with the same clock time. Store timestamps with their UTC offset (timestamptz in PostgreSQL), or include ERCOT's repeated-hour flag in the key; otherwise the second hour silently overwrites the first.

Preserve raw history

Keep each raw artifact exactly as downloaded, keyed by window and download time. Don't overwrite a verified artifact with a new download — store the new one alongside it and compare checksums. If upstream data changed, you want both versions and a record of when you noticed.

Verify each period

No window should become complete just because its task returned success. Run the same completeness check the live pipeline uses — expected intervals and expected entities — before marking it complete and promoting it. Detecting missing ERCOT market data covers the check.

Resume rather than restart

A three-year backfill will be interrupted: by a deploy, a 429, an upstream incident. Because every window's state is stored, resuming is just a query for windows that aren't complete yet. Nothing already verified is downloaded again, and a failure costs one window rather than the whole run.

Keep live collection independent

Run backfills as separate jobs with their own queue and schedule, so a backfill that stalls or fails never delays today's intervals. Promote backfilled periods through the same validation, into the same tables, so that downstream users see one continuous history.

Rate-limit backfills separately

Unless ERCOT tells you otherwise, assume a backfill and your live collector draw on the same API allowance. Give the backfill its own throttle and a lower priority, and pause it automatically when live collection falls behind or starts receiving 429s. Don't look for extra headroom by spreading work across additional accounts: ERCOT staff have cited attempts to bypass usage limits among the causes of degraded API performance (Discussion #124). Building under rate limits covers windowing and backoff.

Verify what history is actually available

Retention and historical coverage differ by product and by channel. A June 2026 question on ERCOT's discussion board (Discussion #142) asked, for several Secure EWS products, whether broad historical requests behave as if only a short recent window is searchable, and whether older reports need a separate process. It had no replies when we reviewed it, so we don't treat its observations as facts about ERCOT's systems.

It is still a useful checklist to work through for each product before planning a backfill:

• Does the request window refer to publication time or operating time?
• How far back does the product's history go through this channel?
• How large a window can you request before results are truncated or throttled?
• How do you tell “no data exists” apart from “request too large” or “not available through this channel”?

If a channel can't reach far enough back, the archived report files may be the better source for history — see ERCOT API vs file downloads.

Practical takeaway

• ✓Generate and store the full list of windows before starting.
• ✓Size windows by data volume; go below a day for nodal data.
• ✓Load idempotently, with a key that survives the repeated November hour.
• ✓Keep every raw artifact, with checksums.
• ✓Mark a window complete only after the completeness check passes.
• ✓Throttle backfills below live collection, within the same allowance.