Talend Data Preparation Lab — Cleaning “customers.xlsx”

Duration: 60–90 minutes | Difficulty: Beginner to Intermediate

Overview & Learning Outcomes

In this lab you will import an Excel file into Talend Data Preparation (TDP), profile its quality, add column metadata, build a reproducible cleaning recipe, and export a clean version of the dataset. An optional section shows how to implement the same logic in Talend Studio (Data Integration) as a batch job.

Learning outcomes:

1. Import and profile a dataset in Talend Data Preparation (TDP).
2. Add and curate metadata (column names, types, semantic types).
3. Build a step-by-step cleaning recipe that fixes real data issues.
4. Export a clean dataset and summarize before/after quality metrics.
5. Optionally, reproduce the logic in Talend Studio as a batch job.

Files & Setup

You will use the provided file: customers.xlsx (sheet “sheet1”, ~6,040 rows, 16 columns).

Open Talend Data Preparation (Talend Cloud or on‑premise): sign in → open the Data Preparation app.

Data costumers.xlsx : https://docs.google.com/spreadsheets/d/1woGsb2ArNN0QSJUgklQRylfzz47E9HQ-/edit?usp=sharing&ouid=106532920956410672496&rtpof=true&sd=true

Part A — Import & Profile the Dataset (TDP)

• From the TDP home page, click “Datasets” in the left menu.
• Click “Add dataset” (or “Add” → “From local file”).
• Select the file customers.xlsx and upload it.
• If prompted for sheet selection, choose “sheet1”. Keep “First row is header” enabled.
• Name the dataset customers_raw and click “Create” (or “Add”).
• Open the dataset by clicking its name. The grid view appears with column quality bars.
• Open the right‑hand “Statistics” / “Details” panel (if hidden, use the panel toggle).
• Scroll columns and observe the quality bars (valid / empty / invalid) and top value distributions.
• Record short notes about issues you see (examples below).

Typical issues you will see in this file:

• Email has missing values (~5.9%) and duplicate emails (~25).
• Phone contains many entries that are not 10 digits after removing punctuation (~700 invalid).
• Zip requires leading‑zero padding for many rows (~950).
• State should be 2‑letter uppercase codes; verify and standardize.
• subscription_date is parseable but formats may be inconsistent.
• marital_status has ~24% missing values.

Deliverable A: 5–8 lines describing the main data quality issues you observed, with 1–2 screenshots.

Part B — Add Metadata & Semantic Types

Rename columns, set data types, and assign semantic types.

1. 1) Rename columns (Right‑click column header → “Rename”):

Original name	New name
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

1. 2) Set column data types:
2. – id → Integer
3. – first_name, last_name, full_name → Text
4. – gender, age, occupation, marital_status, salary_band, address, city, state, zip, email, phone → Text
5. – subscription_date → Date (set format: yyyy-MM-dd)
6. – rentals_count → Integer (non‑negative)
7. 3) Assign semantic types (Column menu → “Semantic type”):
8. – email → Email; state → US state code (or Code); zip → Zip/Postal Code; subscription_date → Date.

Deliverable B: Screenshot of the schema panel and 1–2 lines justifying choices (e.g., zip stored as text to preserve leading zeros).

Part C — Build the Cleaning Recipe (TDP)

Apply the following steps in order. Each action is recorded in the “Recipe” panel. You can reorder steps by dragging them if needed.

Step C1 — Trim & Normalize Text

1. On columns first_name, last_name, city, occupation: Column ▸ Clean ▸ Trim.
2. Then Column ▸ Case ▸ Proper case (Title case) on first_name, last_name, city, occupation.
3. On email: Column ▸ Clean ▸ Trim, then Column ▸ Case ▸ Lowercase.

Step C2 — Standardize US State Codes

• On state: Column ▸ Case ▸ Uppercase.
• Column ▸ Validate ▸ “Value is in list” (paste the 2‑letter codes from Appendix A). Mark non‑matches as Invalid.
• Optionally: Column ▸ Find & replace to fix frequent variants (e.g., ‘Calif.’→‘CA’, ‘N.Y.’→‘NY’). Add each replacement as a separate action so it is documented.

Step C3 — Fix ZIP Codes (Preserve Leading Zeros)

• On zip: Column ▸ Change type ▸ Text (String).
• Column ▸ Pad left ▸ Length = 5, Padding char = ‘0’.
• Optional: Validate ▸ “Matches pattern” with regex ^\d{5}$ to mark non‑5‑digit ZIPs invalid.

Step C4 — Normalize Phone Numbers

Goal: keep only digits, validate length = 10, and format as (XXX) XXX-XXXX.

