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

Question 1

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 2

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')). BigQuery's PIVOT operator requires an aggregate function applied to the value column, followed by FOR IN (). Option A correctly uses SUM(revenue) as the aggregate, FOR quarter to specify the pivot column, and IN ('Q1','Q2','Q3','Q4') with quoted string literals. The remaining columns (year) automatically become the grouping keys, producing one row per year with Q1–Q4 as columns.

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 3

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

Question 4

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 5

A data engineer is building a Looker Studio 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 6

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 models are natively served through Vertex AI endpoints, which provide a managed, low-latency online prediction service. Deploying to a Vertex AI endpoint using the AutoML endpoint service preserves the AutoML model format and gives the sub-100 ms latency required for online 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 7

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 Model Registry (A) is correct because it provides a central repository to manage the lifecycle of ML models, including versioning, tracking lineage, and deploying models to endpoints, which directly satisfies the requirement to register models. Vertex AI Dataset (B) is correct because it lets the engineer create managed, versioned datasets from tabular, image, text, or video data, fulfilling the need to store and version datasets. Vertex AI Experiments (E) is correct because it records and compares experiment runs, including parameters, metrics, and artifacts, which satisfies the requirement to track experiments. Vertex AI Feature Store (C) is not appropriate here because it is designed for serving and sharing online/offline feature values, not for dataset versioning, experiment tracking, or model registration. Vertex AI Matching Engine (D) is also not appropriate because it is a vector similarity search service for embeddings, unrelated to the requested dataset, experiment, and model management tasks.

Answer

Vertex AI Feature Store

Answer

Vertex AI Matching Engine

Question 8

Your team stores raw JSON event logs in Cloud Storage. Analysts need to run ad-hoc SQL queries on this data with minimal setup and cost, but they also require the ability to join it with existing BigQuery tables. Which approach should you recommend?

Accepted Answer

Create an external table in BigQuery that points to the Cloud Storage JSON files, and query it using standard SQL.. Querying external data in Cloud Storage via BigQuery external tables provides a serverless, cost-effective way to run ad-hoc SQL without loading data. It supports joins with native BigQuery tables, fulfilling the requirement. Other options involve data movement or unsuitable tools, adding cost and complexity.

Answer

Load the JSON files into a new BigQuery table using the bq load command with --autodetect, then query the table directly.

Answer

Use Cloud Dataflow to read the JSON files, transform them, and write the results to BigQuery for querying.

Answer

Import the JSON files into Google Sheets and use the BigQuery data connector to join with BigQuery tables.

Question 9

Your company ingests streaming data into BigQuery using the Storage Write API. You need to ensure that duplicate records are not inserted when the streaming job retries due to transient errors. Which feature should you use?

Accepted Answer

Use the Storage Write API's exactly-once semantics by setting a stream name and offset.. The Storage Write API's exactly-once semantics, enabled by specifying a stream name and offsets, guarantee that records are not duplicated even if the writer retries. This is the only option that provides native deduplication for streaming inserts. Other methods either do not apply to the Storage Write API or are not supported in BigQuery.

Answer

Create a unique constraint on the destination table to reject duplicate rows.

Answer

Enable BigQuery's streaming inserts with insertId to deduplicate records.

Answer

Use a MERGE statement to upsert records after each streaming batch.

Question 10

Your analytics team queries a large BigQuery table `events` that is partitioned by `event_date` and clustered by `user_id`. A new dashboard runs a query filtering on `user_id = 'abc123'` but does not include a filter on `event_date`. You want to minimize the bytes scanned by this query. What should you do?

Accepted Answer

Add a filter on `event_date` to the query, such as `event_date >= '2024-01-01'`.. The table is partitioned by `event_date`; without a filter on that column, BigQuery cannot prune partitions and scans the entire table. Adding a date filter enables partition pruning, reducing bytes scanned. Clustering on `user_id` helps only within the scanned partitions, so it does not replace the need for a partition filter.

Answer

Use `SELECT *` instead of selecting specific columns.

Answer

Add a `LIMIT` clause to the query, such as `LIMIT 1000`.

Answer

Create a clustered table on `user_id` instead of `event_date`.

Question 11

You need to load a 500 GB CSV file from Cloud Storage into BigQuery. The file has a header row and uses comma delimiters. You want to load it as quickly as possible without transforming the data. Which approach should you use?

