PDE Practice Test 3 — 10 Questions

Question 1

A company wants to use BigQuery to query data stored in Parquet files in Cloud Storage without loading the data into BigQuery. Which BigQuery feature should they use?

Accepted Answer

BigQuery external tables. BigQuery external tables allow querying data stored in Cloud Storage (including Parquet files) directly without loading it into BigQuery storage. This feature uses a federated query engine that reads the data on the fly, supporting formats like Parquet, Avro, ORC, CSV, and JSON. Option C is correct because it directly addresses the requirement to query Parquet files in Cloud Storage without ingestion.

Answer

BigQuery Omni

Answer

BigQuery ML

Answer

BigQuery BI Engine

Question 2

You are optimizing a BigQuery query that scans 1 TB of data every day. The query joins a large fact table (partitioned by date) with a small dimension table. You notice that the query always scans the entire fact table, even though you only need the last 7 days of data. Which optimization will MOST reduce the bytes scanned?

Accepted Answer

Add a WHERE clause that filters on the date column used for partitioning.. BigQuery partitioned tables allow partition pruning, where the query engine skips partitions that do not match the filter condition. Adding a WHERE clause on the partitioning column (date) restricts the scan to only the last 7 days' partitions, drastically reducing bytes scanned. This is the most direct and effective optimization for the described scenario.

Answer

Create a materialized view that pre-aggregates the data by day.

Answer

Cluster the fact table on the join key used in the query.

Answer

Change the table to use time-unit column partitioning with a 1-day partition interval.

Question 3

A data engineer is designing a BigQuery table for a clickstream dataset with frequent queries aggregating over user sessions. Each user session has multiple events, and the engineer wants to avoid joins for performance. Which schema design pattern should they use?

Accepted Answer

Use nested and repeated fields to store events within each session row. BigQuery's nested and repeated fields (using STRUCT and ARRAY) allow you to store multiple events within a single session row, eliminating the need for joins when aggregating over sessions. This denormalized pattern is ideal for clickstream data and improves query performance by keeping related data together.

Answer

Use a normalized schema with separate tables for sessions and events, then join on session ID

Answer

Store each event as a separate row with session key and use clustering on session ID

Answer

Use partitioning on event timestamp and clustering on user ID

Question 4

A Dataproc cluster uses preemptible worker nodes to reduce costs. The cluster runs a long-running Spark job that occasionally experiences worker failures. How should the job be configured to handle preemptible worker failures gracefully?

Accepted Answer

Set spark.task.maxFailures to a high number to allow retries.. Preemptible VMs on Dataproc can be reclaimed at any time, causing executor loss mid-task. Setting spark.task.maxFailures to a higher value (default is 4) allows Spark to retry failed tasks on remaining executors instead of failing the entire job. This is the standard, low-cost way to tolerate preemptible worker churn without abandoning the cost savings.

Answer

Disable preemptible workers for the job.

Answer

Use persistent disks for preemptible workers.

Answer

Enable automatic restart of the Spark driver on failure.

Question 5

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 6

A company wants to automate model retraining and deployment whenever new training data becomes available. Which service should be used to orchestrate the end-to-end workflow?

Accepted Answer

Vertex AI Pipelines. Vertex AI Pipelines is the correct choice because it is a managed service specifically designed to orchestrate and automate end-to-end ML workflows, including model retraining and deployment triggered by new data. It allows you to define pipelines as a directed acyclic graph (DAG) of steps using the Kubeflow Pipelines SDK or pre-built components, and it integrates natively with other Vertex AI services for training, evaluation, and deployment.

Answer

Cloud Build

Answer

Cloud Scheduler

Answer

Cloud Composer

Question 7

A company is planning to migrate a legacy batch ETL pipeline to Google Cloud. The pipeline involves reading from a relational database, transforming data, and writing to a data warehouse. Which three Google Cloud services can be used as the orchestration layer? (Choose three.)

Accepted Answer

Cloud Scheduler. Cloud Scheduler is a fully managed cron job service that can trigger orchestration workflows on a schedule. It is correct because it can initiate batch ETL pipelines by sending HTTP requests to Cloud Run, Cloud Functions, or Pub/Sub, making it a lightweight orchestration trigger for scheduled batch jobs.

Answer

Cloud Dataproc

Answer

Cloud Dataflow

Question 8

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 9

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 10

An organization is using BigQuery for analytics. They have a table that is 500 GB and is frequently queried by 'date' and 'region'. They want to optimize query performance and reduce costs. Which TWO actions should they take?

Accepted Answer

Cluster the table by region. Option E is correct because partitioning the table by date means BigQuery only scans the partitions that match the query's date filter, drastically reducing bytes processed and cost for a 500 GB table frequently filtered by date. Option D is correct because clustering the table by region physically sorts data within each partition by region, so queries filtering on region benefit from block pruning and scan less data. Together, partitioning by date and clustering by region match the two most common query predicates and are the standard BigQuery optimization pattern. Option A (authorized view) only controls access to data and does not improve query performance or reduce scan costs. Option B (wildcard table) is used to query multiple similarly named tables with a UNION-like syntax and does not optimize a single table's scans. Option C (materialized views) can help some workloads but is not the primary optimization for a frequently filtered base table and adds storage and refresh overhead, so it is not one of the two best actions here.

Answer

Use an authorized view

Answer

Use a wildcard table

Answer

Use materialized views