Real Uncodemy Success Story: Data Engineering Career Change

DAILY REPORT - MANUAL VERSION (what the learner did every day)

1. Log in to system A, export orders.csv
2. Log in to system B, export customers.csv
3. Open both in Excel
4. Delete blank and duplicate rows by hand
5. VLOOKUP customer city into the orders sheet
6. Pivot table: revenue per day
7. Copy pivot into an email, send to the team

Problems: slow, easy to make mistakes,
no record of what changed, stops when they are on leave.

# AUTOMATED VERSION (the goal of the career change)

$ python3 etl.py
loaded=2 rejected=1

# extract  -> read the exports
# transform -> drop blanks and duplicates, fix types
# load     -> upsert into a database table
# check    -> SQL quality checks
# report   -> summary table analysts can query

# Runs on a schedule, logs every step,
# and gives the same result every time.

import csv
import sqlite3

def extract(path):
    with open(path, newline="", encoding="utf-8") as f:
        return list(csv.DictReader(f))

def transform(rows):
    clean, rejected, seen = [], [], set()
    for r in rows:
        if not r["amount"]:
            rejected.append(r)
            continue
        if r["order_id"] in seen:
            continue                      # drop duplicate
        seen.add(r["order_id"])
        clean.append((int(r["order_id"]), r["customer"].strip(),
                      int(r["amount"]), r["order_date"]))
    return clean, rejected

def load(rows, db="warehouse.db"):
    conn = sqlite3.connect(db)
    with conn:
        conn.execute("""CREATE TABLE IF NOT EXISTS orders (
            order_id INTEGER PRIMARY KEY, customer TEXT,
            amount INTEGER, order_date TEXT)""")
        conn.executemany("""
            INSERT INTO orders (order_id, customer, amount, order_date)
            VALUES (?, ?, ?, ?)
            ON CONFLICT(order_id) DO UPDATE SET
                customer = excluded.customer,
                amount = excluded.amount,
                order_date = excluded.order_date""", rows)
    conn.close()

if __name__ == "__main__":
    clean, rejected = transform(extract("orders.csv"))
    load(clean)
    print(f"loaded={len(clean)} rejected={len(rejected)}")

# orders.csv - deliberately messy test data
order_id,customer,amount,order_date
1001,Asha,1200,2026-03-01
1002,Ravi,,2026-03-01
1003,Meera,450,2026-03-02
1001,Asha,1200,2026-03-01

$ python3 etl.py
loaded=2 rejected=1
$ python3 etl.py      # run again
loaded=2 rejected=1

# The table still has 2 rows: the upsert makes
# the load idempotent, so re-runs are safe.
# (ON CONFLICT upsert needs SQLite 3.24 or newer.)

-- checks.sql: run after every load

-- 1. Row count per load date
SELECT order_date, COUNT(*) AS row_count
FROM orders
GROUP BY order_date
ORDER BY order_date;

-- 2. Duplicate keys (should return nothing)
SELECT order_id, COUNT(*) AS copies
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- 3. Missing or invalid values
SELECT COUNT(*) AS bad_rows
FROM orders
WHERE amount IS NULL OR amount <= 0 OR customer IS NULL;

-- 4. Summary table for analysts
CREATE TABLE IF NOT EXISTS daily_sales AS
SELECT order_date, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY order_date;

Results on the sample data:

1. order_date  row_count
   2026-03-01  1
   2026-03-02  1

2. (no rows)        -> no duplicate keys

3. bad_rows = 0     -> rejected row never loaded

4. daily_sales
   2026-03-01  1  1200
   2026-03-02  1  450

In the README, explain what each check protects
against and what the pipeline does when one fails.

WEEKLY LEARNING LOG

Week: ___   Phase: 1 / 2 / 3   Hours this week: ___

SQL      : what I practised ______________________
Python   : what I built ___________________________
Pipeline : what I added or fixed __________________
Committed to GitHub? Y / N   Link: _______________

Stuck on : _______________________________________
Asked for help from: trainer / peers / docs

Next week's single goal: _________________________

# Daily Orders Pipeline

## What it does
Loads daily order exports into a database, removes
duplicates and blank amounts, and builds a daily
sales summary.

## Architecture
CSV export -> extract -> transform -> load (upsert)
-> quality checks -> daily_sales table

## Run it
python3 -m venv .venv && source .venv/bin/activate
python3 etl.py
sqlite3 warehouse.db < checks.sql

## Design choices
- Upsert on order_id so re-runs are safe
- Rejected rows counted and logged, not silently lost

## Next steps
Schedule with cron or Airflow; move storage to cloud

RESUME BULLETS - reporting experience reframed
(describe what you did; do not invent numbers)

Experience
- Built a Python ETL script that replaced a manual daily
  Excel report, with duplicate removal and upserts
- Wrote SQL quality checks for row counts, duplicate keys
  and missing values after each load
- Worked with sales and finance teams to define report
  logic and resolve data mismatches

Projects
- Daily Orders Pipeline: Python, SQLite, SQL (GitHub link)
- API-to-database loader with scheduling (GitHub link)