The Brief
You're a data engineer at QuickCart. Marketing just exported 500 orders from the web logs — but the CSV is messy. Some rows have NULLs, status values are inconsistent (mixed case, extra spaces), and dates are in multiple formats. You can't dump this directly into PostgreSQL. You need a pipeline.
Your job: Build a mini-ETL that reads the CSV, cleans it step by step, loads the clean data into SQLite for staging, validates everything worked, and exports SQL INSERT statements for PostgreSQL migration.
ETL = Extract, Transform, Load. Extract: read raw CSV. Transform: clean, normalize, deduplicate. Load: write to a database. Validation ensures the output is trustworthy before downstream systems use it.
Step 1: Read CSV with Pandas
First, read the messy CSV file. Pandas is built for this — it handles encoding, missing values, and type detection automatically.
Columns: ['customer_id', 'order_date', 'status', 'delivery_city', 'total_amount', 'payment_method']
What the code does:
pd.read_csv()— Loads the CSV into a DataFrame (a table-like structure).len(df)— Count of rows loaded.df.head()— First 5 rows, to see what you're working with.df.dtypes— Data type of each column (object, int64, float64, datetime64).
This is Q1 from the tested results: 100 rows loaded, with columns containing nulls and mixed-format dates.
Step 2: Clean Data — Remove Nulls, Normalize, Fix Types
Now the transformation begins. You'll drop bad rows, standardize status values, and convert columns to the right data types.
After dropping NULLs: 87 rows (13.0% removed)
Final clean dataset: 87 rows
What each cleaning step does:
df.dropna()— Removes any row with a NULL value in any column.str.lower().str.strip()— Converts status to lowercase and removes extra spaces.isin(valid_statuses)— Filters to only allowed status values.pd.to_numeric(..., errors='coerce')— Converts to number; bad values become NaN, then dropna removes them.pd.to_datetime()— Parses mixed date formats into proper datetime objects.
This is Q2 from the tested results: 87 rows remain after cleaning (13 rows dropped due to missing or invalid data).
Step 3: Load into SQLite
Now that the data is clean, load it into a SQLite database for staging and validation. Pandas makes this a one-liner with to_sql().
Tables in database: ['orders']
What the code does:
sqlite3.connect()— Opens a SQLite database (creates it if it doesn't exist).df.to_sql()— Converts the DataFrame to a SQL table.if_exists='replace'drops the old table first.conn.commit()— Writes the transaction to disk.
This is Q3 from the tested results. SQLite automatically infers column types from the DataFrame (customer_id → INTEGER, total_amount → REAL, order_date → TEXT, etc.).
Step 4: Validate — Check Quality Before Downstream Use
Never trust ETL output without validation. Run SQL checks to catch problems early.
Sample Output (from tested results):
Q5: Status Distribution
shipped: 18
delivered: 17
cancelled: 16
returned: 13
pending: 12
Q6: NULL counts (all 0 — good!)
null_customer_id: 0, null_order_date: 0, null_total_amount: 0
Q14: Summary
total_orders: 87, unique_customers: 20
min_order: 255.63, max_order: 7995.02
avg_order: 4559.12, total_revenue: 396643.66
What these checks tell you:
- Row count: Confirms 87 rows loaded (matches what cleaning returned).
- Status distribution: Each status has orders. No unexpected categories.
- NULL check: 0 NULLs everywhere — cleaning worked.
- Summary stats: Reasonable ranges. No $0 or negative orders. Revenue looks correct.
Step 5: Export for PostgreSQL Migration
Your clean SQLite data is ready to move to PostgreSQL. Generate INSERT statements for production.
File size: ~12 KB
What the code does:
- Generates a PostgreSQL-compatible
CREATE TABLEstatement with constraints. - Loops through each cleaned row and builds an
INSERTstatement. - Escapes single quotes in city names to avoid SQL injection.
- Writes all INSERT statements to a file ready for PostgreSQL import.
This is Q15 from the tested results: 87 INSERT statements exported and ready to load into PostgreSQL.
The Full Pipeline: main() Function
Here's the complete, production-ready ETL pipeline in one function. This is how data engineers organize this work in the real world.
How to run this in production:
- Save as
etl_pipeline.py. - Run:
python etl_pipeline.py - Then import the SQL into PostgreSQL:
psql quickcart < orders_for_postgres.sql
Challenge
Add error handling and logging to the pipeline.
- Wrap each step (Extract, Transform, Load, Validate, Export) in a try/except block.
- Log the row count at each step (use print or the logging module).
- If a step fails, print an error message and stop gracefully (don't crash silently).
- Bonus: Write logs to a file called
etl.logwith timestamps.
Use Python's built-in logging module:
Test Your Knowledge
You just built a real ETL pipeline.
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →