PDE Preparing and Using Data for Analysis Practice Test 6 — 15 Questions

Question 1

A data engineer needs to query data from BigQuery and another cloud provider's storage (AWS S3) using a single SQL query. The data must not be moved or copied to GCP. Which Google Cloud service should they use?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data across multiple clouds (AWS S3, Azure Blob Storage) using BigQuery's interface without moving data. BigQuery Omni runs compute in the other cloud's region. BigQuery Transfer Service moves data into BigQuery. Dataplex is for data management, not cross-cloud queries. Cloud Storage Transfer Service is for moving data between clouds.

Answer

Cloud Storage Transfer Service

Answer

Dataplex

Answer

BigQuery Data Transfer Service

Question 2

What is the primary purpose of Vertex AI Feature Store?

Accepted Answer

To store and serve features for machine learning models at scale. Vertex AI Feature Store is a managed service for storing, serving, and sharing ML features at scale, providing low-latency online serving and high-throughput batch serving with point-in-time correctness. It centralizes feature definitions so training and serving use consistent feature values, reducing training-serving skew.

Answer

To manage and track ML experiments

Answer

To train machine learning models using AutoML

Answer

To transform raw data into features using SQL

Question 3

A company uses Looker Studio to create dashboards from BigQuery data. They notice that dashboard queries take several seconds to load. They want to improve performance without changing the underlying data or creating materialized views. Which option should they use?

Accepted Answer

Enable BigQuery BI Engine for the project. BigQuery BI Engine is an in-memory analysis service that accelerates queries from Looker Studio (and other BI tools) by caching data in memory, significantly reducing latency. Replicating data to Cloud SQL would add complexity and may not handle the volume. Using Looker instead of Looker Studio doesn't inherently speed up queries. Increasing BigQuery slots would help but is more expensive and not as targeted for BI tools.

Answer

Increase the number of BigQuery slots

Answer

Switch to Looker instead of Looker Studio

Answer

Replicate the data to Cloud SQL for faster queries

Question 4

To enable data lineage tracking in BigQuery, which feature should be activated?

Accepted Answer

Dataplex Lineage. The correct feature for data lineage tracking in BigQuery is Dataplex Lineage. Dataplex provides end-to-end lineage tracking across data assets, including BigQuery tables and views. BigQuery Lineage API is not a distinct feature; lineage capabilities are integrated into Dataplex. BigQuery Audit Logs capture metadata changes but are not lineage-specific, and Data Catalog is for metadata management, not lineage.

Answer

BigQuery Audit Logs

Answer

Data Catalog

Answer

BigQuery Lineage API

Question 5

A company needs to predict whether a product image contains a specific defect. They have 10,000 labeled images and want to build a model quickly without writing custom code or training from scratch. Which GCP service should they use?

Accepted Answer

AutoML Vision. AutoML Vision is designed for custom image classification tasks with minimal ML expertise. It uses transfer learning and supports up to millions of images. AutoML Tables handles tabular data, not images. Vertex AI custom training would require more effort. AutoML NLP is for text data.

Answer

AutoML Tables

Answer

Vertex AI custom training

Answer

AutoML Natural Language

Question 6

A data engineer needs to split time-series data for training a forecasting model. The data is sorted by timestamp. The engineer wants to avoid leakage where future data influences training. Which data splitting approach should they use?

Accepted Answer

Use a time-series aware split: first 80% of data by timestamp for training, last 20% for testing. Time-series data has a temporal order, so training must only use data that precedes the test data to prevent future information from leaking into the model. Option D holds out the last 20% of records by timestamp for testing and trains on the earlier 80%, which mirrors real forecasting conditions where the model predicts unseen future values. This preserves causality and gives a realistic estimate of out-of-sample performance.

Answer

Use k-fold cross-validation with random assignment

Answer

Use stratified splitting on the target variable

Answer

Perform a random 80/20 split on the entire dataset

Question 7

A data team uses Looker Studio to create a report that combines data from two different BigQuery tables: one with sales transactions and another with customer demographics. They need to join these tables in the report without writing SQL. Which feature should they use?

Accepted Answer

Data blending. Looker Studio's data blending feature allows combining data from multiple sources (including BigQuery tables) using a common key, without writing SQL. It provides a graphical interface to define joins. Creating a custom query requires SQL. Looker Studio reports support multiple charts, but blending is the specific feature for joining data. Calculated fields transform data within a single source.

Answer

Creating a report with multiple charts

Answer

Custom query in BigQuery connector

Answer

Calculated fields

Question 8

A data analyst wants to compute the rank of sales per region and also the difference in sales between consecutive months for each region. Which BigQuery analytic functions should they use? (Select TWO)

Accepted Answer

RANK(). RANK() (option A) is correct because it assigns a rank to each row within a partition ordered by sales, with tied sales values receiving the same rank and subsequent ranks skipped, which directly satisfies the requirement to compute the rank of sales per region. LAG() (option C) is correct because it accesses the value of sales from the preceding row within the same region partition ordered by month, allowing the analyst to compute the difference in sales between consecutive months. ROW_NUMBER() (option B) is not appropriate here because it assigns unique sequential numbers even for tied sales values, which does not produce a true rank. LEAD() (option D) looks forward to the next row rather than backward, so it would not give the previous month's sales for a consecutive-month difference. NTILE() (option E) divides rows into a specified number of buckets, which is unrelated to ranking sales or computing month-over-month differences.

Answer

ROW_NUMBER()

Answer

LEAD()

Answer

NTILE()

Question 9

You train a BigQuery ML linear regression model to predict house prices. The model has high bias during evaluation. Which action BEST reduces bias?

Accepted Answer

Add more features like number of bedrooms and square footage. High bias indicates underfitting, meaning the model is too simple to capture the underlying patterns in the data. Adding more features, such as number of bedrooms and square footage, increases the model's complexity and its ability to fit the training data, thereby reducing bias. This is a standard approach to address underfitting in linear regression.

Answer

Decrease the learning rate in the training options

Answer

Remove features that have low correlation with the label

Answer

Increase L2 regularization

Question 10

You have a BigQuery table with sales data and want to pivot product categories into columns. Which SQL clause should you use?

Accepted Answer

PIVOT. BigQuery supports the PIVOT operator, which rotates rows into columns by aggregating values for each distinct pivot key. It is used in the FROM clause after the source table or subquery, with an aggregate function and a list of pivot values. This is the standard SQL way to turn product categories into columns in a sales table.

Answer

UNPIVOT

Answer

ARRAY_AGG with CROSS JOIN

Answer

STRUCT

Question 11

Your Looker dashboard uses a BigQuery connection. You notice that some queries take over a minute. Which service can you enable to cache results in memory for sub-second Looker queries?

Accepted Answer

BigQuery BI Engine. BigQuery BI Engine is an in-memory analysis service that caches frequently accessed BigQuery data and accelerates queries, delivering sub-second response times for Looker dashboards. It integrates natively with BigQuery and Looker, so enabling BI Engine on the relevant project or dataset is the correct fix. Other options are separate database services that do not accelerate BigQuery queries.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Cloud Memorystore

Question 12

Which Google Cloud service would you use to create a unified data catalog that automatically captures lineage from BigQuery, Cloud Storage, and other sources?

Accepted Answer

Dataplex. Dataplex is Google Cloud's unified data governance and catalog service that automatically discovers, catalogs, and captures lineage across BigQuery, Cloud Storage, and other sources. It provides a unified data catalog with automatic metadata extraction and lineage tracking, which is exactly what the question describes.

Answer

Cloud Composer

Answer

Dataflow

Answer

Data Catalog

Question 13

You need to create a time-series forecast for inventory demand using BigQuery ML. The data includes daily sales for 5 years. Which model type should you use?

Accepted Answer

ARIMA_PLUS. BigQuery ML supports ARIMA_PLUS for time-series forecasting. Linear regression, k-means, and matrix factorization are not appropriate for time-series forecasting.

Answer

K-means

Answer

Linear regression

Answer

Matrix factorization

Question 14

You are using Vertex AI Feature Store to serve features for online predictions. Your model requires features from multiple sources with low latency (<10ms). Which type of serving should you use?

Accepted Answer

Online serving with Bigtable. Vertex AI Feature Store online serving is designed for low-latency lookups at prediction time, and Bigtable is the underlying low-latency store used for online serving. Bigtable provides single-digit millisecond reads at scale, which satisfies the <10ms requirement. Offline serving (BigQuery) is optimized for batch/high-throughput reads, not sub-10ms online lookups.

Answer

Online serving with Cloud SQL

Answer

Offline serving with BigQuery

Answer

Offline serving with Cloud Storage

Question 15

You want to quickly estimate the number of distinct visitors to your website from a large BigQuery table. Which function provides an approximate count with low latency?

Accepted Answer

APPROX_COUNT_DISTINCT. APPROX_COUNT_DISTINCT is a built-in BigQuery function that returns an approximate count of distinct values using a HyperLogLog++ sketch. It is designed for large-scale data, providing fast, low-latency results with a small error rate (typically <1%). This makes it ideal for quickly estimating distinct visitors without the overhead of exact counting.

Answer

HyperLogLog++

Answer

COUNT(DISTINCT)

Answer

APPROX_QUANTILES