Explain how you would use SQL joins and de-duplication techniques to clean and validate data for QA testing.
Use the provided source records and account reference data. Return the latest source record for each normalized email, identify duplicate source records, and expose records that are unmatched or inconsistent.
normalized_email, source_id, full_name, source_status, account_id, account_name, account_status, duplicate_count, and validation_statusnormalized_email ascending with NULL last| Column | Type | Description |
|---|---|---|
| source_idPK | INT | Unique identifier for the incoming source record |
| VARCHAR(255) | Email value received from the source system | |
| full_name | VARCHAR(150) | Name received from the source system |
| account_status | VARCHAR(20) | Account status received from the source system |
| updated_at | TIMESTAMP | Source record update timestamp |
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Reference account identifier |
| normalized_email | VARCHAR(255) | Canonical lowercase and trimmed email |
| account_name | VARCHAR(150) | Reference account name |
| account_status | VARCHAR(20) | Reference account status |