Implement a Python data pipeline that extracts, transforms, and loads (ETL) data into a…
- User
Implement a Python data pipeline that extracts, transforms, and loads (ETL) data into a database.
- Assistant
Planning an ETL pipeline
The user wants to implement a Python ETL (Extract, Transform, Load) data pipeline. Since the request is quite open-ended, I think they might appreciate examples using libraries like Pandas and SQLAlchemy. I should probably clarify data sources and database specifics, but I can propose a common approach: extracting from CSV or JSON, transforming data with Pandas, and loading it into an SQLite database. It’s essential to include logging, config, and core functions like extract(), transform(), and load() while keeping it concise yet informative.Structuring the ETL pipeline
I'm considering whether to structure the ETL pipeline as a module with classes or keep it as a functional script, which might be sufficient. I need to account for environment variables for sensitive information like credentials and include logging, exception handling, and possibly tests. Since the user didn't request in-depth explanations, I’ll keep it concise yet informative by providing a basic conceptual overview followed by the code.
I'll outline files like config.py and etl_pipeline.py while ensuring the template is adaptable for their needs.Creating a streamlined ETL script
I'm opting for a single file approach, which should get the job done alongside some explanations. I'll structure my code using Python with essential imports like logging, os, Pandas, and SQLAlchemy.
The Config class will set up environment variables for the source file, database URL, target table, and chunk size. My functions will cover extracting, transforming, and loading data, with transformation tasks like dropping duplicates, parsing dates, and standardizing column names. I’ll include logging for better tracking and consider how to create tables accordingly.Highlighting ETL concepts
I should consider mentioning "idempotency" since it's relevant, even though the user didn't explicitly ask for it. Including an example SQL schema could be beneficial, but since brevity is requested, I’ll keep it concise.
I want to create a moderately-sized script that covers extraction, possibly from an API using requests, but CSV is simpler. I need to provide a brief conceptual explanation of ETL, along with the code. I can also list required packages like Pandas and SQLAlchemy without getting too technical. Let's ensure everything in the code is consistent and account for edge cases.Implementing the ETL transformation
I’m thinking about how to handle data insertion, and while upserts with a primary key could be relevant, showing simple insertions might be more straightforward. I want to include sample transformations as well, such as converting the amount to a float and renaming columns for clarity.
For the sample CSV, I’ll define columns like order_id, order_date, customer_email, and amount, and include transformations like parsing the order_date to datetime, converting the amount, dropping rows with missing values, standardizing emails to lowercase, and removing duplicates. Adding docstrings will also clarify the purpose of each function. Let's finalize the answer! Below is a self‑contained example of a Python ETL pipeline that:
- Extracts data from a CSV file
- Transforms it with
pandas - Loads it into a relational database using
SQLAlchemy(SQLite by default, easily switchable to PostgreSQL/MySQL/etc.)
1. Requirements
Install dependencies:
pip install pandas sqlalchemy # For PostgreSQL, you'd also need: pip install psycopg2-binary
2. Example folder layout
project/ ├─ etl_pipeline.py └─ data/ └─ input_orders.csvExample
data/input_orders.csv:order_id,order_date,customer_email,amount 1,2024-01-05,john@example.com,100.50 2,2024-01-06,JANE@EXAMPLE.COM,200 2,2024-01-06,JANE@EXAMPLE.COM,200 # duplicate row example 3,2024-01-07,invalid_email,,
3. Full ETL script (
etl_pipeline.py)#!/usr/bin/env python3 import logging import os from dataclasses import dataclass from typing import Iterator import pandas as pd from sqlalchemy import create_engine, text from sqlalchemy.engine import Engine # ---------- Configuration ---------- @dataclass class Config: # Source data source_file: str = os.getenv("SOURCE_FILE", "data/input_orders.csv") # Destination DB (change this to point to PostgreSQL / MySQL, etc.) # Examples: # PostgreSQL: postgresql+psycopg2://user:password@host:5432/dbname # MySQL: mysql+pymysql://user:password@host:3306/dbname db_url: str = os.getenv("DB_URL", "sqlite:///data/warehouse.db") # Target table in the database target_table: str = os.getenv("TARGET_TABLE", "orders") # CSV reading options csv_chunksize: int = int(os.getenv("CSV_CHUNKSIZE", "5000")) # Logging log_level: str = os.getenv("LOG_LEVEL", "INFO") # ---------- Logging setup ---------- def setup_logging(level: str) -> None: logging.basicConfig( level=getattr(logging, level.upper(), logging.INFO), format="%(asctime)s [%(levelname)s] %(name)s - %(message)s", ) # ---------- DB setup ---------- def get_engine(config: Config) -> Engine: engine = create_engine(config.db_url, future=True) return engine def ensure_target_table(engine: Engine, config: Config) -> None: """ Optionally create target table with an explicit schema. If you rely on pandas.to_sql to create the table automatically, you can skip this and let to_sql create it on first load. """ create_table_sql = f""" CREATE TABLE IF NOT EXISTS {config.target_table} ( order_id INTEGER PRIMARY KEY, order_date TIMESTAMP, customer_email TEXT, amount NUMERIC ) """ with engine.begin() as conn: conn.execute(text(create_table_sql)) # ---------- Extract ---------- def extract(config: Config) -> Iterator[pd.DataFrame]: """ Read CSV in chunks to handle large files efficiently. Yields pandas DataFrames. """ logging.info(f"Starting extraction from {config.source_file}") for chunk in pd.read_csv(config.source_file, chunksize=config.csv_chunksize): logging.info("Read chunk with %d rows", len(chunk)) yield chunk # ---------- Transform ---------- def transform(df: pd.DataFrame) -> pd.DataFrame: """ Clean and standardize the data. Example transformations: - Normalize column names - Parse dates - Clean e-mails - Convert numeric fields - Drop duplicates and bad rows """ logging.info("Starting transformation on chunk of size %d", len(df)) # Standardize column names (lowercase, strip spaces) df.columns = [c.strip().lower() for c in df.columns] # Expected columns: order_id, order_date, customer_email, amount required_cols = {"order_id", "order_date", "customer_email", "amount"} missing = required_cols - set(df.columns) if missing: raise ValueError(f"Missing required columns in source data: {missing}") # Convert order_date to datetime, coerce errors to NaT (missing) df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce") # Normalize email to lowercase, strip spaces df["customer_email"] = ( df["customer_email"] .astype(str) .str.strip() .str.lower() ) # Convert amount to numeric, coerce invalid to NaN df["amount"] = pd.to_numeric(df["amount"], errors="coerce") # Remove rows with critical missing values before = len(df) df = df.dropna(subset=["order_id", "order_date", "amount"]) after = len(df) logging.info("Dropped %d rows with missing critical fields", before - after) # Remove duplicates based on primary key (order_id) before = len(df) df = df.drop_duplicates(subset=["order_id"]) after = len(df) logging.info("Dropped %d duplicate order_id rows", before - after) # Ensure correct dtypes where possible df["order_id"] = df["order_id"].astype(int) logging.info("Finished transformation; resulting size: %d", len(df)) return df # ---------- Load ---------- def load(df: pd.DataFrame, engine: Engine, config: Config) -> None: """ Load data into the target database table. Uses INSERT (append) semantics. """ if df.empty: logging.info("Received empty DataFrame, skipping load.") return logging.info("Loading %d rows into table '%s'", len(df), config.target_table) # If you want upserts/merges, you'd need database-specific SQL here. # For simplicity, this uses append semantics. df.to_sql( config.target_table, engine, if_exists="append", index=False, chunksize=1000, method="multi", # batches multiple rows per INSERT where supported ) logging.info("Load completed") # ---------- Pipeline Orchestration ---------- def run_pipeline() -> None: config = Config() setup_logging(config.log_level) logging.info("Starting ETL pipeline") engine = get_engine(config) ensure_target_table(engine, config) total_rows_extracted = 0 total_rows_loaded = 0 for raw_chunk in extract(config): total_rows_extracted += len(raw_chunk) try: transformed = transform(raw_chunk) except Exception as e: logging.exception("Error during transformation: %s", e) continue # skip this chunk before_load = total_rows_loaded load(transformed, engine, config) total_rows_loaded += len(transformed) logging.info( "ETL pipeline finished. Extracted: %d rows; Loaded: %d rows", total_rows_extracted, total_rows_loaded, ) if __name__ == "__main__": run_pipeline()
4. Running the pipeline
From the
projectdirectory:python etl_pipeline.pyYou can override configuration via environment variables:
export SOURCE_FILE="data/other_file.csv" export DB_URL="postgresql+psycopg2://user:pass@localhost:5432/mydb" export TARGET_TABLE="stg_orders" python etl_pipeline.py
5. Adapting to your use case
- Different source: Replace
extract()to read from an API (requests), another database (pd.read_sql), S3 (s3fs), etc. - Different transformations: Modify
transform()for your specific cleaning, enrichment, joins, or aggregations. - Different database: Change
DB_URLto your driver/connection string;SQLAlchemykeeps most code unchanged.
If you describe your real data source/target database, I can tailor this example directly to your scenario.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.