Explain how you would perform complex data deduplication across large dataset partitions using windowing functions like ROW_NUMBER or DENSE_RANK.
Use the supplied ingestion records and source metadata. Return one surviving record per business key, suppressing keys whose latest record is marked deleted.
customer_ref, event_date, and event_type combinationcustomer_ref, event_date, and event_type, with nulls last| Column | Type | Description |
|---|---|---|
| record_idPK | INT | Unique ingestion record identifier |
| customer_ref | VARCHAR(30) | Customer reference used in the business key |
| event_date | DATE | Business date of the event |
| event_type | VARCHAR(30) | Business event type |
| source_system_id | INT | Identifier of the source system |
| source_record_id | VARCHAR(40) | Record identifier from the source system |
| payload | JSONB | Raw event payload |
| updated_at | TIMESTAMP | Source update timestamp |
| ingested_at | TIMESTAMP | Timestamp when the record entered the platform |
| quality_score | DECIMAL(5,2) | Data quality score assigned during ingestion |
| is_deleted | BOOLEAN | Whether the record represents a deletion |
| partition_name | VARCHAR(30) | Physical ingestion partition |
| Column | Type | Description |
|---|---|---|
| source_system_idPK | INT | Unique source system identifier |
| source_name | VARCHAR(50) | Source system name |
| source_priority | INT | Preferred-source priority used as a tie-breaker |