A data engineer is designing an automated post-ingestion cleansing pipeline in BigQuery for a multi-terabyte event ingestion table named telemetry.raw_events. Due to upstream at-least-once streaming semantics, duplicate event payloads are frequently ingested. The cleansing pipeline must meet the following operational requirements: Deduplicate records sharing the same device_id and event_id. Retain only the most recent record based on ingestion_timestamp. Overwrite the existing table with the cleansed dataset without leaving temporary analytical ranking columns in the final schema. Maximize query processing performance and slot efficiency across large partitions. Which SQL pattern should the data engineer implement?

Execute a `CREATE OR REPLACE TABLE telemetry.raw_events AS` statement querying `SELECT * EXCEPT(row_num) FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY device_id, event_id ORDER BY ingestion_timestamp DESC) AS row_num FROM telemetry.raw_events) WHERE row_num = 1`.: 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.

Keep Only the Newest BigQuery Event

Deduplicating BigQuery Events with ROW_NUMBER

A data engineer is designing an automated post-ingestion cleansing pipeline in BigQuery for a multi-terabyte event ingestion table named telemetry.raw_events.

Due to upstream at-least-once streaming semantics, duplicate event payloads are frequently ingested. The cleansing pipeline must meet the following operational requirements:

Deduplicate records sharing the same device_id and event_id.
Retain only the most recent record based on ingestion_timestamp.
Overwrite the existing table with the cleansed dataset without leaving temporary analytical ranking columns in the final schema.
Maximize query processing performance and slot efficiency across large partitions.

Which SQL pattern should the data engineer implement?

Execute a SELECT DISTINCT * query grouped by device_id and event_id, writing the output into a temporary table before dropping and recreating the original table.

Execute a CREATE OR REPLACE TABLE telemetry.raw_events AS statement querying SELECT * EXCEPT(row_num) FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY device_id, event_id ORDER BY ingestion_timestamp DESC) AS row_num FROM telemetry.raw_events) WHERE row_num = 1.

Execute a MERGE INTO telemetry.raw_events target USING (SELECT device_id, event_id, MAX(ingestion_timestamp) AS max_time FROM telemetry.raw_events GROUP BY device_id, event_id) source ON target.device_id = source.device_id AND target.event_id = source.event_id WHEN MATCHED AND target.ingestion_timestamp < source.max_time THEN DELETE.

Execute a DELETE FROM telemetry.raw_events WHERE (device_id, event_id) IN (SELECT (device_id, event_id) FROM telemetry.raw_events GROUP BY device_id, event_id HAVING COUNT(*) > 1).

Explanations

Execute a `SELECT DISTINCT *` query grouped by `device_id` and `event_id`, writing the output into a temporary table before dropping and recreating the original table.

What this option does

SELECT DISTINCT * filters out rows that are 100% identical across all columns in the table.

Why it doesn't meet the requirement

Fails on non-identical duplicates: In streaming ingestion pipelines, duplicate records often have identical business keys (device_id, event_id) but differing system attributes (such as different ingestion_timestamp values). DISTINCT * treats them as separate rows and retains both.
Invalid SQL syntax: Combining SELECT DISTINCT * directly with a GROUP BY on a subset of columns causes a syntax error in GoogleSQL because non-grouped columns must be explicitly aggregated.
Operational complexity: Manually managing temporary tables, dropping destination tables, and recreating them destroys table metadata, time travel history, and access control policies.

Key limitations

DISTINCT evaluates equality across every single column in the projection.
Dropping and recreating tables disrupts downstream querying sessions and revokes dataset permissions.

This answer would be correct if

Duplicates were exact bit-for-bit replicas across every single column and the table did not require time-based resolution.

View source documentation

Execute a `CREATE OR REPLACE TABLE telemetry.raw_events AS` statement querying `SELECT * EXCEPT(row_num) FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY device_id, event_id ORDER BY ingestion_timestamp DESC) AS row_num FROM telemetry.raw_events) WHERE row_num = 1`.

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.

View source documentation

Execute a `MERGE INTO telemetry.raw_events target USING (SELECT device_id, event_id, MAX(ingestion_timestamp) AS max_time FROM telemetry.raw_events GROUP BY device_id, event_id) source ON target.device_id = source.device_id AND target.event_id = source.event_id WHEN MATCHED AND target.ingestion_timestamp < source.max_time THEN DELETE`.

What this option does

This query uses a MERGE DML statement that calculates the maximum timestamp per key using an aggregate subquery, and deletes any row in the target table where the timestamp is strictly older than the maximum.

Why it doesn't meet the requirement

Fails on tie-breaking: If duplicate records arrive with identical ingestion_timestamp values for the same (device_id, event_id), the condition target.ingestion_timestamp < source.max_time evaluates to false for all of them, leaving both duplicate rows intact.
Performance degradation: MERGE statements on massive BigQuery tables require substantial slot processing for join resolution and generate extensive DML mutations compared to an atomic metadata table overwrite (CREATE OR REPLACE TABLE).

Key limitations

Cannot deterministically deduplicate rows when timestamps coincide without additional tie-breaker ordering.
Consumes significantly higher DML write quota and slot-seconds on petabyte-scale tables.

