Prepare and test your skills
Prepare and test your skills
Free sample question, answered and explained below. The Associate Data Practitioner practice bank has 1,156 more like it. Try 10 free: instant score, no account.
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:
transaction_id, and only the most recent entry should be retained.customer_email column are represented as NULL and need a fallback value of 'unknown@example.com'.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?
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.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.
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.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.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.QUALIFY clause operates directly on window function results after WHERE, GROUP BY, and HAVING filters, optimizing compute performance in BigQuery.SAFE. prefix prevents batch pipeline crashes caused by isolated bad records or malformed strings.OVER() clause ensures reproducible selection of the most recent record.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.
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).This approach uses basic aggregation (GROUP BY), standard filtering (WHERE IS NOT NULL), and direct type casting via CAST().
transaction_id requires aggregating all other columns (e.g., using MAX() or MIN()), which risks corrupting cross-column record consistency instead of preserving the exact latest record.WHERE customer_email IS NOT NULL drops all records that lack an email address, directly violating the requirement to preserve rows and populate them with a default fallback string.CAST(string AS DATE) function expects the ISO 8601 format (YYYY-MM-DD). A string using slashes (YYYY/MM/DD) will cause CAST to fail unless formatted with PARSE_DATE, and without a SAFE. prefix, invalid strings will abort the entire query execution.GROUP BY collapses multiple rows into aggregate statistics rather than selecting specific intact records.IS NOT NULL discards data volume rather than imputing or standardizing missing values.CAST statements throw runtime errors whenever input data does not strictly adhere to expected default formats.The business requirement called for aggregating transaction counts per customer ID, excluding incomplete customer profiles, and assuming all date strings were already formatted as ISO standard 'YYYY-MM-DD'.
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.This option suggests using a BigQuery ML anomaly detection function for record deduplication, using IFNULL() with an empty string, and invoking PARSE_DATE() without error handling.
ML.DETECT_ANOMALIES is designed to detect statistical outliers in trained BigQuery ML models, not for standard relational deduplication of business transaction IDs.'' in IFNULL() does not satisfy the business requirement of populating missing emails with 'unknown@example.com'.PARSE_DATE() without the SAFE. prefix causes BigQuery to terminate the query with a runtime error if even a single row contains a corrupted, truncated, or invalid date string.ML.DETECT_ANOMALIES, ML.PREDICT) are intended for model evaluation, clustering, and predictive analysis, not standard SQL record cleaning.The objective was to identify fraudulent transactions using a trained unsupervised machine learning model and substitute null values with empty strings.
DISTINCT * across all columns, NULLIF(customer_email, 'unknown@example.com') for missing values, and EXTRACT(DATE FROM transaction_date) for standardizing dates.This option attempts to deduplicate records using DISTINCT *, handle nulls using NULLIF(), and extract a date component using the EXTRACT() function.
DISTINCT * only deduplicates rows where every single column is identical. If duplicate transaction_id entries have different timestamps or metadata values, DISTINCT * will retain both rows rather than isolating the most recent record.NULLIF(customer_email, 'unknown@example.com') returns NULL if the email equals 'unknown@example.com'. It does not replace existing NULL values with a fallback default.EXTRACT(DATE FROM ...) requires a DATETIME or TIMESTAMP data type as its input; it cannot parse raw string literals formatted as 'YYYY/MM/DD' and will throw a syntax/type error.DISTINCT evaluates entire row equality and does not allow deterministic tie-breaking based on timestamps.NULLIF is designed to convert specific sentinel values to NULL, the opposite of filling missing data.EXTRACT retrieves subfields (such as day, month, or year) from temporal data types rather than parsing raw text strings.The goal were to eliminate 100% identical duplicate rows across all columns, convert the literal placeholder 'unknown@example.com' into NULL, and extract the date part from an existing DATETIME column.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated