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

Question 1

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 2

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 3

A company uses BigQuery and wants to reduce query costs by using BI Engine for Looker Studio dashboards. The data is stored in a BigQuery dataset with 5 TB of frequently accessed tables. The dashboards run dozens of concurrent queries. What is the recommended approach to enable BI Engine acceleration?

Accepted Answer

Create a reservation in the Administration panel and assign it to the project.. BI Engine is a reserved capacity service that caches data in memory. You must reserve capacity (amount of memory) for a specific BigQuery region, and then BI Engine automatically accelerates queries from Looker Studio and other BI tools. It does not require you to grant specific IAM roles to users (they just need BigQuery permissions) or to create materialized views. You do not need to enable it per dataset; it works at the project/region level.

Answer

Enable BI Engine by setting the dataset option 'enable_bi_engine=TRUE' in the dataset metadata.

Answer

Grant all Looker Studio users the 'biengine.user' IAM role on the project.

Answer

Create materialized views of the tables and connect Looker Studio to the views.

Question 4

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 5

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.. Vertex AI Workbench notebooks can use user-managed notebooks with user credentials. By setting the 'User-managed notebook' type and using the 'Add service account' option, you can grant the notebook instance a service account with appropriate BigQuery permissions. Alternatively, you can use the built-in 'Use the same identity as the user' option which uses the user's credentials via OAuth. However, the recommended approach for production is to use a service account for consistent 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 6

Which BigQuery SQL function can be used to get an approximate count of distinct values in a large column faster than COUNT(DISTINCT) with lower accuracy?

Accepted Answer

APPROX_COUNT_DISTINCT. APPROX_COUNT_DISTINCT is a HyperLogLog++ based function that provides an approximate distinct count with standard error of ~1.6%, and is much faster on large datasets.

Answer

APPROX_QUANTILES

Answer

COUNT(DISTINCT)

Answer

DISTINCT_COUNT

Question 7

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 8

An organization wants to integrate BigQuery Omni to query data stored in AWS S3. They have set up the necessary connections. What is the primary benefit of using BigQuery Omni over simply copying the data to BigQuery?

Accepted Answer

Ability to use BigQuery ML models on data in S3 without moving data.. BigQuery Omni allows you to query data across clouds without moving it, providing a unified analytics experience. It reduces data egress costs and avoids duplication.

Answer

Automatic encryption of data at rest in S3.

Answer

Lower latency queries due to in-memory caching.

Answer

Support for real-time streaming inserts into S3.

Question 9

A company uses Dataplex to manage data lakes on Google Cloud. They want to enforce data quality rules on a BigQuery table, such as ensuring that a 'email' column is not null and matches a regex pattern. Which Dataplex feature should they use?

Accepted Answer

Dataplex Data Quality. Dataplex Data Quality is a feature that allows you to define and run data quality checks on BigQuery tables. You can create a Data Quality Task using the Dataplex UI or API to specify rules like NOT_NULL and REGEX.

Answer

Dataplex Universal Catalog

Answer

Dataplex Lake

Answer

Dataplex Data Lineage

Question 10

A machine learning engineer needs to deploy a custom TensorFlow model for online predictions with low latency. The model is already trained and saved in SavedModel format. Which Vertex AI service should they use?

Accepted Answer

Vertex AI Prediction. Vertex AI Prediction allows you to deploy custom models (including TensorFlow SavedModel) to an endpoint for online predictions. It supports autoscaling and low-latency serving.

Answer

Vertex AI Workbench

Answer

Vertex AI Feature Store

Answer

Vertex AI AutoML

Question 11

Which BigQuery function can be used to retrieve the value of a column from the previous row within a partition, ordered by a timestamp?

Accepted Answer

LAG(). LAG() is a window function that returns the value of a column from a row that is a specified number of rows before the current row within the partition. LEAD() retrieves from a following row.

Answer

FIRST_VALUE()

Answer

ROW_NUMBER()

Answer

LEAD()

Question 12

A company wants to use BigQuery's PIVOT operator to transform their sales data. They have a table with columns: 'year', 'quarter', 'revenue'. They want to create a report where each row is a year and each column is a quarter (Q1, Q2, Q3, Q4) showing revenue. Which SQL statement is correct?

Accepted Answer

SELECT * FROM sales PIVOT(SUM(revenue) FOR quarter IN ('Q1','Q2','Q3','Q4')). PIVOT in BigQuery requires specifying an aggregate function, the pivot column, and the list of pivot column values. The syntax is: SELECT * FROM (SELECT year, quarter, revenue FROM sales) PIVOT(SUM(revenue) FOR quarter IN ('Q1','Q2','Q3','Q4')).

Answer

SELECT * FROM sales PIVOT(revenue FOR quarter IN (Q1, Q2, Q3, Q4))

Answer

PIVOT sales ON quarter USING SUM(revenue)

Answer

SELECT * FROM (SELECT year, quarter, revenue FROM sales) PIVOT(SUM(revenue) FOR quarter IN (Q1, Q2, Q3, Q4))

Question 13

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.. ARIMA+ requires a time column and a numeric target column. The time column must be in a date/timestamp format. Additionally, the data should be sorted by time. Missing values should be handled (e.g., filled with 0 or interpolated) but that's not a requirement of the function itself.

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 14

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 15

A data scientist needs to perform feature engineering for a machine learning model using Vertex AI. They want to preprocess data using a pipeline that includes scaling, one-hot encoding, and handling missing values. Which TWO services can they use to define and execute this preprocessing pipeline? (Choose 2.)

Accepted Answer

Vertex AI Pipelines. Vertex AI Pipelines is the recommended service for building and running ML pipelines, including preprocessing steps. Alternatively, you can use BigQuery SQL for feature engineering directly on the data, then export the processed data for training. Cloud Dataflow is an option for batch/streaming data processing but is not specific to ML pipelines. Cloud Functions and Dataproc are less suitable for this purpose.

Answer

Cloud Dataproc

Answer

Cloud Dataflow

Answer

Cloud Functions