How would you handle duplicate records in an SQL table?

Architectural Logic: 1. Identify: GROUP BY key HAVING COUNT() > 1. 2. Resolve: ROW_NUMBER() OVER (PARTITION BY key ORDER BY tiebreaker) then keep rn=1. 3. Prevent: UNIQUE constraint, PK; MERGE/INSERT with conflict handling. Why: Duplicates indicate ingestion bug, missing idempotency, or intentional multi-version (e.g., SCD2). Fix root cause before ad-hoc dedup. Scalability: DELETE from CTE/subquery can lock; prefer INSERT INTO new_table SELECT ... WHERE rn=1 then swap, or use MERGE. Cost: Full scan + sort for ROW_NUMBER; DELETE is row-by-row logged. Example: WITH cte AS (SELECT , ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) rn FROM t) DELETE FROM t WHERE (key, id) IN (SELECT key, id FROM cte WHERE rn > 1)—or use INSERT-overwrite pattern.