This answer would be correct if

The target table was receiving real-time concurrent updates where rewriting the entire table partition was prohibited.

View source documentation

Execute a `DELETE FROM telemetry.raw_events WHERE (device_id, event_id) IN (SELECT (device_id, event_id) FROM telemetry.raw_events GROUP BY device_id, event_id HAVING COUNT(*) > 1)`.

What this option does

This statement identifies all (device_id, event_id) combinations that have more than one occurrence using a subquery with GROUP BY and HAVING COUNT(*) > 1, and passes those keys into a DELETE statement.

Why it doesn't meet the requirement

Deletes all versions: Evaluating WHERE (device_id, event_id) IN (...) removes every row that has duplicates, deleting both the original and the recent entries rather than preserving the latest record.
Slot inefficiency: Running an unconstrained subquery with GROUP BY followed by a large-scale DELETE operation forces extensive data shuffling and triggers high-cost DML mutation locks on the table.

Key limitations

Lacks a mechanism to preserve the row with the most recent ingestion_timestamp.
Causes data loss by purging all instances of duplicated keys.
Incurs heavy write overhead and potential DML quota constraints on petabyte-scale tables.

This answer would be correct if

The business requirement dictated that any record subjected to duplicate generation was considered corrupt and must be completely purged from the system.

View source documentation

Last updated August 27, 2026Validated August 27, 2026

Deduplicating BigQuery Events with ROW_NUMBER

A data engineer is designing an automated post-ingestion cleansing pipeline in BigQuery for a multi-terabyte event ingestion table named telemetry.raw_events.

Due to upstream at-least-once streaming semantics, duplicate event payloads are frequently ingested. The cleansing pipeline must meet the following operational requirements:

Deduplicate records sharing the same device_id and event_id.
Retain only the most recent record based on ingestion_timestamp.
Overwrite the existing table with the cleansed dataset without leaving temporary analytical ranking columns in the final schema.
Maximize query processing performance and slot efficiency across large partitions.

Which SQL pattern should the data engineer implement?

Execute a SELECT DISTINCT * query grouped by device_id and event_id, writing the output into a temporary table before dropping and recreating the original table.

Execute a DELETE FROM telemetry.raw_events WHERE (device_id, event_id) IN (SELECT (device_id, event_id) FROM telemetry.raw_events GROUP BY device_id, event_id HAVING COUNT(*) > 1).

Explanations

Execute a `SELECT DISTINCT *` query grouped by `device_id` and `event_id`, writing the output into a temporary table before dropping and recreating the original table.

What this option does

SELECT DISTINCT * filters out rows that are 100% identical across all columns in the table.

Why it doesn't meet the requirement

Fails on non-identical duplicates: In streaming ingestion pipelines, duplicate records often have identical business keys (device_id, event_id) but differing system attributes (such as different ingestion_timestamp values). DISTINCT * treats them as separate rows and retains both.
Invalid SQL syntax: Combining SELECT DISTINCT * directly with a GROUP BY on a subset of columns causes a syntax error in GoogleSQL because non-grouped columns must be explicitly aggregated.
Operational complexity: Manually managing temporary tables, dropping destination tables, and recreating them destroys table metadata, time travel history, and access control policies.

Key limitations

DISTINCT evaluates equality across every single column in the projection.
Dropping and recreating tables disrupts downstream querying sessions and revokes dataset permissions.

This answer would be correct if

Duplicates were exact bit-for-bit replicas across every single column and the table did not require time-based resolution.

View source documentation

Execute a `CREATE OR REPLACE TABLE telemetry.raw_events AS` statement querying `SELECT * EXCEPT(row_num) FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY device_id, event_id ORDER BY ingestion_timestamp DESC) AS row_num FROM telemetry.raw_events) WHERE row_num = 1`.

What this pattern is

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

View source documentation

Execute a `MERGE INTO telemetry.raw_events target USING (SELECT device_id, event_id, MAX(ingestion_timestamp) AS max_time FROM telemetry.raw_events GROUP BY device_id, event_id) source ON target.device_id = source.device_id AND target.event_id = source.event_id WHEN MATCHED AND target.ingestion_timestamp < source.max_time THEN DELETE`.

What this option does

Why it doesn't meet the requirement

Fails on tie-breaking: If duplicate records arrive with identical ingestion_timestamp values for the same (device_id, event_id), the condition target.ingestion_timestamp < source.max_time evaluates to false for all of them, leaving both duplicate rows intact.
Performance degradation: MERGE statements on massive BigQuery tables require substantial slot processing for join resolution and generate extensive DML mutations compared to an atomic metadata table overwrite (CREATE OR REPLACE TABLE).

Key limitations

Cannot deterministically deduplicate rows when timestamps coincide without additional tie-breaker ordering.
Consumes significantly higher DML write quota and slot-seconds on petabyte-scale tables.

This answer would be correct if

The target table was receiving real-time concurrent updates where rewriting the entire table partition was prohibited.

View source documentation

