The Three Inevitabilities of Web Scraping

  1. The target website will change its CSS selectors and HTML hierarchy without warning.
  2. The target website will introduce rate limits, Cloudflare Turnstile, or Akamai bot protection.
  3. Data will arrive corrupted, truncated, or with unexpected schema anomalies.

The Staging-Table & Dead-Letter Queue (DLQ) Architecture

Never parse scraped data directly into production application tables. If a scraper parses 10,000 records and crashes on record 9,999 due to an unhandled data type, a naive script either rolls back the entire batch or leaves half-committed corrupt data.

Instead, utilize a Three-Tier Staging Pattern:

  • Stage 1 (Raw Ingestion): Save the raw HTML string or untouched JSON payload alongside timestamps and response headers in a partitioned table. Storage is cheap; lost raw data is unrecoverable.
  • Stage 2 (Staging Validation): Transform the raw payloads into typed Pydantic models. Valid records move to staging_valid; invalid records are routed to a dead_letter_queue with exact error traces.
  • Stage 3 (Atomic Upsert): Execute an atomic SQL ON CONFLICT DO UPDATE (upsert) to merge valid staging records into final production tables.