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

Question 1

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?

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

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 2

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 3

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 provides automated data lineage tracking across Google Cloud services, including BigQuery, Dataflow, and Vertex AI. It captures lineage metadata automatically as data flows through ETL/ELT pipelines, enabling end-to-end visibility from source tables to ML training datasets. This is a core capability of Dataplex's data governance and metadata management layer.

Answer

Dataflow

Answer

Data Catalog

Answer

Cloud Composer

Question 4

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. Option A (Clustering on commonly filtered columns) is correct because clustering physically co-locates rows with similar values in the clustered columns, so BigQuery can use block pruning to skip scanning blocks that don't match filter predicates, directly reducing bytes read. Option D (Partitioning on a DATE column) is correct because partitioning splits a table into segments by date, and when queries filter on the partitioning column BigQuery prunes irrelevant partitions and reads only the matching ones, cutting the amount of data scanned. Option B (BI Engine reservation) is not correct here because it is an in-memory analysis acceleration service that speeds up query execution and concurrency but does not reduce the underlying data read by pruning. Option C (Materialized views) can precompute results and improve performance, but they are not a data-read-reduction mechanism in the same direct pruning sense as partitioning/clustering and are not marked correct. Option E (Approximate aggregation functions) reduces computation cost by returning approximate results (e.g., APPROX_COUNT_DISTINCT), but it does not reduce the volume of data read from storage.

Answer

BI Engine reservation

Answer

Materialized views

Answer

Approximate aggregation functions

Question 5

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.. Option A is correct because a time-series model must be evaluated on a holdout set whose timestamps are strictly later than the training data, which simulates real forecasting and prevents look-ahead bias. Option B is correct because time-series cross-validation with expanding windows trains on progressively larger historical periods and validates on subsequent periods, giving a robust estimate of out-of-sample performance. Option D is correct because a chronological split based on a cutoff date ensures all training rows precede all evaluation rows, which is the fundamental requirement for temporal data in BigQuery ML. Option C is incorrect because random splitting mixes past and future observations, causing data leakage and unrealistically optimistic metrics. Option E is incorrect because k-fold cross-validation with random folds also shuffles time order, violating temporal dependencies and producing invalid forecasts.

Answer

Split the data randomly into training and testing sets.

Answer

Use k-fold cross-validation with random folds.

Question 6

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. Option B (Auto Data Quality) is correct because it is a Dataplex feature that automatically generates and applies data quality rules (such as non-null and range/constraint checks) against tables in a lake, letting you validate critical columns without manually authoring every rule. Option D (Data Quality Tasks) is correct because Dataplex data quality tasks let you define and run explicit rule sets—including non-null and custom constraint rules—on lake assets and schedule or trigger them as part of your pipeline. Together these two features directly implement the required null and constraint validations on Dataplex lake data. Option A (Tag Templates) is incorrect because tag templates only define metadata schemas for attaching business/technical tags, not executable quality rules. Option C (Data Catalog) is incorrect because it is a metadata discovery and governance service, not a rule-execution engine for null/constraint validation. Option E (Data Scan) is incorrect because, while Dataplex uses scans to run data quality and profiling, 'Data Scan' is the underlying mechanism rather than the specific feature named for authoring these quality rules, and the question asks for the two features that implement the rules.

Answer

Tag Templates

Answer

Data Catalog

Answer

Data Scan

Question 7

A data engineer needs to train a linear regression model in BigQuery ML using a table with 10 million rows. The model will predict sales based on features like advertising spend, seasonality, and store location. Which SQL statement should they use to create and train the model?

Accepted Answer

CREATE MODEL mymodel OPTIONS(model_type='LINEAR_REG') AS SELECT * FROM sales_data. In BigQuery ML, the CREATE MODEL statement with option MODEL_TYPE='LINEAR_REG' creates a linear regression model. The training data is specified in the AS SELECT clause.

Answer

CREATE MODEL mymodel AS SELECT * FROM sales_data WITH LINEAR REGRESSION

Answer

CREATE OR REPLACE MODEL mymodel OPTIONS(model_type='LINEAR_REGRESSION') AS SELECT * FROM sales_data

Answer

CREATE MODEL mymodel OPTIONS(model_type='linear_reg') AS SELECT * FROM sales_data

Question 8

A data analyst wants to rank products by sales within each category. They need to assign a unique rank to each product, with no gaps in the ranking numbers (i.e., ties should have different ranks). Which window function should they use?

Accepted Answer

ROW_NUMBER(). ROW_NUMBER() assigns a unique sequential integer to each row within a partition, starting at 1, regardless of ties. RANK() would give the same rank to ties and skip numbers, so it would introduce gaps.

Answer

NTILE()

Answer

RANK()

Answer

DENSE_RANK()

Question 9

A data engineer needs to build a LookML model in Looker to define business logic and relationships for a new dataset. They want to create an 'explore' that joins an 'orders' view with a 'customers' view. Where should they define this join?

Accepted Answer

In the model file, within the 'explore' definition, add a 'join' parameter.. In LookML, the explore file (or the explore definition within a model file) is where you define which views to include and how they join together. The view files (*.view.lkml) define the dimension and measure logic for a single table or derived table. The model file (*.model.lkml) ties everything together and defines explores.

