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

Question 1

You need to build a Looker model that joins multiple tables from BigQuery. Which LookML object defines the relationship between tables?

Accepted Answer

EXPLORE. In LookML, an EXPLORE defines the starting point and joins to other views. A VIEW defines a single table or derived table. A MODEL contains explores and views. A JOIN is not a top-level object; joins are defined inside explores.

Answer

JOIN

Answer

VIEW

Answer

MODEL

Question 2

A data scientist wants to use Vertex AI Workbench for exploratory data analysis. Which TWO statements are true about Vertex AI Workbench?

Accepted Answer

It supports custom container images for the notebook environment.. Vertex AI Workbench provides managed Jupyter notebooks with pre-installed ML frameworks. It integrates with BigQuery via the Python client. It does not provide a SQL editor; that's BigQuery. It supports custom containers. It is not serverless; it runs on Compute Engine VMs.

Answer

It is a serverless service that scales to zero when not in use.

Answer

It can only be used with TensorFlow.

Answer

It includes a built-in SQL query editor for BigQuery.

Question 3

You want to query data across Google Cloud and AWS using a single SQL interface without moving data. Which TWO services can you use?

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data in AWS (and Azure) using BigQuery SQL. BigQuery Omni runs on multi-cloud. BigQuery itself is GCP-only. Cloud Spanner and Data Fusion are not for multi-cloud SQL queries across clouds.

Answer

BigQuery Data Transfer Service

Answer

Cloud Spanner

Answer

Cloud Data Fusion

Question 4

You are building a forecasting model to predict daily sales for the next 90 days using historical sales data with clear seasonality and trend. You want to use BigQuery ML with minimal manual tuning. Which model type should you choose?

Accepted Answer

ARIMA_PLUS. ARIMA_PLUS is specifically designed for time-series forecasting and automatically handles seasonality, trend, and holiday effects without manual tuning. ARIMA is less automated; linear regression would require manual feature engineering for time components.

Answer

ARIMA

Answer

Boosted tree (XGBoost)

Answer

Linear regression

Question 5

You have a BigQuery table 'orders' with columns order_id, customer_id, order_amount, and order_date. You need to rank customers by total spend per month, assigning the rank 1 to the highest spender. Which SQL function should you use in a window clause?

Accepted Answer

RANK(). RANK() assigns a rank with gaps for ties; for top-spender ranking, that is appropriate. DENSE_RANK() also works but without gaps; the stem does not specify. ROW_NUMBER() gives unique numbers even for ties. However, typical ranking with ties uses RANK().

Answer

NTILE()

Answer

DENSE_RANK()

Answer

ROW_NUMBER()

Question 6

You are building a multi-cloud analytics solution to join data from Google Cloud and AWS S3. You need to query the S3 data using BigQuery without moving it. Which Google Cloud service should you use?

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data stored in AWS S3 and Azure Blob Storage using BigQuery SQL, without data movement. BigQuery Omni runs in the cloud provider's region.

Answer

Looker

Answer

Dataproc

Answer

BigQuery Data Transfer Service

Question 7

You need to preprocess tabular data for training a classification model using Vertex AI. The dataset has missing values in numerical columns and categorical columns with high cardinality. Which Vertex AI service provides automated feature engineering and preprocessing as part of the pipeline?

Accepted Answer

Vertex AI Pipelines. Vertex AI Pipelines allows you to build ML pipelines with components for feature engineering, including handling missing values and encoding. Vertex AI Feature Store is for serving features, not preprocessing.

Answer

AutoML Tables

Answer

Vertex AI Feature Store

Answer

Vertex AI Workbench

Question 8

A retailer wants to use machine learning to predict customer churn based on transaction history and demographic data. The dataset has 500 features, many of which are correlated. The data is highly imbalanced: only 2% churn. They need to deploy a model that provides feature importance and is interpretable. Which model type should they use in BigQuery ML?

Accepted Answer

Boosted tree classifier. Boosted tree models (like XGBoost) handle imbalanced data well, provide feature importance, and offer interpretability. Deep Neural Networks are less interpretable. Logistic regression is interpretable but may not capture complex patterns. AutoML Tables is powerful but less interpretable and may cost more.

