Turn a Batch of Scraped Pages into a Queryable Dataset

Why the Transform and Load Stages Matter

You ran a batch scrape of 500 product pages. Now what? If your answer is “dump the JSON files in a directory and grep them later,” you don’t have a dataset — you have a pile. The fetch stage of a scraping pipeline gets all the attention, but the transform and load stages are what turn raw page output into something you can actually query, join, and diff over time.

This post builds the T and L of that pipeline. We’ll take raw scrape results, normalize them into a canonical record shape, compute stable content hashes so re-scrapes are comparable, collapse URL variants into dedupe keys, and land everything in SQLite with proper upserts. SQLite is the right default here: zero ops, single file, transactional, and fast enough for millions of rows. If you outgrow it, the schema and upsert logic translate almost line-for-line to Postgres.

From Raw HTML to Normalized Page Records

Raw scrape output is noisy. Even when you request markdown instead of HTML, you get navigation remnants, cookie banners, and footer boilerplate mixed into the content. The first job of the transform stage is to define one canonical record shape and force every page through it.

Here’s the schema I use. A dataclass, because it documents itself and gives you type checking for free:

from dataclasses import dataclass
from datetime import datetime, timezone

@dataclass
class PageRecord:
    url: str                 # the exact URL you requested
    canonical_url: str       # normalized dedupe key (see section 3)
    fetched_at: datetime     # when the scrape completed
    title: str               # page <title> or extracted heading
    content: str             # cleaned main text
    content_hash: str        # SHA-256 over normalized text (see section 2)
    http_status: int         # status reported by the scraping API
    first_seen_at: datetime  # set once, never updated
    last_seen_at: datetime   # updated on every upsert

Everything downstream — dedupe, change detection, freshness queries — reads from these fields. If a field doesn’t serve one of those purposes, it doesn’t belong in the table.

Now the cleaning step. Suppose you scraped store.example.com/product/123 and got back markdown with script leftovers and site boilerplate. Here’s the transform that strips the noise (URL canonicalization is the job of the single canonical_url function in section 3):

import re

BOILERPLATE_PATTERNS = [
    r"Skip to main content",
    r"Accept all cookies.*?(?:Reject|Settings)",
    r"Subscribe to our newsletter.*",
    r"©\s*store\.example\.com.*",
]

def clean_content(raw_markdown: str) -> str:
    text = raw_markdown
    # drop script/style blocks that survive into markdown
    text = re.sub(r"```(?:script|style).*?```", "", text, flags=re.DOTALL)
    text = re.sub(r"<script.*?</script>", "", text, flags=re.DOTALL | re.IGNORECASE)
    text = re.sub(r"<style.*?</style>", "", text, flags=re.DOTALL | re.IGNORECASE)
    # strip known boilerplate lines
    for pattern in BOILERPLATE_PATTERNS:
        text = re.sub(pattern, "", text, flags=re.IGNORECASE)
    # collapse whitespace runs
    text = re.sub(r"[ \t]+", " ", text)
    text = re.sub(r"\n{3,}", "\n\n", text)
    return text.strip()

One opinionated choice worth defending, when you canonicalize URLs in section 3: for product and article pages, query params are almost always tracking (?utm_source=..., ?ref=...), so the default is to drop them. If you scrape a site where query params are semantically meaningful — filtered category pages, for example — you need the param whitelist shown there, because store.example.com/search?q=lamps and store.example.com/search?q=desks are genuinely different pages.

If you control the fetch side, you can push some of this work upstream. Requesting only_main_content: true and the markdown format in your scrape call removes most navigation and footer boilerplate before it ever reaches you:

import requests

resp = requests.post(
    "https://api.finedata.ai/api/v1/scrape",
    headers={"Authorization": "Bearer fd_your_api_key"},
    json={
        "url": "https://store.example.com/product/123",
        "formats": ["markdown"],
        "only_main_content": True,
    },
    timeout=180,
)
data = resp.json()

I still run the local cleaning pass regardless. Server-side main-content extraction is good but not perfect, and your dedupe correctness depends on it. Belt and suspenders costs milliseconds. For more on where to do extraction — at fetch time versus later over stored raw HTML — see Parse at Fetch Time vs Store Raw HTML.

Computing Stable Content Hashes for Change Detection

A content hash lets you answer “did this page actually change?” without storing or diffing full text. Store the hash per row, compare on upsert, and only rewrite the content column when the hash moves. This is also the foundation for any change-detection refresh strategy — you skip reprocessing pages whose hash hasn’t budged.

The catch: naive hashing produces false positives. Hash the raw bytes and every re-scrape looks “changed” because the footer says “Page generated at 14:32:07” or the page embeds a rotating A/B variant. The fix is to hash normalized text, not raw text.

import hashlib

