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

Question 1

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?

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

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 2

A data engineer needs to implement data quality rules and governance policies across multiple data lakes in GCP. They want to automatically discover and catalog data assets, and enforce row-level security. Which two services should they use? (Select TWO)

Accepted Answer

Dataplex. Dataplex (B) provides unified data management including data quality, governance policies, and row-level security via BigQuery. Data Catalog (E) enables automated discovery and cataloging of data assets across multiple data lakes. Together, they satisfy all requirements.

Answer

Security Command Center

Answer

Cloud DLP

Answer

Dataflow

Question 3

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() computes the rank of rows within a partition. LAG() accesses data from a previous row in the same result set, which can be used to compute differences. ROW_NUMBER() assigns unique sequential integers, not rank. NTILE() distributes rows into buckets. LEAD() accesses next row, not previous.

Answer

ROW_NUMBER()

Answer

LEAD()

Answer

NTILE()

Question 4

A company wants to use BigQuery ML to build a recommendation system for movies. The data includes user IDs, movie IDs, and ratings. Which BigQuery ML model types are suitable for this? (Select TWO)

Accepted Answer

Boosted tree classifier. Matrix factorization (via implicit or explicit feedback) is specifically designed for recommendation systems. Boosted tree classifiers can also be used for predicting ratings as a classification problem. AutoML Tables is not a model type in BigQuery ML. ARIMA_PLUS is for time-series. k-means is for clustering, not recommendations directly.

Answer

ARIMA_PLUS

Answer

k-means

Answer

AutoML Tables

Question 5

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. Adding more relevant features (option B) increases model complexity and reduces bias. Decreasing the learning rate (A) does not address model complexity; it only affects convergence speed. Removing features with low correlation (C) further reduces complexity, increasing bias. Increasing L2 regularization (D) penalizes large coefficients, which increases bias.

Answer

Decrease the learning rate in the training options

Answer

Remove features that have low correlation with the label

Answer

Increase L2 regularization

Question 6

You are building a real-time fraud detection system using BigQuery streaming and a BQML logistic regression model. The model must be retrained every hour with new labeled data. What is the MOST cost-effective approach to serve predictions with low latency?

Accepted Answer

Export the model to a Cloud Storage bucket and deploy it to AI Platform Prediction. Exporting the model to Cloud Storage and deploying to AI Platform Prediction is the most cost-effective approach because AI Platform Prediction provides managed, autoscaling prediction serving with pay-per-prediction pricing. It avoids the cost and latency of repeatedly querying BigQuery with ML.PREDICT, which consumes slots and is not designed for real-time serving. Option C (Dataflow with model inference) incurs streaming pipeline costs, while options A and B are inefficient due to repeated BigQuery queries or unsupported materialized views with ML.PREDICT.

Answer

Call ML.PREDICT on a BigQuery table that is updated every hour

Answer

Use a BigQuery materialized view that refreshes every minute and apply ML.PREDICT

Answer

Stream data into Pub/Sub and use a Dataflow pipeline with Apache Beam's model inference

Question 7

You need to analyze customer churn and want to understand the rank of each customer's churn probability within their subscription plan. Which BigQuery window function computes the relative ranking from 1 (highest probability) to N?

Accepted Answer

RANK(). RANK() assigns a rank with gaps for ties; DENSE_RANK() assigns consecutive ranks; ROW_NUMBER() assigns unique sequential numbers; NTILE() divides into buckets. For relative ranking with ties, RANK() is typically used for 'rank' meaning.

Answer

DENSE_RANK()

Answer

NTILE(100)

Answer

ROW_NUMBER()

Question 8

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. PIVOT is the standard SQL clause to rotate rows into columns. UNPIVOT rotates columns to rows. ARRAY_AGG and STRUCT are not used for pivoting.

Answer

UNPIVOT

Answer

ARRAY_AGG with CROSS JOIN

Answer

STRUCT

Question 9

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. BI Engine is an in-memory analysis service that accelerates BigQuery queries by caching data in memory. It integrates with Looker and Looker Studio. Cloud Memorystore is a Redis/Memcached cache, not directly for BigQuery. BigQuery BI Engine is the correct service.

Answer

Cloud SQL

Answer

Cloud Bigtable

Answer

Cloud Memorystore

Question 10

You are building a machine learning pipeline for credit risk assessment. The dataset has a severe class imbalance (1% default rate). You want to use AutoML Tables on Vertex AI. Which strategy should you incorporate to handle imbalance?

Accepted Answer

Use the `class_weight` parameter in the AutoML Tables model. AutoML Tables automatically applies class imbalance handling (e.g., class weighting) by default. You can adjust the weight strategy. SMOTE is not directly supported in AutoML Tables; you would need custom training. Downsampling and upsampling are manual steps not needed.

Answer

Downsample the majority class to a 50-50 ratio

Answer

Apply SMOTE in a Dataflow pipeline before training

Answer

Upsample the minority class using BigQuery SQL

Question 11

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 provides a unified data catalog (Universal Catalog) with automated lineage, discovery, and governance across GCP. Data Catalog is the older standalone service; Dataplex is the recommended unified solution. Cloud Composer and Dataflow are orchestration/processing tools.

Answer

Cloud Composer

Answer

Dataflow

Answer

Data Catalog

Question 12

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 13

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. Online serving (with Bigtable as backing store) provides low-latency feature retrieval. Offline serving is for batch predictions. Feature Store supports both; online is for real-time.

Answer

Online serving with Cloud SQL

Answer

Offline serving with BigQuery

Answer

Offline serving with Cloud Storage

Question 14

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 provides an approximate distinct count with low latency. COUNT(DISTINCT) is exact but slower for large data. APPROX_QUANTILES estimates quantiles. HyperLogLog is not a BQ function.

Answer

HyperLogLog++

Answer

COUNT(DISTINCT)

Answer

APPROX_QUANTILES

Question 15

You are migrating a large on-premises data warehouse to BigQuery. The data includes sensitive PII columns that must be masked for certain users. Which BigQuery feature can automatically redact PII in query results based on user roles?

Accepted Answer

Column-level security with data masking. BigQuery column-level security with policy tags and data masking can automatically mask sensitive data based on IAM roles. Authorized views require manual creation. Dynamic data masking is part of column-level security. IAM conditions don't mask data.

Answer

IAM conditions on tables

Answer

Authorized views

Answer

Cloud DLP API