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

Question 1

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 class weights via the CLASS_WEIGHTS option in CREATE MODEL, which adjusts the loss function to penalize misclassifications of the minority class more heavily.

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 2

A data engineer needs to design a data pipeline that ingests streaming data from Cloud Pub/Sub, performs real-time aggregations, and loads the results into BigQuery for dashboarding. Which Google Cloud service should they use for the streaming aggregation step?

Accepted Answer

Dataflow. Dataflow is a fully managed service for stream and batch processing that integrates with Pub/Sub and BigQuery. It supports exactly-once processing and low-latency streaming.

Answer

Cloud Functions

Answer

Cloud Dataproc

Answer

Cloud Composer

Question 3

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 supports cross-cloud joins by running the query on the cloud where the data resides, using the cross-cloud join feature to pull only the necessary rows from the other cloud.

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

Question 4

A data engineer is building a Looker dashboard that requires a calculated field to compute the running total of sales per day per store. Which Looker Studio function should they use?

Accepted Answer

RUNNING_SUM(). Looker Studio's RUNNING_SUM function computes a running total within a group, exactly what is needed for a running total per store partitioned by date.

Answer

RANK()

Answer

TOTAL()

Answer

PERCENTILE()

Question 5

A company wants to use AutoML Tables to build a classification model on a dataset with 100 features and 500,000 rows. They need to deploy the model for online predictions with low latency (<100 ms). Which deployment option should they choose?

Accepted Answer

Deploy the model to an endpoint in Vertex AI using the AutoML endpoint service. AutoML Tables supports online prediction endpoints that are deployed on a dedicated cluster, providing low latency for real-time predictions.

Answer

Export the model as a TF SavedModel and deploy on Cloud Run

Answer

Deploy the model on AI Platform Prediction

Answer

Use batch prediction in Vertex AI

Question 6

A data engineer needs to implement data lineage tracking for a BigQuery data warehouse. They want to automatically capture column-level lineage from ETL jobs run by Dataform and from manual SQL queries executed in the BigQuery console. Which approach meets these requirements?

Accepted Answer

Use BigQuery's built-in column-level lineage, which automatically tracks lineage for all queries, and query it using INFORMATION_SCHEMA.JOBS. BigQuery's column-level lineage is automatically captured for all queries (including Dataform and console) and can be queried via the INFORMATION_SCHEMA.JOBS_BY_USER views. Dataplex lineage requires integration but BigQuery lineage is native.

Answer

Enable Dataplex Universal Catalog and use the Dataplex Lineage API to capture lineage from both sources

Answer

Use the BigQuery Lineage API provided by Data Catalog to register lineage manually

Answer

Export Dataform execution logs to Cloud Logging and use a custom script to extract lineage

Question 7

A company is using Looker to explore their BigQuery data. They have defined a LookML model with an 'explore' that joins two views: 'orders' and 'customers'. The join is a left join. They want to ensure that only customers with orders are shown when exploring. Which LookML parameter should they modify?

Accepted Answer

Change the join type from 'left_outer' to 'inner'. The 'join_type' parameter in LookML defines the join type. Changing it to 'inner' will exclude customers without orders.

Answer

Set the 'required_joins' parameter on the 'orders' view

Answer

Use a derived table with a WHERE clause

Answer

Add a filter in the 'customers' view to exclude nulls

Question 8

A data engineer is building a feature store for ML models using Vertex AI Feature Store. The features are computed daily from BigQuery and need to be available for both online predictions (low latency) and offline training. Which two actions must the engineer take? (Choose TWO)

Accepted Answer

Create an entity type in the feature store. Vertex AI Feature Store requires creating an entity type and a featurestore (which serves online and offline). Features are ingested via Dataflow or batch jobs.

Answer

Deploy a custom TensorFlow model on Vertex AI

Answer

Use Cloud SQL to store feature metadata

Answer

Create a BigQuery table for offline serving

Question 9

