Data cleaning and storage for scraped data: schemas, validation, JSONL, and databases

Data cleaning turns raw extracted values into records that match a schema: correct types, required fields present, units consistent, duplicates removed. Storage is where those records live so they can be queried, audited, and deleted. A crawl that writes unstructured HTML is not a dataset; it is a pile of pages.

Define the schema before the parser

A schema is a contract between the parser and everything downstream. At minimum, define:

• the field names and types;
• which fields are required;
• the allowed range or enum for constrained fields;
• the canonical unit (cents vs dollars, UTC vs local time);
• the stable key used for deduplication;
• the source URL and the observation time.

A JSON Schema or a Pydantic model is better than a comment, because it can run in CI.

Validate every record

import json

records = [
    {"sku": "A-1", "price": 89.0},
    {"sku": "", "price": -1},
]

for record in records:
    errors = []
    if not record.get("sku"):
        errors.append("sku missing")
    if not isinstance(record.get("price"), (int, float)) or record["price"] < 0:
        errors.append("price invalid")
    print(record, errors or "ok")

Tested on 2 November 2026:

{'sku': 'A-1', 'price': 89.0} ok
{'sku': '', 'price': -1} ['sku missing', 'price invalid']

A validation failure should not silently write a null. Either drop the record with a logged reason or quarantine it for review. Count drops per run; a sudden increase is a layout change.

Store raw and normalised data separately

Keep two layers:

• Raw: the response body or the extracted JSON exactly as received, plus URL, status, headers, fetched-at.
• Normalised: the typed record that passed validation, plus the source raw ID.

The raw layer lets you fix a parser bug without re-crawling. The normalised layer is what applications query. Set a retention window for raw (for example, 7–30 days) and keep normalised records longer.

Choose a format

A common pattern: JSONL for the crawl output, then load into Parquet or Postgres for analysis. Do not use a spreadsheet as the primary store for a recurring crawl.

JSONL example

import json

record = {
    "source_url": "https://example.com/items?page=1",
    "fetched_at": "2026-11-02T08:00:00Z",
    "sku": "A-1",
    "name": "Mechanical keyboard",
    "price_cents": 8900,
    "currency": "USD",
}

with open("items.jsonl", "a", encoding="utf-8") as handle:
    handle.write(json.dumps(record, ensure_ascii=False) + "\n")

Store money in integer minor units and a currency code. 89.0 is ambiguous; 8900 USD is not. Store timestamps in UTC with an explicit offset.

SQLite for a small crawler

import sqlite3

connection = sqlite3.connect("crawl.db")
connection.execute("""
CREATE TABLE IF NOT EXISTS items (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  currency TEXT NOT NULL,
  source_url TEXT NOT NULL,
  fetched_at TEXT NOT NULL
)
""")
connection.execute(
    "INSERT INTO items (sku, name, price_cents, currency, source_url, fetched_at) "
    "VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(sku) DO UPDATE SET "
    "name=excluded.name, price_cents=excluded.price_cents, currency=excluded.currency, "
    "source_url=excluded.source_url, fetched_at=excluded.fetched_at",
    ("A-1", "Mechanical keyboard", 8900, "USD", "https://example.com/items?page=1", "2026-11-02T08:00:00Z"),
)
connection.commit()
print(connection.execute("SELECT COUNT(*) FROM items").fetchone()[0])
connection.close()

Output:

1

The CHECK constraint is doing real work: a parser bug that writes a negative price fails at the database boundary instead of polluting the dataset.

Retention and deletion

Decide retention before the first crawl:

• raw HTML: 7–30 days;
• normalised records: as long as the product needs;
• personal data: the shortest period that satisfies the purpose, with a documented basis;
• logs: 30–90 days, with access controls.

Deletion must be possible by source, by record key, and by time. If you cannot delete a record, you cannot honour a data-subject request or a legal hold. Design for deletion before you need it.

Personal data

Scraped data often contains personal data even when the page is public: names, handles, profile URLs, comments. Public does not mean unrestricted. The GDPR guide covers the legal analysis; the engineering rule is simpler: collect the minimum, store it securely, set a retention period, and do not join it into a profile you do not need.

Sources

• JSON Lines
• Apache Parquet
• SQLite documentation