incremental-fetch

rohunvora 的头像作者 rohunvora

从API构建具有弹性的数据摄取管道。用于创建从外部API(如Twitter、交易所、任何REST API)获取分页数据的脚本时,需要跟踪进度、避免重复、处理速率限制,并支持增量更新和历史回溯。触发器:'从API摄取数据'、'拉取推文'、'获取历史数据'、'从X同步'、'构建数据管道'、'无重下载获取'、'恢复下载'、'回溯旧数据'。不适用于:简单的单次API调用、WebSocket/流连接、文件下载或无分页的API。

📖 中文导读:从API构建具有弹性的数据摄取管道。用于创建从外部API(如Twitter、交易所、任何REST API)获取分页数据的脚本时,需要跟踪进度、避免重复、处理速率限制,并支持增量更新和历史回溯。触发器:'从API摄取数据'、'拉取推文'、'获取历史数据'、'从X同步'、'构建数据管道'、'无重下载获取'、'恢复下载'、'回溯旧数据'。不适用于:简单的单次API调用、WebSocket/流连接、文件下载或无分页的API。 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。

name: incremental-fetch description: "Build resilient data ingestion pipelines from APIs. Use when creating scripts that fetch paginated data from external APIs (Twitter, exchanges, any REST API) and need to track progress, avoid duplicates, handle rate limits, and support both incremental updates and historical backfills. Triggers: 'ingest data from API', 'pull tweets', 'fetch historical data', 'sync from X', 'build a data pipeline', 'fetch without re-downloading', 'resume the download', 'backfill older data'. NOT for: simple one-shot API calls, websocket/streaming connections, file downloads, or APIs without pagination."

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.