Bigquery Post Ingestion Deduplication Sql

What this pattern is

This pattern uses a window function (ROW_NUMBER()) coupled with a CREATE OR REPLACE TABLE ... AS SELECT (DDL/DML) statement and the EXCEPT column modifier to perform an atomic, in-place deduplication of a BigQuery table.

How it meets the requirements

• Deduplicate records by key: Partitioning the window by device_id, event_id isolates duplicate groups into distinct evaluation windows.
• Retain the most recent record: Ordering the partition window by ingestion_timestamp DESC guarantees that the newest record is assigned row_num = 1.
• Schema hygiene: Wrapping the inner query with SELECT * EXCEPT(row_num) ensures that the transient ranking column is excluded from the final schema definition.
• Table overwrite: Executing CREATE OR REPLACE TABLE performs an atomic metadata swap that replaces the target table with the deduplicated dataset.

Key advantages

• High performance: BigQuery's distributed execution engine evaluates window partitions in parallel across distributed slots.
• Atomic replacement: Prevents downstream consumers from reading partial or in-flux datasets during pipeline execution.
• Clean schema output: Eliminates manual schema recreation or ALTER TABLE DROP COLUMN operations by discarding helper attributes on write.

Why this is optimal

Compared to row-by-row DELETE DML statements or expensive self-joins, evaluating window functions in a single-pass CREATE OR REPLACE TABLE AS SELECT query scans the data efficiently, minimizes slot execution time, and avoids concurrency limits associated with multiple mutation operations.