How to Store Scraped Data: Files, DBs, and Pipelines

There is no single best store, only the right one for how the data gets used next. Ask three questions before you write a line of code.

Who reads it, and how? If a non-technical teammate opens it in Excel once a week, a CSV wins. If a dashboard queries it live, you want a database. If a data scientist runs pandas over ten million rows, Parquet beats both.

How often does it change? A one-off scrape of 500 pages is a file. A daily price monitor that updates the same products forever needs upserts and an index, which means a database.

How big does it get? CSV and JSON stay fine into the low hundreds of thousands of rows. Past a few million, or once you need concurrent writers, a real database or a columnar format earns its keep.

Most teams overthink this and reach for Postgres on day one, or underthink it and are still appending to a 4 GB CSV six months later. The honest answer for a lot of projects is a two-layer split: keep an immutable raw layer as newline-delimited JSON, and load a typed, deduplicated copy into SQLite or Postgres for querying. You get replayability and clean reads without committing to heavy infrastructure early. If you are new to the moving parts here, the web scraping overview covers how the extraction step feeds the storage step.

Here is the trade-off across the five stores you will actually choose between for web scraping data storage.

Two rules of thumb come out of this table. SQLite is the correct default for anything running on one machine, and people skip it far too often. Parquet is not a database, it is an export target, so treat it as the place data goes to be analyzed, not the place it lives while it changes.

Storage is easy when the input is already structured. Instead of saving raw HTML and parsing later, pull fields at the source with the SparkProxy Scraping API extract_rules parameter, which returns a JSON object of exactly the fields you name.

import requests

API = "https://scrape.sparkproxy.io/api/v1"
API_KEY = "YOUR_API_KEY"

def fetch_product(url):
    rules = {
        "name":  "h1.product-title",
        "price": ".price .amount",
        "sku":   "[data-sku]",
        "stock": ".availability",
    }
    r = requests.post(
        API,
        headers={"X-API-Key": API_KEY, "Content-Type": "application/json"},
        json={"url": url, "render_js": True, "extract_rules": rules},
        timeout=90,
    )
    r.raise_for_status()
    return r.json()["extracted"]

raw = fetch_product("https://www.sparkproxy.io/demo-store/p/1024")
print(raw)
# {'name': 'Wireless Mouse', 'price': '$24.99', 'sku': 'WM-1024', 'stock': 'In stock'}

The API returns an extracted dict, so you go straight from request to a Python record with no BeautifulSoup in between. That is the shape everything below expects. If you are pulling thousands of pages, run the fetch step concurrently as described in the guide to async scraping with requests and aiohttp, then feed the results into the storage code here.

The most expensive mistake in web scraping data storage is not deciding your columns up front. You end up with a price column holding "$24.99", "24,99 EUR", and "Call for price" in the same field, and every downstream query has to clean it. Decide the target shape first, then make the scraper conform to it.

Four schema rules save the most pain:

• Store money as integer cents, never as a float or a string. 2499, not 24.99 and not "$24.99". Floats lose precision, strings lose sortability.
• Keep a natural key. A stable identifier per record, usually the product URL or a site SKU. This is what dedupe and upsert hinge on.
• Timestamp everything in UTC, ISO 8601. first_seen, last_seen, and scraped_at are worth their weight when you debug a bad run three weeks later.
• Separate the value from the observation. The current price lives in one row per product. Every price you ever saw lives in an append-only history table. More on that below.

Before the code, here is the map of what cleaning actually does to each field. Decide these transformations once and apply them on every insert.

A cleaning function is where the schema gets enforced. It takes the messy extracted dict and returns one flat, typed record.

import re
import datetime

def now_utc():
    return datetime.datetime.now(datetime.timezone.utc).isoformat()

def clean(raw, url):
    price_str = raw.get("price") or ""
    digits = re.sub(r"[^\d.]", "", price_str)
    cents = int(round(float(digits) * 100)) if re.search(r"\d", digits) else None
    return {
        "url":         url,
        "name":        (raw.get("name") or "").strip(),
        "sku":         (raw.get("sku") or "").strip() or None,
        "price_cents": cents,
        "currency":    "USD" if "$" in price_str else None,
        "in_stock":    "in stock" in (raw.get("stock") or "").lower(),
        "scraped_at":  now_utc(),
    }

"$24.99" becomes 2499. "In stock" becomes True. An empty parse becomes None instead of an empty string, so you can filter it out before it pollutes the table. Validate here too: if name is empty or price_cents is absurd, drop or flag the row rather than storing garbage.

For a bounded scrape you hand to someone else, CSV is genuinely the right call. The one detail people get wrong is the line ending, which produces blank rows on Windows. Always open with newline="".

import csv
from pathlib import Path

FIELDS = ["scraped_at", "url", "sku", "name", "price_cents", "currency", "in_stock"]

