What Is a Data Pipeline? Building Your First ETL Pipeline in Python

Contents

Python ETL data pipeline diagram showing extract transform load workflow with pandas

Here's a fact worth sitting with before writing a single line of code: most new data projects in 2026 don't actually do ETL anymore — they do ELT. The order of operations quietly flipped. Instead of transforming data mid-flight before it lands anywhere, teams increasingly dump raw data into a cloud warehouse first, then transform it there, because warehouses now offer cheap, scalable compute that makes "transform later" the more practical default. Worth knowing before you assume the acronym in this article's title is the whole story.

This is genuinely the hands-on companion to the data engineering courses article from earlier in this series — every course on that list eventually has you build exactly this project. Consider this the "actually do it" version, building the same core pipeline the field's foundational courses use as their entry point.

By the end of this guide, you'll understand what a data pipeline actually is, build a working ETL pipeline in plain Python, and know exactly when that simple approach stops being enough and Airflow becomes necessary. IMO, this is the project that makes "data engineer" stop being an abstract job title and start feeling like a concrete, learnable skill :)

What a Data Pipeline Actually Is

A data pipeline is a series of steps that move data from one or more sources to a destination, usually transforming it along the way. Strip away the buzzwords and it's genuinely just: get data, do something to it, put it somewhere useful.

Extract — pull data from wherever it originates: an API, a CSV file, a database, a message queue.
Transform — clean it, reshape it, join it with other data, filter out garbage — whatever makes the raw data actually usable.
Load — write the transformed result somewhere queryable: a database, a data warehouse, a file others can consume.

ETL does this transformation step between extraction and loading. ELT loads the raw data first, then transforms it inside the destination system — typically a cloud warehouse with cheap compute sitting idle, ready to do that transformation work after the fact. Both patterns are genuinely valid; which one fits depends on where your compute is cheapest and how much flexibility you want to keep in your raw data before committing to a specific transformation.

Why Python Specifically, Not a Dedicated ETL Tool

Python isn't an ETL tool by itself — it's a general-purpose language that happens to be the most common choice for building ETL pipelines, specifically because it gives you complete flexibility to extract from any source, transform with any logic, and load to any destination without being boxed into a vendor's specific workflow assumptions.

Paired with an orchestrator like Airflow or Dagster, Python is genuinely considered by many experienced data engineers to be the most versatile combination available — not the fastest for every specific task, but the one that adapts to genuinely any pipeline shape you need to build.

Setting Up Your Environment

python -m venv etl-env
source etl-env/bin/activate  # Windows: etl-env\Scripts\activate

pip install pandas requests sqlalchemy

Pandas handles the transform step — recall this being the workhorse library across data-adjacent tutorials throughout this series. SQLAlchemy handles loading into a database without you hand-writing raw SQL insert statements. Requests handles extraction from an API, one of the two most common source types alongside flat files.

Step 1: Extract

Let's pull data from a public API — genuinely one of the most common real-world extraction sources.

import requests
import pandas as pd

def extract_from_api(url):
    response = requests.get(url)
    response.raise_for_status()
    data = response.json()
    return pd.DataFrame(data)

df = extract_from_api("https://api.example.com/sales")

That response.raise_for_status() call matters more than it looks — without it, a failed API call silently returns whatever error page the server sent back, and your pipeline happily tries to process that as if it were real data. Catching failures at the extraction boundary, before bad data ever enters your transformation logic, is genuinely one of the cheapest reliability wins available.

Extracting From a CSV File

def extract_from_csv(filepath):
    return pd.read_csv(filepath)

df = extract_from_csv("raw_sales_data.csv")

Pandas handles a genuinely wide range of source formats out of the box — CSV, Excel, JSON, Parquet, and more — all through similarly named read_* functions, which is a big part of why it's become the default transformation-layer library for Python-based ETL work.

Step 2: Transform

This is where raw, messy data becomes something actually usable — cleaning, reshaping, and combining.

def transform_sales_data(df):
    df = df.dropna(subset=["order_id", "amount"])
    df["amount"] = df["amount"].astype(float)
    df["order_date"] = pd.to_datetime(df["order_date"])
    
    df["amount_category"] = pd.cut(
        df["amount"],
        bins=[0, 50, 200, float("inf")],
        labels=["small", "medium", "large"]
    )
    
    daily_summary = df.groupby(df["order_date"].dt.date).agg(
        total_sales=("amount", "sum"),
        order_count=("order_id", "count")
    ).reset_index()
    
    return df, daily_summary

