API Data Extraction

## API Data Extraction Design: [API Name]

### API Assessment

| Attribute | Value |
|-----------|-------|
| API type | [REST / GraphQL / SOAP] |
| Base endpoint | [Base URL pattern] |
| Data format | [JSON / XML / CSV] |
| Rate limit | [Requests per time window] |
| Authentication | [Method] |
| Documentation | [Reference location] |

### Authentication Design

- **Method:** [API Key / OAuth 2.0 / Basic Auth]
- **Credential storage:** [Secrets manager / Environment variable reference]
- **Token refresh:** [Strategy for token expiry -- if applicable]
- **Auth failure handling:** [What happens when auth fails]

### Data Retrieval Strategy

- **Extraction type:** [Full / Incremental]
- **Incremental key:** [Field used for incremental detection, if applicable]
- **State storage:** [Where last extraction point is recorded]
- **Backfill process:** [How to handle initial load or catch-up]
- **Endpoints used:**

| Endpoint | Method | Parameters | Returns | Use Case |
|----------|--------|------------|---------|----------|
| [path] | [GET/POST] | [key params] | [What it returns] | [Why this endpoint] |

### Pagination Design

- **Method:** [Offset / Cursor / Link header / Keyset]
- **Page size:** [Records per page]
- **Last page detection:** [How to know extraction is complete]
- **Estimated pages per run:** [Count based on data volume]
- **Resume on failure:** [How to restart from last successful page]

### Rate Limit Handling

- **Published limit:** [Rate limit from API docs]
- **Throttling strategy:** [Proactive / Reactive / Both]
- **Backoff method:** [Fixed / Exponential / Retry-After header]
- **Budget allocation:** [If shared with other pipelines]
- **Estimated extraction time:** [Based on data volume and rate limits]

### Response Parsing

