PDE Practice Test 13 — 15 Questions

Question 1

A data scientist wants to use Vertex AI Workbench for exploratory data analysis. Which TWO statements are true about Vertex AI Workbench?

Accepted Answer

It supports custom container images for the notebook environment.. Vertex AI Workbench provides managed Jupyter notebooks with pre-installed ML frameworks. It integrates with BigQuery via the Python client. It does not provide a SQL editor; that's BigQuery. It supports custom containers. It is not serverless; it runs on Compute Engine VMs.

Answer

It is a serverless service that scales to zero when not in use.

Answer

It can only be used with TensorFlow.

Answer

It includes a built-in SQL query editor for BigQuery.

Question 2

You want to query data across Google Cloud and AWS using a single SQL interface without moving data. Which TWO services can you use?

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data in AWS (and Azure) using BigQuery SQL. BigQuery Omni runs on multi-cloud. BigQuery itself is GCP-only. Cloud Spanner and Data Fusion are not for multi-cloud SQL queries across clouds.

Answer

BigQuery Data Transfer Service

Answer

Cloud Spanner

Answer

Cloud Data Fusion

Question 3

You are building a multi-cloud analytics solution to join data from Google Cloud and AWS S3. You need to query the S3 data using BigQuery without moving it. Which Google Cloud service should you use?

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data stored in AWS S3 and Azure Blob Storage using BigQuery SQL, without data movement. BigQuery Omni runs in the cloud provider's region.

Answer

Looker

Answer

Dataproc

Answer

BigQuery Data Transfer Service

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. Partitioning and clustering both reduce data scanned by narrowing the data read. BI Engine caches data but does not reduce scan size. Materialized views may reduce scans but the question asks for features, not views. Approximate aggregation reduces computation but not data read.

Answer

BI Engine reservation

Answer

Materialized views

Answer

Approximate aggregation functions

Question 5

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 6

You have a BigQuery table 'events' with a TIMESTAMP column 'event_time'. You need to compute, for each event, the difference in seconds from the previous event of the same user. Which window function should you use?

Accepted Answer

LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time). LAG() allows accessing the previous row in a partition. Combined with TIMESTAMP_DIFF, you can compute the difference. LEAD() accesses next row. ROW_NUMBER() and FIRST_VALUE() are not suitable.

Answer

FIRST_VALUE(event_time) OVER (PARTITION BY user_id ORDER BY event_time)

Answer

LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time)

Answer

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time)

Question 7

A data engineer needs to transfer 500 TB of on-premises data to Google Cloud Storage. The data is stored on NAS devices and the network bandwidth is limited to 100 Mbps. What is the most cost-effective and timely transfer method?

Accepted Answer

Use Transfer Appliance. At 100 Mbps, transferring 500 TB over the network would take over 500 days. Transfer Appliance is designed for petabyte-scale offline transfer, shipping a physical appliance to your data center. Other options are not feasible due to bandwidth constraints.

Answer

Use Storage Transfer Service over the internet

Answer

Use a VPN connection and rsync

Answer

Use gsutil cp in parallel

Question 8

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 9

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 10

You are loading 10 GB of daily CSV files from a GCS bucket into a BigQuery table. The files contain some malformed rows that you want to skip. Which BigQuery load configuration should you use?

Accepted Answer

Use the 'max_bad_records' option set to a value like 10.. BigQuery allows setting max_bad_records in load jobs; records exceeding this threshold cause the job to fail. Setting max_bad_records to a value greater than 0 allows the load to succeed while skipping malformed rows.

Answer

Use the 'skip_leading_rows' option.

Answer

Use the 'ignore_unknown_values' option.

Answer

Use the 'allow_jagged_rows' option.

Question 11

You are migrating an on-premises Kafka cluster to Google Cloud. The cluster has 50 topics with a total throughput of 200 MB/s. You want to minimize operational overhead. Which approach is the most cost-effective?

Accepted Answer

Use Dataproc to run Kafka on a managed cluster. Managed Kafka services are not available natively on GCP; the recommended approach is to run Kafka on Dataproc, which provides a managed Hadoop/Spark environment with autoscaling.

Answer

Use Pub/Sub with Kafka-compatible client libraries

Answer

Deploy Kafka on Compute Engine instances with a managed instance group

Answer

Use Cloud NAT to route traffic to an on-premises Kafka cluster

Question 12

A company wants to use Dataprep to clean and transform raw CSV files stored in Cloud Storage before loading into BigQuery. The data quality checks show missing values and inconsistent date formats. Which Dataprep feature should they use to handle these issues?

Accepted Answer

Recipe steps. Recipe steps allow chaining transformations like fill missing values and format dates. Data quality profiling identifies issues but doesn't fix them. Scheduling automates execution. Wrangler is the UI, not a specific feature for transformations.

Answer

Data quality profiling

Answer

Scheduling

Answer

Wrangler

Question 13

You are designing a data pipeline that processes streaming events with late-arriving data (up to 2 hours late). The pipeline must compute hourly aggregations and emit results as soon as possible, but must also accurately update results when late data arrives. You want to minimize overall processing cost. Which Dataflow windowing and trigger configuration should you use?

Accepted Answer

Fixed windows of 1 hour with allowed lateness of 2 hours and trigger every 5 minutes (early) and on watermark (late) with accumulating fired panes. Session windows are ideal for capturing bursts of user activity but not for fixed hourly aggregations. The best approach is to use fixed windows with allowed lateness of 2 hours and triggering early every N minutes (e.g., 5 minutes) and also on watermark advancement. This provides early results while allowing late data to update the window. Using accumulating and discarding late panes (or just accumulating) depends on the use case; but here, accumulating fired panes is typical for correctness.

Answer

Global window with triggers every 5 minutes

Answer

Sliding windows of 1 hour with 30-minute offset

Answer

Session windows with 10-minute gap duration

Question 14

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 15

You need to run a one-time data transformation job on a small CSV file (100 MB) using a visual, code-free interface. Which Google Cloud service is designed for this?

Accepted Answer

Dataprep. Dataprep (Trifacta) is a visual data wrangling tool for exploring and transforming data without code. It's ideal for ad-hoc, small to medium datasets.

Answer

Dataflow

Answer

Cloud Data Fusion

Answer

Dataproc