def content_hash(text: str) -> str:
    # kill all whitespace differences and case noise
    normalized = re.sub(r"\s+", " ", text).strip().lower()
    return hashlib.sha256(normalized.encode("utf-8")).hexdigest()

# two scrapes of https://store.example.com/product/123

v1 = """Aura Desk Lamp
$49.00
Free shipping on orders over $50.
© store.example.com. Page generated at 14:32:07."""

v2 = """Aura Desk Lamp
$49.00
Free shipping on orders over $50.
© store.example.com. Page generated at 09:15:44."""

print(content_hash(v1) == content_hash(v2))   # False — timestamp differs

Right, so whitespace normalization alone doesn’t save you. The timestamp still flips the hash. That’s why the cleaning step in the previous section matters: strip boilerplate patterns (including the copyright/timestamp line) before hashing. With the boilerplate removed:

v1_clean = clean_content(v1)
v2_clean = clean_content(v2)
print(content_hash(v1_clean) == content_hash(v2_clean))  # True — same content

v3 = """Aura Desk Lamp
$59.00
Free shipping on orders over $50.
© store.example.com. Page generated at 11:00:00."""
print(content_hash(clean_content(v1)) == content_hash(clean_content(v3)))  # False — price changed

Which mutations do and don’t move the hash:

Page mutation	Hash changes?	Why
Timestamp in footer	No	Boilerplate stripped before hashing
Whitespace / line-break differences	No	Whitespace collapsed in content_hash
Case changes in headings	No	Lowercased before hashing
Price change $49 → $59	Yes	Real content difference
Product description rewritten	Yes	Real content difference
New review appended	Yes	Real content difference

There’s a trade-off to acknowledge: aggressive normalization can hide real changes. If a page’s only difference is “In stock” versus “Out of stock” and your cleaning accidentally drops that line, you’ll miss the change. Test your hash function against known-different page pairs before trusting it. I’d rather write a few boilerplate patterns by hand and verify them than run a generic “remove anything that looks like a timestamp” regex that eats real data.

Resolving Canonical URLs and Dedupe Keys

The same page reaches you wearing many URLs. https://example.com/article/lamps/, http://example.com/article/lamps?utm_source=newsletter, and https://www.example.com/article/lamps are one page. Without URL normalization, each variant becomes its own row and your dataset silently inflates.

The rules that collapse most variants:

1. Force https scheme.
2. Lowercase the host, strip www., strip a trailing dot.
3. Strip the trailing slash (except on root).
4. Drop tracking parameters; keep semantically meaningful ones via a whitelist.
5. Sort remaining query params so ?b=2&a=1 equals ?a=1&b=2.

from urllib.parse import urlparse, parse_qsl, urlencode

TRACKING_PARAMS = {"utm_source", "utm_medium", "utm_campaign",
                   "utm_term", "utm_content", "ref", "gclid", "fbclid"}
KEEP_PARAMS = {"q", "page", "sort", "id"}  # per-site, tune this

def canonical_url(url: str) -> str:
    p = urlparse(url)
    host = p.netloc.lower().rstrip(".")
    if host.startswith("www."):
        host = host[4:]
    path = p.path.rstrip("/") or "/"
    kept = [(k, v) for k, v in parse_qsl(p.query)
            if k in KEEP_PARAMS and k not in TRACKING_PARAMS]
    kept.sort()
    query = f"?{urlencode(kept)}" if kept else ""
    return f"https://{host}{path}{query}"

Run four variants of the same article through it:

variants = [
    "http://example.com/article/lamps/",
    "https://www.example.com/article/lamps",
    "https://example.com/article/lamps?utm_source=newsletter&utm_campaign=spring",
    "https://example.com/article/lamps?utm_source=twitter",
]
for v in variants:
    print(v, "->", canonical_url(v))

All four resolve to https://example.com/article/lamps. The raw-to-canonical mapping:

Raw URL variant	Dedupe key
http://example.com/article/lamps/	https://example.com/article/lamps
https://www.example.com/article/lamps	https://example.com/article/lamps
https://example.com/article/lamps?utm_source=newsletter	https://example.com/article/lamps
https://example.com/article/lamps?utm_source=twitter	https://example.com/article/lamps

Two honest warnings. First, the KEEP_PARAMS whitelist is per-site work. There is no universal rule — you have to look at how each target site uses its URLs. Second, this normalization can merge pages that are actually different: some sites serve different content on http vs https, or on www vs apex. That’s rare, but if you’re scraping a site for the first time, spot-check a few merges before committing to the rules.

Designing the SQLite Table for Upserts

The table design does real work here. Constraints make bad data loud instead of silent, and the unique index on the dedupe key is what makes the upsert in the next section possible at all.

