Backfill and Incrementally Sync a Dataset

Backfill a dataset once, then keep it current with high-watermark incremental syncs

Backfill a Grid Status dataset, then keep your copy current using its latest stored timestamp.

This pattern has two phases:

1. Run an initial backfill with no start or end parameters to fetch all available rows.

2. Before each later run, derive a high watermark from the latest time-index value in your copy and fetch only newer rows.

What is a High Watermark?

A high watermark is the latest timestamp your system has successfully written. It is used for determining where the next incremental sync should resume.

For datasets with a publish_time_column, use the publish time as your high watermark. Forecast datasets, such as ERCOT Load Forecast by Forecast Zone, can publish new versions for the same forecast interval, so watermarking on the time index alone can miss later publications.

Setup

Pick a dataset with a time_index_column, then create a local CSV file.

from datetime import UTC, datetime, timedelta
from pathlib import Path

import pandas as pd

DATASET_ID = "ercot_fuel_mix"

data_path = Path(f"{DATASET_ID}.csv")

# Read the time-index column from metadata instead of hard-coding it.
metadata = client.get_dataset_metadata(DATASET_ID)
TIME_INDEX_COLUMN = metadata["time_index_column"]

Phase 1: Initial Backfill

The first run omits start and end, so it fetches all available rows for the dataset.

print(f"Backfilling all available rows for {DATASET_ID}...")
df = client.get_dataset(DATASET_ID)

df.to_csv(data_path, index=False)

print(f"Backfilled {len(df):,} rows")

Phase 2: Incremental Sync

After the initial backfill, each sync finds the high watermark in the stored data and requests rows after it. Schedule this phase in your own system with a cron job, orchestrated workflow, or manual trigger.

# Grid Status returns rows oldest first, so the last row is the high watermark.
watermark = pd.read_csv(data_path, usecols=[TIME_INDEX_COLUMN]).iloc[-1, 0]

# Grid Status loads complete intervals. Since the API's start filter is
# inclusive, advance slightly to request only records after the last interval
# you stored.
start = (
    datetime.fromisoformat(watermark) + timedelta(microseconds=1)
).isoformat()

print(f"Fetching {DATASET_ID} rows after {watermark}...")
df = client.get_dataset(DATASET_ID, start=start)

if len(df) == 0:
    print("No new rows.")
else:
    # Append only the newly returned rows to the existing local export.
    df.to_csv(data_path, mode="a", header=False, index=False)

    print(f"Synced {len(df):,} rows")

For a database or warehouse, use an indexed MAX(time_index_column) query instead of reading the last CSV row.

Use Publish Time for Forecast Datasets

For forecast datasets, use the dataset's publish_time_column as the high watermark. Sort by publish time, then time index, before each write so the last stored row always contains the latest publication.

DATASET_ID = "ercot_load_forecast_by_forecast_zone"
data_path = Path(f"{DATASET_ID}.csv")

metadata = client.get_dataset_metadata(DATASET_ID)
WATERMARK_COLUMN = metadata["publish_time_column"]
TIME_INDEX_COLUMN = metadata["time_index_column"]

# Initial backfill: write rows in watermark order.
df = client.get_dataset(DATASET_ID)
df = df.sort_values([WATERMARK_COLUMN, TIME_INDEX_COLUMN])
df.to_csv(data_path, index=False)

# Read the latest publish time already in your destination.
watermark = pd.read_csv(data_path, usecols=[WATERMARK_COLUMN]).iloc[-1, 0]

# Fetch forecasts published after the high watermark.
publish_time_start = (
    datetime.fromisoformat(watermark) + timedelta(microseconds=1)
).isoformat()

df = client.get_dataset(
    DATASET_ID,
    publish_time_start=publish_time_start,
)

if len(df) > 0:
    # Preserve watermark order when appending each incremental batch.
    df = df.sort_values([WATERMARK_COLUMN, TIME_INDEX_COLUMN])
    df.to_csv(data_path, mode="a", header=False, index=False)

If you want to be extra conservative and avoid gaps, run each incremental sync with a small overlap window and upsert by the dataset's primary key columns while preserving the same sort order.

Initial Backfill for Large Datasets

Fetching all available rows in a single request is best suited to smaller datasets. For a large dataset, query the initial backfill one day at a time. Smaller requests limit the amount of work that must be retried after a failure and make it easier to validate that every day was fetched.

This example queries one-day windows beginning January 1, 2025:

day = datetime(2025, 1, 1, tzinfo=UTC)
backfill_end = datetime.now(UTC)

while day < backfill_end:
    next_day = min(day + timedelta(days=1), backfill_end)
    df = client.get_dataset(
        DATASET_ID,
        start=day.isoformat(),
        end=next_day.isoformat(),
        # Uncomment to use market-local time boundaries.
        # timezone="market",
    )
    # Process or store this day's data.
    day = next_day

Periodically Run a Full Reconciliation

If maintaining an exact copy is mission-critical, we recommend complementing incremental syncs with periodic full reconciliations. Re-fetch the complete dataset and reconcile it with your destination. This provides an independent check on the incremental pipeline and increases confidence that missed rows, processing errors, late corrections, and historical revisions are reflected in your copy.

Avoid Managing Your Own Incremental Sync

If you do not want to operate your own replication pipeline, our Snowflake Marketplace listing provides SQL access to nearly all Grid Status datasets, generally within 1–2 minutes of API publication. Grid Status keeps the shared tables current, so you do not need to manage backfills or incremental syncs.

For file-based workflows, Bulk CSV Downloads delivers the complete catalog as compressed CSV files through Amazon S3. Grid Status refreshes the export daily, and a single AWS CLI sync command keeps a local folder current by downloading only missing or changed files, including historical partitions updated with corrections or late-arriving data.