• Create helper column phone_digits: Column ▸ Duplicate. Rename the duplicate to phone_digits.
• On phone_digits: Column ▸ Replace with RegEx. Pattern: \D  Replacement: (empty). This removes all non‑digits.
• Validate: Column ▸ Compute length (or Validate ▸ Matches pattern ^\d{10}$). Mark non‑10‑digit as Invalid.
• Create formatted column phone_formatted: Add column ▸ Formula (or Derived column). Build:  ‘(‘ + SUBSTR(phone_digits,1,3) + ‘) ‘ + SUBSTR(phone_digits,4,3) + ‘-‘ + SUBSTR(phone_digits,7,4) 
• Replace original phone with phone_formatted (Column ▸ Replace values from another column), or keep both and later delete helpers.
• Delete helper columns phone_digits and phone_formatted after verifying results.

Step C5 — Validate & De‑duplicate Emails

• On email: Column ▸ Validate ▸ Email format. Review invalid values.
• Optional standardizations: Column ▸ Find & replace common typos (e.g., ‘gmal.com’→‘gmail.com’).
• Remove duplicates: Toolbar ▸ Remove duplicates ▸ Select column email ▸ Keep first occurrence.

Step C6 — Parse Dates & Derive Year/Month

• On subscription_date: Column ▸ Change type ▸ Date. Specify the input format if asked (e.g., MM/dd/yyyy).
• Standardize display format to yyyy‑MM‑dd (Column ▸ Format date).
• Add derived columns: Column ▸ Extract ▸ Year (signup_year) and Extract ▸ Month (signup_month).

Step C7 — Normalize Categorical Values

• On gender: Column ▸ Find & replace to map variations to a canonical set (e.g., ‘F’→‘Female’, ‘M’→‘Male’).
• On marital_status: Decide a policy. If you need a value for analytics, Column ▸ Fill empty cells ▸ with ‘Unknown’. Otherwise leave empty.

Step C8 — Validate Numeric Columns

• On rentals_count: Column ▸ Change type ▸ Integer.
• Validate non‑negative: Filter or Validate ▸ “Greater or equal to 0”. If invalid, choose to set to 0 (Column ▸ Replace invalids) or exclude rows.

Step C9 — Optional: Addresses and Full Name

• Create full_name: Add column ▸ Concatenate → first_name + ‘ ‘ + last_name.
• Standardize address abbreviations (Column ▸ Find & replace list): St.→Street, Rd.→Road, Ave.→Avenue, Blvd.→Boulevard.

Step C10 — Final Validation

• Re‑scan quality bars for email, phone, zip, state; no reds ideally.
• Remove columns you no longer need (Column ▸ Delete).
• Sort by id (Toolbar ▸ Sort) for deterministic output.
• Save the preparation. Give it a clear name, e.g., customers_clean_recipe.

Part D — Export the Clean Dataset

• Click Export.
• Choose CSV (UTF‑8) or Excel (XLSX).
• Set file name: customers_clean.csv (or .xlsx).
• Confirm delimiter (comma) and date format (yyyy‑MM‑dd).
• Export and download the file.

Target schema of the clean file:

Column	Type	Notes
id	Integer	Primary key
first_name	Text	Trimmed, Proper Case
last_name	Text	Trimmed, Proper Case
full_name	Text	Derived
gender	Text	Canonical values
age	Text	Keep bands as text
occupation	Text	Proper Case
marital_status	Text	‘Unknown’ or empty
salary_band	Text	Category
address	Text	Optional normalized abbreviations
city	Text	Proper Case
state	Text	2‑letter uppercase
zip	Text	5 chars, zero‑padded
phone	Text	(XXX) XXX‑XXXX or empty
email	Text	Lowercase, valid, unique
subscription_date	Date	yyyy‑MM‑dd
rentals_count	Integer	>= 0
signup_year	Integer	Derived
signup_month	Integer	Derived

Deliverable D: the exported file plus a 1‑page summary with before/after quality metrics (valid emails/phones/ZIPs, duplicate emails removed).

Appendix A — US State Codes (2‑letter)

AL, AK, AZ, AR, CA, CO, CT, DE, FL, GA, HI, ID, IL, IN, IA, KS, KY, LA, ME, MD, MA, MI, MN, MS, MO, MT, NE, NV, NH, NJ, NM, NY, NC, ND, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VT, VA, WA, WV, WI, WY, DC

Appendix B — Before/After Quality Metrics to Report

• Email: % valid, % missing, duplicate count.
• Phone: % with 10 digits after normalization.
• Zip: % 5‑digit after padding.
• State: % valid 2‑letter codes.
• Dates: % successfully parsed (subscription_date).
• Rows removed (if any) and reason (invalid state/phone, duplicate email, etc.).