def append_csv(rows, path="products.csv"):
    path = Path(path)
    write_header = not path.exists()
    with path.open("a", newline="", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=FIELDS, extrasaction="ignore")
        if write_header:
            writer.writeheader()
        writer.writerows(rows)

For anything with nested structure, or when you want an immutable log you can re-parse for free, use newline-delimited JSON (JSONL). One JSON object per line means you can append safely and stream-read huge files without loading them into memory.

import json
import gzip

def save_raw(record, path="raw/products.jsonl.gz"):
    Path(path).parent.mkdir(parents=True, exist_ok=True)
    stamped = {**record, "_scraped_at": now_utc()}
    with gzip.open(path, "at", encoding="utf-8") as f:
        f.write(json.dumps(stamped, ensure_ascii=False) + "\n")

Here is the insight that saves the most money on a metered scraper: write the raw response to JSONL before you clean it. Re-scraping costs credits. Re-parsing a JSONL file costs nothing. When you discover a bug in your clean() function next month, you replay the raw file instead of paying to fetch every page again. Treat the raw layer as the source of truth and the database as a derived, rebuildable view.

Once the data changes over time, files stop being enough and you want a database. SQLite is built into Python and handles single-machine pipelines up to tens of gigabytes without a server. Start with two tables: a current-state table keyed on the natural key, and an append-only price history table.

import sqlite3

def get_db(path="scrape.db"):
    db = sqlite3.connect(path)
    db.execute("""
        CREATE TABLE IF NOT EXISTS products (
            url          TEXT PRIMARY KEY,
            sku          TEXT,
            name         TEXT,
            price_cents  INTEGER,
            currency     TEXT,
            in_stock     INTEGER,
            content_hash TEXT NOT NULL,
            first_seen   TEXT NOT NULL,
            last_seen    TEXT NOT NULL
        )
    """)
    db.execute("""
        CREATE TABLE IF NOT EXISTS price_history (
            url         TEXT NOT NULL,
            price_cents INTEGER,
            in_stock    INTEGER,
            observed_at TEXT NOT NULL
        )
    """)
    db.execute("CREATE INDEX IF NOT EXISTS ix_hist_url ON price_history(url, observed_at)")
    db.commit()
    return db

Postgres uses the same schema with real types (BIGINT, BOOLEAN, TIMESTAMPTZ) and gives you concurrent writers, which SQLite does not. The rule is simple: one process writing means SQLite is plenty. Multiple workers writing at once, or a live dashboard reading while jobs write, means Postgres. The managed API versus self-run infrastructure comparison applies to the storage tier as much as the proxy tier: run SQLite until concurrency forces the upgrade, not before.

Duplicates come from two places: scraping the same URL twice, and storing an unchanged record as if it were new. A PRIMARY KEY on the natural key kills the first. Content hashing kills the second, and it does something more useful besides.

Compute a hash over only the business fields you care about, not the whole record. Exclude timestamps, because otherwise every scrape looks like a change.

import hashlib

BUSINESS_FIELDS = ("name", "sku", "price_cents", "currency", "in_stock")

def content_hash(row):
    payload = json.dumps(
        {k: row.get(k) for k in BUSINESS_FIELDS},
        sort_keys=True,
    )
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()

The hash is not just a dedupe token. It is a change-detection signal. If the natural key already exists and the hash matches, nothing changed, so you skip the write. If the key exists but the hash differs, something real changed and that is worth recording. Most tutorials dedupe on URL alone and then either overwrite blindly (losing the fact that a change happened) or skip (missing the update entirely). The hash lets you tell "seen before" apart from "changed since," which is exactly the distinction a price monitor lives or dies on.

An upsert inserts a new row or updates the existing one in a single statement, so a re-run is idempotent and never doubles your data. Below is the SQLite version that combines the natural key, the content hash, and the price history in one function.

def store(db, row):
    h   = content_hash(row)
    now = row["scraped_at"]
    cur = db.execute("SELECT content_hash FROM products WHERE url = ?", (row["url"],))
    existing = cur.fetchone()

    if existing is None:
        db.execute(
            "INSERT INTO products (url, sku, name, price_cents, currency, in_stock, "
            "content_hash, first_seen, last_seen) VALUES (?,?,?,?,?,?,?,?,?)",
            (row["url"], row["sku"], row["name"], row["price_cents"], row["currency"],
             int(row["in_stock"]), h, now, now),
        )
        _record_price(db, row, now)
    elif existing[0] != h:
        db.execute(
            "UPDATE products SET name=?, sku=?, price_cents=?, currency=?, in_stock=?, "
            "content_hash=?, last_seen=? WHERE url=?",
            (row["name"], row["sku"], row["price_cents"], row["currency"],
             int(row["in_stock"]), h, now, row["url"]),
        )
        _record_price(db, row, now)
    else:
        db.execute("UPDATE products SET last_seen=? WHERE url=?", (now, row["url"]))
    db.commit()