cleaned_df, summary_df = transform_sales_data(df)

Notice this function does three genuinely distinct things: dropping invalid rows, enforcing correct data types, and aggregating into a summary view. Keeping transformation logic in small, named functions like this — rather than one giant script — is exactly what makes a pipeline maintainable once it grows past a handful of lines.

Designing for Idempotency

Here's a genuinely important production concept worth understanding even at this small scale: an idempotent transformation produces the same result no matter how many times you run it on the same input. If your pipeline fails halfway through and you rerun it, idempotent transformations mean you won't accidentally double-count or duplicate data. This is one of the specific best practices that separates a script that works once from a pipeline that survives real production failures.

Step 3: Load

With clean data in hand, write it somewhere queryable.

from sqlalchemy import create_engine

def load_to_database(df, table_name, connection_string):
    engine = create_engine(connection_string)
    df.to_sql(table_name, engine, if_exists="append", index=False)

load_to_database(
    cleaned_df,
    "sales",
    "postgresql://user:password@localhost:5432/warehouse"
)

That if_exists="append" parameter is worth understanding deliberately, not defaulting to blindly — "replace" would wipe the entire table and rewrite it fresh, "append" adds new rows to what's already there, and "fail" refuses to write if the table already exists. Picking the wrong one is a genuinely common way to accidentally destroy existing production data.

Putting the Full Pipeline Together

def run_pipeline():
    raw_data = extract_from_api("https://api.example.com/sales")
    cleaned_data, summary = transform_sales_data(raw_data)
    load_to_database(cleaned_data, "sales", DB_CONNECTION_STRING)
    load_to_database(summary, "daily_sales_summary", DB_CONNECTION_STRING)
    print(f"Pipeline complete: {len(cleaned_data)} rows loaded")

if __name__ == "__main__":
    run_pipeline()

Run this manually, and you've genuinely got a working ETL pipeline — extract, transform, load, all in maybe 40 lines of code. This is exactly the kind of "simple version first" foundation the RAG and RL series earlier in this content built toward before introducing frameworks.

When Plain Python Stops Being Enough

Recall the MLOps articles' pipeline orchestration section — this is exactly where that concept becomes concretely necessary rather than abstract.

Using Airflow makes the most sense specifically when you perform long ETL jobs or when a project involves multiple interdependent steps. A key advantage: you can restart from any point within the process rather than rerunning everything from scratch after a failure partway through.
Airflow is genuinely not a library — you have to actually deploy and operate it, which makes it a poor fit for small, infrequent ETL jobs where that operational overhead outweighs the benefit.
Apache Airflow 3.1.8 is the current stable version as of 2026, with task retries and alerting features specifically designed for exactly the failure-recovery scenarios a plain Python script handles poorly on its own.

A Minimal Airflow DAG for the Same Pipeline

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime

with DAG("sales_etl", start_date=datetime(2026, 1, 1), schedule="@daily") as dag:
    extract_task = PythonOperator(task_id="extract", python_callable=extract_from_api, op_args=["https://api.example.com/sales"])
    transform_task = PythonOperator(task_id="transform", python_callable=transform_sales_data)
    load_task = PythonOperator(task_id="load", python_callable=load_to_database)

    extract_task >> transform_task >> load_task

That >> syntax defines task dependencies explicitly — extract must finish before transform starts, transform before load. This is genuinely the same DAG (Directed Acyclic Graph) concept referenced across the MLOps orchestration coverage earlier in this series, just applied to raw data movement instead of ML training pipelines specifically.

Choosing the Right Library at the Right Scale

Not every transformation step should default to pandas — matching library to data volume matters genuinely more here than people initially assume.

Pandas — the default choice for data that comfortably fits in memory; genuinely the right starting point for learning and for most small-to-medium pipelines.
Dask — extends pandas-like syntax to datasets too large for memory, parallelizing computation across cores or even a cluster while keeping a genuinely familiar API.
PySpark — the right choice once you're genuinely at big-data scale — one referenced production example processes roughly 25 million rows using PySpark's parallel processing specifically because pandas alone would choke at that volume.

Start with pandas regardless of your eventual scale ambitions. Migrating transformation logic to Dask or PySpark later is a genuinely incremental step once you've actually hit pandas' memory ceiling — premature optimization toward Spark for a dataset that fits comfortably in RAM just adds unnecessary complexity.