**Response structure:**
```json
{
  "data": [ ... ],
  "pagination": { "next_cursor": "..." },
  "meta": { "total_count": N }
}

## Rules

1. NEVER include actual API keys, tokens, or credentials in the extraction design -- use placeholder references to a secrets manager
2. ALWAYS specify the pagination strategy -- without it, the extraction will only retrieve the first page of results
3. Rate limit handling must be proactive (throttle before hitting limits), not only reactive (wait after getting a 429) -- proactive throttling is more reliable and does not risk API key suspension
4. The incremental extraction state (last timestamp, last ID, last cursor) must be stored durably outside the pipeline execution -- if the state is only in memory, a crash loses the extraction position
5. NEVER assume API response schemas are stable -- include a schema change detection strategy (log unexpected fields, alert on missing required fields)
6. Error handling must distinguish between retryable errors (429, 5xx, timeout) and non-retryable errors (400, 403, 404) -- retrying a 400 Bad Request wastes rate limit budget
7. Estimated extraction time must account for rate limits AND pagination -- the total time = (total pages x page request time) + (total requests / rate limit) whichever is larger
8. This skill produces the extraction DESIGN, not executable code -- describe the logic, parameters, and flow, not the implementation
9. Backfill strategy must be defined at design time -- every API extraction eventually needs a backfill (initial load, catch-up after downtime, schema migration)
10. ALWAYS include an extraction time estimate based on rate limits and data volume -- this determines whether the extraction fits within the pipeline's time window

## Edge Cases

- **API with no pagination (returns all records at once):** If the dataset is small (under 10,000 records), this is acceptable. If large, request from the API provider whether pagination is available but undocumented. If truly no pagination, design the extraction to handle large responses (streaming JSON parser, memory management).

- **API with inconsistent rate limits (varies by endpoint or plan):** Document rate limits per endpoint. Design the extraction to use the most restrictive limit as the default, with endpoint-specific overrides where documented. Monitor actual 429 responses to calibrate.

- **OAuth token expires during a long extraction:** Design the token refresh to happen proactively before expiry (refresh when 80% of the token lifetime has elapsed). If a 401 occurs mid-extraction, refresh the token and resume from the last successful page (do not restart from the beginning).

- **API returns data in a different timezone than expected:** Explicitly specify the timezone assumption in the extraction design. Convert all timestamps to UTC immediately upon extraction. Document the API's timezone behavior (UTC, server-local, or user-configured).

- **API deprecation or version change:** Include the API version in the extraction design. Monitor the API provider's deprecation notices. Design the extraction to be version-aware: specify the version in the request header or URL, and include a fallback plan for when the version is deprecated (upgrade timeline, breaking change assessment).

## Example

**Input:** "Design data extraction from the HubSpot CRM API to pull contact records into our data warehouse. We have about 50,000 contacts and need daily updates."

**Output:**

## API Data Extraction Design: HubSpot CRM Contacts

### API Assessment

| Attribute | Value |
|-----------|-------|
| API type | REST (HubSpot CRM API v3) |
| Base endpoint | /crm/v3/objects/contacts |
| Data format | JSON |
| Rate limit | 100 requests per 10 seconds (private app), 150,000 requests per day |
| Authentication | Private app access token (Bearer token in Authorization header) |
| Documentation | HubSpot Developer Docs - CRM API |

### Authentication Design

- **Method:** Private app access token (Bearer token)
- **Credential storage:** Stored in [SECRETS_MANAGER/hubspot_access_token]
- **Token refresh:** Private app tokens do not expire but can be rotated. Rotation triggered manually or via secrets manager policy (every 90 days).
- **Auth failure handling:** On 401 response, log the error and alert #data-engineering. Do not retry automatically -- token rotation requires manual action in HubSpot portal.

### Data Retrieval Strategy

- **Extraction type:** Incremental (retrieve contacts modified since last successful run)
- **Incremental key:** hs_lastmodifieddate property (HubSpot's built-in last-modified timestamp)
- **Filter:** Use the HubSpot Search API (/crm/v3/objects/contacts/search) with filter: hs_lastmodifieddate > [last_extraction_timestamp]
- **State storage:** Pipeline state table: pipeline_state.hubspot_contacts.last_modified_at (stored as ISO-8601 UTC)
- **Backfill process:** For initial load or catch-up, set last_modified_at to "1970-01-01T00:00:00Z" to retrieve all contacts. Estimated time for full 50K backfill: ~17 minutes at rate limit.
- **Endpoints used:**

| Endpoint | Method | Parameters | Returns | Use Case |
|----------|--------|------------|---------|----------|
| /crm/v3/objects/contacts/search | POST | filterGroups (hs_lastmodifieddate > timestamp), properties list, limit, after | Matching contacts with requested properties | Incremental extraction |
| /crm/v3/objects/contacts | GET | limit, after, properties | All contacts (paginated) | Full backfill only |

### Pagination Design

- **Method:** Cursor-based (HubSpot returns "paging.next.after" token in response)
- **Page size:** 100 contacts per page (HubSpot maximum)
- **Last page detection:** Response does not include "paging.next" object
- **Estimated pages per run:** ~5 pages for daily incremental (500 modified contacts/day); 500 pages for full backfill
- **Resume on failure:** Store the last successful page's "after" cursor alongside the extraction state. On restart, resume from the stored cursor.

### Rate Limit Handling

- **Published limit:** 100 requests per 10 seconds (10 req/sec effective), 150,000 requests per day
- **Throttling strategy:** Proactive -- space requests at minimum 100ms intervals (10 req/sec target)
- **Backoff method:** If 429 received, use Retry-After header value if present; otherwise exponential backoff starting at 1 second (1s, 2s, 4s, max 30s)
- **Budget allocation:** This pipeline uses ~5 pages/day (0.003% of daily limit). No budget contention expected. If other pipelines are added, revisit.
- **Estimated extraction time:** Daily incremental: ~5 requests x 100ms spacing = ~1 second (plus network latency, ~10 seconds total). Full backfill: ~500 requests at 10 req/sec = ~50 seconds (plus network, ~5 minutes total).

### Response Parsing

**Response structure:**
```json
{
  "total": 487,
  "results": [
    {
      "id": "123",
      "properties": {
        "email": "jane@example.com",
        "firstname": "Jane",
        "lastname": "Doe",
        "company": "Acme Corp",
        "lifecyclestage": "customer",
        "hs_lastmodifieddate": "2026-02-25T14:30:00.000Z"
      },
      "createdAt": "2025-01-15T10:00:00.000Z",
      "updatedAt": "2026-02-25T14:30:00.000Z"
    }
  ],
  "paging": {
    "next": {
      "after": "cursor_token_abc"
    }
  }
}