def _record_price(db, row, now):
    db.execute(
        "INSERT INTO price_history (url, price_cents, in_stock, observed_at) VALUES (?,?,?,?)",
        (row["url"], row["price_cents"], int(row["in_stock"]), now),
    )

Notice the three branches: insert when new, update plus a history row when the hash changed, and a cheap last_seen touch when nothing changed. That touch is how you detect delisted products later, since a row whose last_seen stops advancing has dropped off the site.

For Postgres, do the same thing in bulk with execute_values and native ON CONFLICT, which is far faster than row-by-row inserts on large batches.

import psycopg2
from psycopg2.extras import execute_values

UPSERT = """
INSERT INTO products
    (url, sku, name, price_cents, currency, in_stock, content_hash, first_seen, last_seen)
VALUES %s
ON CONFLICT (url) DO UPDATE SET
    name         = EXCLUDED.name,
    price_cents  = EXCLUDED.price_cents,
    currency     = EXCLUDED.currency,
    in_stock     = EXCLUDED.in_stock,
    content_hash = EXCLUDED.content_hash,
    last_seen    = EXCLUDED.last_seen
WHERE products.content_hash IS DISTINCT FROM EXCLUDED.content_hash
"""

def bulk_upsert(conn, rows):
    values = [
        (r["url"], r["sku"], r["name"], r["price_cents"], r["currency"],
         r["in_stock"], content_hash(r), r["scraped_at"], r["scraped_at"])
        for r in rows
    ]
    with conn.cursor() as cur:
        execute_values(cur, UPSERT, values)
    conn.commit()

The WHERE products.content_hash IS DISTINCT FROM EXCLUDED.content_hash clause skips no-op writes, so unchanged rows never touch disk and any updated_at trigger stays quiet. If you would rather keep last_seen fresh on every run, drop that WHERE line and pay for the extra writes. MongoDB users get the same behavior with a bulk UpdateOne and upsert=True, keying on _id set to the URL and using $setOnInsert for first_seen.

The single most common storage mistake in a monitoring project is overwriting the price column and calling it done. You lose the entire history, which was the point. The fix is the append-only pattern already wired into store() above: the products table holds current state, and price_history holds one immutable row per observed change. You never update a history row, you only insert.

That structure makes analysis trivial. Ranges, drops, and volatility are one query away.

SELECT url,
       MIN(price_cents) / 100.0  AS lowest,
       MAX(price_cents) / 100.0  AS highest,
       COUNT(*)                  AS changes,
       MIN(observed_at)          AS tracking_since
FROM price_history
GROUP BY url
ORDER BY changes DESC;

Because a history row is only written when the content hash changes, the table stays compact. A product that never moves has exactly one row, not one per day, so a year of daily scrapes on stable catalog does not balloon your storage. This pattern is the backbone of the datasets described in the guide on proxies for market research and data collection, where the value is in the trend, not the snapshot.

When it is time to analyze, get the data out of the operational store and into a columnar format. Parquet is the standard: it keeps data types, compresses roughly 5 to 10 times smaller than CSV, and lets tools read only the columns a query needs.

import pandas as pd

def export_parquet(db, out="exports/products.parquet"):
    Path(out).parent.mkdir(parents=True, exist_ok=True)
    df = pd.read_sql_query("SELECT * FROM products", db)
    df["price"] = df["price_cents"] / 100
    df.to_parquet(out, engine="pyarrow", compression="zstd", index=False)
    return len(df)

From there, DuckDB queries the Parquet file directly with SQL and no import step, pandas loads it in a fraction of CSV's time, and it drops straight into a data lake. Keep CSV for the human handoff and Parquet for the machine one. Do not use either as the place data lives while it is still being updated, because rewriting a whole file on every change does not scale.

Put the pieces together and the whole flow is short. Fetch structured records, log the raw response, clean and validate, upsert with dedupe and history, then export. This is what a maintainable scraped data pipeline looks like end to end.

def run(urls):
    db = get_db()
    stored = 0
    for url in urls:
        raw = fetch_product(url)     # SparkProxy extract_rules -> structured dict
        save_raw(raw)                # immutable JSONL layer, re-parse for free later
        row = clean(raw, url)        # enforce schema, cents, UTC timestamps
        if not row["name"]:          # validation gate: drop empty parses
            continue
        store(db, row)               # natural key + content hash + price history
        stored += 1
    n = export_parquet(db)
    print(f"stored {stored} rows, exported {n} products to parquet")
    return stored

if __name__ == "__main__":
    run([
        "https://www.sparkproxy.io/demo-store/p/1024",
        "https://www.sparkproxy.io/demo-store/p/1025",
    ])

Four layers, each rebuildable from the one before it: raw JSONL is the source of truth, the database is the deduplicated query layer, price history is the audit trail, and Parquet is the analysis export. Run it on a schedule and every re-run is idempotent, so a crashed job that restarts never produces a duplicate. That property, more than any single format choice, is what separates a pipeline you trust from a folder of CSV files nobody wants to open.