How to delete Duplicate records in snowflake database table

Question

how to delete the duplicate records from snowflake table. Thanks

ID Name
1  Apple
1  Apple
2  Apple
3  Orange
3  Orange

Result should be:

ID Name
1  Apple
2  Apple
3  Orange

Answer 1

Adding here a solution that doesn't recreate the table. This because recreating a table can break a lot of existing configurations and history.

Instead we are going to delete only the duplicate rows and insert a single copy of each, within a transaction:

-- find all duplicates
create or replace transient table duplicate_holder as (
    select $1, $2, $3
    from some_table
    group by 1,2,3
    having count(*)>1
);

-- time to use a transaction to insert and delete
begin transaction;

-- delete duplicates
delete from some_table a
using duplicate_holder b
where (a.$1,a.$2,a.$3)=(b.$1,b.$2,b.$3);

-- insert single copy
insert into some_table
select * 
from duplicate_holder;

-- we are done
commit;

Advantages:

• Doesn't recreate the table
• Doesn't modify the original table
• Only deletes and inserts duplicated rows (good for time travel storage costs, avoids unnecessary reclustering)
• All in a transaction