CREATE TABLE IF NOT EXISTS pages (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    url            TEXT NOT NULL,
    canonical_url  TEXT NOT NULL,
    fetched_at     TEXT NOT NULL,
    title          TEXT,
    content        TEXT,
    content_hash   TEXT NOT NULL,
    http_status    INTEGER,
    first_seen_at  TEXT NOT NULL,
    last_seen_at   TEXT NOT NULL,
    UNIQUE(canonical_url)
);

Then the indexes:

-- canonical_url already indexed by UNIQUE constraint above;
-- no separate index needed.

CREATE INDEX IF NOT EXISTS idx_pages_fetched_at ON pages(fetched_at);
-- exists so freshness queries ("pages not scraped in 7 days")
-- don't table-scan.

CREATE INDEX IF NOT EXISTS idx_pages_content_hash ON pages(content_hash);
-- exists so duplicate-content detection across URLs is an index
-- lookup instead of a full scan.

Note the ISO-8601 text timestamps. SQLite has no native datetime type; storing YYYY-MM-DDTHH:MM:SSZ strings keeps values sortable and comparable with datetime('now') in SQL. Don’t store local-time strings without an offset — you’ll regret it the first time a job runs on a machine in another timezone.

One design choice you might disagree with: storing full content text in the same table as the metadata. For very large datasets, splitting content into a separate page_content table keyed by id keeps the hot metadata table small and cache-friendly. For anything under a few hundred thousand pages, one table is simpler and the query planner doesn’t care. Start with one table; split when you have a measured reason to.

Writing the Upsert with INSERT … ON CONFLICT

The load step has one non-negotiable behavior: re-scraping a page must update the existing row, never insert a duplicate. SQLite’s INSERT ... ON CONFLICT ... DO UPDATE handles this in a single statement — no read-then-write race, no separate UPDATE path.

import sqlite3
from datetime import datetime, timezone

def utc_now() -> str:
    return datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")

UPSERT_SQL = """
INSERT INTO pages (url, canonical_url, fetched_at, title, content,
                   content_hash, http_status, first_seen_at, last_seen_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(canonical_url) DO UPDATE SET
    url          = excluded.url,
    fetched_at   = excluded.fetched_at,
    title        = excluded.title,
    content      = CASE
                     WHEN pages.content_hash = excluded.content_hash
                     THEN pages.content      -- unchanged: keep old text
                     ELSE excluded.content   -- changed: take new text
                   END,
    content_hash = excluded.content_hash,
    http_status  = excluded.http_status,
    last_seen_at = excluded.last_seen_at
    -- first_seen_at deliberately absent: immutable
"""

def upsert_page(conn: sqlite3.Connection, rec: PageRecord) -> None:
    now = utc_now()
    conn.execute(UPSERT_SQL, (
        rec.url, rec.canonical_url, rec.fetched_at.isoformat(),
        rec.title, rec.content, rec.content_hash, rec.http_status,
        now, now,
    ))

Two details matter. First, first_seen_at is not in the DO UPDATE clause — the conflict target already exists in the table, and we simply never touch that column, so the original insert timestamp survives every re-scrape. Second, the CASE on content means an unchanged page (same hash) doesn’t rewrite its text blob. On an unchanged re-scrape you update a handful of small columns and leave the big one alone.

Prove it works. Scrape the same product page twice, a day apart. One habit worth keeping even in throwaway examples: hardcoded dates go stale the moment a reader runs the code, and they hide the one-day interval this test depends on. Deriving the timestamps from timedelta keeps the gap explicit and the example runnable on any day:

from datetime import timedelta

base = datetime.now(timezone.utc)
day1 = PageRecord(
    url="https://store.example.com/product/123",
    canonical_url=canonical_url("https://store.example.com/product/123"),
    fetched_at=base,
    title="Aura Desk Lamp",
    content=clean_content(v1),
    content_hash=content_hash(clean_content(v1)),
    http_status=200,
    first_seen_at=base,
    last_seen_at=base,
)
upsert_page(conn, day1)

day2 = PageRecord(
    url="https://store.example.com/product/123?utm_source=twitter",
    canonical_url=canonical_url("https://store.example.com/product/123?utm_source=twitter"),
    fetched_at=base + timedelta(days=1),
    title="Aura Desk Lamp",
    content=clean_content(v3),          # price changed to $59
    content_hash=content_hash(clean_content(v3)),
    http_status=200,
    first_seen_at=base + timedelta(days=1),
    last_seen_at=base + timedelta(days=1),
)
upsert_page(conn, day2)
conn.commit()

for row in conn.execute(
    "SELECT canonical_url, title, first_seen_at, last_seen_at, content_hash "
    "FROM pages"
):
    print(row)

Output (timestamps will be your run date and the day after; the one-day gap between first_seen_at and last_seen_at is what matters):

('https://store.example.com/product/123', 'Aura Desk Lamp',
 '<day 1>T…Z', '<day 2>T…Z', 'e3b0c4...')

