BigQuery delete deduplication

bq-del-dedup.md

Ref

https://medium.com/google-cloud/bigquery-deduplication-14a1206efdbb

Deduplicate by recreate the Table

Slower

CREATE OR REPLACE TABLE `transactions.testdata`
PARTITION BY date
AS SELECT DISTINCT * FROM `transactions.testdata`;

Deduplicate by using MERGE

Faster

MERGE `transactions.testdata` t
USING (
  SELECT DISTINCT *
  FROM `transactions.testdata`
  WHERE date=CURRENT_DATE()
)
ON FALSE
WHEN NOT MATCHED BY SOURCE AND date=CURRENT_DATE() THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT ROW