Big query De-duplication query is not working properly

user_14353401 0
Viewed 71

anyone please tell me the below query is not working properly, It suppose to delete the duplicate records only and keep the one of them (latest record) but it is deleting all the record instead of keeping one of the duplicate records, why is it so?

    delete
    from
        dev_rahul.page_content_insights
    where
        (sha_id,
        etl_start_utc_dttm) in (
        select
            (a.sha_id,
            a.etl_start_utc_dttm)
        from
            (
            select
                sha_id,
                etl_start_utc_dttm,
                ROW_NUMBER() over (Partition by sha_id
            order by
                etl_start_utc_dttm desc) as rn
            from
                dev_rahul.page_content_insights
            where
                (snapshot_dt) >= '2021-03-25' ) a
        where
            a.rn <> 1)
google-bigquery duplicates
1 Answers

Query looks ok, though I don't use that syntax for cleaning up duplicates.

Can I confirm the following:

  1. sha_id, etl_start_utc_dttm is your primary key?
  2. You wish to keep sha_id and the latest row based on etl_start_utc_dttm field descending?

If so, try this two query pattern:

create or replace table dev_rahul.rows_not_to_delete as
SELECT col.* FROM (SELECT ARRAY_AGG(pci ORDER BY etl_start_utc_dttm desc LIMIT 1
) OFFSET(0)]  col
FROM dev_rahul.page_content_insights pci
where snapshot_dt >= '2021-03-25' )
GROUP BY sha_id
);
delete dev_rahul.page_content_insights p
where not exists (select 1 from DW_pmo.rows_not_to_delete d 
                  where p.sha_id = d.sha_id and p.etl_start_utc_dttm = d.etl_start_utc_dttm
) and snapshot_dt >= '2021-03-25';

You could do this in a singe query by putting the first statement into a CTE.

user_123341660
Related