Skip to content
Tutorial 15 min read

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.

FE
FineData Engineering · Editorial Policy
|
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 keyWhat it coversHow it fails
Raw URLNothing, in practiceAny query param, scheme change, or trailing slash mints a new “product”
Normalized URL hashTracking params, host case, scheme, trailing slashRenumbered IDs, locale prefixes (/en/, /de/), canonical moves
Source product IDThe strongest option, when the site exposes stable IDs in the path or structured dataID scheme changes; pages that lack an ID entirely
Content hashNot an identity key at allAny 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
#idempotency #upserts #data pipeline #web scraping #python #slot:pipeline

Related Articles