Data Persistence Architectures

A scraper is useless if it cannot reliably store the extracted data. Depending on the scale of your operation—from a 100-row script to a 10-million-row distributed crawler—your storage strategy must adapt.

1. Flat Files (CSV / JSON)

For small to medium scraping jobs (up to ~100,000 records), flat files are perfectly adequate. - CSV (Comma Separated Values): Best for highly structured, flat data (e.g., product catalogs). Easily imported into Excel or Pandas for analysis. - JSON / JSONL (JSON Lines): Best for nested, hierarchical data (e.g., an article with an array of comments). JSONL is particularly powerful for scraping because you can append to the file line-by-line in real-time, meaning you won't lose data if the scraper crashes halfway through.

import json

# Appending to a JSONL file in real-time
def save_record(data):
    with open('scraped_data.jsonl', 'a', encoding='utf-8') as f:
        f.write(json.dumps(data) + '\n')

2. Relational Databases (SQLite / PostgreSQL)

When scraping structured relational data that requires deduplication, constraints, or complex querying, you need a SQL database. - SQLite: Incredible for standalone Python scripts. It stores the entire database in a single local file. No server setup required. Excellent for storing millions of rows locally. - PostgreSQL: Required when you have a fleet of concurrent scrapers distributed across multiple servers, all writing to the same centralized data warehouse.

Pro-tip for SQL scraping: Always define a UNIQUE constraint on your target's primary identifier (like product_sku or article_url). Use INSERT ON CONFLICT DO UPDATE (Upsert) logic to effortlessly handle duplicate data without crashing your script.

3. Document Databases (MongoDB)

If the target website's schema changes frequently, or if the data is highly unstructured, MongoDB is the go-to solution. It allows you to dump massive nested JSON payloads directly into collections without defining rigorous SQL schemas upfront.

4. Object Storage (AWS S3)

If your scraper downloads media assets (images, PDFs, videos), do not store them in your database. Store the binary files in an S3-compatible object bucket, and save the resulting S3 URL string in your database alongside the metadata.

Schema Hygiene for Scrapers

The schemas you miss in week one cost you rewrites in month six. Three habits prevent most of that pain. First, timestamp everything: fetched_at (when the page was read) and updated_at (when this row was last refreshed) are the columns that make incremental-scraping and freshness monitoring possible. Second, index what you query: the columns you filter on in alerts and dashboards get indexes, not just the primary key. Third, make the key honest: a business key (SKU, external id, canonical URL) — not the database row id — is what surviving re-scrapes and deduplication depend on.

CREATE TABLE products (
    sku        TEXT PRIMARY KEY,
    title      TEXT NOT NULL,
    price      REAL,
    fetched_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
CREATE INDEX idx_products_updated ON products(updated_at);

BEGIN;
INSERT INTO products (sku, title, price, fetched_at, updated_at)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT(sku) DO UPDATE SET
    price = excluded.price,
    updated_at = excluded.updated_at;
COMMIT;

Write in Batches, Not Single-Spin

A database write per record is the slowest way to store a six-figure scrape. Wrap a page's worth of rows in one transaction: the BEGIN/COMMIT above turns thousands of individual inserts into a handful of committed batches, and SQLite's PRAGMA synchronous = NORMAL plus journal_mode = WAL mean each commit costs a fraction of the default. The batch boundary is also your natural durability checkpoint — which is exactly the point where the crash-resume lesson wires in.