How to find duplicates records across multiple columns

You could use some window functions and an unpivot to accomplish something like this. I'm using rank() in my example, which will give you a numeric dupe group. I am only doing emails in this example, but you could simply do a union with the same logic for phones:

with x as (
    SELECT id, email1, email2, email3
    FROM (VALUES (1,'email1','email2','email3'),
                 (2,'email2','email4','email5'),
                 (3,'email6','email7','email8'),
                 (4,'email9','email11','email10') y (id,email1,email2,email3)
         )
)
SELECT id, 
--     emails, 
       RANK() OVER (ORDER BY emails) as dupe_group
FROM x
UNPIVOT(emails for email in (email1,email2,email3))
QUALIFY COUNT(DISTINCT id) OVER (PARTITION BY emails) > 1
;

Please note that the cte at the beginning is just creating a dataset to test with. And I commented out emails in the output, since you don't have it in yours, but I used it to make sure my code was working.

In below query -

• First CTE with data - is only data.
• Second CTE cte_1 combines all email and phone columns into an array.
• Third CTE cte_2 is the core of the entire query. It uses function arrays_overlap to compare current record with all other records in the table using self-join. In case of match it picks the least of two IDs that are matching. Column child in cte_2 creates the pivot or starting point for different hierarchical groups in the data-set.
• Fourth CTE final_cte picks the minimum and creates the final hierarchical data-set which links each id (parent) via column chk (child) in final_cte.
• Lastly, hierarchial query is used to get the top root for each element.

I had to use coalesce as function arrays_overlap is NULL-safe, so it returns true when comparing NULLs, so by using coalesce, I ensured that null records in across rows are unique.

with data (id,email1,email2,email3,phone1,phone2) as (
select * from values
(1,'email1','email2','email3',123,321),
(2,'email2','email4','email5',234,432),
(3,'email6','email7','email8',234,null),
(4,'email9','email10','email11',null,null),
(5,'email12','email9',null,null,null)
), cte_1 (id,arr) as
(select id,
array_construct(
coalesce(email1, id::string),
coalesce(email2, id::string),
coalesce(email3, id::string),
coalesce(phone1, id::number),
coalesce(phone2, id::number))
from data), cte_2 as (
select c1.id id1,c2.id id2,
case when arrays_overlap(c1.arr,c2.arr) then least(c1.id,c2.id) else null end chk,
case when (id1 = chk) then null else chk end child
from cte_1 c1 left join cte_1 c2
where c1.id != c2.id ), final_cte as (
select id1, 
min(child) chk
from cte_2
group by id1
order by id1
)
select id1, connect_by_root id1 as parent from final_cte 
start with chk is null 
connect by chk = prior id1 
order by id1;

If you indeed need dup_group as A,B,C then add another query at the end; something like -

select id1, 
chr(65+conditional_change_event(parent) 
over (order by id1)) dup_group 
from cte_name;

With the help from @mike-walton and @pankaj, I achieved the goal with the following SQL:

with data as (
    SELECT user_id, email1, email2, email3
    FROM (VALUES (1,'email1','email2','email3'),
                 (2,'email2','email4','email5'),
                 (3,'email6','email7','email8'),
                 (4,'email9','email11','email10'),
                 (5,'email4','email12','email13'),
                 (6,'email14','email9','email15') y (user_id,email1,email2,email3)
         )
),
-- 1. Find duplicates
dups as (
    SELECT 
        user_id, 
        RANK() OVER (ORDER BY emails) as group_id
    FROM data
    UNPIVOT(emails for email in (email1, email2 ,email3))
    QUALIFY  COUNT(DISTINCT user_id) OVER (PARTITION BY emails) > 1
),
-- 2. Merge overlapped dup groups.
group_members as (
    select 
        group_id, array_agg(user_id) users
    from dups
    group by group_id
),
overlapped_group as (
    select 
        c1.group_id g1,
        c2.group_id g2,
        least(g1, coalesce(g2, g1)) as min_group,
        min(min_group) over (partition by g2) as merge_to
    from group_members c1
    left join 
        group_members c2 on arrays_overlap(c1.users, c2.users) 
        and g1 <> g2
), 
merge_mapping as (
    select
        distinct
        g1 as group_id,
        iff(g2 is null, g1, min(merge_to) over (partition by g1)) as merge_to
    from overlapped_group
)

select
    distinct
    user_id,
    m.merge_to as group_id
from dups 
left join merge_mapping m using(group_id) order by group_id;