incremental-fetch

Incremental Fetch

Build data pipelines that never lose progress and never re-fetch existing data.

The Two Watermarks Pattern

Track TWO cursors to support both forward and backward fetching:

Watermark	Purpose	API Parameter
newest_id	Fetch new data since last run	since_id
oldest_id	Backfill older data	until_id

A single watermark only fetches forward. Two watermarks enable:

• Regular runs: fetch NEW data (since newest_id)
• Backfill runs: fetch OLD data (until oldest_id)
• No overlap, no gaps

Critical: Data vs Watermark Saving

These are different operations with different timing:

What	When to Save	Why
Data records	After EACH page	Resilience: interrupted on page 47? Keep 46 pages
Watermarks	ONCE at end of run	Correctness: only commit progress after full success

fetch page 1 → save records → fetch page 2 → save records → ... → update watermarks

Workflow Decision Tree

First run (no watermarks)?
├── YES → Full fetch (no since_id, no until_id)
└── NO → Backfill flag set?
    ├── YES → Backfill mode (until_id = oldest_id)
    └── NO → Update mode (since_id = newest_id)

Implementation Checklist

1. Database: Create ingestion_state table (see patterns.md)
2. Fetch loop: Insert records immediately after each API page
3. Watermark tracking: Track newest/oldest IDs seen in this run
4. Watermark update: Save watermarks ONCE at end of successful run
5. Retry: Exponential backoff with jitter
6. Rate limits: Wait for reset or skip and record for next run

Pagination Types

This pattern works best with ID-based pagination (numeric IDs that can be compared). For other pagination types:

Type	Adaptation
Cursor/token	Store cursor string instead of ID; can't compare numerically
Timestamp	Use last_timestamp column; compare as dates
Offset/limit	Store page number; resume from last saved page

See references/patterns.md for schemas and code examples.