Execute a `DELETE FROM telemetry.raw_events WHERE (device_id, event_id) IN (SELECT (device_id, event_id) FROM telemetry.raw_events GROUP BY device_id, event_id HAVING COUNT(*) > 1)`.

What this option does

Why it doesn't meet the requirement

Deletes all versions: Evaluating WHERE (device_id, event_id) IN (...) removes every row that has duplicates, deleting both the original and the recent entries rather than preserving the latest record.
Slot inefficiency: Running an unconstrained subquery with GROUP BY followed by a large-scale DELETE operation forces extensive data shuffling and triggers high-cost DML mutation locks on the table.

Key limitations

Lacks a mechanism to preserve the row with the most recent ingestion_timestamp.
Causes data loss by purging all instances of duplicated keys.
Incurs heavy write overhead and potential DML quota constraints on petabyte-scale tables.

This answer would be correct if

The business requirement dictated that any record subjected to duplicate generation was considered corrupt and must be completely purged from the system.

View source documentation

Last updated August 27, 2026Validated August 27, 2026

Deduplicating BigQuery Events with ROW_NUMBER

Explanations

Execute a SELECT DISTINCT * query grouped by device_id and event_id, writing the output into a temporary table before dropping and recreating the original table.

What this option does

Why it doesn't meet the requirement

Key limitations

This answer would be correct if

Execute a CREATE OR REPLACE TABLE telemetry.raw_events AS statement querying SELECT * EXCEPT(row_num) FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY device_id, event_id ORDER BY ingestion_timestamp DESC) AS row_num FROM telemetry.raw_events) WHERE row_num = 1.

What this pattern is

How it meets the requirements

Key advantages

Why this is optimal

What this option does

Why it doesn't meet the requirement

Key limitations

This answer would be correct if

Execute a DELETE FROM telemetry.raw_events WHERE (device_id, event_id) IN (SELECT (device_id, event_id) FROM telemetry.raw_events GROUP BY device_id, event_id HAVING COUNT(*) &gt; 1).

What this option does

Why it doesn't meet the requirement

Key limitations

This answer would be correct if

Try 10 interactive questions like this one, free

Related questions

Dataplex DataScan Data Profiling and Quality

Apache Beam Dead Letter Side Outputs

Google Cloud Environment Isolation and Service Account Impersonation

Cloud Monitoring Observability and Alerting

BigQuery Reservations Workload Isolation

Shared VPC Per Environment with Folder Hierarchy

Deduplicating BigQuery Events with ROW_NUMBER

Explanations

Execute a SELECT DISTINCT * query grouped by device_id and event_id, writing the output into a temporary table before dropping and recreating the original table.

What this option does

Why it doesn't meet the requirement

Key limitations

This answer would be correct if

Execute a CREATE OR REPLACE TABLE telemetry.raw_events AS statement querying SELECT * EXCEPT(row_num) FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY device_id, event_id ORDER BY ingestion_timestamp DESC) AS row_num FROM telemetry.raw_events) WHERE row_num = 1.

What this pattern is

How it meets the requirements

Key advantages

Why this is optimal

What this option does

Why it doesn't meet the requirement

Key limitations

This answer would be correct if

Execute a DELETE FROM telemetry.raw_events WHERE (device_id, event_id) IN (SELECT (device_id, event_id) FROM telemetry.raw_events GROUP BY device_id, event_id HAVING COUNT(*) &gt; 1).

What this option does

Why it doesn't meet the requirement

Key limitations

This answer would be correct if

Try 10 interactive questions like this one, free

Related questions

Dataplex DataScan Data Profiling and Quality

Apache Beam Dead Letter Side Outputs

Google Cloud Environment Isolation and Service Account Impersonation

Cloud Monitoring Observability and Alerting

BigQuery Reservations Workload Isolation

Shared VPC Per Environment with Folder Hierarchy

Execute a `SELECT DISTINCT *` query grouped by `device_id` and `event_id`, writing the output into a temporary table before dropping and recreating the original table.

Execute a `CREATE OR REPLACE TABLE telemetry.raw_events AS` statement querying `SELECT * EXCEPT(row_num) FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY device_id, event_id ORDER BY ingestion_timestamp DESC) AS row_num FROM telemetry.raw_events) WHERE row_num = 1`.

Execute a `DELETE FROM telemetry.raw_events WHERE (device_id, event_id) IN (SELECT (device_id, event_id) FROM telemetry.raw_events GROUP BY device_id, event_id HAVING COUNT(*) > 1)`.

Execute a `SELECT DISTINCT *` query grouped by `device_id` and `event_id`, writing the output into a temporary table before dropping and recreating the original table.

Execute a `CREATE OR REPLACE TABLE telemetry.raw_events AS` statement querying `SELECT * EXCEPT(row_num) FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY device_id, event_id ORDER BY ingestion_timestamp DESC) AS row_num FROM telemetry.raw_events) WHERE row_num = 1`.

Execute a `DELETE FROM telemetry.raw_events WHERE (device_id, event_id) IN (SELECT (device_id, event_id) FROM telemetry.raw_events GROUP BY device_id, event_id HAVING COUNT(*) > 1)`.