How to Build Your First ETL Pipeline in Python

The first time someone told me to "go build an ETL pipeline," it sounded like a job for a big team with a cluster and a budget. It is not. An ETL pipeline is three ordinary steps: get some data, tidy it up, and put it somewhere useful. That is the whole idea.

In this guide you will learn how to build an ETL pipeline in Python from scratch, one small piece at a time, and finish with a real script you can run and schedule. No prior data-engineering experience needed. If you can write a Python function and run a file, you can follow along. We will pull live data from a free public API, clean it with pandas, and load it into a database, then talk about what changes when this stops being a toy.

help What an ETL pipeline actually is

Forget the jargon for a second. You already run ETL pipelines in real life. Making dinner is one. You buy ingredients (extract), you wash and chop them (transform), and you plate the meal (load). Data pipelines do the same thing with information instead of vegetables.

Here is why people bother splitting it into three named steps. Raw data is almost never in the shape you need. It has missing values, weird column names, prices stored as text, and duplicates. If you jam fetching, cleaning, and saving into one tangled block of code, you get something that works once on your laptop and breaks the moment anything changes. Three clear steps stay easy to read, easy to fix, and easy to reuse.

bolt What you'll build

We will build a small pipeline that pulls the top 20 cryptocurrencies and their prices from a free public API, cleans the response into a tidy table, and saves it to a local database you can query. It is about 40 lines of code total, split across the three stages.

Pick any API you like once you understand the shape. Weather, exchange rates, your own app's data. The steps do not change.

settings Set up your environment

Create a folder, make a virtual environment so your packages stay isolated, and install the three libraries we need.

python -m venv venv
source venv/bin/activate      # on Windows: venv\Scripts\activate
pip install requests pandas sqlalchemy

That is the whole setup. requests fetches data over HTTP, pandas is the workhorse for cleaning tabular data, and SQLAlchemy gives us a simple way to write to a database.

download Step 1: Extract the data

Extraction just means "go get the raw data." We ask the API for the top 20 coins and hand back whatever it gives us. Notice we do not clean anything here. Fetching and cleaning are different jobs, so they get different functions.

import requests
 
def extract():
    """Pull the top 20 coins by market cap from the CoinGecko API."""
    url = "https://api.coingecko.com/api/v3/coins/markets"
    params = {
        "vs_currency": "usd",
        "order": "market_cap_desc",
        "per_page": 20,
        "page": 1,
    }
    response = requests.get(url, params=params, timeout=30)
    response.raise_for_status()   # stop loudly if the API returns an error
    return response.json()        # a list of dictionaries

Run this on its own and you get back a list of Python dictionaries, one per coin, each with a couple of dozen fields you mostly do not care about. Cleaning that up is the next step.

cleaning_services Step 2: Transform it with pandas

This is the heart of the pipeline. We load the raw records into a pandas DataFrame (think of it as a spreadsheet in code), keep only the columns we want, rename them, fix the types, and drop anything broken.

import pandas as pd
from datetime import datetime, timezone
 
def transform(raw_records):
    """Keep the useful columns, clean them, and stamp each row."""
    df = pd.DataFrame(raw_records)
 
    # 1. keep only the columns we actually need
    df = df[["id", "symbol", "name", "current_price", "market_cap"]]
 
    # 2. rename to clearer names
    df = df.rename(columns={
        "current_price": "price_usd",
        "market_cap": "market_cap_usd",
    })
 
    # 3. force numbers to be numbers, and drop rows with no price
    df["price_usd"] = pd.to_numeric(df["price_usd"], errors="coerce")
    df = df.dropna(subset=["price_usd"])
 
    # 4. record when this batch was pulled
    df["loaded_at"] = datetime.now(timezone.utc)
 
    return df

Read those four moves top to bottom, because they are the four you will repeat in almost every pipeline you ever write. Trim to the columns you need. Give them honest names. Make sure each column is the type you think it is. Add a timestamp so future-you knows when the data arrived. That last one feels optional now and saves you a headache later when you are staring at two batches and cannot tell which is fresh.

The errors="coerce" bit is quietly important. If a price comes back as junk instead of a number, pandas turns it into a blank rather than crashing, and the next line drops those blank rows. Real data is messy, so you plan for messy.

database Step 3: Load it into a database

Loading writes the clean table to its final home. We will use SQLite, a database that lives in a single file and needs zero setup, which makes it perfect for learning. SQLAlchemy lets us swap that for PostgreSQL later by changing one line.