An e-commerce company uses BigQuery to analyze customer behavior. They need to compute the number of distinct customers per day, approximate quantiles of purchase amounts, and assign a row number per customer partition by date. Which BigQuery SQL functions should they use? (Choose THREE)

Accepted Answer

APPROX_COUNT_DISTINCT(customer_id). APPROX_COUNT_DISTINCT for approximate distinct counts, APPROX_QUANTILES for approximate quantiles, ROW_NUMBER for row numbering within partitions.

Answer

NTILE(4) OVER (ORDER BY purchase_amount)

Answer

COUNT(DISTINCT customer_id)

Question 10

A company uses Dataplex to manage data quality across multiple BigQuery datasets. They need to define data quality rules that check for null values in critical columns and enforce uniqueness constraints. Which two Dataplex features should they use? (Choose TWO)

Accepted Answer

Dataplex Data Quality Rules. Dataplex Data Quality can define rules (including null check and uniqueness) and schedule them as Data Quality Tasks. Data Quality Rules are defined in YAML and can be attached to entities.

Answer

Dataplex Lake

Answer

Dataplex Data Lineage

Answer

Dataplex Universal Catalog

Question 11

A data engineer is preparing a dataset for ML training in Vertex AI. The dataset includes a timestamp column, a categorical column with high cardinality (1000 distinct values), and a numerical column with outliers. Which two preprocessing steps should they apply? (Choose TWO)

Accepted Answer

Label encode the categorical column. One-hot encoding for high cardinality may be too sparse; label encoding (ordinal encoder) is more common. Winsorizing clips outliers.

Answer

Drop the timestamp column

Answer

Normalize the numerical column using Z-score

Answer

One-hot encode the categorical column

Question 12

A company wants to use BigQuery ML to train a time-series forecasting model on historical sales data. The data is recorded daily for 3 years. They need to evaluate model accuracy using time-series aware cross-validation. Which two options should they configure in the CREATE MODEL statement? (Choose TWO)

Accepted Answer

Specify a time_series_timestamp_col and time_series_data_col. For ARIMA+ models, you can set 'horizon' (forecast length) and 'data_frequency' (auto-detect or set). Cross-validation is not built-in for ARIMA; instead, you evaluate on held-out periods.

Answer

Set the data_frequency parameter to 'daily'

Answer

Use the 'num_trials' parameter for hyperparameter tuning

Answer

Set the 'split_method' to 'time_series'

Question 13

A data engineer needs to build a real-time dashboard in Looker Studio that displays live sales data from BigQuery. The dashboard must refresh every minute. The underlying BigQuery table is updated continuously via streaming inserts. Which two approaches can reduce query cost and latency? (Choose TWO)

Accepted Answer

Use BigQuery BI Engine to accelerate the Looker Studio queries. BI Engine can accelerate queries in Looker Studio by caching data in memory. Partitioning the table on the date column reduces the amount of data scanned.

Answer

Use the BigQuery Data Transfer Service to copy data to a separate dataset

Answer

Create a materialized view that pre-aggregates the data

Answer

Schedule a script to export the table to Cloud Storage and load into Cloud SQL

Question 14

A company stores sensitive customer data in BigQuery. They need to implement column-level security to restrict access to personally identifiable information (PII) columns. Which two BigQuery features can they use together? (Choose TWO)

Accepted Answer

Authorized views. Column-level security can be achieved via policy tags (with Data Catalog) and then authorized views that filter columns based on user roles. Policy tags enforce access control at the column level.

Answer

BigQuery row-level security

Answer

BigQuery foreign key constraints

Answer

BigQuery data masking

Question 15

A data engineer is using Vertex AI Workbench to develop a custom ML model. They want to store and version datasets, track experiments, and register models. Which three Vertex AI services should they use? (Choose THREE)

Accepted Answer

Vertex AI Model Registry. Vertex AI Dataset stores and manages datasets. Vertex AI Experiments tracks ML experiments. Vertex AI Model Registry stores and versions trained models.

Answer

Vertex AI Feature Store

Answer

Vertex AI Matching Engine