Google Cloud Professional Data Engineer · 15% practice allocation

Google Cloud Professional Data Engineer: Preparing and using data for analysis Practice Questions

5 free sample questions from the Preparing and using data for analysis domain, with answers and explanations. The full Professional Data Engineer bank has 252 questions across 5 domains. Questions are scenario-based and written from the official Google Cloud exam guide.

1.

A logistics company stores delivery data in BigQuery with columns: delivery_date, delivery_id, warehouse_id, and delivery_time_minutes. They want to identify delivery anomalies by finding the delivery time that is greater than the 95th percentile within each warehouse. Which BigQuery SQL function best enables this comparison?

  • A. STDDEV(delivery_time_minutes) OVER (PARTITION BY warehouse_id)
  • B. PERCENTILE_CONT(delivery_time_minutes, 0.95) OVER (PARTITION BY warehouse_id)✓ Correct
  • C. PERCENT_RANK() OVER (PARTITION BY warehouse_id ORDER BY delivery_time_minutes)
  • D. NTILE(100) OVER (PARTITION BY warehouse_id ORDER BY delivery_time_minutes)
Explanation

PERCENTILE_CONT() is the correct function to compute the 95th percentile value within each warehouse partition, allowing direct comparison of individual delivery times against that threshold. PERCENT_RANK() returns a rank value (0–1) rather than the actual percentile value. NTILE(100) divides data into 100 buckets but doesn't directly compute a percentile value. STDDEV() computes standard deviation, not percentiles. The window function approach with PARTITION BY warehouse_id ensures the calculation is done independently for each warehouse.

2.

A manufacturing company connects Looker Studio to BigQuery to visualize production metrics. The dataset contains 5 million daily records spanning 10 years. Reports load slowly. The team has already optimized the underlying queries. Which of the following approaches would most likely reduce Looker Studio load times? (Select TWO.)

  • A. Enable BigQuery caching at the dataset level using the cache_expiration parameter
  • B. Create a Dataform model that incrementally aggregates data by day/week and expose it as a dbt-style table for Looker Studio✓ Correct
  • C. Partition and cluster the BigQuery table on dimensions frequently used in filters (e.g., date, region)
  • D. Create a pre-aggregated materialized view in BigQuery at the daily or weekly grain and use it as the Looker Studio data source instead of raw data✓ Correct
  • E. Convert the BigQuery dataset into CSV files and upload them to Cloud Storage for Looker Studio to query
  • F. Increase the Looker Studio cache time in the connector settings to reduce the frequency of queries to BigQuery
Explanation

Pre-aggregating data (option D) and using Dataform/dbt to maintain aggregated tables (option B) both reduce query scope and complexity, directly improving load times by querying smaller datasets. Option A is incorrect; cache_expiration is a table refresh parameter, not a performance tuning mechanism for query speed. Option E is incorrect and impractical; CSV files are not efficient for Looker Studio and bypass BigQuery's query optimization. Option F misunderstands the issue; Looker Studio cache settings control dashboard refresh frequency, not the speed of individual queries to BigQuery. Option C (partitioning/clustering) helps but is not as effective as reducing the data volume queried via aggregation.

3.

Your organization is exporting BigQuery query results to Looker Studio for visualization. The dataset contains a user_segment column with policy tags that restrict visibility to users without the 'SEGMENT_VIEWER' role. A Looker Studio user without this role runs a dashboard connected to BigQuery. Which of the following will happen? (Select TWO.)

  • A. The dashboard will display NULL in all cells of the user_segment column✓ Correct
  • B. The dashboard will raise an authentication error and prevent the user from viewing the dashboard
  • C. The dashboard will apply masking rules defined on the policy tag, displaying hashed or redacted values✓ Correct
  • D. Looker Studio will cache the results from the last time a SEGMENT_VIEWER ran the query, displaying those unmasked values to this user
  • E. The dashboard will display all data normally because Looker Studio applies its own access control layer independent of BigQuery policy tags
Explanation

The correct answers are A (NULL values) and C (masking rules). When a user without 'SEGMENT_VIEWER' role accesses a policy-tagged column via Looker Studio, BigQuery enforces the policy tag rules: by default showing NULL (Answer A), or applying a configured masking rule like hashing (Answer C). Answer B is wrong—the user can still view the dashboard; only the specific column is restricted. Answer D is false; Looker Studio doesn't cache across different users' access levels, and policy tags are enforced at query execution time. Answer E is false; Looker Studio relies on BigQuery's native policy tag enforcement and does not bypass it.

4.

A data engineer needs to parse a BigQuery column containing JSON arrays of user purchase events (schema: [{product_id, amount, date}, ...]). They want to flatten this into rows, one per event. Which approach is most efficient?

  • A. Write a JavaScript UDF that iterates through the JSON array and returns a TABLE<> with one row per event
  • B. Load the JSON into a staging table, parse it using STRING_AGG, then GROUP BY event
  • C. Use a Python UDF in a Dataform transformation to parse and flatten the JSON
  • D. Use UNNEST(JSON_EXTRACT_ARRAY(events_json, '$')) and CROSS JOIN with the original table✓ Correct
Explanation

The correct answer is UNNEST with JSON_EXTRACT_ARRAY. This native BigQuery approach is the most efficient: JSON_EXTRACT_ARRAY parses the array, UNNEST flattens it into rows, and it all executes in the native SQL engine with minimal overhead. Answer A (JavaScript UDF) would work but is slower than native functions and has higher memory footprint. Answer C (Python UDF) is even slower and overkill for this task. Answer B (STRING_AGG and GROUP BY) is incorrect conceptually—STRING_AGG aggregates strings, which is the opposite of what you need for flattening.

5.

A financial services company implements column-level security on a BigQuery dataset using policy tags. The credit_scores column has a PII tag, and salary has a SENSITIVE tag. An analyst from the Credit Risk team has permission to view PII but not SENSITIVE data. What happens when this analyst queries a view that includes both columns?

  • A. The analyst sees both columns; policy tags do not restrict data within authorized views
  • B. The query is rejected entirely because the view contains a column the analyst cannot access
  • C. The salary column is redacted as [REDACTED], and credit_scores is visible
  • D. The view returns NULL for salary and the actual credit_scores values✓ Correct
Explanation

Policy tags in BigQuery use fine-grained column-level security. When an analyst lacks permission for a column (salary with SENSITIVE tag), that column returns NULL in query results; other columns they have permission for (credit_scores with PII tag) are returned normally. Option B is incorrect because the analyst has permission for at least one column in the view. Option A is incorrect; policy tags do enforce restrictions even in views. Option C misrepresents how redaction works; BigQuery uses NULL, not literal redaction strings.

Test yourself across every Professional Data Engineer domain

The free Professional Data Engineer practice test mixes about 20 questions weighted like the exam. The full 252-question bank adds instant grading, flashcards and a timed mock exam.

Take the free Professional Data Engineer practice testPractice the full bank

Study this domain

Preparing and using data for analysis study guide (Professional Data Engineer) →

Other Professional Data Engineer domains

← All Google Cloud Professional Data Engineer study resources