How do you approach data cleaning when the source data is fragmented or incomplete?
Write a PostgreSQL query that cleans customer updates from two source systems, consolidates duplicate identities, and compares the latest available source data with the customer master. Preserve master customers even when no usable source update exists.
customer_id, customer_name, source_name, clean_email, clean_phone, source_record_count, and data_status.no_source, matched_complete, or matched_incomplete status values.customer_id ascending.| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Canonical customer identifier |
| customer_name | VARCHAR(120) | Customer name in the master record |
| VARCHAR(255) | Master email address | |
| phone | VARCHAR(30) | Master phone number |
| Column | Type | Description |
|---|---|---|
| update_idPK | INT | Support update identifier |
| source_customer_ref | VARCHAR(80) | Customer reference from support |
| VARCHAR(255) | Email supplied by support | |
| phone | VARCHAR(30) | Phone supplied by support |
| full_name | VARCHAR(120) | Name supplied by support |
| updated_at | TIMESTAMP | Source update timestamp |
| Column | Type | Description |
|---|---|---|
| update_idPK | INT | Billing update identifier |
| source_customer_ref | VARCHAR(80) | Customer reference from billing |
| VARCHAR(255) | Email supplied by billing | |
| phone | VARCHAR(30) | Phone supplied by billing |
| full_name | VARCHAR(120) | Name supplied by billing |
| updated_at | TIMESTAMP | Source update timestamp |