Answer

In the Looker admin panel, under 'Joins', create a new join.

Answer

In the 'orders.view.lkml' file, add a 'join' parameter.

Answer

In the 'customers.view.lkml' file, add a 'join' parameter.

Question 10

A company uses Vertex AI Workbench notebooks for data exploration and model development. They want to ensure that the notebook environment can access BigQuery data using the same permissions as the user's Google Cloud account. What is the recommended setup?

Accepted Answer

Create a service account with BigQuery access and attach it to the notebook instance.. The recommended setup is to create a service account with BigQuery access and attach it to the notebook instance. This allows the Vertex AI Workbench notebook to authenticate to BigQuery using the service account's permissions, which can be configured to match the user's Google Cloud account permissions.

Answer

Use a Cloud Functions proxy to authenticate to BigQuery from the notebook.

Answer

Log in to the notebook using the user's Google Cloud credentials via oauth2client.

Answer

Grant the Compute Engine default service account BigQuery access.

Question 11

A data scientist is using AutoML Tables to build a classification model for predicting customer churn. The dataset is highly imbalanced (only 1% churn). Which strategy should they use to handle the class imbalance within AutoML Tables?

Accepted Answer

No action needed; AutoML Tables automatically handles class imbalance by adjusting class weights.. AutoML Tables automatically computes class weights and applies them during training to handle imbalanced data. You do not need to manually apply SMOTE or change the training budget; AutoML Tables handles it out of the box.

Answer

Manually apply SMOTE to the training data before uploading to AutoML Tables.

Answer

Enable the 'enable_class_imbalance_handling' flag during training.

Answer

Set the 'class_weight' parameter in the AutoML Tables training configuration to 'balanced'.

Question 12

A data engineer is planning a time-series forecasting model using BigQuery ML ARIMA+ on a dataset with daily sales data spanning 3 years. Which TWO actions are required to prepare the data for ARIMA+? (Choose 2.)

Accepted Answer

Sort the data by the time column in ascending order.. Option C is correct because BigQuery ML ARIMA_PLUS requires the input time series data to be sorted by the time column in ascending order before training; unsorted data can cause incorrect model fitting or errors. Option D is correct because the time column must be of type DATE or TIMESTAMP (or a type convertible to TIMESTAMP) for ARIMA_PLUS to recognize and process the temporal ordering. Option A is not required: partitioning is a performance optimization, not a prerequisite for ARIMA_PLUS training. Option B is not required as a separate action because NULL time values are invalid for the time column and must be handled, but the question asks for required preparation actions specific to ARIMA_PLUS, and the canonical requirements are sorting and correct time data type. Option E is incorrect because one-hot encoding applies to categorical features, not to the numeric target variable used in time-series forecasting.

Answer

Create a partition on the time column to improve performance.

Answer

Remove any rows with NULL values in the time column.

Answer

Encode the target variable using one-hot encoding.

Question 13

A company wants to track data lineage for their BigQuery tables to understand how data flows from source to derived tables. Which TWO Google Cloud services can be used to capture and visualize data lineage? (Choose 2.)

Accepted Answer

Dataplex Data Lineage. Dataplex provides a comprehensive Data Lineage feature that automatically captures lineage for BigQuery jobs. Additionally, the BigQuery Lineage API (part of Data Catalog) allows you to retrieve lineage programmatically.

Answer

Vertex AI Feature Store

Answer

Cloud Composer

Answer

Cloud Data Fusion

Question 14

A company wants to train a machine learning model to predict customer churn using BigQuery ML. The dataset has a severe class imbalance (only 2% churn). Which approach should the data engineer take to handle this imbalance within BigQuery ML?

Accepted Answer

Set the CLASS_WEIGHTS option to 'balanced' in the CREATE MODEL statement. BigQuery ML supports the CLASS_WEIGHTS option in the CREATE MODEL statement, which allows you to assign weights to classes to handle imbalanced datasets. Setting it to 'balanced' automatically adjusts weights inversely proportional to class frequencies, effectively giving more weight to the minority class during training. This is the native, efficient way to handle class imbalance within BigQuery ML without external tools or data manipulation.

Answer

Use SMOTE directly in BigQuery SQL before training

Answer

Create a custom Vertex AI model using TensorFlow and use the class_weight parameter

Answer

Oversample the minority class using a SQL query that duplicates rows

Question 15

A company uses BigQuery Omni to query data stored in AWS S3. They need to join this data with data in BigQuery (GCP). The dataset in AWS is large (10 TB) and frequently updated. Which approach minimizes data movement and cost?

Accepted Answer

Use BigQuery Omni cross-cloud join with the query processed in AWS region. BigQuery Omni cross-cloud join processes the query in the AWS region where the S3 data resides, joining it with BigQuery (GCP) data by moving only the smaller result set or by pushing computation to the AWS side. This minimizes data movement of the 10 TB frequently updated dataset and reduces egress costs, since the large dataset stays in place.

Answer

Create a materialized view in BigQuery that includes the AWS data

Answer

Copy the AWS data into BigQuery storage using a scheduled transfer, then join

Answer

Use a federated query from BigQuery to read the AWS data directly and join in BigQuery