How do you identify and remove duplicate records in a large dataset using SQL?
Assume records are duplicates when they share the same source, customer reference, and account reference. Retain the newest record by ingestion timestamp, using the record ID to break timestamp ties.
record_id, source_id, customer_reference, account_reference, and ingested_at.source_id, customer reference, account reference, ingestion time, and record ID.| Column | Type | Description |
|---|---|---|
| source_idPK | INT | Unique source identifier |
| source_name | VARCHAR(100) | Name of the record source |
| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique record identifier |
| source_id | INT | Source associated with the record |
| customer_reference | VARCHAR(50) | Customer business reference |
| account_reference | VARCHAR(50) | Account business reference |
| record_payload | TEXT | Raw record content |
| ingested_at | TIMESTAMP | Timestamp when the record was ingested |