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.

python
Loaded 100 rows from CSV
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.

python
Before cleaning: 100 rows
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().

python
Loaded 87 rows into SQLite table 'orders'
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.

sql

Sample Output (from tested results):

Q4: 87 total orders

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.

python
Exported 87 INSERT statements to orders_for_postgres.sql
File size: ~12 KB

What the code does:

  • Generates a PostgreSQL-compatible CREATE TABLE statement with constraints.
  • Loops through each cleaned row and builds an INSERT statement.
  • 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.

python

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.log with timestamps.
Show hint

Use Python's built-in logging module:

python

Test Your Knowledge

Question 1 of 5
Name 3 data quality issues that this ETL pipeline fixes.
Question 2 of 5
What pandas method loads a DataFrame into SQLite?
Question 3 of 5
How do you drop rows with NULL in a specific column?
Question 4 of 5
Why do you validate data in the ETL pipeline before using it downstream?
Question 5 of 5
What's the main difference between SQLite and PostgreSQL for ETL?
0
/5
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

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 →