Re-Run a Scrape Batch Without Duplicate Rows
Run a batch of URLs through a scrape pipeline and load results with idempotent upserts, so a re-run updates rows instead of duplicating them.
On this page
The Problem: A Re-Run That Duplicates Every Row
You run a nightly batch over 50 product pages, load the results into Postgres, and the next morning someone re-runs the job because a few pages failed. Nothing errors. Everything succeeds — twice. Your product table now holds two rows per page, your price dashboard averages each item with itself, and nobody notices until a downstream join misbehaves.
The fix is not in the scraper. It is in the load step. This post rebuilds that pipeline so a re-run is a no-op by construction: same batch, any number of executions, one row per page. The examples below fetch through the FineData API, but every loading technique here is plain Postgres and psycopg2 — swap the fetch for requests and BeautifulSoup and nothing else changes.
What a Naive INSERT Pipeline Does on a Second Run
The starting point is a loop like this. One URL, one fetch, one INSERT:
import requests
# Single-scrape endpoint per the API reference: POST /api/v1/async/scrape
API = "https://api.finedata.ai/api/v1/async/scrape"
HEADERS = {"Authorization": "Bearer fd_your_api_key"}
EXTRACT = {
"title": "h1",
"price": ".price",
"stock": ".availability",
}
def fetch_product(url: str) -> dict:
resp = requests.post(
API,
# "url" is the required field on the single-scrape request body;
# "extract_rules" is the optional CSS-selector extraction spec.
json={"url": url, "extract_rules": EXTRACT},
headers=HEADERS,
timeout=300,
)
resp.raise_for_status()
# Response envelope per the API reference: {"data": {"extract": {...}}}.
# Field names depend on the response envelope; the load logic is the point.
return resp.json()["data"]["extract"]
URLS = [f"https://store.example.com/products/{i}" for i in range(1, 51)]
def load_naive(conn) -> None:
with conn.cursor() as cur:
for url in URLS:
fields = fetch_product(url)
cur.execute(
"INSERT INTO scraped_products (url, title, price, stock)"
" VALUES (%s, %s, %s, %s)",
(url, fields["title"], fields["price"], fields["stock"]),
)
conn.commit()
The first run is fine: 50 fetches, 50 INSERTs, 50 rows. The second run is also fine, by its own lights — and that is exactly the issue. Measure it before touching anything:
-- before the re-run
SELECT COUNT(*) FROM scraped_products;
-- 50
SELECT url, COUNT(*) AS copies
FROM scraped_products
GROUP BY url
HAVING COUNT(*) > 1;
-- 0 rows returned
Run the job again and re-check:
SELECT COUNT(*) FROM scraped_products;
-- 100
SELECT url, COUNT(*) AS copies
FROM scraped_products
GROUP BY url
HAVING COUNT(*) > 1
ORDER BY copies DESC;
-- 50 rows, every one with copies = 2
Two things stand out. First, the logs of run one and run two are identical — same status codes, same row counts per statement, no warning anywhere. Second, the naive loop has no memory, so a crash at item 23 forces a full restart, which re-inserts items 1 through 22 and duplicates the prefix even without a deliberate re-run. Silent duplication and crash-induced duplication are the same bug.
Choosing a Stable Deduplication Key for Scraped URLs
Before you can dedupe, you have to define “the same page.” Raw URLs are a weaker identity than they look. The same product often arrives as http and https, with and without a trailing slash, with utm parameters, and with mixed-case hosts. Normalize first, then hash:
import hashlib
from urllib.parse import parse_qsl, urlencode, urlsplit, urlunsplit
TRACKING = ("utm_", "gclid", "fbclid", "msclkid", "mc_", "ref")
def canonical_url(raw: str) -> str:
parts = urlsplit(raw.strip())
host = (parts.hostname or "").lower().rstrip(".")
if parts.port and parts.port not in (80, 443):
host = f"{host}:{parts.port}"
path = parts.path or "/"
path = "/" + "/".join(p for p in path.split("/") if p != "")
if path != "/":
path = path.rstrip("/") or "/"
keep = [
(k, v)
for k, v in parse_qsl(parts.query, keep_blank_values=True)
if not k.lower().startswith(TRACKING)
]
query = urlencode(sorted(keep)) if keep else ""
scheme = "https" if parts.scheme in ("http", "https") else parts.scheme
return urlunsplit((scheme, host, path, query, ""))
def url_hash(raw: str) -> str:
return hashlib.sha256(canonical_url(raw).encode()).hexdigest()
What that does to real input:
https://store.example.com/products/42?utm_source=newsletter&utm_campaign=weekly
-> https://store.example.com/products/42
https://STORE.example.com/products/42/
-> https://store.example.com/products/42
If the site publishes a canonical URL (rel=canonical, or a url field in its JSON-LD), prefer that over your own normalization — the site knows its identity better than your regex does.
| Candidate key | What it covers | How it fails |
|---|---|---|
| Raw URL | Nothing, in practice | Any query param, scheme change, or trailing slash mints a new “product” |
| Normalized URL hash | Tracking params, host case, scheme, trailing slash | Renumbered IDs, locale prefixes (/en/, /de/), canonical moves |
| Source product ID | The strongest option, when the site exposes stable IDs in the path or structured data | ID scheme changes; pages that lack an ID entirely |
| Content hash | Not an identity key at all | Any price or title change creates a new “product” |
My recommendation: normalized URL hash as the row identity, source product ID instead when you can extract it reliably, and content hash reserved for change detection — which is what we build two sections from now. Teams that skip normalization and dedupe on raw URL get away with it until a marketing email appends utm_source and every product doubles overnight.
Enforcing Uniqueness at the Schema Level First
Opinion: do not fix this in the loader first. An application-level “check if exists, then insert” is a race with yourself, and once you have two workers it is a race you lose. The constraint is the actual fix; the upsert we write next is just the polite client for it. Put the constraint in before changing any Python:
CREATE TABLE scraped_products (
id BIGSERIAL PRIMARY KEY,
url_hash CHAR(64) NOT NULL UNIQUE,
url TEXT NOT NULL,
title TEXT,
price_cents INTEGER,
stock_status TEXT,
content_hash CHAR(64),
run_id TEXT,
scraped_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
content_changed_at TIMESTAMPTZ
);
-- batch lookups: "what did run X produce?"
CREATE INDEX scraped_products_run_id_idx ON scraped_products (run_id);
If the table already contains duplicates, the UNIQUE creation fails — which is the constraint doing its job on day one. Deduplicate once, keeping the newest row per url_hash:
WITH ranked AS (
SELECT id,
ROW_NUMBER() OVER (
PARTITION BY url_hash
ORDER BY scraped_at DESC, id DESC
) AS rn
FROM scraped_products
)
DELETE FROM scraped_products
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
Eyeball what you are about to delete first with DISTINCT ON, which keeps the same winner per url_hash:
SELECT DISTINCT ON (url_hash) *
FROM scraped_products
ORDER BY url_hash, scraped_at DESC, id DESC;
One design note: I kept a BIGSERIAL id instead of making url_hash the primary key. Surrogate keys keep foreign keys and partial rewrites cheaper, and url_hash remains the uniqueness boundary. Reasonable people make url_hash the PK and drop a column; both work, so pick one deliberately.
Rewriting the Load Step as an Upsert with ON CONFLICT
Now replace the INSERT loop. The Postgres shape: try to insert; on a conflict, update the existing row from excluded, the pseudo-row holding the values you just tried to insert.
INSERT INTO scraped_products AS sp
(url_hash, url, title, price_cents, stock_status, content_hash, run_id)
VALUES (%s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (url_hash) DO UPDATE SET
url = excluded.url,
title = excluded.title,
price_cents = excluded.price_cents,
stock_status = excluded.stock_status,
content_hash = excluded.content_hash,
run_id = excluded.run_id,
scraped_at = now(),
updated_at = now();
In Python, load the whole batch in one round trip with execute_values instead of 50 sequential INSERTs:
from psycopg2.extras import execute_values
UPSERT_SQL = """
INSERT INTO scraped_products AS sp
(url_hash, url, title, price_cents, stock_status, content_hash, run_id)
VALUES (%s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (url_hash) DO UPDATE SET
url = excluded.url,
title = excluded.title,
price_cents = excluded.price_cents,
stock_status = excluded.stock_status,
content_hash = excluded.content_hash,
run_id = excluded.run_id,
scraped_at = now(),
updated_at = now()
"""
def load_batch(conn, run_id: str, rows: list[dict]) -> None:
payload = [
(
row["url_hash"], row["url"], row["title"], row["price_cents"],
row["stock_status"], row["content_hash"], run_id,
)
for row in rows
]
with conn.cursor() as cur:
execute_values(cur, UPSERT_SQL, payload)
conn.commit()
Two details. execute_values defaults to page_size=100, so a 50-row batch is one statement, and larger batches split transparently. And every column you care about must appear in the DO UPDATE SET list — ON CONFLICT updates only what you name, so forgetting a column means it silently keeps stale values. That omission is the most common bug in upsert migrations.
Skipping Unchanged Pages with a Content Hash Guard
The upsert is idempotent now, but it has a side effect: every re-run rewrites all 50 rows, so updated_at stops meaning anything. If your dashboard sorts by “recently updated,” a nightly re-run makes everything look recently updated forever.
Hash the fields you actually extract — not the raw HTML. Raw HTML changes constantly (asset hashes, CSRF tokens, A/B classes, whitespace), so an HTML hash marks every page as changed on every run. Normalize the extracted values, then hash:
import hashlib
import json
from decimal import Decimal, InvalidOperation
def to_cents(price: str) -> int | None:
"""Integer cents, or None when the price cannot be parsed."""
try:
cleaned = str(price).replace("$", "").replace(",", "")
return int(Decimal(cleaned) * 100)
except (InvalidOperation, ValueError):
return None
def content_hash(title: str, price_cents: int | None, stock_status: str) -> str:
payload = json.dumps(
{
"title": (title or "").strip(),
# None serializes as null -- a missing price is not $0.
"price_cents": price_cents,
"stock": (stock_status or "").strip().lower(),
},
sort_keys=True,
separators=(",", ":"),
)
return hashlib.sha256(payload.encode()).hexdigest()
The normalization inside the hash matters: price as integer cents, not the string the page gave you, so "12.99", "$12.99", and "12.990" all land on the same identity. And an unparseable price is None, which loads as NULL and hashes as null — never as 0, which would invent a price the page never showed. Then extend the conflict clause:
ON CONFLICT (url_hash) DO UPDATE SET
url = excluded.url,
title = excluded.title,
price_cents = excluded.price_cents,
stock_status = excluded.stock_status,
content_hash = excluded.content_hash,
run_id = excluded.run_id,
scraped_at = now(),
content_changed_at = now(),
updated_at = now()
WHERE excluded.content_hash IS DISTINCT FROM sp.content_hash;
Rows that conflict but carry identical content are skipped entirely — no update, no updated_at bump. Rows that changed get rewritten with a fresh content_changed_at. On the very first run against a populated table, sp.content_hash is NULL, and IS DISTINCT FROM treats NULL as different from any value, so the update fires and backfills the hash. A plain != would have silently skipped those rows.
A semantics decision you will have to defend eventually: I keep updated_at meaning “last content change” and scraped_at meaning “last successful fetch.” Some teams expect updated_at to mean last-seen instead. If that is your camp, add a separate last_seen_at column and update it unconditionally — do not overload one column to mean both, because downstream consumers will guess wrong.
Tracking Per-URL Status So Re-Runs Resume Instead of Restart
The upsert fixes duplicates. It does not fix waste: a re-run still re-fetches all 50 pages, and a crash at item 23 tells you nothing about what finished. Give the run a ledger:
CREATE TABLE scrape_run_items (
run_id TEXT NOT NULL,
url_hash CHAR(64) NOT NULL,
url TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'pending', -- pending | succeeded | failed
attempts INTEGER NOT NULL DEFAULT 0,
last_error TEXT,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (run_id, url_hash)
);
Seed it once per run — DO NOTHING makes seeding re-entrant — then select only what still needs work:
INSERT INTO scrape_run_items (run_id, url_hash, url)
VALUES (%s, %s, %s)
ON CONFLICT (run_id, url_hash) DO NOTHING;
SELECT url
FROM scrape_run_items
WHERE run_id = %s
AND status IN ('pending', 'failed')
AND attempts < 3
ORDER BY url_hash;
The same two statements as Python helpers, used by everything that follows:
def seed_pending(conn, run_id: str, urls: list[str]) -> None:
"""Idempotent: re-seeding an existing run is a no-op."""
with conn.cursor() as cur:
execute_values(
cur,
"INSERT INTO scrape_run_items (run_id, url_hash, url)"
" VALUES %s"
" ON CONFLICT (run_id, url_hash) DO NOTHING",
[(run_id, url_hash(u), u) for u in urls],
)
conn.commit()
def select_pending(conn, run_id: str) -> list[str]:
"""URLs that still need work: never-finished plus retryable failures."""
with conn.cursor() as cur:
cur.execute(
"SELECT url FROM scrape_run_items"
" WHERE run_id = %s"
" AND status IN ('pending', 'failed')"
" AND attempts < 3"
" ORDER BY url_hash",
(run_id,),
)
return [row[0] for row in cur.fetchall()]
The load and the checkpoint write must share one transaction. If the upsert commits but the status update does not, a crash re-processes a URL that already loaded — harmless now, but you lose the ability to trust the ledger:
# Same guarded statement as above, one row at a time, for the per-URL path.
UPSERT_SINGLE = """
INSERT INTO scraped_products AS sp
(url_hash, url, title, price_cents, stock_status, content_hash, run_id)
VALUES (%s, %s, %s, %s, %s, %s, %s)
ON CONFLICT (url_hash) DO UPDATE SET
url = excluded.url,
title = excluded.title,
price_cents = excluded.price_cents,
stock_status = excluded.stock_status,
content_hash = excluded.content_hash,
run_id = excluded.run_id,
scraped_at = now(),
content_changed_at = now(),
updated_at = now()
WHERE excluded.content_hash IS DISTINCT FROM sp.content_hash
"""
def process_url(conn, run_id: str, url: str, fields: dict | None = None) -> None:
"""Fetch one page (or accept pre-fetched fields) and load + checkpoint it."""
if fields is None:
fields = fetch_product(url) # POST /api/v1/async/scrape, body {"url": url, ...}
uh = url_hash(url)
# None (unparseable price) flows through as NULL in price_cents and as
# null in the content hash -- a malformed price must never masquerade
# as $0 in the row or in change detection.
price_cents = to_cents(fields["price"])
values = (
uh,
url,
fields["title"],
price_cents,
fields["stock"],
content_hash(fields["title"], price_cents, fields["stock"]),
run_id,
)
# No explicit BEGIN: psycopg2 opens a transaction implicitly on the first
# statement, and commit()/rollback() below close it.
with conn.cursor() as cur:
try:
cur.execute(UPSERT_SINGLE, values)
cur.execute(
"UPDATE scrape_run_items"
" SET status = 'succeeded', attempts = attempts + 1,"
" last_error = NULL, updated_at = now()"
" WHERE run_id = %s AND url_hash = %s",
(run_id, uh),
)
conn.commit()
except Exception:
conn.rollback()
raise
def mark_failed(conn, run_id: str, url: str, error: str) -> None:
with conn.cursor() as cur:
cur.execute(
"UPDATE scrape_run_items"
" SET status = 'failed', attempts = attempts + 1,"
" last_error = %s, updated_at = now()"
" WHERE run_id = %s AND url_hash = %s",
(error[:500], run_id, url_hash(url)),
)
conn.commit()
If the batch is large, stop fetching synchronously and submit the pending URLs through FineData’s async batch endpoint — the ledger logic does not change:
import time
pending = select_pending(conn, run_id)
# POST /api/v1/async/batch -- "requests" is the required body field: an array
# where each item supports the same fields as the single-scrape request
# (AsyncScrapeRequest), including the required "url". "callback_url" is the
# optional webhook fired when every job in the batch completes.
submit = requests.post(
"https://api.finedata.ai/api/v1/async/batch",
headers=HEADERS,
json={"requests": [{"url": u, "extract_rules": EXTRACT} for u in pending]},
timeout=30,
)
submit.raise_for_status()
submit = submit.json()
batch_id = submit["batch_id"] # response field per the API reference
# The submit response echoes one job per request. Record the job id -> URL
# pairing here, once, while the requests are still fresh in our hands --
# every lookup after this point goes through that dict, never through
# array position.
url_by_job = {
job["job_id"]: url for job, url in zip(submit["jobs"], pending)
}
while True:
# GET /api/v1/async/batch/{batch_id} -- include_results=true attaches each
# job's payload so we can load without a second fetch.
resp = requests.get(
f"https://api.finedata.ai/api/v1/async/batch/{batch_id}",
params={"include_results": True},
headers=HEADERS,
timeout=30,
)
resp.raise_for_status()
batch = resp.json()
# batch["status"] per the API reference: pending | processing | completed | failed | cancelled
if batch["status"] in ("completed", "failed", "cancelled"):
break
time.sleep(5)
# Match results by job id, not position: the status response can reorder its
# jobs array or drop jobs entirely, and a positional zip would then write
# page A's price onto page B's row. A job missing from the results is an
# explicit failure, not a silent shift.
results = {job["job_id"]: job for job in batch["jobs"]}
for job_id, url in url_by_job.items():
job = results.get(job_id)
if job is None:
mark_failed(conn, run_id, url, "job missing from batch results")
elif job["status"] == "completed":
# Same response envelope as the single scrape: {"data": {"extract": {...}}}
process_url(conn, run_id, url, job["data"]["extract"])
else:
mark_failed(conn, run_id, url, str(job.get("error", "job did not complete")))
When the batch completes, feed each result through the same process_url transaction — the checkpoint row and the upsert stay in lockstep regardless of how the bytes arrived, and because results are matched by job id, a reordered or dropped job on the API side can never cross-wire a URL. If you would rather skip polling, pass a callback_url on the batch submission and mark items from the webhook handler. The retry side of this pattern — capping attempts and re-driving only what failed — has its own writeup in Re-Drive Only the Failures: Handling Partial Batch Results.
Proving Idempotency: Run the Batch Twice and Measure
An upsert you have not tested is a hypothesis. Make the double-run a script so it runs in CI or as a make target, not as a thing you do by hand once and forget. It accepts the same --run-id the crontab passes, so the scheduled invocation and the tested invocation share one code path:
import argparse
import psycopg2
def run_batch(conn, run_id: str) -> None:
"""One full pass: seed the ledger, work what is still pending, record failures."""
seed_pending(conn, run_id, URLS)
for url in select_pending(conn, run_id):
try:
process_url(conn, run_id, url)
except Exception as exc:
mark_failed(conn, run_id, url, str(exc))
def snapshot(conn):
with conn.cursor() as cur:
cur.execute("SELECT
Related Articles
Build a Scraping Pipeline You Can Safely Restart Mid-Run
A tutorial on checkpointing batch scraping runs with run manifests and idempotent writes, so a crashed pipeline resumes exactly where it stopped.
TutorialBuild a Three-Layer Scraping Pipeline: Raw, Clean, Curated
Build a scraping pipeline in three layers: raw HTML landing zone, cleaned records, curated dataset. Re-run extraction without re-fetching a page.
TutorialBatch a URL List Into Partitioned Parquet Files
Turn a list of URLs into partitioned Parquet files with a staged Python pipeline: fetch, extract, validate, and load, with a run manifest you can audit.