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!
Prepare and test your skills
Prepare and test your skills
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.
Keep the momentum going with these hand-picked practice scenarios
Want more questions like this?
Get a free certification question every week.
Last updated
An analytics team ingests daily transaction records into a staging dataset in Google Cloud BigQuery. Before publishing the data to downstream reporting tables, the team needs to assess data quality by verifying that:
StudyCerts
StudyCertsorder_id contain no null or duplicate valuesorder_amount are strictly greater than zerocustomer_id matches an existing record in the master customers tableWhich approach should the team implement in BigQuery to evaluate data accuracy and identify these discrepancies?
Configure a Pub/Sub dead-letter topic on the BigQuery dataset to automatically filter and reject rows violating relational consistency rules.
Execute SQL validation queries and profiling checks using conditional aggregations and anti-joins to identify constraint violations and orphan records.
Configure Cloud Storage Object Lifecycle Management rules to validate data types and column boundaries before data is loaded.
Enable BigQuery BI Engine reservations to enforce foreign key constraints and reject invalid transaction amounts upon table creation.
Configure a Pub/Sub dead-letter topic on the BigQuery dataset to automatically filter and reject rows violating relational consistency rules.
Execute SQL validation queries and profiling checks using conditional aggregations and anti-joins to identify constraint violations and orphan records.
SQL-based data validation in Google Cloud BigQuery involves running structured queries against staging tables to profile datasets and verify business rules, constraints, and data integrity before moving data into production. By combining standard SQL constructs like COUNTIF, CASE, window functions, and LEFT JOIN ... WHERE NULL (anti-joins), data practitioners can systematically identify anomalies, nulls, duplicates, and referential mismatches.
COUNTIF(order_id IS NULL) and aggregate queries like GROUP BY order_id HAVING COUNT(*) > 1 to immediately detect missing identifiers and duplicate transactions.COUNTIF(order_amount <= 0) accurately profiles numerical columns to spot negative or zero values that violate business domain rules.LEFT JOIN customers ON staging.customer_id = customers.customer_id WHERE customers.customer_id IS NULL) detects orphan records that do not exist in the master customer dimension.Executing custom SQL profiling and validation rules inside BigQuery provides immediate, comprehensive visibility into data accuracy, domain constraints, and cross-dataset consistency directly at the storage and analysis layer before downstream consumers query the data.
Configure Cloud Storage Object Lifecycle Management rules to validate data types and column boundaries before data is loaded.
Enable BigQuery BI Engine reservations to enforce foreign key constraints and reject invalid transaction amounts upon table creation.