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.

Bigquery Post Ingestion Deduplication Sql | professional-cloud-data-engineer

Bigquery Post Ingestion Deduplication Sql

Worked example. The correct answer is already marked and every option is explained below, so there is nothing to select here. To answer questions yourself, start the free trial.

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).

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 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.

Professional Cloud Data Engineer

Bigquery Post Ingestion Deduplication Sql

Related questions