How to Build a Backfill Process When Your Data Source Was Added Too Late

A historical backfill is easy to underestimate. Your live ingestion path is working, but a new source, parser, or product field arrives after months of relevant records already exist. The tempting response is a long-running script that loops over old pages and inserts whatever it finds.

That approach tends to fail operationally: a deploy interrupts the script, pagination shifts, a boundary date is collected twice, the source returns 429 Too Many Requests, or a retry creates duplicate rows. Treat the task instead as a resumable data migration. The crawler or extraction provider is only one part of the system; state, delivery guarantees, and auditability belong in your own orchestration and storage layers.

This guide lays out a practical design for backfilling historical web data without turning the job into an unrepeatable one-off.

Define the backfill contract before making requests

Write down what the job is intended to produce:

• Source scope: hosts, feeds, sections, or API endpoints included.
• Historical range: the exact earliest and latest instants to include.
• Record identity: the stable key that makes a source item unique.
• Completeness rule: whether success means every listed page was visited, every API cursor was exhausted, or every time window reached a known terminal condition.
• Destination behavior: insert-only, upsert, or versioned history.
• Operational limits: allowed request rate, concurrency, retries, and deadline.

This contract prevents a common mistake: using the run ID as record identity. A run ID identifies an attempt. It should not decide whether a web record is new. The same historical record may be encountered by a retry, a re-run, or a later repair process.

A useful separation looks like this:

Backfill plan → windows → pages/cursors → fetches → normalized records → idempotent writes
                     ↓                       ↓                 ↓
                checkpoints             raw evidence       provenance

Each layer has a different responsibility. The plan defines intended coverage; checkpoints enable resumption; the fetch layer obeys source limits; destination constraints prevent duplicates; and provenance makes later investigation possible.

Use UTC and half-open time windows

Ambiguous timestamps create quiet data loss. Define all planning boundaries in UTC using RFC 3339 values such as 2024-01-01T00:00:00Z. RFC 3339 specifies fully qualified timestamps and the Z UTC designator, avoiding accidental dependence on a machine's local timezone or daylight-saving transitions. See RFC 3339.

Partition the full interval into non-overlapping, half-open windows:

[start, end)

For example:

[2023-01-01T00:00:00Z, 2023-02-01T00:00:00Z)
[2023-02-01T00:00:00Z, 2023-03-01T00:00:00Z)
[2023-03-01T00:00:00Z, 2023-04-01T00:00:00Z)

A record stamped exactly 2023-02-01T00:00:00Z belongs only to the second window. This is a direct application of range boundary semantics: adjacent ranges can meet at an endpoint without overlapping. PostgreSQL's range documentation is a useful reference for these semantics: Range Types.

Choose window size based on source behavior, not convenience. A high-volume source may need hourly or daily windows. A sparse archive may work well by month. Smaller windows reduce rework after a failure; larger windows reduce checkpoint overhead. Start conservatively, then adjust after measuring page counts and response patterns.

Model state explicitly: plan, window, and page

A process is resumable only if it saves progress after durable work is complete. Do not store a single vague value such as last_processed_at. Persist state at the narrowest practical unit: a window plus its continuation state.

A minimal schema might include:

create table backfill_window (
  run_id text not null,
  source_name text not null,
  window_start timestamptz not null,
  window_end timestamptz not null,
  status text not null,
  next_cursor text,
  next_page_url text,
  completed_at timestamptz,
  primary key (run_id, source_name, window_start)
);

For every successful page:

1. Fetch the page or call the endpoint.
2. Validate and normalize the response.
3. Upsert its records and provenance in one durable transaction where possible.
4. Persist the next cursor, page URL, or terminal status.

If the worker stops before step 4, repeating the page is safe because writes are idempotent. If it stops after step 4, it can continue from the saved continuation point.

For APIs, prefer the continuation mechanism the source provides. GitHub's pagination documentation, for example, recommends following the response link header for subsequent pages rather than assuming page counts or constructing navigation yourself: Using pagination in the REST API.

Make delivery idempotent, not merely hopeful

Retries are normal. Workers crash, networks time out, and a response may succeed just as your client loses its connection. Processing therefore has an at-least-once shape: an item can be delivered more than once.

Build the destination to tolerate that reality. AWS describes idempotency as making repeated operations have the same effect as one operation, a core requirement when requests can be retried: Make all responses idempotent. Similarly, Google Cloud notes that retryable pipeline work may execute more than once and recommends idempotent custom sinks: Exactly-once in Dataflow.

Use the strongest identity available:

1. A source-provided immutable record ID.
2. A normalized canonical URL plus a source publication timestamp.
3. A deterministic content hash, with carefully selected fields.

For example:

dedupe_key = sha256(source_host + "\n" + canonical_url + "\n" + published_at_utc)

Put a unique constraint on that key and use an upsert. Keep backfill_run_id as provenance, not part of the uniqueness definition. If the source can revise an item, model that explicitly with a content hash or a version table instead of silently overwriting history.

Rate control is part of correctness

A backfill is often much more aggressive than live ingestion because it has a large backlog. That makes rate control a correctness requirement, not a performance optimization.

Use a queue per host with controls for:

• maximum concurrent requests,
• requests per second,
• connection and response timeouts,
• maximum retry count,
• exponential backoff with jitter,
• a circuit breaker or pause when failures persist.

Start serially or with very low concurrency. GitHub's API guidance specifically recommends avoiding concurrent requests and using a queue to reduce secondary rate-limit failures; it also recommends exponential backoff after rate-limit failures: REST API best practices.

Handle 429 as a scheduling response. It means the client sent too many requests in a period, and the response may carry Retry-After: 429 Too Many Requests. Retry-After can be a delay in seconds or an HTTP date under RFC 9110. If present, honor it before applying your normal retry policy.

A simple decision rule is:

if response is 429 and Retry-After exists:
  wait until Retry-After permits retry
else if response is transient and attempts remain:
  wait with exponential backoff plus jitter
else:
  mark page failed and retain enough state for repair

Do not hide permanently failed pages inside a generic retry loop. Put them in a repair queue with the request details, error class, and latest attempt time.

Ready to validate a small extraction path before scheduling the full archive? Create a PagePith account and keep the backfill state, deduplication, and audit records in your own system: Start a staged run.

Preserve provenance alongside normalized data

A backfill is likely to be questioned later: Which URL produced this value? Which parser version extracted it? Did the source page change after the run?

Store enough information to answer those questions. The W3C PROV model frames provenance around entities, activities, and agents, and highlights its value for trust and reproducibility: PROV Primer.

For each fetched page or extracted record, capture:

source_url
canonical_url
fetched_at_utc
request_parameters
http_status
response_content_hash
parser_version
source_published_at_utc
backfill_run_id
window_start_utc
window_end_utc

When historical accuracy matters, retain the raw response where policy and storage constraints allow, or at least retain a content hash plus durable location metadata. That gives you a reproducible input when a parser changes or a source corrects past content.

A small, honest PagePith validation example

Before running a broad job, validate the workflow on one or two windows and a limited URL set. The supplied PagePith proof shows a successful fetch-tier request for the RFC 3339 page at https://www.rfc-editor.org/rfc/rfc3339.html. The result contained 38,079 characters of content and began with the RFC's heading and publication metadata.

That is useful evidence for a narrow smoke test: confirm that your extraction step receives content, attaches the requested URL and fetch timestamp as provenance, computes a hash, and can write the result idempotently. It is not evidence that PagePith manages your historical range, pagination cursor, retry queue, or database checkpoint. Keep those responsibilities in the backfill orchestrator.

A staged validation checklist:

1. Run one UTC window against a small source sample.
2. Confirm every persisted record has a stable dedupe key.
3. Interrupt the worker deliberately and resume from the saved checkpoint.
4. Re-run the same window and verify record counts do not grow unexpectedly.
5. Inspect 429, timeout, and parse-error behavior in logs.
6. Only then widen the date range and cautiously raise concurrency.

Respect crawl rules and source policy

Before fetching, verify that the use is authorized and aligned with the source's terms and technical requirements. For crawler behavior, RFC 9309 says crawlers that successfully retrieve robots.txt must follow parseable rules. It also makes clear that robots.txt is not an access-control mechanism: RFC 9309. In practice, crawl permission, authentication, contractual terms, and rate limits are separate checks.

Launch with measurable completion criteria

A backfill is done when every planned window is either completed or explicitly triaged—not when a process exits with code zero. Report at least:

• planned versus completed windows,
• pages fetched and pages permanently failed,
• records attempted, inserted, updated, and deduplicated,
• request and retry counts by host,
• rate-limit events,
• records missing required provenance.

Keep the plan and run metadata after completion. Historical data is frequently reprocessed after parser changes, source corrections, or new product requirements. A well-designed first backfill becomes the repeatable migration framework for the next one.

Build the small staged workflow first, prove that it resumes and deduplicates correctly, then expand it with deliberate rate control. Create your PagePith account to begin testing the extraction step in that workflow: Sign up.