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

Question 1

A data engineer wants to train a linear regression model in BigQuery ML to predict sales. The training data includes a categorical feature with 1000+ unique values. Which method is most appropriate to handle this feature in the CREATE MODEL statement?

Accepted Answer

Use TRANSFORM clause with ML.FEATURE_CROSS or manual hashing.. BigQuery ML automatically one-hot encodes categorical features with fewer than a threshold of unique values. For high-cardinality features, you can use TRANSFORM to apply feature engineering like hashing or bucketizing.

Answer

Set max_categorical_features=100 in the model options.

Answer

Use the OPTIONS(ENCODE='ONE_HOT_ENCODING') parameter in the model options.

Answer

The model automatically handles high-cardinality features without any additional steps.

Question 2

You need to create a Looker model that defines a 'sales' view based on a BigQuery table, with a measure for total revenue. Which LookML object defines the table and dimensions?

Accepted Answer

view. In LookML, a view defines the mapping to a database table (or derived table) and contains dimensions and measures.

Answer

explore

Answer

model

Answer

dimension

Question 3

A company uses Looker Studio to build dashboards from BigQuery data. They notice that queries take several seconds to return. They want to improve performance without changing the schema or adding materialized views. Which option should they use?

Accepted Answer

Enable BigQuery BI Engine on the relevant project.. BI Engine accelerates sub-second query response times in Looker Studio by caching data in memory within the BigQuery region.

Answer

Move the data to Cloud SQL.

Answer

Switch to BigQuery Omni for cross-cloud queries.

Answer

Use APPROX_COUNT_DISTINCT to speed up distinct counts.

Question 4

A data scientist is training a binary classification model on an imbalanced dataset (95% negative, 5% positive) using AutoML Tables. Which strategy should they use to handle the class imbalance?

Accepted Answer

Specify a weight column with higher weights for positive examples in the dataset.. AutoML Tables automatically handles class imbalance by applying class weights and downsampling. Users can also specify a weight column explicitly.

Answer

Set the budget to a higher value to allow more training on minority class.

Answer

Use SMOTE in a Dataflow pipeline before importing the data to AutoML Tables.

Answer

Create duplicate copies of the positive class rows to balance the dataset.

Question 5

You need to split a time-series dataset into training and evaluation sets for a forecasting model. The data is ordered by timestamp. Which splitting technique should you use?

Accepted Answer

Sequential split where training data precedes evaluation data in time.. For time-series data, a random split would leak future information into training. A sequential split (earlier data for training, later for evaluation) is required.

Answer

Use k-fold cross-validation with random folds.

Answer

Stratified split based on the target variable.

Answer

Random split with 80% training, 20% evaluation.

Question 6

Which BigQuery SQL function returns the rank of a row within a window, with gaps in the ranking for ties?

Accepted Answer

RANK(). RANK() assigns the same rank to ties and leaves gaps (e.g., 1,1,3). ROW_NUMBER() assigns unique consecutive numbers. DENSE_RANK() does not leave gaps.

Answer

NTILE()

Answer

DENSE_RANK()

Answer

ROW_NUMBER()

Question 7

A company uses Dataplex to manage data quality across multiple BigQuery datasets. They want to define a data quality rule that checks if a column 'email' contains a valid email format. Which Dataplex feature should they use?

Accepted Answer

Create a custom Data Quality rule using the 'regex' type.. Dataplex Data Quality allows predefined rule types including a 'regex' rule for pattern matching. There is no built-in 'email' rule, so a regex check is appropriate.

Answer

Use Cloud DLP to classify and validate emails.

Answer

Use the built-in 'email' rule type in Dataplex.

Answer

Create a Dataflow pipeline to validate emails and write results to a separate table.

Question 8

A data engineer needs to query data across BigQuery (in Google Cloud) and Snowflake (in AWS) without moving the data. Which service should they use?

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data across multiple clouds using BigQuery's interface, with compute running in the respective cloud. Data stays in place.

Answer

Dataflow

Answer

Cloud SQL