Answer

Logistic regression

Answer

AutoML Tables model

Answer

Deep Neural Network (DNN) classifier

Question 9

Your team uses Looker Studio to build dashboards on top of BigQuery. The dashboards are slow when filtering on a high-cardinality dimension (e.g., user ID). You want to improve performance without changing the underlying BigQuery table design. Which action should you take?

Accepted Answer

Enable BigQuery BI Engine on the project.. BI Engine automatically caches the BigQuery tables in memory for Looker Studio, accelerating queries on any dimension. Enabling BI Engine on the project will improve dashboard performance.

Answer

Create a clustered table on the user ID column.

Answer

Apply a filter to limit data to the last month only.

Answer

Use Looker Studio's extract data functionality.

Question 10

You are using BigQuery ML to train a matrix factorization model for a recommendation system. The training data consists of user-item interactions. You notice that the model is overfitting. Which of the following hyperparameter changes would most likely reduce overfitting?

Accepted Answer

Increase w_reg (regularization weight) from 0.1 to 0.5. Increasing the L2 regularization weight (w_reg) penalizes large weights and reduces overfitting. Increasing number of factors (num_factors) increases model complexity, worsening overfitting. Decreasing learning rate may help but not as directly as regularization.

Answer

Decrease w_reg (regularization weight) from 0.1 to 0.01

Answer

Increase num_factors from 10 to 20

Answer

Increase num_training_iterations from 10 to 20

Question 11

You need to track data lineage from a BigQuery table through a series of transformations and into a Vertex AI model training pipeline. Which Google Cloud service provides automated data lineage tracking?

Accepted Answer

Dataplex. Dataplex includes data lineage tracking that captures metadata about data movement and transformations across BigQuery, Vertex AI, and other services. Data Catalog provides metadata but not automated lineage.

Answer

Dataflow

Answer

Data Catalog

Answer

Cloud Composer

Question 12

You have a BigQuery table 'events' with a TIMESTAMP column 'event_time'. You need to compute, for each event, the difference in seconds from the previous event of the same user. Which window function should you use?

Accepted Answer

LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time). LAG() allows accessing the previous row in a partition. Combined with TIMESTAMP_DIFF, you can compute the difference. LEAD() accesses next row. ROW_NUMBER() and FIRST_VALUE() are not suitable.

Answer

FIRST_VALUE(event_time) OVER (PARTITION BY user_id ORDER BY event_time)

Answer

LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time)

Answer

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time)

Question 13

You are using Looker to model data from BigQuery. You have a dimension that should be filtered by a user attribute (e.g., user's region). Which LookML concept allows you to apply dynamic row-level security based on user attributes?

Accepted Answer

Access filter. Access filters in LookML allow dynamic filtering based on user attributes from the authentication system. Required access grants are for permissions, not dynamic filtering.

Answer

Custom field

Answer

Derived table

Answer

Required access grant

Question 14

You need to select two BigQuery features that improve query performance by reducing the amount of data read. Which two options accomplish this? (Choose TWO)

Accepted Answer

Clustering on commonly filtered columns. Partitioning and clustering both reduce data scanned by narrowing the data read. BI Engine caches data but does not reduce scan size. Materialized views may reduce scans but the question asks for features, not views. Approximate aggregation reduces computation but not data read.

Answer

BI Engine reservation

Answer

Materialized views

Answer

Approximate aggregation functions

Question 15

You are building a time-series forecasting model with BigQuery ML. Which three steps should you perform to properly split the data and evaluate the model? (Choose THREE)

Accepted Answer

Evaluate on a holdout set that is later in time than the training set.. For time-series, you must maintain temporal order: split chronologically (not randomly), use a cutoff date for training/validation, and evaluate on unseen future data. Cross-validation should be time-series aware (e.g., expanding window). Random split is invalid for time-series. Using a single train/test split may be insufficient; multiple windows are better.

Answer

Split the data randomly into training and testing sets.

Answer

Use k-fold cross-validation with random folds.