> For the complete documentation index, see [llms.txt](https://docs.gridstatus.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.gridstatus.io/developers/guides/recipes/backfill-and-incremental-sync.md).

# Backfill and Incrementally Sync a Dataset

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.

{% hint style="info" %}
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](https://www.gridstatus.io/datasets/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.
{% endhint %}

## Setup

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

```python
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.

```python
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.

```python
# 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.

```python
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:

```python
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](/developers/snowflake-guides/getting-started.md) 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](/developers/bulk-csv-downloads/getting-started.md) 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.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.gridstatus.io/developers/guides/recipes/backfill-and-incremental-sync.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.