Write SQL and Python, run instantly in your browser, and track your progress.
You are a Data Engineer at Shopify. The CRM team needs clean customer data for import. The data has multiple quality issues: missing phone numbers, inconsistent name formatting, and missing state information. Perform a multi-step cleanup process.
| Column Name | Type |
|---|---|
| customer_id | int64 |
| full_name | object |
| object | |
| phone | object |
You are a Data Engineer at Shopify. The CRM team needs clean customer data for import. The data has multiple quality issues: missing phone numbers, inconsistent name formatting, and missing state information. Perform a multi-step cleanup process.
| Column Name | Type |
|---|---|
| customer_id | int64 |
| full_name | object |
| object | |
| phone | object |
| city | object |
| city | object |
| state | object |
| state | object |
| country | object |
| country | object |
| created_at | object |
| created_at | object |
| customer_id | full_name | phone | city | state | country | created_at | |
|---|---|---|---|---|---|---|---|
| 1 | Sophia Lewis | sophia.lewis@gmail.com | San Diego | CA | US | 2024-10-10 19:19:27 |
| customer_id | full_name | phone | city | state | country | created_at | |
|---|---|---|---|---|---|---|---|
| 1 | Sophia Lewis | sophia.lewis@gmail.com | San Diego | CA | US | 2024-10-10 19:19:27 |
| customer_id | full_name | phone | city | state | country | created_at | name_clean | |
|---|---|---|---|---|---|---|---|---|
| 3 | Andrea Miller | andrea_miller@zoho.com | +1-803-484-1106 | Winnipeg | MB | CA | 2025-06-22 16:35:50 | Andrea Miller |
| 4 | Luis Green |
| customer_id | full_name | phone | city | state | country | created_at | name_clean | |
|---|---|---|---|---|---|---|---|---|
| 3 | Andrea Miller | andrea_miller@zoho.com | +1-803-484-1106 | Winnipeg | MB | CA | 2025-06-22 16:35:50 | Andrea Miller |
| 4 | Luis Green |
Showing first 5 of 34 rows.
Showing first 5 of 34 rows.
1. Data Selection:
2. Cleaning Operations:
3. Output:
1. Data Selection:
2. Cleaning Operations:
3. Output:
| 2 | Marilyn Kelly | marilynkelly@yahoo.com | New York | NY | US | 2024-11-30 08:33:24 |
| 2 | Marilyn Kelly | marilynkelly@yahoo.com | New York | NY | US | 2024-11-30 08:33:24 |
| 3 | Andrea Miller | andrea_miller@zoho.com | +1-803-484-1106 | Winnipeg | MB | CA | 2025-06-22 16:35:50 |
| 3 | Andrea Miller | andrea_miller@zoho.com | +1-803-484-1106 | Winnipeg | MB | CA | 2025-06-22 16:35:50 |
| 4 | Luis Green | lgreen@aol.com | +1-544-304-2519 | San Antonio | TX | US | 2025-01-26 13:10:44 |
| 4 | Luis Green | lgreen@aol.com | +1-544-304-2519 | San Antonio | TX | US | 2025-01-26 13:10:44 |
| 5 | Anthony Alvarez | anthonya@protonmail.com | +1-749-327-7201 | Los Angeles | CA | US | 2024-10-01 14:28:15 |
| 5 | Anthony Alvarez | anthonya@protonmail.com | +1-749-327-7201 | Los Angeles | CA | US | 2024-10-01 14:28:15 |
| lgreen@aol.com |
| lgreen@aol.com |
| +1-544-304-2519 |
| +1-544-304-2519 |
| San Antonio |
| San Antonio |
| TX |
| TX |
| US |
| US |
| 2025-01-26 13:10:44 |
| 2025-01-26 13:10:44 |
| Luis Green |
| Luis Green |
| 5 | Anthony Alvarez | anthonya@protonmail.com | +1-749-327-7201 | Los Angeles | CA | US | 2024-10-01 14:28:15 | Anthony Alvarez |
| 5 | Anthony Alvarez | anthonya@protonmail.com | +1-749-327-7201 | Los Angeles | CA | US | 2024-10-01 14:28:15 | Anthony Alvarez |
| 7 | Jacob Howard | jacob.howard@yahoo.com | +44-60-6977-3664 | Bristol | ENG | UK | 2025-01-22 18:20:12 | Jacob Howard |
| 7 | Jacob Howard | jacob.howard@yahoo.com | +44-60-6977-3664 | Bristol | ENG | UK | 2025-01-22 18:20:12 | Jacob Howard |
| 8 | Patrick Turner | patrickturner@hotmail.com | +353-3-646-5010 | Dublin | DUB | IE | 2024-11-03 11:01:17 | Patrick Turner |
| 8 | Patrick Turner | patrickturner@hotmail.com | +353-3-646-5010 | Dublin | DUB | IE | 2024-11-03 11:01:17 | Patrick Turner |