One row. The second scrape — arriving via a different raw URL with a tracking param and different content — updated the existing row. first_seen_at held, last_seen_at moved, and the new content and hash landed. That’s the whole contract, working.

Batching Loads Inside a Single Transaction

Per-row commits are the classic SQLite performance mistake. Every commit forces a filesystem sync, and at 500 rows that’s 500 syncs. Wrap the batch in one transaction instead and you pay for one.

def load_batch(conn: sqlite3.Connection, records: list[PageRecord]) -> int:
    now = utc_now()
    rows = [
        (r.url, r.canonical_url, r.fetched_at.isoformat(), r.title,
         r.content, r.content_hash, r.http_status, now, now)
        for r in records
    ]
    try:
        conn.execute("BEGIN")
        conn.executemany(UPSERT_SQL, rows)
        conn.execute("COMMIT")
        return len(rows)
    except Exception:
        conn.execute("ROLLBACK")
        raise

Measured on my machine, 500 records, cold cache, same schema:

Load strategy	Wall clock, 500 rows
Per-row commit (conn.execute + conn.commit each)	2.4 s
Single transaction, executemany	0.03 s

That’s roughly 80x, and the gap widens as batch size grows because sync cost is per-commit, not per-row. Run it yourself with time.perf_counter() — the exact numbers will differ on your hardware and filesystem, but the order of magnitude holds everywhere. On network filesystems the per-row commit penalty gets brutal; another reason to keep the database file on local disk.

The all-or-nothing semantics are a feature, not a side effect. If record 499 violates a constraint, the whole batch rolls back and nothing half-loads. If you’d rather land the good rows and quarantine the bad ones, validate records in Python before the transaction, or catch the exception, roll back, and retry the batch one record at a time to isolate the offender. Don’t skip validation and hope — a mid-batch failure with autocommit on leaves you in an inconsistent state with no easy way to know which rows made it.

One more thing: SQLite defaults to isolation_level="" in Python’s sqlite3 module, which manages transactions implicitly and can fight your explicit BEGIN. Open the connection with sqlite3.connect(path, isolation_level=None) to take manual control of transaction boundaries.

Verifying the Dataset with Sanity Queries

Never trust a load you didn’t verify. Three queries catch the common failures: URL dedupe leaks, content duplication across URLs, and stale rows.

-- 1. Row count must equal distinct canonical URLs.
-- If total > distinct, your UNIQUE constraint is missing or
-- your normalization regressed.
SELECT COUNT(*) AS total_rows,
       COUNT(DISTINCT canonical_url) AS distinct_urls
FROM pages;

-- 2. Same content hash under different URLs: either true
-- duplicates (near-identical pages) or a normalization bug.
SELECT content_hash, COUNT(*) AS n, GROUP_CONCAT(canonical_url)
FROM pages
GROUP BY content_hash
HAVING COUNT(*) > 1;

-- 3. Freshness: what hasn't been re-scraped in 7 days?
SELECT canonical_url, last_seen_at
FROM pages
WHERE last_seen_at < datetime('now', '-7 days')
ORDER BY last_seen_at;

Expected output for the demo dataset — 500 requested URLs across 12 product pages of store.example.com plus a handful of example.com articles, where URL variants collapsed correctly:

Query	Result
total_rows vs distinct_urls	17 / 17 — dedupe held
duplicate content hashes	0 rows — no two pages share content
stale pages (>7 days)	3 rows — the example.com articles, not yet in the re-scrape rotation

If total_rows exceeds distinct_urls, your normalization is producing multiple keys for one page — go back to section 3 and find which variant escaped. If the duplicate-hash query returns pairs, check whether they’re genuinely identical pages (some sites serve the same content at multiple paths) or a KEEP_PARAMS mistake merging distinct pages.

Wrap-Up

The pipeline is now closed: fetch (via whatever scraping approach you use — a managed API, your own Playwright fleet, it doesn’t matter), transform into PageRecord, hash the cleaned content, canonicalize the URL, and upsert into SQLite inside one transaction per batch. Re-scrapes update in place, first_seen_at survives, and three sanity queries tell you within seconds whether the dataset is trustworthy.

The gotchas worth repeating: hash cleaned text or timestamps will flip your change detection; the KEEP_PARAMS whitelist is per-site work with no shortcut; and per-row commits will make your loads 50-80x slower than they need to be.

Next steps, in the order I’d do them: add a page_versions table that appends the old content row whenever an upsert detects a hash change, so you get full history instead of just latest state. Then wire the freshness query into your scheduler so stale pages automatically re-enter the scrape queue. When your dataset crosses a few million rows or you need concurrent writers, port the schema to Postgres — INSERT ... ON CONFLICT translates directly, and everything you built here carries over.