ISA-010 — Production Data Sanitization
🟡 Intermediate
⏱ 30 Minutes
⭐ 50 Points
🔓 Free
💻 SQL | PySpark
🏢 Retail Analytics
🏆 Level 1 Capstone
Business Context
A retail company receives daily customer data from multiple source systems.
The ingestion team has discovered several data quality issues in the incoming dataset:
- Duplicate customer records
- Missing country values
- Invalid email addresses
- Poorly formatted addresses
Before loading data into the Customer Master table, all records must be standardized and validated.
The analytics team has asked Data Engineering to produce a clean dataset suitable for reporting and downstream analytics.
Business Impact
Poor data quality can lead to:
- Duplicate customers in reports
- Failed marketing campaigns
- Incorrect regional analysis
- Inaccurate customer segmentation
- Reduced trust in analytics
The customer master dataset must be cleaned before it reaches business users.
Dataset
| customer_id | country | address | |
|---|---|---|---|
| 101 | john@example.com | India | bangalore |
| 101 | john@example.com | India | bangalore |
| 102 | alice.example.com | MUMBAI | |
| 103 | bob@example.com | USA | chennai |
| 104 | sarah.gmail.com | hyderabad | |
| 105 | david@example.com | Canada | DELHI |
Task
Create a cleaned dataset using the following business rules.
Rule 1
Remove duplicate customer records.
Keep only one record per customer.
Rule 2
Replace NULL country values with:
Unknown
Rule 3
Standardize addresses.
Requirements:
- Trim spaces
- Convert to Proper Case
Rule 4
Only keep customers with valid email addresses.
For this challenge:
Valid Email = Contains '@'
Records with invalid emails must be excluded.
Expected Output
| customer_id | country | address | |
|---|---|---|---|
| 101 | john@example.com | India | Bangalore |
| 103 | bob@example.com | USA | Chennai |
| 105 | david@example.com | Canada | Delhi |
Constraints
- Duplicate records may exist.
- Country values may contain NULL.
- Address values may contain leading or trailing spaces.
- Address values may contain inconsistent capitalization.
- Email addresses must contain '@'.
- Return only clean customer records.
Supported Languages
✅ SQL
✅ PySpark
Hint
Apply the cleaning rules one step at a time before producing the final dataset.
Solution
🔒 Premium Solution
Premium members receive:
- SQL Solution
- PySpark Solution
- Step-by-Step Explanation
- Optimization Discussion
- Alternative Approaches