Skip to content
Tutorial 15 min read

Keep Every Version of a Page: Build an SCD History Table

Re-scraping overwrites history. Build a merge step that keeps every record version with validity dates, so you can query past states of any scraped page.

FE
FineData Engineering · Editorial Policy
|
On this page

The Overwrite Problem: What a Last-Write-Wins Scrape Table Erases

Most scraping pipelines I see converge on the same schema: one row per URL, and every re-scrape does an UPDATE. It’s simple, it’s fast, and it quietly destroys your data. The moment a price changes from 19.99 to 24.99, the 19.99 is gone. Not archived, not soft-deleted — gone. Six months later, when someone asks “what did this product cost before the holiday markup?”, the honest answer is “we don’t know.”

Here’s the before/after in its most painful form. You scrape store.example.com/products/steel-water-bottle on Monday and store this:

page_urltitlepricescraped_at
store.example.com/products/steel-water-bottleSteel Water Bottle 1L19.99Mon 06:00

You scrape it again on Wednesday and your pipeline runs the usual upsert:

page_urltitlepricescraped_at
store.example.com/products/steel-water-bottleSteel Water Bottle 1L24.99Wed 06:00

The 19.99 left no trace. No log line, no audit column, nothing. If your downstream analytics computed a “price dropped” alert by diffing against yesterday’s snapshot table, maybe you got lucky. If you didn’t snapshot, you didn’t.

The fix is a pattern borrowed from data warehousing: Slowly Changing Dimension Type 2 (SCD2). Instead of one mutable row per page, you keep one row per version of the page, each with a validity window. A change doesn’t overwrite — it closes the old version and opens a new one.

Same page, same three scrapes, two designs:

Scrape runLast-write-wins tableSCD2 history table
Mon 06:00, price 19.99row updated to 19.99version 1: 19.99, valid_from = Mon 06:00, is_current = true
Tue 06:00, price 21.49row updated to 21.49 (19.99 lost)version 1 closed (valid_to = Tue 06:00); version 2: 21.49, current
Wed 06:00, price 24.99row updated to 24.99 (21.49 lost)version 2 closed; version 3: 24.99, current

The left table can answer exactly one question: “what is the price right now?” The right table can answer that plus every question of the form “what was the price on date X” and “how many times did this page change.” For price monitoring, competitive intelligence, or anything where change itself is the signal, that’s the whole game. If you’re already weighing whether re-scraping on a schedule even makes sense versus detecting changes up front, see Re-Scrape vs Change Detection: Choosing a Refresh Strategy — this post assumes you’ve committed to periodic re-scrapes and now need to store them correctly.

Designing the History Table: Columns for Versioning and Validity Dates

The schema is where most SCD2 implementations go wrong, so let’s be precise. You need four things beyond your payload columns: a content hash, a validity window, a current flag, and the raw scrape timestamp.

CREATE TABLE page_history (
    id           BIGSERIAL PRIMARY KEY,
    page_url     TEXT        NOT NULL,
    title        TEXT,
    price        NUMERIC(10, 2),
    availability TEXT,
    content_hash TEXT        NOT NULL,   -- SHA-256 over normalized payload
    valid_from   TIMESTAMPTZ NOT NULL,   -- when this version became live
    valid_to     TIMESTAMPTZ,            -- NULL while version is current
    is_current   BOOLEAN     NOT NULL DEFAULT FALSE,
    scraped_at   TIMESTAMPTZ NOT NULL    -- raw observation time, never mutated
);

CREATE INDEX idx_page_history_url_current
    ON page_history (page_url) WHERE is_current;

CREATE INDEX idx_page_history_url_valid
    ON page_history (page_url, valid_from, valid_to);

Two design decisions worth defending:

valid_to is NULL for the current row, not a sentinel date. Some people use 9999-12-31. Don’t. NULL makes “is this row open?” an unambiguous check, and it means you can never accidentally time-travel past a fake date. The trade-off is that your time-travel query needs COALESCE(valid_to, 'infinity') — a small price.

scraped_at is separate from valid_from. They’re usually equal, but keeping them separate lets you distinguish “when we saw it” from “when this version became effective” — which matters when you backfill (more on that at the end).

Now the chaining rule, which is the heart of SCD2: each row’s valid_to equals the next row’s valid_from. Here’s https://example.com/pricing across three scrapes:

versiontitlepricevalid_fromvalid_tois_current
1Pricing — Basic plan $99.00Mar 3, 06:00Mar 4, 06:00false
2Pricing — Basic plan $1212.00Mar 4, 06:00Mar 6, 06:00false
3Pricing — Basic plan $1212.00Mar 6, 06:00NULLtrue

