How would you identify and remove duplicate records while retaining the necessary data?

To identify duplicates, use a window function that assigns a row number partitioned by the columns that define uniqueness. For example:

SELECT *, ROW_NUMBER() OVER (PARTITION BY key_col1, key_col2 ORDER BY last_modified DESC) AS rn

FROM my_table;

Rows where rn > 1 are duplicates. To retain the most recent record, keep rows with rn = 1 and delete the rest:

DELETE FROM my_table

WHERE (key_col1, key_col2, rn) NOT IN (

SELECT key_col1, key_col2, 1 FROM my_table

);

Alternatively, use a CTE to capture the duplicates and then delete them. If you need to keep a history, insert the duplicates into an archive table before deletion. This approach ensures that only unique rows remain while preserving the latest data.