from sqlalchemy import create_engine
 
def load(df):
    """Write the cleaned rows into a local SQLite database."""
    engine = create_engine("sqlite:///crypto.db")
    df.to_sql("coins", engine, if_exists="replace", index=False)

play_arrow Put it together and run it

Now the three functions become one pipeline. This is the file you actually run.

from extract import extract
from transform import transform
from load import load
 
def run_pipeline():
    raw = extract()
    clean = transform(raw)
    load(clean)
    print(f"Loaded {len(clean)} rows into crypto.db")
 
if __name__ == "__main__":
    run_pipeline()

python pipeline.py
# Loaded 20 rows into crypto.db

That is a working ETL pipeline. Twenty rows of clean crypto prices, pulled live and saved to a database, in about 40 lines. To prove it landed, query the database:

import sqlite3
 
conn = sqlite3.connect("crypto.db")
rows = conn.execute(
    "SELECT name, price_usd FROM coins ORDER BY market_cap_usd DESC LIMIT 5"
)
for name, price in rows:
    print(f"{name:<15} ${price:,.2f}")

Seeing your own data come back out of the database is the moment it clicks. You built the thing.

trending_up From a script to a real pipeline

Here is the honest part most tutorials skip. What you just built is correct, and it is also a toy. The jump from "runs on my laptop" to "a business depends on it at 2am" is where data engineering actually lives. You do not need all of this today, but you should know it exists, because it is what the next questions will be about.

Logging instead of print. print disappears the moment the script ends. Real pipelines record what happened so you can debug a 3am failure from the logs alone.

import logging
 
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s %(levelname)s %(message)s",
)
 
def run_pipeline():
    logging.info("Pipeline started")
    try:
        raw = extract()
        logging.info("Extracted %d records", len(raw))
        clean = transform(raw)
        load(clean)
        logging.info("Loaded %d rows", len(clean))
    except Exception:
        logging.exception("Pipeline failed")
        raise

Scheduling. A pipeline you run by hand is a script. A pipeline that runs itself is infrastructure. The simplest scheduler is cron, built into Mac and Linux.

# run the pipeline every day at 6am
0 6 * * * /path/to/venv/bin/python /path/to/pipeline.py >> /path/to/pipeline.log 2>&1

Idempotency and incremental loads. Running the pipeline twice should not create duplicate or double-counted data. And once your source has millions of rows, you stop reprocessing everything every run and start loading only what changed. That single idea, process the delta and not the whole world, is the biggest performance lever in data engineering. On a production pipeline I built that moves millions of records a day, switching to incremental upserts instead of full reloads cut data-prep time by about 40%. Same code shape you just wrote, different loading strategy.

Knowing when to graduate. A single script with cron is genuinely fine for a long time. You outgrow it when you have many pipelines with dependencies (run B only after A succeeds), or when one step gets too slow to run in a line. The first problem is what orchestrators like Dagster, Airflow and Prefect solve, and I compared them in Dagster vs Airflow vs Prefect. The second is when you reach for a queue and a pool of workers, which is how one pipeline I re-architected went from 40,000 to over two million records a day (the story is in scaling data pipelines with RabbitMQ). You do not need either yet. You just need to recognise the day you do.

error Common beginner mistakes

I made most of these myself, so here they are before you do.

• Cramming all three steps into one function. It feels faster and it always costs you later. Keep extract, transform and load separate.
• Trusting the API blindly. Networks fail and APIs change. Use raise_for_status() and a timeout, and expect a bad response sometimes.
• Confusing replace and append. One keeps history, one erases it. Pick on purpose.
• No logging. When a scheduled run fails silently at night, print statements you cannot see are worthless.
• Hardcoding secrets. API keys and database passwords belong in environment variables, never pasted into the script you push to GitHub.
• Skipping the timestamp. Add a loaded_at column from day one. You will thank yourself the first time you debug stale data.

quiz Frequently asked questions

rocket_launch Where to go next

You now know how to build an ETL pipeline in Python, and more importantly, you know what the three steps are for and where a real one grows beyond a script. That mental model, extract cleanly, transform deliberately, load carefully, is the same one behind pipelines moving billions of rows. The scale changes. The shape does not.

Play with it next. Swap the crypto API for a weather one. Change replace to append and keep a history table. Add a second transform. Every change teaches you something the first version hid.

If you are past the toy stage and staring at a real pipeline that is slow, fragile, or quietly losing data, that is the work I do for a living. Here is how I can help, or tell me what you are building and I will point you in the right direction.