Building a Reliable Data Pipeline (ETL) for Scraped Data

A scraper that returns rows is not a data product. The first time a client hands me a folder of CSV dumps from a one-off crawl, the data looks usable for about ten minutes - until you notice the duplicate companies, the prices stored as "$1,299.00" strings, the half-finished runs, and the fields that silently changed shape last Tuesday. Turning raw scraping output into something a business can actually trust requires a real data pipeline for scraped data: a repeatable ETL pipeline that extracts, validates, cleans, deduplicates, enriches, stores, schedules and monitors itself. That is what separates a weekend script from infrastructure people can build on.

I have built these for lead databases, price-monitoring feeds, and competitor catalogs. The scraping itself is rarely the hard part - if you want the upstream side, see my notes on how to scrape websites without getting blocked. This article is about everything that happens after the HTML comes back.

Why raw scraped data needs a pipeline

Web data is hostile by default. Sites change their markup without warning, the same entity appears under three slightly different names, optional fields are missing on half the pages, and any large crawl will fail partway through at least some of the time. Load that straight into a database and every downstream consumer inherits the mess. The pipeline absorbs that chaos in one place, so what lands in storage is clean, typed, deduplicated and queryable. The rule I work by: never let raw data touch the system of record without passing through validation first.

The stages of an ETL pipeline

Every pipeline I build, regardless of the source, follows the same backbone. Each stage has a single job and hands off a slightly cleaner artifact to the next.

Extract: keep the raw layer

The single most valuable habit is storing the raw payload before you parse anything. Disk is cheap; a re-crawl of a protected site is not. When I find a parser bug three weeks later, I want to re-run extraction over the stored HTML, not hammer the source again. I keep a raw layer (object storage or a raw_pages collection) keyed by URL + fetch timestamp, and treat parsing as a pure function over that layer.

Validate, clean, deduplicate

Validation is where I define what "good" means. A Pydantic model or a JSON Schema describes the required fields, their types, and sane ranges - a price must be a positive number, an email must look like an email. Records that fail go to a quarantine table with the reason attached, never silently dropped; that quarantine tells you exactly how the source changed. Cleaning is the unglamorous core: strip whitespace, cast "$1,299.00" to a float, normalize phones to E.164, lowercase emails, standardize state codes. Deduplication then needs a stable business key - not the Mongo _id, but something derived from the data: a normalized domain, a phone, or a hash of (name, city). That key is the anchor for idempotent writes.

Schema design and idempotent upserts

The single most important design decision in a data pipeline for scraped data is the deduplication key, because it makes your writes idempotent. Idempotent means you can run the same batch twice and the database ends up identical - no duplicates, no surprises. You get there by upserting on the business key instead of inserting blindly.

Here is the pattern I use in MongoDB. The key fields define identity, $set updates the mutable fields, $setOnInsert stamps creation time only on first write, and a bulk unordered write keeps a single bad record from killing the batch.

from pymongo import UpdateOne

def business_key(rec: dict) -> dict:
    # stable identity derived from the data, not a random id
    return {"domain": rec["domain"].lower().strip()}

def upsert_batch(col, records: list[dict]):
    ops = []
    for rec in records:
        ops.append(UpdateOne(
            business_key(rec),
            {
                "$set": {
                    "name": rec["name"],
                    "price": rec["price"],
                    "updated_at": rec["fetched_at"],
                },
                "$setOnInsert": {"created_at": rec["fetched_at"]},
            },
            upsert=True,
        ))
    if ops:
        # unordered: one failure does not abort the rest
        return col.bulk_write(ops, ordered=False)

Back this with a unique index on the business key (db.companies.create_index("domain", unique=True)) so the database itself enforces uniqueness even under concurrent runs. In PostgreSQL the equivalent is INSERT ... ON CONFLICT (domain) DO UPDATE. Either way, the contract is the same: re-running is always safe.

Handling dirty data and partial failures

