Experience cleaning tabular data using Python

Exercise — Customers Data Cleaning & KPI‑Driven Data Warehouse (Python Implementation) — Tasks Only

Prepared for: Genoveva Vargas-Solar — Date: 05 Nov 2025

Context & Goal

You will implement, in Python, a complete workflow to: (1) clean the raw Excel dataset customers.xlsx, (2) compute KPIs from the cleaned data, and (3) design and populate a small star‑schema data warehouse to answer those KPIs using SQL. This document lists explicit tasks only (no starter code). Mirror the cleaning actions defined in the Talend exercise.

Materials & Constraints

Part 0 — Reproducible Setup

  • Create a project folder with subfolders: data/, notebooks/ or src/, sql/, reports/.
  • Record your environment (Python version; main library versions) in a README.
  • Parameterise input/output locations and database connection via environment variables or a config file.
  • Log each processing step (start/end time; counts of rows affected; warnings).

Part 1 — Data Understanding & Profiling

  • Open customers.xlsx (first sheet) and list the columns found (exact names).
  • Map original column names to normalised names you will use (e.g., First_Name → first_name).
  • Produce a profiling table with: dtype, #non‑null, #nulls, %nulls, #unique for each column.
  • Identify and list suspected quality issues with examples (e.g., invalid emails, malformed phones, ZIP padding, date parsing, duplicates).

Deliverable: a one‑page ‘Data Quality Findings’ note summarizing issues and initial hypotheses.

Part 2 — Cleaning Tasks (Mirror the Talend Recipe)

Apply all tasks in order. Document any assumption or deviation.

  • Rename columns using the following mapping: Id→id, First_Name→first_name, Last_Name→last_name, Gender→gender, Age→age, Occupation→occupation, MaritalStatus_Out→marital_status, Salary_Out→salary_band, Address→address, City→city, State→state, Zip→zip, Phone→phone, Email→email, SubDate→subscription_date, Number_of_rentals→rentals_count.
  • Trim whitespace and apply proper casing to text fields: first_name, last_name, city, occupation, and address.
  • Normalise email: lowercase, correct common domains (e.g., gmal.com→gmail.com, gnail.com→gmail.com, hotmial.com→hotmail.com, yaho.com→yahoo.com, outlok.com→outlook.com, icloud.cmo→icloud.com), and flag email_valid via a regex check.
  • Normalise phone: remove non‑digits, flag phone_valid if 10 digits, and store a canonical 10‑digit display format.
  • Normalise state: uppercase 2‑letter code; create state_valid flag if in the accepted list.
  • Normalise zip: store as 5‑character, left‑padded with zeros if needed (truncate if longer than 5).
  • Parse subscription_date; create signup_year and signup_month derived attributes.
  • Standardise gender values (e.g., Title Case).
  • Standardise marital_status; replace blanks with ‘Unknown’.
  • Coerce rentals_count to non‑negative integer; replace invalid/missing with 0; report how many values were changed.
  • Create full_name = first_name + ‘ ‘ + last_name.
  • De‑duplicate records by non‑null email, keeping the first occurrence; report how many duplicates you removed.

Export the cleaned dataset as customers_clean.csv, including a short changelog with the row counts before/after each step. Are the data clean enough for building a DW?

Part 3 — KPIs (Compute from Cleaned Data)

  • Total customers.
  • Email contactable rate (%) = share of records with email_valid = true.
  • Phone contactable rate (%) = share of records with phone_valid = true.
  • Duplicates removed (count) during de‑duplication step.
  • Average and median rentals (rentals_count).

High‑value share (%) = share of customers with rentals_count ≥ 20 (justify threshold if you adjust it).

  • Valid state rate (%) among records with a state value present.
  • Monthly new customers: counts grouped by subscription year‑month.
  • Top 10 states by number of customers.

Deliverable: a one‑page KPI summary (table/figure) with a 3–5 line interpretation.

Part 4 — DW Modelling & Construction (Use the Cleaned Dataset)

  • Analyse the model a minimal star schema to answer all KPIs. Use four tables: dim_date, dim_geo, dim_customer, fact_customer.
  • Execute the DDL to create the schema (PostgreSQL preferred). Include FK constraints and helpful indexes (e.g., on date_key and geo_key).
  • Populate dim_date using the min/max of subscription_date; populate dim_geo from distinct (city, state, zip).
  • Populate dim_customer from the cleaned dataset (one row per customer_key).
  • Populate fact_customer by joining cleaned data to dim_geo and dim_date to resolve keys. Ensure referential integrity (no orphan FKs). – What do you observe regarding the status of your “cleaned data set”?
  • Verify row counts: #fact rows equals #cleaned customers; check FK join rates; report any anomalies.
  • Export your DDL and discuss (row counts, anomalies).

Part 5 — KPI Queries (Run on the DW)

  • Write one SQL query per KPI to reproduce: monthly new customers, contactability rates, top states, high‑value share, average/median rentals (if supported by SQL dialect).
  • Execute the queries in your SQL client or from Python; save results as tables or figures in your report.
  • Cross‑check SQL results vs Python KPI values from Part 3; explain any discrepancy in 3–6 lines.

Part 6 — Packaging & Submission

  • Deliverables: (1) customers_clean.csv, (2) ‘Data Quality Findings’ note, (3) KPI summary, (4) SQL queries file (5) short README with run instructions.
  • The GitHub repository structure is transparent and reproducible; scripts/notebooks can be re-run end-to-end without manual edits.