Accepted Answer

Use the `bq load` command with `--source_format=CSV` and `--skip_leading_rows=1`.. A direct load job using `bq load` with CSV format and skipping the header is the fastest and most straightforward way to load a large CSV into BigQuery without transformation. It uses BigQuery's native loading capabilities, which are optimized for bulk data.

Answer

Create an external table pointing to the CSV and then run `CREATE TABLE AS SELECT * FROM external_table`.

Answer

Use `bq query` with an `EXTERNAL_QUERY` function to read the CSV from Cloud Storage.

Answer

Use the `gcloud storage cp` command to copy the CSV into BigQuery.

Question 12

You need to load a large CSV file from Cloud Storage into BigQuery. The file contains a header row and is comma-delimited. You want to ensure that the header row is skipped and that the schema is automatically detected. Which BigQuery load option should you use?

Accepted Answer

Set skip_leading_rows to 1 and autodetect to true.. To load a CSV with a header row and automatically detect the schema, you should use skip_leading_rows=1 to skip the header and autodetect=true to infer the schema. These options are part of the load job configuration in BigQuery. They streamline the loading process and reduce manual schema definition.

Answer

Set allow_jagged_rows to true and ignore_unknown_values to true.

Answer

Set field_delimiter to '	' and skip_leading_rows to 0.

Answer

Set max_bad_records to 1 and autodetect to false.

Question 13

You have a BigQuery table 'logs' with a column 'timestamp' of type TIMESTAMP. You need to create a new table that contains only the logs from the last 7 days, partitioned by day on the 'timestamp' column. Which SQL statement should you use?

Accepted Answer

CREATE TABLE new_logs PARTITION BY DATE(timestamp) AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY). To create a partitioned table from a query, you must use the CREATE TABLE ... PARTITION BY clause followed by the partitioning expression and then AS SELECT. Partitioning by DATE(timestamp) is correct for daily partitions. The WHERE clause should filter on the timestamp column using TIMESTAMP_SUB to include all records from the last 7 days, not just those from the last 7 calendar days.

Answer

CREATE TABLE new_logs PARTITION BY DATE(timestamp) AS SELECT * FROM logs WHERE DATE(timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

Answer

CREATE TABLE new_logs PARTITION BY timestamp AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

Answer

CREATE TABLE new_logs AS SELECT * FROM logs WHERE timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

Question 14

You have a BigQuery table `orders` with columns `order_id` (STRING), `customer_id` (STRING), `order_date` (DATE), and `amount` (NUMERIC). You need to create a view that shows, for each order, the cumulative sum of `amount` for that customer, ordered by `order_date` ascending. Which SQL window function or clause should you use?

Accepted Answer

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). To compute a cumulative sum per customer ordered by date, you need a window function with PARTITION BY customer_id and ORDER BY order_date, and an explicit frame that includes all preceding rows up to the current row. The ROWS frame ensures that each row's sum includes only rows up to that point, avoiding grouping of same-date orders. This yields a true running total for each order.

Answer

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Answer

SUM(amount) OVER (ORDER BY order_date PARTITION BY customer_id)

Answer

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) WITH TIES

Question 15

You are building a data pipeline that ingests JSON files from Cloud Storage into BigQuery. The JSON files contain deeply nested arrays and objects. You need to load these files with minimal transformation so that analysts can query individual nested fields using dot notation and also unnest arrays when needed. Which approach should you use?

Accepted Answer

Load the JSON files into BigQuery with a schema that defines nested fields as RECORD and arrays as REPEATED, allowing queries to reference nested fields with dot notation and use UNNEST for arrays.. BigQuery supports semi-structured data through nested and repeated fields. Defining nested objects as RECORD and arrays as REPEATED preserves the original structure, enabling dot notation for nested fields and UNNEST for arrays. This avoids flattening during load, aligning with minimal transformation and providing flexible query capabilities.

Answer

First load the JSON files into a Cloud SQL PostgreSQL instance, then use federated queries to access the data from BigQuery.

Answer

Convert the JSON files to CSV using a Dataflow pipeline that flattens all arrays, then load the CSV into BigQuery.

Answer

Load the JSON files into BigQuery using the autodetect schema option, which will automatically flatten all nested structures into separate columns.