Two failure modes dominate. First, dirty and changing data: assume every field can be missing, malformed, or freshly renamed. Validate at the boundary, quarantine the rejects, and alert when the rejection rate spikes - a sudden jump usually means the site changed and your parser needs attention. Second, partial failures: a crawl of 50,000 pages will drop connections, hit rate limits, and time out. I make extraction resumable by tracking per-URL status (pending, fetched, failed, parsed) so a re-run only picks up what is left. Because the upsert is idempotent, re-processing handled records costs nothing. Never wrap the whole batch in one transaction a single bad row can roll back - process in chunks and let each chunk succeed or fail independently.

Choosing storage: MongoDB vs PostgreSQL vs a warehouse

There is no universal answer, only a fit. I pick based on how the data is shaped and how it will be queried.

• MongoDB - my default when the source schema is messy or evolving. Scraped pages rarely have a fixed shape, and a flexible document model lets me ingest first and tighten the schema later with validation rules. Great for nested data (a product with variants, a company with multiple contacts).
• PostgreSQL - when relationships and integrity matter, or the consumer is a BI tool expecting SQL. Strong constraints, ON CONFLICT upserts, and JSONB columns give you a flexible-but-strict middle ground.
• A warehouse (BigQuery, Snowflake, Redshift) - when the goal is large-scale analytics over historical snapshots rather than serving an app. I usually land cleaned data in Mongo or Postgres for the operational side and sync aggregates to a warehouse for analysis.

Incremental vs full refresh

A full refresh re-scrapes and rebuilds everything. It is simple and self-healing - any past mistake gets corrected - but it is expensive and slow, and it hammers the source. An incremental load only fetches what changed since the last run, using a cursor like a timestamp, an ID watermark, or a sitemap lastmod. Incremental is cheaper and gentler on the target site, but it can drift if you miss updates. My usual compromise: incremental on a frequent schedule (hourly or daily) plus a periodic full refresh (weekly or monthly) as a backstop to catch anything the incremental run missed.

Scheduling and orchestration

For a single pipeline, a cron job calling a Python entrypoint is genuinely fine - do not reach for heavy tooling before you need it. Once you have multiple dependent stages, retries, and backfills, an orchestrator earns its keep: Apache Airflow, Prefect, or Dagster give you a DAG of tasks, automatic retries with backoff, and a UI showing what ran when. I treat orchestration as the same discipline as the rest of my automation work - the principles carry straight over from business workflow automation with Python. Each task should be idempotent and independently retryable, so a failure in stage four never forces you to re-run stages one through three.

Observability and data-quality checks

A pipeline you cannot see into will fail silently, and that is the worst kind of failure because you trust stale data without knowing it. I instrument every run with the boring metrics that actually catch problems: rows in vs rows out, validation rejection rate, duplicate rate, and freshness (how old is the newest record). On top of those I add data-quality assertions - row count within an expected band, no more than X percent of a required field null, prices in a plausible range. When an assertion trips, the pipeline alerts and, depending on severity, halts rather than overwriting good data with bad. The goal: I learn the source broke from my own alert, not from the client.

A reference architecture

Putting it together, here is the shape I reach for again and again:

1. Scrapers write raw payloads to a raw layer (object storage or raw_pages), tagged with URL and timestamp.
2. Parser reads the raw layer and emits structured records - a pure, re-runnable function.
3. Validation gate: valid records continue, rejects go to quarantine with a reason.
4. Transform: clean, normalize, deduplicate to a business key, enrich.
5. Idempotent upsert into the system of record (Mongo or Postgres) behind a unique index.
6. Orchestrator (cron or Airflow/Prefect) runs incremental loads frequently and a full refresh periodically.
7. Monitoring tracks counts, freshness, and quality assertions, and alerts on drift.

Conclusion

The difference between a scraper and a data pipeline is the difference between data you hope is right and data you can build a business on. Keep the raw layer, validate at the boundary, deduplicate on a stable key so every write is idempotent, choose storage that matches the shape of your data, blend incremental loads with periodic full refreshes, and watch freshness and quality like a hawk. Do that, and a re-run is always safe and a broken source announces itself instead of quietly poisoning your numbers.

If you have scraped data that needs to become a reliable, scheduled feed your team can trust, I can help you design and build the pipeline end to end. Book a call and tell me what you are working with.