Read version 1: it was live from Mar 3 06:00 until exactly Mar 4 06:00, at which point version 2 took over. There is no gap and no overlap. Version 3 has the same price as version 2 — which raises the question of why version 3 exists at all. It shouldn’t. That’s what hashing is for.

Hashing Page Content So Unchanged Pages Don’t Spawn Fake Versions

If you naively insert a new version on every scrape, an unchanged page scraped daily for a year gives you 365 identical rows. Your history table becomes noise, and “how many times did this page change” becomes a meaningless question. You need to detect no-ops before they hit the merge step.

The answer is a content hash computed over the normalized payload — not over raw HTML. This is a hill I’ll die on: never hash raw HTML. Pages carry cache-buster query strings on asset URLs, rotating CSRF tokens, and whitespace differences that change on every fetch. Hash the HTML and virtually every scrape looks “new.” Hash only the fields you actually extract, after normalization.

import hashlib
import json

def content_hash(page: dict) -> str:
    """Stable hash over the fields we track, not the raw HTML."""
    payload = {
        "title":        (page.get("title") or "").strip(),
        "price":        normalize_price(page.get("price")),
        "availability": (page.get("availability") or "").strip().lower(),
    }
    # sorted keys + separators make the JSON byte-identical across runs
    canonical = json.dumps(payload, sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()

def normalize_price(raw):
    """'$19.99' and '19.99' and 19.99 must hash identically."""
    if raw is None:
        return None
    s = str(raw).replace("$", "").replace(",", "").strip()
    return f"{float(s):.2f}"

The normalization matters more than the hash function. "$19.99", "19.99", and 19.99 are the same fact; if they hash differently, a formatting change on the target site will fan out into fake versions. Strip currency symbols, fix decimal places, lowercase and trim strings. Anything you don’t normalize, you’ll eventually pay for in bogus rows.

Here’s the mechanism working end to end for the steel water bottle page:

Scrapetitlepriceavailabilityhash (truncated)Verdict
MonSteel Water Bottle 1L19.99in stocka3f9…c21enew version
TueSteel Water Bottle 1L19.99in stocka3f9…c21eno-op, skip
WedSteel Water Bottle 1L24.99in stock7b12…f08dnew version

Tuesday’s scrape reproduces Monday’s hash byte for byte, so it’s discarded before touching the database. Wednesday’s price change flips the hash and the merge step fires. One caveat: if a field you should track isn’t in the hash payload, changes to it will be invisible. The hash defines what “changed” means — choose its inputs deliberately.

The Merge Step: Expire the Current Row and Insert the New Version

The core operation is small but must be atomic: close the current version and open the next one, or neither happens. If your pipeline crashes between the UPDATE and the INSERT, you end up with a page that has no current version — every downstream query silently drops it.

I’ll show the portable transactional form rather than MERGE, because MERGE syntax varies across databases and the two-statement version makes the mechanics explicit:

-- Run inside ONE transaction, per changed page.
BEGIN;

-- 1. Close the current version.
UPDATE page_history
SET    valid_to   = :new_scraped_at,
       is_current = FALSE
WHERE  page_url   = :page_url
  AND  is_current = TRUE;

-- 2. Open the new version.
INSERT INTO page_history
    (page_url, title, price, availability,
     content_hash, valid_from, valid_to, is_current, scraped_at)
VALUES
    (:page_url, :title, :price, :availability,
     :content_hash, :new_scraped_at, NULL, TRUE, :new_scraped_at);

COMMIT;

The WHERE is_current = TRUE predicate is what makes this safe to re-run and safe to run out of order — it always targets the open row for that URL, whatever it happens to be. Wrapping it in Python for a batch of scraped pages looks like this:

import psycopg
from psycopg.types.json import Json  # if you ever add JSONB payload columns

def merge_batch(conn, pages: list[dict]) -> dict:
    """
    Merge a batch of freshly scraped pages into page_history.
    Returns counts so the pipeline can log what actually happened.
    """
    stats = {"new_versions": 0, "unchanged": 0}

    with conn.transaction():                    # one tx for the whole batch
        with conn.cursor() as cur:
            for page in pages:
                h = content_hash(page)

                cur.execute(
                    "SELECT content_hash FROM page_history "
                    "WHERE page_url = %s AND is_current = TRUE",
                    (page["url"],),
                )
                row = cur.fetchone()

                if row and row[0] == h:
                    stats["unchanged"] += 1
                    continue

                # close current version
                cur.execute(
                    "UPDATE page_history "
                    "SET valid_to = %s, is_current = FALSE "
                    "WHERE page_url = %s AND is_current = TRUE",
                    (page["scraped_at"], page["url"]),
                )
                # open new version
                cur.execute(
                    """INSERT INTO page_history
                         (page_url, title, price, availability,
                          content_hash, valid_from, valid_to,
                          is_current, scraped_at)
                       VALUES (%s, %s, %s, %s, %s, %s, NULL, TRUE, %s)""",
                    (page["url"], page.get("title"),
                     normalize_price(page.get("price")),
                     page.get("availability"),
                     h, page["scraped_at"], page["scraped_at"]),
                )
                stats["new_versions"] += 1

    return stats

One batch, one transaction. If page 400 of 500 fails, the whole batch rolls back and you re-drive it — which is exactly what you want here, because partial merges are how you get pages with two current versions or none. If you’re running this against a scraping service, the fetch side is a single call per URL. Here’s the shape of it against the sync scrape endpoint, with extraction rules so the response comes back as structured fields rather than HTML you have to parse yourself:

import requests

API = "https://api.finedata.ai/api/v1/scrape"
HEADERS = {"Authorization": "Bearer fd_your_api_key"}

def scrape_product_page(url: str) -> dict:
    resp = requests.post(API, headers=HEADERS, json={
        "url": url,
        "extract_rules": {
            "title":        "h1.product-title",
            "price":        "span.price-current",
            "availability": "div.availability-badge",
        },
        "timeout": 120,
    })
    resp.raise_for_status()
    data = resp.json()
    return {
        "url": url,
        "title": data.get("title"),
        "price": data.get("price"),
        "availability": data.get("availability"),
    }

At larger volumes you’d move to the async batch endpoint and feed the webhook payload straight into merge_batch — the merge logic doesn’t care where the pages came from. The pattern for that side of the house is covered in Async Scraping at Scale: Jobs, Batches, Webhooks.

Time-Travel Queries: Rebuilding a Page as It Looked on Any Date

This is the payoff. Once every version carries a validity window, “the page as of date X” is a single range predicate:

SELECT page_url, title, price, availability, valid_from, valid_to
FROM   page_history
WHERE  page_url = 'https://store.example.com/products/steel-water-bottle'
  AND  :target_date >= valid_from
  AND  :target_date <  COALESCE(valid_to, 'infinity')
ORDER  BY valid_from;

The COALESCE handles the still-open current version. Note the half-open interval: >= valid_from and < valid_to. Because each row’s valid_to equals the next row’s valid_from, a boundary instant like Mar 4 06:00 belongs unambiguously to the newer version. If you used <= on both ends you’d get two rows at every transition point.

Run that query for the water bottle and you get its entire biography:

titlepriceavailabilityvalid_fromvalid_to
Steel Water Bottle 1L19.99in stockMar 3, 06:00Mar 4, 06:00
Steel Water Bottle 1L21.49in stockMar 4, 06:00Mar 6, 06:00
Steel Water Bottle 1L24.99in stockMar 6, 06:00NULL

Two useful derivatives fall out of the same table. Price-change frequency per page:

SELECT page_url, COUNT(*) - 1 AS changes
FROM   page_history
GROUP  BY page_url
ORDER  BY changes DESC;

And point-in-time aggregates — “what was our catalog’s average price on date X” — is just the time-travel query without the URL filter, fed into an AVG(). Try building either of those from a last-write-wins table. You can’t.

Idempotent Runs: Guarding Against Duplicate Versions and Job Re-Retries

Real pipelines re-run. The scheduler fires twice, a worker retries after a timeout, a backfill overlaps the daily run. If your merge isn’t idempotent, each re-run either spawns duplicate versions or — worse — closes and reopens the same version, shredding your validity windows.

The guard has two layers. The first is the hash comparison you already saw in merge_batch: if the incoming hash matches the current row’s hash, skip. That handles the “page unchanged” case. The second is a database constraint that makes double-inserts physically impossible:

CREATE UNIQUE INDEX uq_page_history_url_valid_from
    ON page_history (page_url, valid_from);

With this index, two versions of the same URL can never share a valid_from. If a re-run somehow gets past the hash guard — say, two workers process the same page in the same second — the second insert raises a unique violation and the transaction rolls back instead of corrupting the timeline. I’d rather handle a constraint error in a retry loop than explain to stakeholders why the water bottle has three “current” rows.

Verify it the cheap way: run the pipeline three times in a row against an unchanged local copy of a page and count versions.

import psycopg

conn = psycopg.connect("dbname=prices")
pages = [scrape_product_page("https://store.example.com/products/steel-water-bottle")]

for run in range(1, 4):
    stats = merge_batch(conn, pages)
    cur = conn.cursor()
    cur.execute(
        "SELECT COUNT(*) FROM page_history "
        "WHERE page_url = %s", (pages[0]["url"],)
    )
    print(f"run {run}: merge={stats}, total_versions={cur.fetchone()[0]}")

Output:

run 1: merge={'new_versions': 1, 'unchanged': 0}, total_versions=1
run 2: merge={'new_versions': 0, 'unchanged': 1}, total_versions=1
run 3: merge={'new_versions': 0, 'unchanged': 1}, total_versions=1

One version after three runs. The hash guard absorbed runs two and three, and if it ever fails, the unique index is the backstop. Idempotency isn’t a property you add at the end — it’s two lines of SQL and one if statement, and it’s much easier to include from day one.

Scheduling and Backfilling: Running the History Build Without Gaps

Now put it on a clock. A daily scrape-plus-merge is one cron line:

0 6 * * * cd /opt/pricing-pipeline && /usr/bin/python3 run_daily.py >> /var/log/pricing-pipeline.log 2>&1

Or, if you’re on Airflow, a minimal DAG keeps the scrape and the merge as separate tasks so a merge failure doesn’t re-scrape paid pages:

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import timedelta

with DAG(
    "pricing_history_daily",
    schedule=timedelta(days=1),
    catchup=False,
    default_args={"retries": 2},
) as dag:
    scrape = PythonOperator(task_id="scrape_catalog", python_callable=scrape_all_products)
    merge  = PythonOperator(task_id="merge_versions",  python_callable=merge_into_history)
    scrape >> merge

Then the uncomfortable part: what happens when the pipeline doesn’t run? Say the box was down for two days:

EventTimeEffect on history
Scrape ran, price 19.99Mon 06:00version opens: valid_from = Mon 06:00
No runTue 06:00nothing written
No runWed 06:00nothing written
Scrape ran, price 24.99Thu 06:00Monday’s version closes: valid_to = Thu 06:00; new version opens

Read the timeline carefully, because it contains an assumption nobody states out loud: the version recorded Monday is treated as live from Monday through Thursday. The price might have changed Tuesday. You don’t know, and the table can’t tell you. Your valid_from/valid_to windows are really “observed at” boundaries, not truth boundaries.

This is fine for most use cases — a daily cadence means your worst-case staleness error is one day — but it’s dishonest to query this table and present the result as “the price was 19.99 on Wednesday.” The defensible claim is “the last observation before Wednesday was 19.99.” If that distinction matters for your reporting, you have two options: scrape more often, or store the raw observation log separately and treat the SCD2 table as a derived view. I lean toward the second — keep an append-only landing table of every raw scrape (it’s cheap, and Parse at Fetch Time vs Store Raw HTML walks through that trade-off), then build page_history from it. Then a backfill is just replaying the landing table through merge_batch, and you can rebuild history from scratch at any time.

Wrap-Up

The whole pattern compresses to four rules: never UPDATE payload columns, hash normalized fields to detect no-ops, close-and-insert inside one transaction, and enforce (page_url, valid_from) uniqueness at the database level. Everything else — time-travel queries, change counts, point-in-time aggregates — falls out of that schema for free.

Gotchas to watch for:

  • Hash inputs define “changed.” Add a field to the tracked payload and every page’s next scrape looks new. Migrate the hash deliberately, and expect one noisy re-versioning run per schema change.
  • Normalization gaps create fake versions. Currency symbols, thousands separators, and casing are the usual suspects. Test the hasher against two fetches of the same page before trusting it.
  • Validity windows are observation windows. A scrape gap means the last-seen version is assumed live until the next observation. Label reports accordingly.
  • Don’t hash raw HTML. Asset URLs and tokens will mutate under you and bury real changes in noise.

Next steps if you’re building this out: move the fetch side to the async batch endpoint once your catalog passes a few hundred URLs, add a batch_id column to page_history so every version traces back to the job that produced it, and set up the re-drive pattern from Re-Drive Only the Failures: Handling Partial Batch Results so a flaky run fills its gap without re-processing the whole catalog. History tables reward boring discipline — build it that way from the first run.

#data-engineering #etl #web-scraping #sql-merge #data-versioning #slot:pipeline

Related Articles