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

Question 1

You are using BigQuery to analyze a dataset that contains a nested and repeated field `items` in a table `orders`. The `items` field is an ARRAY of STRUCTs, each with `product_id` (STRING) and `quantity` (INT64). You need to write a query that returns the total quantity sold for each product across all orders. Which TWO approaches will produce the correct result? (Choose two.)

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

Accepted Answer

SELECT item.product_id, SUM(item.quantity) AS total_quantity FROM `project.dataset.orders` CROSS JOIN UNNEST(items) AS item GROUP BY item.product_id. To aggregate data from a nested repeated field, you must flatten the array using UNNEST. Both the comma cross join and explicit CROSS JOIN with UNNEST achieve this by expanding the array into rows. You must alias the unnested element to reference its fields. Grouping by the product ID and summing quantity then yields the total per product. Other approaches either fail syntactically or incorrectly include orders with no items.

Answer

SELECT items.product_id, SUM(items.quantity) AS total_quantity FROM `project.dataset.orders` GROUP BY items.product_id

Answer

SELECT item.product_id, SUM(item.quantity) AS total_quantity FROM `project.dataset.orders` LEFT JOIN UNNEST(items) AS item ON TRUE GROUP BY item.product_id

Answer

SELECT product_id, SUM(quantity) AS total_quantity FROM `project.dataset.orders`, UNNEST(items) GROUP BY product_id

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 underlying database table and contains its dimensions (columns) and measures (aggregations like total revenue). The view is the fundamental building block that maps a physical table to a logical set of fields, and it is referenced by explores to make those fields queryable.

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 supports a weight column that lets you assign higher importance to specific rows during training. By giving positive examples (the 5% minority class) higher weights, the model's loss function penalizes misclassification of the minority class more heavily, effectively rebalancing the learning signal without altering the dataset. This is the native, supported mechanism in AutoML Tables for handling class imbalance.

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 forecasting, the training set must only contain data that occurs before the evaluation set to prevent data leakage from the future into the past. A sequential split preserves the temporal order, ensuring the model is trained on historical data and evaluated on subsequent, unseen future data. This mimics real-world forecasting where you predict future values based on past observations. Random or stratified splits violate this principle by allowing future data points to influence training, leading to overly optimistic performance estimates.

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(). The RANK() function assigns a rank to each row within a window, and when there are ties, it leaves gaps in the ranking sequence. For example, if two rows tie for rank 1, the next row receives rank 3. This behavior distinguishes it from DENSE_RANK(), which does not leave gaps. RANK() is part of BigQuery's window functions and is used with an OVER clause specifying the partitioning and ordering.

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 tasks support a set of built-in rule types (range, non-null, uniqueness, set, regex, sql_assertion, row_condition), and email format validation is not one of the built-in types. The 'regex' rule type lets you supply a regular expression that each value in the 'email' column must match, so a pattern like ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$ enforces valid email formatting. This is the intended Dataplex mechanism for pattern-based validation.

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 is Google Cloud's managed Jupyter notebook environment integrated with Vertex AI, allowing you to develop and train custom TensorFlow models directly. It provides pre-built containers, GPU support, and seamless integration with Vertex AI Training and Pipelines.

Answer

Cloud Datalab

Answer

Vertex AI Training

Answer

AI Platform Notebooks

Question 10

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 a model_path pointing to a Cloud Storage location containing the SavedModel. This allows batch prediction using ML.PREDICT directly in BigQuery without moving data to Vertex AI.

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 11

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. The BigQuery Lineage API provides programmatic access to lineage information, showing how tables are derived from other tables through queries, including column-level lineage. It captures dependencies from SQL queries, scheduled queries, and other BigQuery operations.

Answer

Cloud Composer

Answer

Cloud Data Catalog

Answer

Dataflow

Question 12

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. APPROX_QUANTILES [CORRECT] is a valid BigQuery approximate aggregation function that returns approximate quantile boundaries for a dataset, letting the team trade a small amount of accuracy for much faster results on large data. APPROX_COUNT_DISTINCT [CORRECT] is also a real BigQuery approximate aggregation function that estimates the number of distinct values using HyperLogLog++ sketches, providing fast, scalable cardinality estimates. The other options are not BigQuery functions: APPROX_SUM, APPROX_AVG, and APPROX_MEDIAN do not exist in BigQuery's function library, so they cannot be used for approximate aggregation.

Answer

APPROX_SUM

Answer

APPROX_AVG

Answer

APPROX_MEDIAN

Question 13

A financial analytics team uses Looker to explore BigQuery data. They need to allow business users to filter by a custom date range that is not tied to an existing dimension. The date range must be user-input at query time. What is the best approach in Looker?

Accepted Answer

Create a parameter in LookML using Liquid templating. Creating a parameter in LookML using Liquid templating allows business users to input a custom date range at query time. Parameters are user-input fields that can be referenced in SQL queries via Liquid, enabling dynamic filtering. This is the most flexible approach for ad-hoc date ranges not tied to existing dimensions.

Answer

Create an explore with a custom filter field in the Looker UI

Answer

Use a filter parameter directly on the date dimension

Answer

Add a dimension with a yesno filter that toggles the date range

Question 14

A data scientist wants to train a custom TensorFlow model on Vertex AI using a managed Jupyter notebook. Which Vertex AI service should they use to set up a notebook environment with pre-installed deep learning frameworks?

Accepted Answer

Vertex AI Workbench. Vertex AI Workbench provides managed Jupyter notebooks with pre-installed deep learning frameworks (TensorFlow, PyTorch, etc.) and easy scaling options. Notebooks on Compute Engine would require manual setup. AI Platform Training is for training jobs, not interactive notebooks. Vertex AI Pipelines is for orchestrating ML workflows.

Answer

Compute Engine with Deep Learning VM

Answer

Vertex AI Training via custom job

Answer

Vertex AI Pipelines

Question 15

A retail company uses BigQuery to store sales data and wants to forecast weekly demand for the next 8 weeks using historical data from the past 2 years. They need to account for seasonality and holidays. Which BigQuery ML model type and configuration is most appropriate?

Accepted Answer

ARIMA_PLUS with holiday_region parameter. ARIMA_PLUS with the holiday_region parameter is the most appropriate BigQuery ML model for forecasting weekly demand with seasonality and holidays. ARIMA_PLUS is specifically designed for time-series forecasting, automatically handles seasonality, trend, and holidays when the holiday_region is specified. It also provides explainability and handles missing data.

Answer

Boosted tree classifier

Answer

Linear regression with engineered time features

Answer

Time-series DECOMPOSE model