Deduplicate a key before joining, and prove the join is clean

applied · 50 min · Objective 2.1

Task

Build a customer lookup from the raw file that has exactly one row per real customer, join the orders to it, and prove with counts that no order was lost or duplicated.

Steps

  1. Read data/customers_raw.csv. Group rows by lower-cased email, ignoring blank emails, and for each group of two keep the row with the lower customer_id.
  2. Write the surviving rows to lab/dedup/customers.csv with the same header as the raw file.
  3. Join data/orders.csv to lab/dedup/customers.csv on customer_id and write the result to lab/dedup/joined.csv.
  4. Record in lab/dedup/checks.txt the lines customers: N, joined rows: N and unmatched orders: N.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

grep -E '^(customers|joined rows|unmatched orders): ' lab/dedup/checks.txt
python3 -c "import csv; ids=[r['customer_id'] for r in csv.DictReader(open('lab/dedup/customers.csv', encoding='utf-8'))]; print(len(ids) - len(set(ids)), 'duplicate id(s)')"

300 customers, 2000 joined rows, 0 unmatched orders and 0 duplicate ids: one row per customer in, one row per order out. The fourteen second copies all had new identifiers that no order uses, which is why dropping them loses nothing.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.