Answer

Vertex AI Feature Store

Question 9

You want to train a custom TensorFlow model on Vertex AI using a managed Jupyter notebook environment. Which service should you use?

Accepted Answer

Vertex AI Workbench. Vertex AI Workbench provides managed Jupyter notebooks with pre-installed frameworks and easy access to Vertex AI services.

Answer

Cloud Datalab

Answer

Vertex AI Training

Answer

AI Platform Notebooks

Question 10

A company uses Looker to define business logic in LookML. They need to create a new measure that calculates the average order value, defined as total revenue divided by number of orders. Which LookML syntax should they use?

Accepted Answer

measure: avg_order_value { type: average; sql: ${revenue} / ${order_count} ;; }. Measures in LookML are defined with type and sql expression. The correct syntax for a calculated measure is: measure: avg_order_value { type: average; sql: ${revenue} / ${order_count} ;; }

Answer

measure: avg_order_value { type: sum; sql: ${revenue} / ${order_count} ;; }

Answer

dimension: avg_order_value { type: number; sql: ${revenue} / ${order_count} ;; }

Answer

dimension: avg_order_value { type: average; sql: ${revenue} / ${order_count} ;; }

Question 11

A data scientist wants to import a pre-trained TensorFlow model into BigQuery ML for batch predictions. The model is stored in a Cloud Storage bucket. Which statement is correct?

Accepted Answer

Use CREATE MODEL with model_type='tensorflow' and model_path='gs://bucket/model'.. BigQuery ML supports importing TensorFlow models via CREATE MODEL with model_type='tensorflow' and model_path pointing to the SavedModel directory in Cloud Storage.

Answer

Use CREATE MODEL with model_type='imported_tensorflow' and model_path='gs://bucket/model'.

Answer

First upload the model to Vertex AI Model Registry, then reference it in BigQuery ML.

Answer

Use the ML.IMPORT_MODEL function to load the model into BigQuery.

Question 12

You need to track the lineage of data in BigQuery, showing how tables are derived from other tables via queries. Which service provides this capability?

Accepted Answer

BigQuery Lineage API. BigQuery lineage API and Dataplex lineage both provide data lineage tracking. BigQuery lineage is built-in, while Dataplex extends it across the data lake.

Answer

Cloud Composer

Answer

Cloud Data Catalog

Answer

Dataflow

Question 13

A data engineer needs to build a feature engineering pipeline using Vertex AI Pipelines. The pipeline should preprocess data, train a model, and deploy it. Which two components are required to define the pipeline? (Choose 2)

Accepted Answer

Kubeflow Pipelines SDK. Vertex AI Pipelines uses the Kubeflow Pipelines SDK (or TFX) to define components and compile them into a pipeline. The pipeline is then run on Vertex AI Pipelines.

Answer

Vertex AI Feature Store

Answer

Dataflow

Answer

Cloud Composer

Question 14

A company uses AutoML Tables to train a classification model. They want to improve model performance by engineering new features from existing timestamp columns. Which three techniques can they apply within AutoML Tables? (Choose 3)

Accepted Answer

Create a new column with the difference between two timestamps.. AutoML Tables automatically extracts features from timestamp columns, such as day of week, month, hour, etc. Users can also manually create new columns via the UI or by preprocessing data before import. However, manual SQL functions cannot be used directly within AutoML Tables.

Answer

Manually add a column with a boolean indicating if the timestamp falls on a weekend.

Answer

Apply a SQL UDF in the AutoML Tables training configuration.

Question 15

A data team wants to use Approximate Aggregation Functions in BigQuery to get faster query results. Which two functions can they use? (Choose 2)

Accepted Answer

APPROX_QUANTILES. BigQuery provides APPROX_COUNT_DISTINCT for approximate distinct counts and APPROX_QUANTILES for approximate quantiles. Other approximate functions include APPROX_TOP_COUNT and APPROX_TOP_SUM.

Answer

APPROX_SUM

Answer

APPROX_AVG

Answer

APPROX_MEDIAN