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.
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_url | title | price | scraped_at |
|---|---|---|---|
| store.example.com/products/steel-water-bottle | Steel Water Bottle 1L | 19.99 | Mon 06:00 |
You scrape it again on Wednesday and your pipeline runs the usual upsert:
| page_url | title | price | scraped_at |
|---|---|---|---|
| store.example.com/products/steel-water-bottle | Steel Water Bottle 1L | 24.99 | Wed 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 run | Last-write-wins table | SCD2 history table |
|---|---|---|
| Mon 06:00, price 19.99 | row updated to 19.99 | version 1: 19.99, valid_from = Mon 06:00, is_current = true |
| Tue 06:00, price 21.49 | row 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.99 | row 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:
| version | title | price | valid_from | valid_to | is_current |
|---|---|---|---|---|---|
| 1 | Pricing — Basic plan $9 | 9.00 | Mar 3, 06:00 | Mar 4, 06:00 | false |
| 2 | Pricing — Basic plan $12 | 12.00 | Mar 4, 06:00 | Mar 6, 06:00 | false |
| 3 | Pricing — Basic plan $12 | 12.00 | Mar 6, 06:00 | NULL | true |
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:
| Scrape | title | price | availability | hash (truncated) | Verdict |
|---|---|---|---|---|---|
| Mon | Steel Water Bottle 1L | 19.99 | in stock | a3f9…c21e | new version |
| Tue | Steel Water Bottle 1L | 19.99 | in stock | a3f9…c21e | no-op, skip |
| Wed | Steel Water Bottle 1L | 24.99 | in stock | 7b12…f08d | new 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:
| title | price | availability | valid_from | valid_to |
|---|---|---|---|---|
| Steel Water Bottle 1L | 19.99 | in stock | Mar 3, 06:00 | Mar 4, 06:00 |
| Steel Water Bottle 1L | 21.49 | in stock | Mar 4, 06:00 | Mar 6, 06:00 |
| Steel Water Bottle 1L | 24.99 | in stock | Mar 6, 06:00 | NULL |
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:
| Event | Time | Effect on history |
|---|---|---|
| Scrape ran, price 19.99 | Mon 06:00 | version opens: valid_from = Mon 06:00 |
| No run | Tue 06:00 | nothing written |
| No run | Wed 06:00 | nothing written |
| Scrape ran, price 24.99 | Thu 06:00 | Monday’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.
Related Articles
Build 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.
TutorialRe-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.
TutorialYour Data Sits in an Iframe: Fix Extraction Across Frames
Your selector matches nothing, yet the data is visible in the browser. Here's why iframes break extraction and how to fetch frame content reliably.