Production Reliability Practices Worth Building In Early

A few concrete practices separate a working script from something you'd trust running unattended.

Structured logging at each stage — not just print statements, but genuine logging that records what was extracted, how many rows survived transformation, and confirmation of successful load, so failures are diagnosable after the fact rather than requiring you to reproduce them live.
Task retries — Airflow's built-in retry mechanism handles transient failures (a flaky API, a momentary database connection drop) without requiring manual intervention every time something hiccups.
Idempotent transformations, covered above — genuinely essential once retries are in play, since a retried task needs to be safe to rerun without corrupting already-loaded data.
Docker containerization — packaging your pipeline's dependencies into a container ensures it runs identically in development, staging, and production, avoiding the classic "works on my machine" failure mode.

Want to Go Deeper?

If the ETL pipeline or pandas transformation concepts clicked and you want to dig into the theory behind data pipeline architecture and production-grade data systems, Educative's Data Engineering path covers data pipelines in detail alongside the broader data engineering landscape — worth exploring if you're building production data pipelines beyond this tutorial.

Recommended Books

Fundamentals of Data Engineering by Joe Reis & Matt Housley — the definitive reference for exactly what this article covers: building reliable data pipelines from extraction through loading. Covers ETL vs ELT, orchestration, and production reliability practices with genuine depth.
Designing Data-Intensive Applications by Martin Kleppmann — the canonical reference for understanding distributed systems at the depth a production data engineer needs. Covers storage, replication, partitioning, and the tradeoffs behind every architectural choice including pipeline design.
Python for Data Analysis by Wes McKinney — written by the creator of pandas itself, this is the authoritative guide to the transformation library this entire pipeline is built on. Covers data cleaning, transformation, and aggregation patterns directly applicable to ETL work.
Data Pipelines Pocket Reference by James Densmore — a concise, practical reference specifically focused on building and operating data pipelines. Covers the exact extraction, transformation, and loading patterns this article demonstrates, with production-grade advice.

Common Mistakes People Make

Skipping error handling on the extraction step. Without checking for failed requests or malformed data, your transform step ends up processing garbage silently rather than failing loudly and early.
Writing one giant transformation function instead of small, named steps. This genuinely hurts both debuggability and testability — isolate distinct operations (cleaning, type conversion, aggregation) into separate functions.
Choosing if_exists="replace" without meaning to. This silently destroys existing data in the target table — always confirm this parameter matches your actual intent before running against a real database.
Reaching for Airflow before you actually need orchestration. For a small, infrequent, single-step job, plain Python run via cron is genuinely simpler and entirely sufficient — Airflow's deployment overhead isn't worth it until you have multiple interdependent steps or need failure recovery.
Ignoring idempotency until a pipeline failure forces the issue. Design transformations to be safely rerunnable from the start — retrofitting this after a production duplicate-data incident is a genuinely painful lesson to learn the hard way.

Where This Fits With the Rest of This Series

This article connects directly to the MLOps articles earlier in this series — the pipeline orchestration tools covered there (Airflow, Kubeflow Pipelines) are exactly the same tools you'd graduate to once this plain Python pipeline outgrows manual execution.

The data engineering courses article recommended courses that teach these exact skills; this article is the "actually do it" version building the same foundational project those courses spend weeks covering.

Wrapping This Up

A data pipeline, stripped of buzzwords, is genuinely just extract-transform-load in whichever order fits your infrastructure — plain Python with pandas handles this comfortably at small-to-medium scale, and Airflow becomes worth its deployment overhead specifically once you have multi-step dependencies or need genuine failure recovery. The ETL-versus-ELT distinction matters less than understanding both patterns exist and picking based on where your compute is actually cheap.

Remember that idempotent transformations and proper error handling at the extraction boundary are the two cheapest reliability investments you can make early, and that reaching for Airflow before you actually need multi-step orchestration just adds deployment overhead without matching benefit. FYI, this is genuinely the exact project every course in the data engineering courses article from earlier in this series builds toward as its foundational exercise — you've now built the thing those courses spend weeks teaching, in miniature :)

Now go point this pipeline at a real API you actually care about — weather data, a stock price feed, your own project's usage logs — and watch it run on a schedule instead of manually. That's the moment a data pipeline stops being a tutorial exercise and starts being infrastructure you'd genuinely rely on.