A data practitioner has loaded a raw customer transactions table into BigQuery. The dataset contains several data quality issues that need to be resolved before downstream business intelligence reports can consume it: Multiple duplicate records exist for each transaction_id, and only the most recent entry should be retained. Missing values in the customer_email column are represented as NULL and need a fallback value of 'unknown@example.com'. The transaction_date column is formatted as string text ('YYYY/MM/DD') and must be converted to a DATE type safely without failing the query if an invalid string is encountered. Which combination of BigQuery SQL techniques should the practitioner use to clean and prepare this dataset?

Use `QUALIFY ROW_NUMBER() OVER(PARTITION BY transaction_id ORDER BY transaction_date DESC) = 1` for deduplication, `COALESCE(customer_email, 'unknown@example.com')` for missing values, and `SAFE.PARSE_DATE('%Y/%m/%d', transaction_date)` for standardizing dates.: ### **What the Solution Is** This approach leverages **BigQuery Standard SQL** analytical functions and safe conversion operators to perform multi-step data cleansing directly in the data warehouse. By combining window partitioning, conditional null replacement, and safe type transformation, data practitioners can clean messy raw data into a reliable, standardized schema for downstream analytics. ### **How it Meets the Requirements** - **Deduplication (`QUALIFY ROW_NUMBER()`)**: The `ROW_NUMBER()` window function partitions rows by `transaction_id` and orders them descending by `transaction_date`. Applying the `QUALIFY` clause with `= 1` filters out any older duplicate records and retains only the single latest entry per transaction ID in a single query pass without requiring complex self-joins or subqueries. - **Handling Missing Values (`COALESCE`)**: The `COALESCE()` function returns the first non-null expression in its argument list. Evaluating `COALESCE(customer_email, 'unknown@example.com')` preserves valid email addresses while safely substituting `NULL` values with the designated placeholder. - **Standardizing Formats Safely (`SAFE.PARSE_DATE`)**: The `PARSE_DATE()` function converts custom string patterns into standardized `DATE` objects (`YYYY-MM-DD`). Prefixing with the `SAFE.` operator ensures that any improperly formatted strings return `NULL` instead of generating runtime exceptions that would terminate the entire query. ### **Key Advantages and Capabilities** - **Efficient Single-Pass Execution**: The `QUALIFY` clause operates directly on window function results after `WHERE`, `GROUP BY`, and `HAVING` filters, optimizing compute performance in BigQuery. - **Fault-Tolerant Transformations**: The `SAFE.` prefix prevents batch pipeline crashes caused by isolated bad records or malformed strings. - **Deterministic Deduplication**: Explicit ordering inside the `OVER()` clause ensures reproducible selection of the most recent record. - **ANSI SQL Compliance**: Standard SQL functions integrate natively with all BigQuery analytics tools and downstream visualization services such as Looker Studio. ### **Why This Is Optimal** Using native **BigQuery SQL** capabilities allows data transformations and cleansing to occur directly inside BigQuery's distributed compute engine (ELT pattern). This eliminates the overhead of moving raw data out to external tools or running multiple intermediate table writes.

StudyCerts

Bigquery Deduplication Null Handling Date Cleaning | Unlock the power of your data in the cloud! Get hands-on with Google Cloud's core data services like BigQuery and Looker to validate your practical skills in data ingestion, analysis, and management, and earn your Associate Data Practitioner certification! | StudyCerts

Associate Data Practitioner

Unlock the power of your data in the cloud! Get hands-on with Google Cloud's core data services like BigQuery and Looker to validate your practical skills in data ingestion, analysis, and management, and earn your Associate Data Practitioner certification!

Fundamental

Prepare and test your skills

Track Readiness

Set Exam Goal

Fundamental

Prepare and test your skills

Track Readiness

Set Exam Goal

Bigquery Deduplication Null Handling Date Cleaning

Section: Conduct data cleaning (e.g., Cloud Data Fusion, BigQuery, SQL, Dataflow)

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 practitioner has loaded a raw customer transactions table into BigQuery. The dataset contains several data quality issues that need to be resolved before downstream business intelligence reports can consume it:

Multiple duplicate records exist for each transaction_id, and only the most recent entry should be retained.
Missing values in the customer_email column are represented as NULL and need a fallback value of 'unknown@example.com'.
The transaction_date column is formatted as string text ('YYYY/MM/DD') and must be converted to a DATE type safely without failing the query if an invalid string is encountered.

Which combination of BigQuery SQL techniques should the practitioner use to clean and prepare this dataset?

Use QUALIFY ROW_NUMBER() OVER(PARTITION BY transaction_id ORDER BY transaction_date DESC) = 1 for deduplication, COALESCE(customer_email, 'unknown@example.com') for missing values, and SAFE.PARSE_DATE('%Y/%m/%d', transaction_date) for standardizing dates.

Use GROUP BY transaction_id with COUNT(*), filter nulls using a WHERE customer_email IS NOT NULL clause, and convert dates using CAST(transaction_date AS DATE).

Use DISTINCT * across all columns, NULLIF(customer_email, 'unknown@example.com') for missing values, and EXTRACT(DATE FROM transaction_date) for standardizing dates.

Use ML.DETECT_ANOMALIES to remove duplicate transactions, IFNULL(customer_email, '') to delete null columns, and PARSE_DATE('%Y/%m/%d', transaction_date) without safety prefixes.

Associate Data Practitioner

Bigquery Deduplication Null Handling Date Cleaning

Related questions