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.
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:
order_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?
Cloud Pub/Sub dead-letter topics are messaging mechanisms designed to capture and isolate undeliverable messages after a subscriber fails to acknowledge them a configured number of times during real-time stream ingestion.
customers table in BigQuery) or perform SQL-based data profiling.The scenario required capturing unparseable JSON messages from a real-time streaming ingestion pipeline before they entered the data warehouse when message delivery fails repeatedly.
Cloud Storage Object Lifecycle Management allows administrators to define automated actions on storage objects, such as transitioning objects to colder storage classes (e.g., Nearline or Coldline) or deleting objects after a specific retention age.
The organization needed to automatically delete raw export files in a Cloud Storage staging bucket after 30 days to reduce storage costs and meet compliance standards.
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.
BigQuery BI Engine is an in-memory analysis service built into Google Cloud BigQuery that accelerates SQL queries and interactive dashboards by caching frequently used table partitions and columns.
The scenario asked how to speed up repeated analytical dashboard queries and visual reports generated from an already cleaned and validated reporting table.
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