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:

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:

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:

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