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

Question 1

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 2

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 3

Your team uses Looker to develop a model on top of BigQuery. The data is partitioned by ingestion time, and analysts frequently query the last 7 days. However, Looker queries are scanning the entire table, causing high costs. Which two changes should you implement? (Pick two) Wait, this is multiple_choice. Pick one best approach.

Accepted Answer

Apply a LookML access_filter to dynamically filter on the partition column.. The single best approach is to apply a partition filter requirement in LookML and enable partition pruning in BigQuery. Other options are not directly about Looker or are suboptimal.

Answer

Enable BI Engine on the BigQuery table to accelerate queries.

Answer

Create a materialized view that aggregates data daily and point Looker to that view.

Answer

Use clustering on the order_date column to improve query performance.

Question 4

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 5

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 6

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 7

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 8

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 9

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 10

You are building a binary classification model using AutoML Tables on Vertex AI. The dataset has a severe class imbalance (1% positive class). Which strategy should you use to handle the imbalance?

Accepted Answer

Use the class_weight parameter in AutoML Tables training.. AutoML Tables automatically applies class weighting and sampling to handle imbalance; the best practice is to let AutoML handle it. Manually resampling may not be optimal.

Answer

Oversample the minority class using SMOTE before training.

Answer

Do nothing; AutoML Tables automatically handles class imbalance.

Answer

Downsample the majority class to match the minority class size.

Question 11

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 12

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 13

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 14

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.

Question 15

You need to implement data quality rules on a Dataplex lake to ensure that critical columns are not null and meet certain constraints. Which two Dataplex features can you use? (Choose TWO)

Accepted Answer

Auto Data Quality. Dataplex Data Quality Tasks allow you to define and run data quality checks. Dataplex Auto Data Quality automates profiling and monitoring. The other options are not specific to quality: Catalog is for discovery, Scans for security, and Tag Templates for metadata.

Answer

Tag Templates

Answer

Data Catalog

Answer

Data Scan