--- title: "What Is ETL? Extract, Transform, Load With a Python Example" description: "ETL means extracting data from a source, cleaning it into a fixed shape and loading it into a database. How it works, ETL vs ELT and a tested Python job." url: https://proxynet.io/blog/what-is-etl date: 2026-09-28 author: "Acar Diveroli" category: "Web Scraping, Proxies" lang: en --- # What Is ETL? Extract, Transform, Load With a Python Example A scraper that ran fine last month now fills a spreadsheet with prices like `£51.77`, the same book three times and a column where "In stock" sits next to "In stock (19 available)". Nobody can chart that. The scraper did its job; what is missing is everything after it: turning raw page text into typed, deduplicated rows and putting them somewhere that survives the next run. That missing part has a name, and it is older than web scraping: ETL. In this post we explain what ETL means, what each of the three steps does and how an ETL pipeline runs from trigger to finished table. We compare ETL with ELT, look at the two properties that separate a script from a pipeline (safe re-runs and monitoring), and build a complete, tested Python job that extracts book data from a scraping sandbox, cleans it and loads it into SQLite. The last sections cover use cases, common mistakes and a short decision guide. > **Note: Short answer** > > ETL stands for **extract, transform, load**. Extract pulls raw data from a source such as a website, an API or a file. Transform turns it into a fixed shape: correct types, no duplicates, rows that fail validation set aside. Load writes the clean rows into a target such as SQLite, PostgreSQL or a data warehouse. In ELT the order changes: raw data is loaded first and transformed inside the target. A good ETL job can run twice without creating duplicates and tells you when it fails. ## What is ETL? ETL is a data integration process: data comes out of one or more sources, is reshaped by rules you define and lands in a single store where it can be queried. Microsoft's architecture guide describes it as a process that [consolidates data from diverse sources into a unified data store](https://learn.microsoft.com/en-us/azure/architecture/data-guide/relational-data/etl), with the transformation applied according to business rules before loading. The idea comes from data warehousing, and it fits scraping just as well. A website is a source like any other, only messier: its data is formatted for people, split across pages and free to change without notice. If you have ever written a scraper and then a second script to fix its output, you have already built two thirds of an ETL pipeline. ## What does each step do? **Extract** collects the raw data and changes it as little as possible. For web data that means sending requests, following pagination and pulling the fields you need out of the HTML. The output is still text: `"£51.77"`, `"Three"`, `"\n In stock\n"`. Keeping extraction dumb has a benefit: when something breaks, you can tell whether the source changed or your cleaning rules did. **Transform** is where the rules live. Typical operations are: - converting types (price text to a number, a rating word to an integer, a date string to a date) - normalising text (whitespace, Unicode forms, letter case) - deduplicating on a stable key, such as the product URL - validating (a price must be positive, a rating must be 1 to 5) and setting aside rows that fail - enriching or joining (adding a currency, a category, a scrape timestamp) Turning the page's HTML into fields is itself a parsing step; we explain the parser types and where they fail in [What Is Data Parsing?](/blog/what-is-data-parsing). A full cleaning pass with pandas is in [How to Clean Scraped Data With pandas](/blog/clean-scraped-data-with-pandas). **Load** writes the clean rows into the target and makes the result visible in one piece. For a small project that is a SQLite file; for a team, PostgreSQL or a cloud warehouse. Loading is also where most duplicate-row bugs are born, which is why the method matters more than the destination. The trade-offs between CSV, JSON and SQLite are covered in [How to Save Scraped Data to CSV, JSON and SQLite](/blog/save-scraped-data-csv-json-sqlite). ## How does an ETL pipeline run? A single run of a scheduled scraping ETL job goes through these steps: 1. **A trigger starts the run.** A cron entry, an orchestrator such as Apache Airflow or a person running the script. 2. **Extract fetches the source.** Pages are requested at a polite rate, failed requests are retried with a pause, and the raw fields are collected. 3. **Transform cleans each record.** Types are converted, duplicates dropped, and records that break a rule are counted and logged instead of silently passing through. 4. **A quality gate decides.** If extraction returned nothing or too many rows were rejected, the run stops before touching the target. A layout change on the site should not overwrite yesterday's good data. 5. **Load writes in one transaction.** Rows are upserted by key; either the whole batch is committed or nothing is. 6. **The run reports.** Counts for extracted, clean, rejected and loaded rows go to a log or an alert, so a quiet failure becomes visible. On large jobs the phases can overlap, with transformation starting on data that has already arrived. For a few thousand rows, running them in sequence is easier to debug. ## ETL vs ELT: what is the difference? ELT (extract, load, transform) keeps the same three steps but moves transformation into the target. Raw data is loaded as it is, and SQL or the warehouse engine reshapes it later. Microsoft's guide puts the difference plainly: in ELT the transformation occurs in the target data store. | | ETL | ELT | |---|---|---| | Order | Extract, transform, then load | Extract, load, then transform | | Where the cleaning runs | In your script or a separate engine | Inside the database or warehouse (SQL) | | What the target holds | Only clean, validated rows | Raw rows plus cleaned tables or views | | Raw data kept? | Only if you store it separately | Yes, by design | | Target needs | Any store, even a SQLite file | A target strong enough to transform at scale | | Fixing a cleaning bug | Re-extract, or re-run from saved raw files | Rewrite the SQL and rebuild from raw tables | | Good fit for | Small to medium scraping jobs, strict schemas | Large volumes, exploratory analysis, changing rules | For scraping there is a useful middle path: save the raw HTML or raw JSON of each run to disk (a staging area), then transform from those files. If a cleaning rule turns out to be wrong, you rebuild the table without sending a single new request to the site. ## Why must an ETL job be safe to run twice? Jobs fail halfway. A connection drops on page 38, the machine restarts, a scheduler retries. The question is what the target looks like after the second attempt. Apache Airflow's best-practices guide answers it directly: treat a task like a [transaction in a database](https://airflow.apache.org/docs/apache-airflow/stable/best-practices.html), never produce partial results, and replace a plain `INSERT` with an upsert, because a re-run with `INSERT` can leave duplicate rows. That property is called **idempotency**: running the job once or three times over the same input leaves the same result. In practice it comes from three habits: - **A natural key.** Every row has an identifier that stays the same between runs. For a product page the URL works; an auto-increment ID does not. - **Upsert instead of insert.** SQLite has supported `INSERT … ON CONFLICT DO UPDATE` since version 3.24.0, and the [special `excluded.` qualifier](https://sqlite.org/lang_upsert.html) refers to the values that would have been inserted. PostgreSQL has the same syntax. - **One transaction per load.** The batch is committed as a whole or rolled back, so a crash never leaves half a table. ## Scheduling and monitoring an ETL job A job that runs only when you remember it is a script. For one machine and one job, cron (or Task Scheduler on Windows) is enough. When jobs depend on each other (scrape, then clean, then build a report), an orchestrator earns its place. In Airflow a pipeline is a DAG, a set of tasks with dependencies, and its [`schedule` argument](https://airflow.apache.org/docs/apache-airflow/stable/core-concepts/dags.html) accepts presets such as `@daily`, a cron string or a time interval. Airflow also retries failed tasks, which is one more reason the tasks must be idempotent. Monitoring does not need a platform on day one. Log four numbers on every run (pages fetched, rows extracted, rows rejected, rows loaded) and alert on two conditions: the job did not run, or one of those numbers moved far from its usual value. A drop from 1,000 rows to 20 usually means the site changed its layout, not that the shop sold out. For the scraping side, [How to Monitor a Website for Changes](/blog/website-change-monitoring) shows how to detect when a page itself changes. ## Where do proxies fit into ETL? Only in the extract step, and only when the job needs them. A few pages a day from a public site rarely do. They matter when you collect prices that differ by country, when a legitimate job with many requests runs into the per-IP rate limits a site applies, or when the source shows location-dependent content. A [Residential Proxy](https://proxynet.io/residential-proxy) lets the extract step request pages from a chosen country and city; a [Rotating Proxy](https://proxynet.io/rotating-proxy) spreads requests across IPs for larger crawls. A proxy does not change the rules. The site's robots.txt, its terms and a reasonable request rate still apply, and an official API or export comes first whenever one exists. We discuss the request side in more depth in [How to Scrape Websites Without Getting Blocked](/blog/web-scraping-without-getting-blocked) and on our [data scraping](/data-scraping) page. ## A complete ETL job in Python The script below is a whole pipeline in one file. It extracts every book card from [books.toscrape.com](https://books.toscrape.com/), a sandbox built for scraping practice, transforms the fields into typed rows and loads them into SQLite with an upsert. It retries on `429` and `5xx` responses with growing pauses, waits one second between pages, stops if too many rows fail validation, and routes traffic through a proxy only if you set `PROXY_URL`. Install the two dependencies first: ```bash pip install requests beautifulsoup4 ``` Then save this as `etl_books.py`: ```python """A small ETL job: books.toscrape.com -> clean rows -> SQLite.""" import argparse import logging import os import sqlite3 import time import unicodedata from datetime import datetime, timezone from urllib.parse import urljoin import requests from bs4 import BeautifulSoup from requests.adapters import HTTPAdapter from urllib3.util.retry import Retry BASE = "https://books.toscrape.com/catalogue/" RATINGS = {"One": 1, "Two": 2, "Three": 3, "Four": 4, "Five": 5} log = logging.getLogger("etl") def make_session(): retry = Retry(total=4, backoff_factor=1.5, status_forcelist=(429, 500, 502, 503, 504), respect_retry_after_header=True) s = requests.Session() s.mount("https://", HTTPAdapter(max_retries=retry)) s.headers["User-Agent"] = "etl-demo/1.0 (contact: you@example.com)" proxy = os.getenv("PROXY_URL") # e.g. http://user:pass@pr.proxynet.io:8000 if proxy: s.proxies = {"http": proxy, "https": proxy} return s # ---------- EXTRACT: fetch pages, keep the raw strings ---------- def extract(session, max_pages, delay=1.0): raw, url, page = [], urljoin(BASE, "page-1.html"), 0 while url and page < max_pages: resp = session.get(url, timeout=15) resp.raise_for_status() soup = BeautifulSoup(resp.content, "html.parser") for card in soup.select("article.product_pod"): raw.append({ "title": card.h3.a["title"], "href": card.h3.a["href"], "price": card.select_one("p.price_color").get_text(), "rating": card.select_one("p.star-rating")["class"][-1], "stock": card.select_one("p.availability").get_text(), "page_url": url, }) page += 1 nxt = soup.select_one("li.next a") url = urljoin(url, nxt["href"]) if nxt else None time.sleep(delay) log.info("extract: %d pages, %d raw rows", page, len(raw)) return raw # ---------- TRANSFORM: types, cleaning, dedupe, validation ---------- def transform(raw): clean, rejected, seen = [], 0, set() now = datetime.now(timezone.utc).isoformat(timespec="seconds") for r in raw: try: url = urljoin(r["page_url"], r["href"]) if url in seen: continue seen.add(url) title = " ".join(unicodedata.normalize("NFKC", r["title"]).split()) price = float(r["price"].strip().lstrip("£")) rating = RATINGS[r["rating"]] in_stock = "in stock" in r["stock"].lower() if not title or price <= 0: raise ValueError("empty title or bad price") except (KeyError, ValueError) as exc: rejected += 1 log.warning("rejected %r: %s", r.get("title"), exc) continue clean.append((url, title, price, rating, int(in_stock), now)) log.info("transform: %d clean, %d rejected", len(clean), rejected) return clean, rejected # ---------- LOAD: upsert into SQLite in one transaction ---------- def load(rows, db_path): con = sqlite3.connect(db_path) with con: # commits on success, rolls back on error con.execute("""CREATE TABLE IF NOT EXISTS books ( url TEXT PRIMARY KEY, title TEXT NOT NULL, price_gbp REAL NOT NULL, rating INTEGER, in_stock INTEGER, updated_at TEXT)""") con.executemany("""INSERT INTO books VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(url) DO UPDATE SET title=excluded.title, price_gbp=excluded.price_gbp, rating=excluded.rating, in_stock=excluded.in_stock, updated_at=excluded.updated_at""", rows) total = con.execute("SELECT COUNT(*) FROM books").fetchone()[0] con.close() log.info("load: %d rows upserted, %d rows in table", len(rows), total) return total def main(): ap = argparse.ArgumentParser() ap.add_argument("--pages", type=int, default=3) ap.add_argument("--db", default="books.db") args = ap.parse_args() logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s") raw = extract(make_session(), args.pages) rows, rejected = transform(raw) if not rows or rejected > len(raw) * 0.1: # guard: don't load a broken batch raise SystemExit(f"aborting load: {len(rows)} clean, {rejected} rejected") load(rows, args.db) if __name__ == "__main__": main() ``` Run it for the whole catalogue with `python etl_books.py --pages 60`. The site has 50 pages, so the loop stops on its own when the "next" link disappears. Our run printed: ```text INFO extract: 50 pages, 1000 raw rows INFO transform: 1000 clean, 0 rejected INFO load: 1000 rows upserted, 1000 rows in table ``` Run it a second time and the last line still ends in `1000 rows in table`. The upsert updated the existing rows instead of adding copies, which is the idempotency described above. We also tested the failure paths. When a connection timed out, the retries ran and the script exited with an error before the load step, so the table kept its previous rows. Feeding `transform()` a price of `£x` and a rating of `Six` rejected both rows with a logged reason, and a repeated URL was dropped as a duplicate. To send the extract step through a proxy, set the variable before running; nothing else changes: ```bash export PROXY_URL="http://user:pass@pr.proxynet.io:8000" python etl_books.py --pages 60 ``` To run it every night at 03:00 on Linux, add a crontab line such as `0 3 * * * cd /opt/etl && .venv/bin/python etl_books.py --pages 60 >> etl.log 2>&1`. Once you have several dependent jobs, move them into an orchestrator. ## Where ETL is used with web data - **Price monitoring:** a nightly job extracts competitor prices, normalises currencies and loads a history table; see our [price monitoring](/price-monitoring) page and [How to Track Competitor Prices](/blog/competitor-price-tracking). - **Market research:** product counts, ratings and assortment changes across many shops, loaded into one schema; see [market research](/market-research). - **Large crawls:** discovering thousands of URLs first, then extracting from each; our [web crawler](/web-crawler) page covers the discovery side. - **Alternative data:** analysts combine web data with other sources, and the quality rules in the transform step decide whether the result can be trusted; see [What Is Alternative Data?](/blog/what-is-alternative-data). - **Analysis and mining:** a clean, loaded table is the input that [data mining](/blog/what-is-data-mining) needs. ## Common ETL mistakes - **Cleaning inside the extract step.** When parsing and converting happen in the same loop, you cannot tell a site change from a bug in your rules. Keep extraction raw. - **Plain `INSERT` on every run.** The second run doubles the table. Use a natural key and an upsert. - **No quality gate.** A layout change turns every price into `None`, and the job happily loads 1,000 empty rows over good data. - **Committing row by row.** A crash halfway leaves a table that is neither the old state nor the new one. - **Silent rejects.** Dropping bad rows without counting them hides problems until someone notices the numbers look thin. - **Wrong encoding.** Reading bytes with the wrong charset turns `£` into `£`; our [Python Unicode errors](/blog/python-unicode-encoding-errors) post explains the cause. - **Ignoring the source's limits.** No delay, no retry pause, no respect for `Retry-After`. The site starts answering with `429`; see [429 Too Many Requests Explained](/blog/http-429-too-many-requests). ## Decision guide | Your situation | What to use | |---|---| | One source, a few thousand rows, one person | A single Python ETL script and SQLite, run by cron | | Rules change often, you want to keep raw data | ELT: load raw rows or files, transform with SQL | | Several jobs that depend on each other | An orchestrator such as Apache Airflow | | Large volumes, a cloud warehouse already in place | ELT inside the warehouse | | Prices or content that differ by country | ETL with a residential proxy in the extract step | | The source offers an API or a download | Extract from the API; skip scraping entirely | ## Frequently asked questions ### What does ETL stand for? Extract, transform, load. Data is extracted from a source, transformed into a consistent, validated shape and loaded into a target store such as a database or data warehouse. ### Is web scraping the same as ETL? No. Scraping is one way to do the extract step. ETL also covers what happens after it: cleaning, deduplicating, validating and loading the data somewhere it can be queried and kept up to date. ### Is ETL still used, or has ELT replaced it? Both are used. ELT is common when a cloud warehouse can do the heavy transformation and raw data should be kept. ETL remains a natural fit when the target is small, the schema is strict or data must be cleaned before it is stored. ### Can I build an ETL pipeline with just Python? Yes. For small and medium jobs, `requests`, an HTML parser and the standard library's `sqlite3` module are enough, as the script above shows. Libraries such as pandas help when the transform step gets heavier. ### What is an ETL pipeline? It is the ETL process set up to run repeatedly: a trigger, the three steps, a quality check and a report, usually on a schedule. A one-off script becomes a pipeline once it can run unattended and recover from failures. ### Do I need Airflow for ETL? Not for one job on one machine; cron is enough. An orchestrator helps when several tasks depend on each other, need retries and run history, or are shared by a team. ## Summary ETL is the part of a data project that comes after collection: extract raw data, transform it into typed, deduplicated, validated rows, and load it in one transaction with an upsert so a re-run never doubles the table. ELT moves the transform step into the target and suits large warehouses; for most scraping jobs a small ETL script with SQLite is the right start. Add a quality gate and four logged numbers, schedule it, and bring in a proxy only when the extract step needs a different location or more IPs. When it does, see our [proxy plans](/proxy).